I can't fetch cursor in python

Question

I am using postgresql function and calling from python. but I can't fetch cursor.

this is my postgresql function.

  CREATE LANGUAGE plpgsql;
        create or replace FUNCTION getPerson() returns REFCURSOR
           AS $$
        DECLARE
        p_cursor refcursor;   
        BEGIN
           open p_cursor FOR
           select m.name AS name, m.surname AS surname from person m;
           RETURN p_cursor;
        END; $$
        LANGUAGE plpgsql;

this is my python function

 import psycopg2    
    try:
       conn = psycopg2.connect(database="dt", user = "us", password = "pass", host = "127.0.0.1", port = "5432")

       cur = conn.cursor()
       res = cur.callproc('getPerson')
       result = cur.fetchall()

       for row in result:
           print "printed row"
           print row   

       conn.commit()
       cur.close()
       conn.close()  

    except:     
        print "error"

RESULT:

printed row
('<unnamed portal 1>',)

You need to make additional step: create yet another cursor for fetch all from "<unnamed portal 1>" (use name returned by function) and then fetch your data from it. — Abelisto
– Abelisto, Commented Sep 17, 2017 at 22:48

Wiil · Accepted Answer · 2017-10-12 01:22:42Z

1

As Abelisto mentionned, when calling a procedure, you need to get cursor's name first. With psycopg2 it's:

res = cur.callproc('getPerson')
row = cur.fetchone()

then proceed with another FETCH query:

cur.execute(f'FETCH ALL IN "{row[0]}"') #Note: python 3.6
results = cur.fetchall()

Full code:

import psycopg2    
try:
   conn = psycopg2.connect(database="dt", user = "us", password = "pass", host = "127.0.0.1", port = "5432")
   cur = conn.cursor()

   res = cur.callproc('getPerson')
   row = cur.fetchone()

   cur.execute(f'FETCH ALL IN "{row[0]}"')
   results = cur.fetchall()

   for row in results:
       print row   

   cur.close()

except (Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
        conn.close()
        print('Database connection closed.')

answered Oct 12, 2017 at 1:22

Wiil

6593 gold badges8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I can't fetch cursor in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related