CODEWITHSUNDEEP
pandassqlalchemy
AZhao
AZhao

Reputation: 14405

Convert sqlalchemy ORM query object to sql query for Pandas DataFrame

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.

Upvotes: 6

Views: 6260

Answers (3)

brddawg
brddawg

Reputation: 452

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.

Upvotes: 2

joris
joris

Reputation: 139132

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.

Upvotes: 4

AZhao
AZhao

Reputation: 14405

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)

Upvotes: 1

Related Questions