CODEWITHSUNDEEP
pythonsqlpostgresql
user2772836
user2772836

Reputation: 79

I can't fetch cursor in python

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>',)

Upvotes: 2

Views: 1127

Answers (1)

Wiil
Wiil

Reputation: 649

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.')

Upvotes: 1

Related Questions