[FIXED] SQLAlchemy v1.4 ObjectNotExecutableError when executing any SQL query using AsyncEngine

Issue

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

Solution

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 and marked for removal in SQLAlchemy 2.0.

Answered By - snakecharmerb