postgresql return multirow table that is not 'PANDAS' freindly

Question

I am migrating to postgresql from mysql, and having problem with the output from postgresql function. in mysql it returns tuples of data, but postgresql it returns list of strings to simulate issue:

CREATE TABLE public.dummy
(
    id1 integer,
    id2 integer,
    label text COLLATE pg_catalog."default"
)

INSERT INTO public.dummy(id1, id2, label) VALUES (1, 2, 'a');
INSERT INTO public.dummy(id1, id2, label) VALUES (2, 3, 'b');
INSERT INTO public.dummy(id1, id2, label) VALUES (3, 4, 'c');

CREATE FUNCTION public."test"(IN id integer)
    RETURNS TABLE 
    (id1  integer,
     id2 integer,
     label text)
    LANGUAGE 'sql'

AS $BODY$
SELECT  * from dummy;
$BODY$;

using psycopg2 and pandas to create a simple connection

sqlconn="postgresql+psycopg2://" + user + ":" + password + "@"+ host + ":"+str(port)+"/" + db 
engine = sqlalchemy.create_engine(sqlconn,encoding='utf8')
conn=engine.connect()
result=conn.execute('select test(1)')
resultset=result.fetchall()

this will yield

[('(1,2,a)',), ('(2,3,b)',), ('(3,4,c)',)]

to convert to pandas

pd.DataFrame(resultset)

yields

         0
0  (1,2,a)
1  (2,3,b)
2  (3,4,c)

what i wanted is output like

pd.read_sql('select * from dummy',conn)


id1  id2 label
0    1    2     a
1    2    3     b
2    3    4     c

I can't use read_sql method all the time as many functions are far too complex to put into a string, i never had this issue when i was using MySQL/MariaDB Anyone has face this issue?

score 0 · Answer 1 · answered Oct 12 '19 at 16:29

You want to select * from test(1) not select test(1). When you select test(1), it may only return one column (because that is all you selected) so it packages each table row into a single object of type RECORD, rather than the expanded multi-column format.

postgresql return multirow table that is not 'PANDAS' freindly

1 Answers1