Cannot load table object with sqlalchemy asyncio

Question

I am attempting to load a table using sqlalchemy asyncio. The synchronous way I would run it is as follows:

connect_string = 'db_handle://user:password@db_address:port/database' 
# where db_handle is postgressql+psycopg2
engine = create_engine(connect_string)
table = Table(table, metadata, autoload=True, autoload_with=engine)

None of the solutions I implemented allow me (sqlalchemy core user) to load my table object to then use for querying (ie stmt=select([table.c.col])

I have attempted the following:

connect_string = 'db_handle://user:password@db_address:port/database' 
# where db_handle is postgressql+asyncpg
engine = create_async_engine(connect_string, echo=True)
metadata = MetaData()

#try 1
table = await Table(table, metadata, autoload=True, autoload_with=engine)
# error 1: sqlalchemy.exc.NoInspectionAvailable: Inspection on an AsyncEngine is currently not supported. Please obtain a connection then use ``conn.run_sync`` to pass a callable where it's possible to call ``inspect`` on the passed connection. (Background on this error at: https://sqlalche.me/e/14/xd3s)

#try 2
metadata.bind(db_engine_object)
table = await Table(table, metadata, autoload=True)
# error 2: TypeError: 'NoneType' object is not callable

#try 3
connection = db_engine_object.connect()
table = await Table(table, metadata, autoload=True, autoload_with=connection)
# error 3: sqlalchemy.exc.NoInspectionAvailable: Inspection on an AsyncConnection is currently not supported. Please use ``run_sync`` to pass a callable where it's possible to call ``inspect`` on the passed connection. (Background on this error at: https://sqlalche.me/e/14/xd3s)

#try 4
connection = db_engine_object.connect()
table = await Table(table, metadata, autoload=True, autoload_with=connection.run_sync())
# error 4: TypeError: run_sync() missing 1 required positional argument: 'fn'

I can't run a query without having a table to direct the query to, and I can't find out how to get the table object.

Can you clarify how you would suggest the implementation? I tried: t = await connection.run_sync(Table("reporting_entity", metadata, autoload=True)) which failed with error: sqlalchemy.exc.UnboundExecutionError: No engine is bound to this Table's MetaData. Pass an engine to the Table via autoload_with=<someengine_or_connection> Fatal error on SSL transport — Christina Stebbins
– Christina Stebbins, Commented Dec 23, 2022 at 10:51

snakecharmerb · Accepted Answer · 2022-12-23 11:24:43Z

3

This works for me. The lambda inside run_sync creates a function that run_sync can call. Credit to @zzzeek's answer to this discussion on GitHub.

import asyncio

import sqlalchemy as sa
from sqlalchemy.ext.asyncio import create_async_engine


async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg:///test",
        echo=True,
    )

    async with engine.begin() as conn:
        tbl = await conn.run_sync(
            lambda conn: sa.Table('users', sa.MetaData(), autoload_with=conn)
        )

    async with engine.connect() as conn:
        # select a Result, which will be delivered with buffered
        # results
        result = await conn.execute(sa.select(tbl))

        print(result.fetchall())

    # for AsyncEngine created in function scope, close and
    # clean-up pooled connections
    await engine.dispose()


asyncio.run(async_main())

answered Dec 23, 2022 at 11:24

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

2 Comments

Christina Stebbins Over a year ago

I'll attempt the implementation- thank you! Right now I am using a synchronous connection for the table grabbing T.T.

Christina Stebbins Over a year ago

Worked! I didn't use engine.begin(), I used a connection instead, but it should work the same! Happy to post my code for anyone interested

Collectives™ on Stack Overflow

Cannot load table object with sqlalchemy asyncio

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related