Is it possible to call pandas.read_sql_query using the alchemy session ( https://stackoverflow.com/a/33648514)? example:
with db_session(connection) as db:
df = pd.read_sql(sql_query, db, index_col=None, coerce_float...)
So far I have received the following error:
AttributeError: 'scoped_session' object has no attribute 'cursor'
However I beleive it is good practice to use sessions instead of just reading using a sql alchemy connection/engine.
Use the SQLAlchemy session by passing session.connection().
with db_session(connection_url) as session:
# session.execute('INSERT INTO ...')
df = pd.read_sql(sql_query, session.connection())
A possible use case, as shown in the commented code above, is to read uncommitted changes.
A more reasonable way to use SQLAlchemy sessions is reusing connections from engine pool.
Define once:
engine = create_engine(connection_url)
Session = scoped_session(sessionmaker(bind=engine))
Call any number of times:
with Session() as session:
# session.execute('INSERT INTO ...')
df = pd.read_sql(sql_query, session.connection())
It is possible to call pandas.read_sql_query using the SQLAlchemy session.
In your example, db is the session (sqlalchemy.orm.session.Session). So just use db.bind:
dp = pd.read_sql_query(sql_query, db.bind, ...)
But it will be the same as passing the engine used to create the session.
