SQLALchemy 异步操作以及在FastAPI中的用例

mysql为例，不做额外配置。

创建异步引擎

from sqlalchemy.ext.asyncio import create_async_engine
url = "mysql+aiomysql://root:*****@127.0.0.1:3306/demodemo?charset=utf8"
async_egn = create_async_engine(url)

metadata

两种方式 一种是Table收集 一种是Mapper类声明

Table收集

第一个参数是表名

from sqlalchemy import MetaData
metadata = MetaData()

from sqlalchemy import Table
from sqlalchemy.types import Integer,String

User = Table(
    "user",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(30))
)

 Mapper类声明

获取Base的方法非常多

from sqlalchemy.orm import registry
mapper_registry = registry()
Base = mapper_registry.generate_base()

# mapper_registry.metadata即为MetaData单例


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))

# User.__table__  可以获取到Table版的User

execute

分为连接和会话两种 区别不大 

使用连接

async with async_egn.connect() as conn:
    result = await conn.execute(...)
    print(result.fetchall())

async with async_egn.connect() as conn:
    async_result = await conn.stream(...)
    async for row in async_result:
        print(row)