[FIXED] Can only get data from read_sql if the query is executed first

Issue

I'm trying to read data into a DataFrame using read_sql and a SQLAlchemy query object. I'm getting zero rows returned, even though there's data in the database. Further, if I run the query with query.all(), then read_sql returns data.

For a concrete example

query = session.query(SomeSqlAlchemyModel)
# query.all()  # If I uncomment this line, I get data back. Without it I get an empty DataFrame.
df = pd.read_sql(query.statement, session.connection())
assert not df.empty

I'm logging out the SQL statements, and I've stepped into the SQLAlchemy code and from what I can tell, the exact same query is being executed in both scenarios.

How can I get read_sql to return data without running query.all() first?

Solution

I found the problem—I was inserting data into the database using the same session, and I hadn't called flush yet. The query.all() statement implicitly called a flush for me, persisting the data first.

Answered By - Kris Harper