1

According to SQLAlchemy documentation you are supposed to use Session object when executing SQL statements. But using a Session with Pandas .read_sql, gives an error: AttributeError 'Session' object has no attribute 'cursor'. However using the Connection object works even with the ORM Mapped Class:

with ENGINE.connect() as conn:
        df = pd.read_sql_query(
            sqlalchemy.select(MeterValue),
            conn
        )

Where MeterValue is a Mapped Class.

This doesn't feel like the correct solution, because SQLAlchemy documentation says you are not supposed to use engine connection with ORM. I just can't find out why. Does anyone know if there is any issue using the connection instead of Session with ORM Mapped Class? What is the correct way to read sql in to a DataFrame using SQLAlchemy ORM?

I found a couple of old answers on this where you use the engine directly as the second argument, or use session.bind and so on. Nothing works.

Improve this question

1 Answer 1

Reset to default
2

Just reading the documentation of pandas.read_sql_query:

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None)

Parameters:

  • sql: str SQL query or SQLAlchemy Selectable (select or text object) SQL query to be executed.
  • con: SQLAlchemy connectable, str, or sqlite3 connection Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.
  • ...

So pandas does allow a SQLAlchemy Selectable (e.g. select(MeterValue)) and a SQLAlchemy connectable (e.g. engine.connect()), so your code block is correct and pandas will handle the querying correctly.

with ENGINE.connect() as conn:
    df = pd.read_sql_query(
        sqlalchemy.select(MeterValue),
        conn,
    )
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Yes, and "use the engine directly as the second argument" works just fine, too. Example here.
Yes, but my issue is that the SQLAlchemy documentation says it should be: ``` with Session(ENGINE) as session: df = pd.read_sql_query( sqlalchemy.select(MeterValue), session, ) ``` But that does not work. So the question is actually whether there is any issue with the engine.connect()?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.