CODEWITHSUNDEEP
pythonpandassqlalchemyorm
Ali Al-Alak
Ali Al-Alak

Reputation: 35

How to convert SQL Query to Pandas DataFrame using SQLAlchemy ORM?

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.

Upvotes: 1

Views: 3530

Answers (1)

ljmc
ljmc

Reputation: 5264

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

Upvotes: 2

Related Questions