How to get Returned Query Object of PostgreSql Function in Python?

Question

With pgsql function SelRec(integer) as defined below:

-- select record
CREATE OR REPLACE FUNCTION SelRec(_sno integer)
  RETURNS SETOF app_for_leave AS
$BODY$
    BEGIN
    RETURN QUERY
        SELECT * FROM "app_for_leave" WHERE "sno"=_sno;
    END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

I tried to call this function inside my python code, and expected the returned result as query object which furthermore I could get an individual value inside the query object.

import psycopg2

connection_string = 'postgresql://postgres:111111@localhost:5432/myDB'

conn = psycopg2.connect(connection_string)
cursor = conn.cursor()

obj = cursor.execute("select * from SelRec(1)");

conn.commit()

print obj # None

Yet, the result is None as if there is no data stored, but it does exist like below:

 sno | eid | ename |     sd     |     ed     | sid | status 
-----+-----+-------+------------+------------+-----+--------
   1 | 101 |       | 2013-04-04 | 2013-04-04 |   2 | f

My need is the obj return an query object which I would further call DataSet = obj.first(), and DataSet.status, ect for each individual value.

Any suggestion how I can achieve that? Thanks.

Thanks, I got it, @jmunsch, the object return as turple, wondering if it is possible to access each value by column name instead of index 0,1,2.. Can we archive that just like using sqlalchemy? Thanks. — Houy Narun
– Houy Narun, Commented Sep 16, 2018 at 4:54

jmunsch · Accepted Answer · 2018-09-16 04:29:03Z

1

I think it might be expected behavior.

From the docs http://initd.org/psycopg/docs/cursor.html#cursor.execute

The method returns None. If a query was executed, the returned values can be retrieved using fetch*() methods.

Try something like:

print(dir(obj), type(obj))
print(cursor.fetchall())

edited Sep 16, 2018 at 4:29

answered Sep 16, 2018 at 4:03

jmunsch

24.3k12 gold badges102 silver badges120 bronze badges

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

1 Comment

Houy Narun Over a year ago

Thanks, after I ran command obj.fetchall(), I got an exception AttributeError: 'NoneType' object has no attribute 'fetchall'. I'm pretty sure my function ` SelRec(1)` return a result when I run it in pgSql select * from SelRec(1).

Bharti Mohane · Accepted Answer · 2018-09-16 07:47:57Z

0

I think you should try query given below. As you want to return set of rows.

select * from SelRec(1) as result (sno int, eid int, ename varchar, sd data_type, ed data_type, sid int, status boolean)

answered Sep 16, 2018 at 7:47

Bharti Mohane

7067 silver badges20 bronze badges

12 Comments

Houy Narun Over a year ago

Thanks so much, does this statement run in Postgresql? I think I need it executed in python code, :)

Bharti Mohane Over a year ago

If this return set of rows in postgresql then I think it should run in python also with fetchall

Houy Narun Over a year ago

Also get error running in postgresql, ERROR: a column definition list is only allowed for functions returning "record"

Bharti Mohane Over a year ago

Does * in select query means all the column stated in as result section??

Houy Narun Over a year ago

Meaning it is not possible I could achieve that expectation. Understand cursor.execute(query_string) always return a tuple initd.org/psycopg/docs/cursor.html#fetch . Tone of thanks for your help on this :)

|

Collectives™ on Stack Overflow

How to get Returned Query Object of PostgreSql Function in Python?

2 Answers 2

1 Comment

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related