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?

I think that is the key problem. The record is not formatted how I would expect it to be with typical sqlalchemy results. But this is the result I get from executing a stored procedure. — Casey
– Casey, Commented Sep 15, 2017 at 3:19
Do you know the data types of each "field" of your result. What type is "cleaning" and "available"? Strings? — Laurent LAPORTE
– Laurent LAPORTE, Commented Sep 15, 2017 at 3:26
What DB are you using? How is getopenappointments defined? Include a proper minimal reproducible example. — Ilja Everilä
– Ilja Everilä, Commented Sep 15, 2017 at 8:06
Thank you for the feedback! I updated the question with more detail. — Casey
– Casey, Commented Sep 15, 2017 at 19:24

Sam Hartman · Accepted Answer · 2017-09-18 19:36:57Z

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.

edited Sep 18, 2017 at 19:36

answered Sep 17, 2017 at 19:34

Sam Hartman

6,5393 gold badges27 silver badges45 bronze badges

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

1 Comment

Sam Hartman Over a year ago

@Ilja Everilä link fixed, sorry 'bout that

Collectives™ on Stack Overflow

Parsing result from sqlalchemy stored procedure execution

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related