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

@ReedJones f"{}" gives the same result as str(), it wasn't the cause. — Karol Zlot
– Karol Zlot, Commented Oct 12, 2021 at 12:31

snakecharmerb · Accepted Answer · 2023-02-05 09:14:56Z

158

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.

edited Feb 5, 2023 at 9:14

answered Oct 8, 2021 at 5:44

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Stanbridge Over a year ago

very grateful for this answer - I genuinely thought I was going mad...

Friedrich · Accepted Answer · 2023-10-02 13:51:04Z

11

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(query))

edited Oct 2, 2023 at 13:51

Friedrich

5,45816 gold badges83 silver badges63 bronze badges

answered Aug 10, 2023 at 17:12

kiranavhad

1211 silver badge3 bronze badges

Comments

William Miller · Accepted Answer · 2023-09-07 09:37:14Z

6

This issue is related to this thread

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

Firstly, confirm if your SQLAlchmey version is 2.0 or beyond. To do so, run

pip show sqlalchemy

Which will show something like this:

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: [email protected]
License: MIT
Location: /Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages
Requires: typing-extensions
Required-by: pandasql

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.

edited Sep 7, 2023 at 9:37

William Miller

10.4k4 gold badges31 silver badges50 bronze badges

answered Apr 7, 2023 at 12:37

Shamik Guha Ray

2374 silver badges4 bronze badges

2 Comments

bfontaine Over a year ago

Downvoted because that’s a complicated solution (going back to 1.4 may require changing a lot of code) to a non-problem: the accepted answer already works with SQLAlchemy 2.0.

t3chb0t Over a year ago

@bfontaine and yet it saved my day, thus +1 ;-] It's not a non-problem if you accidentally install the latest version and wonder what's going on? Upgrading may require changing a lot of code.

Collectives™ on Stack Overflow

ObjectNotExecutableError when executing any SQL query using AsyncEngine

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related