Reputation: 79
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
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