Table reflection doesn't work when using aiosqlite

[ruimartinsptl]

Describe the bug

Table reflection doesn't work when I'm using aiosqlite, it will fail when run Table("users", metadata, autoload_with=engine)

To Reproduce

import asyncio

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession

from sqlalchemy import MetaData
from sqlalchemy import Table, Column, Integer, String

metadata = MetaData()

engine = create_async_engine(
    "sqlite+aiosqlite:////Users/rui/database.db",
    echo=True,
    future=True,
)

# Table Reflection (Loading existing table)
users = Table("users", metadata, autoload_with=engine)

Error

File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 597, in __new__
    metadata._remove_table(name, schema)
  File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 592, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 667, in _init
    self._autoload(
  File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 700, in _autoload
    insp = inspection.inspect(autoload_with)
  File "/usr/local/Caskroom/miniconda/base/envs/playground/lib/python3.9/site-packages/sqlalchemy/inspection.py", line 71, in inspect
    raise exc.NoInspectionAvailable(
sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type <class 'sqlalchemy.ext.asyncio.engine.AsyncEngine'>
python-BaseException

Process finished with exit code 1

Versions.

• OS: Mac OS 11.2.3 with Conda 4.9.2
• Python: 3.9.2
• SQLAlchemy: 1.4.5
• Database: SQLite file
• DBAPI: aiosqlite==0.17.0

Additional context
If I use the default DBAPI instead of aiosqlite, it works

Have a nice day! 🍻

JUST A NOTE AFTER I READ THE zzzeek commentary: I ran my code inside an async main, I forgot to refer that in the issue, but my code was:

# (...)
async def async_main():
    engine = (...)
    content_creators = Table("users", metadata, autoload_with=engine)

asyncio.run(async_main())

[zzzeek]

hey there -

the example uses the create_async_engine call, not create_engine as would be used by the normal SQLite drivers, so it's not the driver that's changing here as much as the Python API in use.

Using create_async_engine, this object is only valid to be used in the asyncio event loop, which means a coroutine function here would need to be invoked with asyncio.run.

For SQLAlchemy APIs that don't explicitly provide an awaitable interface, the run_sync approach is provided so that non-async APIs like metadata.create_all can be used, which also applies to reflection-oriented things like working with inspectors and reflection. See below.

import asyncio
import os

from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine

metadata = MetaData()

if os.path.exists("file.db"):
    os.unlink("file.db")
engine = create_async_engine(
    "sqlite+aiosqlite:///file.db",
    echo=True,
    future=True,
)


# Table Reflection (Loading existing table)


async def main(engine):
    async with engine.begin() as conn:
        await conn.execute(text("create table users (id integer primary key)"))

    async with engine.connect() as conn:
        users = await conn.run_sync(
            lambda conn: Table("users", metadata, autoload_with=conn)
        )

        print(users.select())

asyncio.run(main(engine))

[ruimartinsptl]

It works well, thanks