CODEWITHSUNDEEP
pythonpostgresqlsqlalchemypsycopg2
vipin
vipin

Reputation: 2510

How to Fetch Data from cursor in Postgres with Flask, SQLAlchemy or psycopg2

I am using Flask and make procedure on Postgres database like 

CREATE OR REPLACE FUNCTION "public"."   "()
  RETURNS "pg_catalog"."refcursor" AS $BODY$
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR select posts.*, users.username, users.name from posts left join users on posts.user_id = users.id;
    RETURN ref;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

and In my code with SQLAlchemy

connection.execute("SELECT home_data()").fetchall()

it returns cursor name home_data as "unnamed portal 1"

and with psycopg2 

conn = psycopg2.connect(host="localhost",database="xxxx", user="xxxx", password="xxxx")

def home_pro():
        cur = conn.cursor()
        return cur.callproc('home_data')

and this code returns none.

Please help me how can i get data from my procedure, I search this on the internet but didn't get anything.

Upvotes: 3

Views: 2090

Answers (1)

vipin
vipin

Reputation: 2510

I get answer,

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

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

Upvotes: 3

Related Questions