pandas read_sql_query using scoped session

Question

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.

Answer 1

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.

Reusing connections

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())

Answer 2

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.