Parsing result from sqlalchemy stored procedure execution

Question

I have a stored procedure in a postgresql database.

I'm trying to use the function within a python flask app with sqlalchemy. That query looks like this:

from sqlalchemy import func

appts = db.session.execute(func.getopenappointments(current_user.id))

for appt in appts:
    # work with each appt

The result from this query is an object of type sqlalchemy.engine.result.ResultProxy. Each iteration of that object looks like this:

('(2,"2017-09-15 10:00:00",6,cleaning,available,5)',)

The problem is I am used to referring to the columns with something like:

for appt in appts:
    print(appt.id)

But this fails due to id not existing. What I have realized is the output is pretty much a string that I have to parse with python split() just to get the values I need. How I can keep this a stored procedure but be able to refer to the output by columns, or at least as a tuple and not a regular string?

Answer 1

Take a look at this question. There is a construct called from_statement that can be used to interpret the results of a SQL statement as an SQLAlchemy ORM model. So I'm assuming that you have an Appointment class that is an ORM mapper, either because you used declarative_base or because you used the mapper function directly. Then you can do something like

appts = db.session.query(Appointment).from_statement(func.getopenappointments(current_user.id))

That will run your SQL stored procedure and interpret the result if it is a set of Appointment objects.