ObjectNotExecutableError when executing any SQL query using AsyncEngine

Question

I'm using async_engine. When I try to execute anything:

async with self.async_engine.connect() as con:
    query = "SELECT id, name FROM item LIMIT 50;"
    result = await con.execute(f"{query}")

I'm getting:

Exception has occurred: ObjectNotExecutableError
Not an executable object: 'SELECT id, name FROM item LIMIT 50;'

---

This question was asked before by user @stilmaniac but it is now deleted from SO.

I found it in Google Search cache, here is copy.

I have the same issue so I'm reasking it, but the original version is below:

---

I'm trying to create tables from metadata as follows:

Base = declarative_base()

properties = Table(
    'properties', Base.metadata,
    # ...
    Column('geolocation', Geography(geometry_type='POINT', srid=4326)),
    # ... 
)

engine = create_async_engine("postgresql+asyncpg://user:password@postgres/")
async with engine.begin() as conn:
    await conn.run_sync(Base.metadata.create_all)

Gives me the following error:

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: 'CREATE INDEX "idx_properties_geolocation" ON "properties" USING GIST ("geolocation")'

Considering this doc

Versions:

• OS: macOS 11.4 ARM
• SQLAlchemy: 1.4.22
• Python: 3.6

Answer 1

As the exception message suggests, the str 'SELECT id, name FROM item LIMIT 50;' is not an executable object. To make it executable, wrap it with sqlalchemy.text.

from sqlalchemy import text

async with self.async_engine.connect() as con:
    query = "SELECT id, name FROM item LIMIT 50;"
    result = await con.execute(text(query))

async.connection.execute requires that its statement argument

[...] is always an object that is in both the ClauseElement and Executable hierarchies, including:

• Select

• Insert, Update, Delete

• TextClause and TextualSelect

• DDL and objects which inherit from DDLElement

The synchronous connection.execute method permits raw strings, but this is deprecated in v1.4 and has been removed in SQLAlchemy 2.0.

Answer 2

Revert back to older version (install a specific version of SQLAlchemy using below command):

pip install SQLAlchemy==1.4.17

Confirm the version once:

pip show sqlalchemy

Re-run your script. It works for me.

Answer 3

This issue is related to below thread: https://github.com/pandas-dev/pandas/issues/51061

Workaround: To avoid this bug, I just reverted the version of SQLAlchemy back to older than 2.0 :

1. Firstly, Confirm if your SQLAlchmey version is 2.0 or beyond. To do so, execute: pip show sqlalchemy

Verify current version from output: Name: SQLAlchemy Version: 2.0.9

Summary: Database Abstraction Library Home-page: https://www.sqlalchemy.org Author: Mike Bayer Author-email: mike_mp@zzzcomputing.com License: MIT Location: /Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages Requires: typing-extensions Required-by: pandasql sguharay@s-guharay-n60cm month_end_stats % pip install SQLAlchemy== --use-deprecated=legacy-resolver

2. Revert back to older version(install a specific version of SQLAlchemy using below command):

pip install SQLAlchemy==1.4.17

Confirm the version once:
pip show sqlalchemy

Rerun your script. It works for me.

Thanks, let me know if this helped you.

Answer 4

It is due to sqlalchemy treats query as string, below workaround is working

from sqlalchemy import text
query = 'insert into table_name values(1,2,3)'
 with pool.connect() as db_conn:
     db_conn.execute(text(q))