Getting Pandas read_sql working with SQLAlchemy session ContextManager and ORM

Question

I'm using SQLAlchemy's ORM to construct queries with a ContextManager to manage the session. It's been suggested that you can get Pandas' read_sql working with a Query object by read_sql(sql=q.statement, con=q.session.bind) however this doesn't work within a with block.

For example, the following setup results in an UnboundExecutionError. I think the session is somehow not bound.

@contextmanager
def db_session():
    session = Session()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()

class MyTable(Base):
    __tablename__ = 'MyTables'
    __table_args__ = {'schema': 'MyDatabase.dbo'}

    A = Column(Integer)
    B = Column(String(100, 'SQL_Latin1_General_CP1_CI_AS'))

with db_session() as s:
    q = s.query(table.A, table.B).filter(table.A >= 10)
    q = q.yield_per(1000).enable_eagerloads(False)
    results = pd.read_sql(sql=q.statement, con=q.session.bind)

pd.DataFrame(results, columns=['A', 'B'])


sqlalchemy.exc.UnboundExecutionError: This session is not bound to a single Engine or Connection, and no context was provided to locate a binding.

score 1 · Answer 1 · answered Aug 14 '23 at 18:19

If you are using current versions of SQLAlchemy and pandas then you can avoid many of the niggling details like declaring a context manager explicitly and extracting the .statement. In fact, your code can be as simple as this:

from pandas import read_sql_query
from sqlalchemy import Column, create_engine, Integer, select, String
from sqlalchemy.orm import declarative_base, Session

engine = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")
Base = declarative_base()


class MyTable(Base):
    __tablename__ = "my_table"
    id = Column(Integer, primary_key=True)
    txt = Column(String(100, "SQL_Latin1_General_CP1_CI_AS"))


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)


# create example data
with Session(engine) as sess:
    sess.add(MyTable(txt="Hello world!"))
    sess.commit()

# TASK: perform the query
df = read_sql_query(select(MyTable), engine)
print(df)
"""
   id           txt
0   1  Hello world!
"""

Thanks. The reason I'm using a `ContextManager` is because I have many different databases and like the convenience of it binding the appropriate connection automatically, based on the query. Obviously, it's not doing it right now. When I just do `q.all()`, instead of the `pd.read_sql()` above, it works fine. — siki, Aug 15 '23 at 15:37

Getting Pandas read_sql working with SQLAlchemy session ContextManager and ORM

1 Answers1