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

Question

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?

Kris Harper · Accepted Answer · 2022-03-04 18:04:09Z

1

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 Mar 4, 2022 at 18:04

Kris Harper

5,9229 gold badges56 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related