3.FastAPI ORM建表

ORM - 创建数据库引擎

使用 create_async_engine 创建异步引擎

# pip install sqlalchemy[asyncio] aiomysql fastapi uvicorn
from contextlib import asynccontextmanager
from datetime import datetime
from typing import AsyncGenerator

from fastapi import FastAPI, Depends
from sqlalchemy import DateTime, func, String, Float, select
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

# ==================== 数据库配置 ====================
ASYNC_DB_URL = "mysql+aiomysql://root:123456@localhost:3306/fastapi01"

async_engine = create_async_engine(
    ASYNC_DB_URL,
    echo=True,  # 打印SQL语句，生产环境关闭
    pool_size=10,
    max_overflow=20,
)

# 异步会话工厂 通过注入执行增删改查
AsyncSessionLocal = async_sessionmaker(
    bind=async_engine,
    class_=AsyncSession,
    expire_on_commit=False,  # 提交后不失效对象，开发常用优化
)

# ==================== ORM基类 ====================
class Base(DeclarativeBase):
    # 创建时间：仅新增时自动填充
    create_time: Mapped[datetime] = mapped_column(
        DateTime, default=func.now(), comment="创建时间"
    )
    # 更新时间：新增默认填充，更新自动刷新
    update_time: Mapped[datetime] = mapped_column(
        DateTime, default=func.now(), onupdate=func.now(), comment="修改时间"
    )

class Book(Base):
    __tablename__ = "book"
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True, comment="编号")
    bookname: Mapped[str] = mapped_column(String(100), comment="书名")
    author: Mapped[str] = mapped_column(String(100), comment="作者")
    price: Mapped[float] = mapped_column(Float, comment="ä»·æ ¼")
    publisher: Mapped[str] = mapped_column(String(255), comment="出版社")

# ==================== 数据库初始化 ====================
async def create_tables():
    """启动时自动创建数据表（仅开发使用，生产使用Alembic迁移）"""
    async with async_engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

# 获取数据库会话依赖
async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with AsyncSessionLocal() as session:
        yield session
        await session.commit()

# ==================== FastAPI Lifespan ====================
@asynccontextmanager
async def lifespan(app: FastAPI):
    print("🚀 服务启动，初始化数据库...")
    await create_tables()
    yield
    print("🛑 服务关闭，释放数据库连接")
    await async_engine.dispose()  # 关闭异步引擎，释放连接池

app = FastAPI(lifespan=lifespan)

# ==================== 接口示例 ====================
@app.get("/")
async def home():
    return {"msg": "欢迎访问首页"}

# 查询书籍示例接口
@app.get("/books")
async def get_book_list(db: AsyncSession = Depends(get_db)):
    stmt = select(Book)
    result = await db.execute(stmt)
    books = result.scalars().all()
    return [
        {
            "id": item.id,
            "bookname": item.bookname,
            "author": item.author,
            "price": item.price,
            "publisher": item.publisher,
            "create_time": item.create_time,
            "update_time": item.update_time
        }
        for item in books
    ]