[Python] SQLAlchemy AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'

I am trying to use the async version of SQLAlchemy in an asyncio app. However, when trying to create the tables using Metadata().create_all(), the following error occurs

AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'
​

How do we fix this problem? Thanks!

import asyncio
from sqlalchemy import Column, String,
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.sql.schema import MetaData
from sqlalchemy.ext.asyncio import create_async_engine


connection_url = f"postgresql+asyncpg://{user}:{password}@{host}:{port}/{dbname}"
engine = create_async_engine(connection_url)
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
db_session = scoped_session(Session)

Base = declarative_base()
Base.query = db_session.query_property()

class Foo(Base):
__tablename__ = "foo"
id = Column(String, primary_key=True)
name = Column(String)

class Store:
def __init__(self):
super().__init__()
self.connection = None

async def connect(self):
self.connection = await engine.begin()
metadata = MetaData(bind=engine)
await self.connection.run_sync(metadata.create_all())


async def main():
store = Store()
await store.connect()

if __name__ == '__main__':
asyncio.run(main())


Attempt #2

import asyncio
from sqlalchemy import Column, String,
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.sql.schema import MetaData
from sqlalchemy.ext.asyncio import create_async_engine


connection_url = f"postgresql+asyncpg://{user}:{password}@{host}:{port}/{dbname}"
engine = create_async_engine(connection_url)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)

Base = declarative_base()

class Foo(Base):
__tablename__ = "foo"
id = Column(String, primary_key=True)
name = Column(String)
__mapper_args__ = {"eager_defaults": True}

class Store:
def __init__(self):
super().__init__()

async def connect(self):
async with async_session() as db_session:
async with db_session.begin():
await db_session.run_sync(Base.metadata.create_all)

async def main():
store = Store()
await store.connect()

if __name__ == '__main__':
asyncio.run(main())


Error:

AttributeError: 'Session' object has no attribute '_run_ddl_visitor'
​

Continue reading...