Convert sqlalchemy ORM query object to sql query for Pandas DataFrame

Question

This question feels fiendishly simple but I haven't been able to find an answer.

I have an ORM query object, say

query_obj = session.query(Class1).join(Class2).filter(Class2.attr == 'state')

I can read it into a dataframe like so:

testdf = pd.read_sql(query_obj.statement, query_obj.session.bind)

But what I really want to do is use a traditional SQL query instead of the ORM:

with engine.connect() as connection:
    # Execute the query against the database
    results = connection.execute(query_obj)
    # Fetch all the results of the query
    fetchall = results.fetchall()
    # Build a DataFrame with the results
    dataframe = pd.DataFrame(fetchall)

Where query is a traditional SQL string. Now when I run this I get an error along the lines of "query_obj is not executable" Anyone know how to convert the ORM query to a traditional query? Also how does one get the columns in after getting the dataframe?

Context why I'm doing this: I've set up an ORM layer on top of my database and am using it to query data into a Pandas DataFrame. It works, but it's frequently maxing out my memory. I want to cut my in-memory overhead with some string folding (pass 3 outlined here: http://www.mobify.com/blog/sqlalchemy-memory-magic/). That requires (and correct me if I'm wrong here) not using the read_sql string and instead processing the query's return as raw tuples.

What is exactly your question? Just how to put the results of a fetchall into a dataframe? Further, what is not working about the code above? It seems fine to me. — joris
– joris, Commented Aug 5, 2015 at 13:13
For the column names, you can get that from fetchall.keys() — joris
– joris, Commented Aug 5, 2015 at 13:14
I made an edit to clarify my question. I'm just not clear on how to convert the ORM query_obj into a SQL string. — AZhao
– AZhao, Commented Aug 5, 2015 at 13:19
See the docs here: sqlalchemy.readthedocs.org/en/latest/faq/… — joris
– joris, Commented Aug 5, 2015 at 13:33

joris · Accepted Answer · 2015-08-05 13:36:10Z

4

The long version is described in detail in the FAQ of sqlalchemy: http://sqlalchemy.readthedocs.org/en/latest/faq/sqlexpressions.html#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined

The short version is:

statement = query.statement
print(statement.compile(engine))

The result of this can be used in read_sql.

answered Aug 5, 2015 at 13:36

joris

140k37 gold badges258 silver badges207 bronze badges

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

2 Comments

runningwild Over a year ago

The link is no longer valid.

runningwild Over a year ago

The new location seems to be here: docs.sqlalchemy.org/en/14/faq/….

brddawg · Accepted Answer · 2019-01-21 17:05:08Z

2

this may be a later version of sqlalchemy since the post.

print(query)

outputs the query you can copy and paste back into your script.

answered Jan 21, 2019 at 17:05

brddawg

4528 silver badges20 bronze badges

Comments

AZhao · Accepted Answer · 2015-08-05 13:35:52Z

1

Fiendishly simple indeed. Per Jori's link to the docs, it just query_obj.statement to get the SQL query. So my code is:

with engine.connect() as connection:
    # Execute the query against the database
    results = connection.execute(query_obj.statement)
    # Fetch all the results of the query
    fetchall = results.fetchall()
    # Build a DataFrame with the results
    dataframe = pd.DataFrame(fetchall)

answered Aug 5, 2015 at 13:35

AZhao

14.5k8 gold badges33 silver badges55 bronze badges

Collectives™ on Stack Overflow

Convert sqlalchemy ORM query object to sql query for Pandas DataFrame

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related