How to return a readable result from Postgres function by SQLAlchemy?

Question

I'm calling a Postgres function from Flask-SQLAlchemy. The function returns a table with 2 values: string and number:

CREATE OR REPLACE FUNCTION public.test (
  id varchar
)
RETURNS TABLE (
  project_name varchar,
  projects_num integer
) AS
$body$
DECLARE 
BEGIN    
...
RETURN QUERY (SELECT project.name as project_name, project_nums as projects_num);   
END;
$body$
LANGUAGE 'plpgsql'

On the Flask side I'm doing the following call:

engine = create_engine(url)
connection = engine.connect()
result = connection.execute('SELECT "test"(\'123\')').fetchone()

Then I'm trying to get data from result.

for row in result:
    print(literal_eval(row))

This throws the exception because actually row has a string:

(my_project, 3)

And of course, it doesn't know what the heck is my_project. How to make it return results correctly or handle them correctly? For example it could return ('my_project', 3)

I solved this problem added parentheses

('"' || account_object.valid_status || '"')

But it looks awful and can't believe there is no normal way to solve? Another problem with Null - it just isn't included in the tuple: (,0) - how is supposed to handle it?

Ilja Everilä · Accepted Answer · 2018-12-07 07:43:54Z

2

The string value is the representation of a Postgresql composite (row) value, and as such is clearly not meant to be evaluated as a Python literal. Psycopg2 can handle and adapt composite type values to Python values, but that requires using a created type. In your case the simple solution is to select from the table valued function, instead of using it as a select list item:

result = connection.execute('SELECT * FROM "test"(\'123\')').fetchone()
print(result.project_name, result.projects_num)

answered Dec 7, 2018 at 7:43

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

1 Comment

chaami Mar 27 at 21:42

I believe that you now need to import sqlalchemy.text and wrap the execute SQL statement in the function.

Collectives™ on Stack Overflow

How to return a readable result from Postgres function by SQLAlchemy?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related