SQLAlchemy ORM conversion to pandas DataFrame

python pandas sqlalchemy flask-sqlalchemy

Below should work in most cases:

df = pd.read_sql(query.statement, query.session.bind)

See pandas.read_sql documentation for more information on the parameters.

python pandas sqlalchemy flask-sqlalchemy

Just to make this more clear for novice pandas programmers, here is a concrete example,

pd.read_sql(session.query(Complaint).filter(Complaint.id == 2).statement,session.bind)

Here we select a complaint from complaints table (sqlalchemy model is Complaint) with id = 2

python pandas sqlalchemy flask-sqlalchemy

The selected solution didn't work for me, as I kept getting the error

AttributeError: 'AnnotatedSelect' object has no attribute 'lower'

I found the following worked:

df = pd.read_sql_query(query.statement, engine)

CodeHunter