Python--orm框架：sqlalchemy2.0【异步】
一、SQLAlchemy 是什么
总体：可对照Python--orm框架：sqlalchemy - Edmond辉仔 - 博客园 一起看
SQLAlchemy 是 Python 中最流行的数据库工具库，它让开发者可以用 Python 代码代替 SQL 语句来操作数据库。
它分为两层：底层叫 Core，负责连接池管理和 SQL 语句构建，适合需要精细控制 SQL 的场景；上层叫 ORM，能将数据库表映射成 Python 类，让你像操作普通对象一样增删改查，无需手写 SQL。简单说，它把数据库的行变成了对象，把列变成了属性。
二、安装与数据库连接
安装只需一行 pip install sqlalchemy（基础安装），但连接不同数据库还需要对应的驱动。
连接的核心是创建引擎，它通过一个 URL 来指定数据库地址、账号密码和目标库。引擎内部维护着一个连接池，复用连接能显著提升性能。除了引擎，你还需要一个会话工厂，每次数据库操作都应该创建一个新的会话，用完即关，避免数据混乱。
# 连接的核心是创建引擎，它通过一个 URL 来指定数据库地址、账号密码和目标库。
# 引擎内部维护着一个连接池，复用连接能显著提升性能。
# 除了引擎，你还需要一个会话工厂，每次数据库操作都应该创建一个新的会话，用完即关，避免数据混乱。
pip install aiomysql SQLAlchemy[asyncio] （包含异步扩展安装）
# mysql8 还需要安装 依赖cryptography库来密码的解密和验证
pip install cryptography
### 同步
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "mysql+pymysql://root:1234@localhost:3306/snail-cloud?charset=utf8mb4"
# 创建引擎
engine = create_engine(
DATABASE_URL,
echo=True, # 打印 SQL，调试用
pool_size=10, # 连接池大小
max_overflow=5, # 最大溢出连接数
pool_recycle=3600, # 连接回收时间
pool_pre_ping=True # 检查连接是否可用
)
# 创建会话工厂
SessionLocal = sessionmaker(
bind=engine,
autocommit=False,
autoflush=False,
expire_on_commit=False
)
# 获取数据库池中的 连接 可用 with 语句
conn = engine.raw_connection() # 建立链接
异步操作
### 异步
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import sessionmaker
ASYNC_DATABASE_URL = "mysql+aiomysql://root:123456@localhost:3306/fastapi-demo?charset=utf8mb4"
# 创建异步引擎
async_engine = create_async_engine(
ASYNC_DATABASE_URL,
echo=True, # 输出 SQL日志，调试用
max_overflow=5, # 最大溢出连接数
pool_size=10, # 连接池大小
pool_recycle=3600, # 连接回收时间
pool_pre_ping=True # 检查连接是否可用
)
# 创建异步会话工厂 AsyncSession
AsyncSessionLocal = async_sessionmaker(
bind=async_engine,
class_=AsyncSession, # 指定会话类
autocommit=False,
autoflush=False,
expire_on_commit=False # 会话对象不过期，不重新查询数据库
)
# 获取数据库池中的 连接 可用 with 语句
conn = async_engine.begin() # 建立链接
三、定义 ORM 模型
模型就是一个 Python 类，用来描述一张表的结构：类名对应表名，属性对应列。
首先创建一个基类，然后让所有模型继承它。每个属性用类型注解标明它存什么数据，比如 int 还是 str，同时可以加上约束：主键、唯一、索引、默认值等。定义好模型后，数据库表的结构就被清晰地表达在代码中了，这是后续一切操作的基础。
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy import String, Integer, DateTime, Text, ForeignKey, func
from datetime import datetime
# ============================================================
# 第一步：创建基类
# 所有模型类都继承自它，SQLAlchemy 通过它识别哪些是 ORM 模型、哪些是普通类
# ============================================================
class Base(DeclarativeBase):
pass
# ============================================================
# 第二步：定义 User 模型——映射到 users 表
# ============================================================
class User(Base):
# 表名
__tablename__ = "users"
# 创建索引：提升索引速度
__table_args__ = (
# Index('索引名称', 字段名) 索引名称 建议显式指定
# 外键索引：通常以 fk_ 开头
# 普通查询索引：通常以 idx_ 开头
Index('fk_news_category_idx', 'category_id'),
Index('idx_publish_time'， 'publish_time')
)
# 主键，自增，唯一标识每一行
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
# 用户名，最长50字符，不可重复，加索引加速查询
username: Mapped[str] = mapped_column(String(50), unique=True, index=True)
# 邮箱，最长100字符，不可重复
email: Mapped[str] = mapped_column(String(100), unique=True)
# 年龄，整数，默认值为 0
age: Mapped[int] = mapped_column(Integer, default=0)
# 个人简介，长文本，可选（允许为空）
bio: Mapped[str | None] = mapped_column(Text, nullable=True)
# 创建时间，插入行时自动填入当前时间
created_at: Mapped[datetime] = mapped_column(
DateTime,
insert_default=func.now(), # 首次插入时的默认值 func.now()是SQL层面的函数【MySQL的NOW()】
default=func.now(), # 首次插入时的默认值 Python层面的默认值 只保留insert_default【通常】
comment="创建时间"
)
# 更新时间，每次修改行时自动更新为当前时间
updated_at: Mapped[datetime] = mapped_column(
DateTime,
insert_default=func.now(), # 首次插入时，由数据库生成时间
server_onupdate=func.now(), # 更新时由数据库自动生成时间
onupdate=func.now(), # 仅在Python层面执行update时触发 只保留server_onupdate【通常】
comment="更新时间"
)
# ----------------------------------------------------------
# 关联关系：User 与 Post 是一对多（一个用户有多篇文章）
# 这不是数据库中的真实列，而是 ORM 层面的对象引用
# 双向显式绑定：必须在两个模型中都显式定义 relationship，并通过 back_populates 参数互相指向对方的属性名
# back_populates 指向 Post 模型中对应关系的属性名
# ----------------------------------------------------------
posts: Mapped[list["Post"]] = relationship(back_populates="author")
# ============================================================
# 第三步：定义 Post 模型——映射到 posts 表
# ============================================================
class Post(Base):
# 表名
__tablename__ = "posts"
# 主键，自增
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
# 文章标题，最长200字符
title: Mapped[str] = mapped_column(String(200))
# 文章内容，长文本
content: Mapped[str] = mapped_column(Text)
# 外键，指向 users 表的 id 列，表名该文章属于哪个用户
# 加索引，方便根据用户查文章时加速
author_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)
# 创建时间
created_at: Mapped[datetime] = mapped_column(default=func.now())
# ----------------------------------------------------------
# 反向关联关系：通过 post.author 可直接拿到对应的 User 对象
# ----------------------------------------------------------
author: Mapped["User"] = relationship(back_populates="posts")
四、自动创建数据表
模型定义好之后，调用基类的 create_all 方法，SQLAlchemy 会自动检查数据库中哪些表还不存在，并帮你创建。非常适合开发阶段快速建表。但生产环境通常会改用专业的迁移工具（如 Alembic）来精细控制表结构的变更历史。
# ============================================================
# 自动建表——开发阶段用
# 检查数据库中哪些表不存在，只创建缺失的表，已存在的不会动
# 注意：如果表结构变了（比如加了列），它不会自动更新，需要删表重建
# ============================================================
# 同步 写在 定义引擎后，直接调用就行，不用写在数据库连接中
Base.metadata.create_all(bind=engine) # 创建基于Base的所有表
Base.metadata.drop_all(engine) # 删除基于Base的所有表
# 异步：创建数据库表
# 1.从连接池获取异步连接，开启事务，执行ORM操作
async def create_tables():
# 同步连接
# conn = engin.raw_connection()
async with async_engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all) # 连接操作，异步执行-创建所有表
# 2.FastAPI应用启动时，就创建数据库表
### 旧版：FastAPI 官方现已明确弃用（Deprecated）这种写法
@app.on_event("startup")
async def startup_event():
print("应用正在启动，正在初始化数据库...")
await create_tables()
@app.on_event("shutdown")
async def shutdown_event():
print("应用正在关闭，正在清理资源...")
await async_engine.dispose()
### 新版：FastAPI 引入了基于Python异步上下文管理器（async with）的 lifespan 参数
from contextlib import asynccontextmanager
from fastapi import FastAPI
# 定义 Lifespan 上下文管理器
@asynccontextmanager
async def lifespan(_: FastAPI): # 写 app: FastAPI 编辑器会报格式错误提醒，换成 _
# 应用启动阶段：yield 之前的代码
print("应用正在启动，正在初始化数据库...")
# 可以在这里执行建表等操作:
# async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all)
await create_tables()
yield # 应用在此处开始接收请求，并运行
# 应用关闭阶段：yield 之后的代码
print("应用正在关闭，正在清理资源...")
await async_engine.dispose() # 确保优雅关闭，防止连接泄漏
# 将 lifespan 传递给 FastAPI 实例
app = FastAPI(lifespan=lifespan)
五、数据库会话依赖注入
在 Web 应用中，每个请求都应该拥有独立的数据库会话，请求结束时就关闭它，避免连接泄漏和数据串扰。
借助 FastAPI 的依赖注入机制，可以把“获取会话”写成一个可复用的函数。在路由函数中声明依赖后，框架会自动在请求到来时创建会话，在请求处理完毕后关闭它。这样路由函数里只需关注业务逻辑，不用关心会话的创建和销毁。
from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# ============================================================
# 全局：引擎和会话工厂（只创建一次）
# ============================================================
DATABASE_URL = "mysql+pymysql://root:password@localhost:3306/mydb?charset=utf8mb4"
engine = create_engine(DATABASE_URL, pool_pre_ping=True)
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False, expire_on_commit=False)
# ============================================================
# 依赖函数：每个请求获取一个独立会话
# 用 yield 实现：请求进来时创建会话，请求结束时自动关闭
# ============================================================
def get_db():
"""FastAPI 依赖注入，为每个请求提供独立的数据库会话"""
session = SessionLocal() # 请求进来，创建新会话
try:
yield session # 把会话交给路由函数使用
finally:
session.close() # 请求结束，关闭会话，归还连接
# ============================================================
# 创建 FastAPI 应用
# ============================================================
app = FastAPI()
# ============================================================
# 路由函数：只需声明 db 依赖，框架自动注入
# 业务代码里直接用 db，不用关心会话生命周期
# ============================================================
@app.get("/users/{user_id}")
def get_user(user_id: int, db: Session = Depends(get_db)):
"""查询单个用户"""
user = db.query(User).filter(User.id == user_id).first()
return user
@app.post("/users")
def create_user(name: str, email: str, db: Session = Depends(get_db)):
"""新增用户"""
user = User(name=name, email=email)
db.add(user)
db.commit()
db.refresh(user)
return user
异步操作
from typing import Annotated, AsyncGenerator
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import select
from uvicorn import lifespan
# 全局：引擎和会话工厂（只创建一次）
见：二、安装与数据库连接 和 四、自动创建数据库表
# 创建 FastAPI 应用，挂载 lifespan
app = FastAPI(lifespan=lifespan)
# 依赖函数：每个请求获取一个独立会话
# 用 yield 实现：请求进来时创建会话，请求结束时自动关闭
async def get_db() -> AsyncGenerator[AsyncSession, None]:
"""FastAPI 依赖注入，为每个请求提供独立的数据库会话"""
async with AsyncSessionLocal() as session: # 请求进来，创建新会话
try:
yield session # 把会话交给路由函数使用
# 注意：不要全局 commit
# 过度提交：查询接口不需要commit
# 增删改接口请在业务逻辑中自行 await session.commit()
# await session.commit() # 无异常，提交事务
except Exception:
await session.rollback() # 有异常，则回滚
raise
# 注意：不需要 finally: await session.close()
# async with AsyncSessionLocal() 是一个异步上下文管理器，会在退出时，自动处理连接的释放和关闭
# finally:
# await session.close() # 请求结束，关闭会话，归还连接
# 类型别名: 将依赖和类型绑定在一起 告别冗长的 Depends
DBSession = Annotated[AsyncSession, Depends(get_db)]
# 路由函数：只需声明 db 依赖，框架自动注入
# 业务代码里直接用 db，不用关心会话生命周期
from config.db_config import get_db, DBSession
@app.get("/users/{user_id}")
async def get_user(user_id: int, db: AsyncSession = Depends(get_db)):
"""查询单个用户"""
# 异步模式: 必须使用 execute + select
result = await db.execute(select(User).filter(User.id == user_id))
# 手动处理：查询不到 返回None, 而不是抛出异常
user = result.scalar_one_or_none() # scalar n.数量
if not user:
raise HTTPException(status_code=404, detail="User not found")
return user
@app.post("/users")
async def create_user(name: str, email: str, db: DBSession):
"""新增用户"""
user = User(name=name, email=email)
db.add(user)
# 注意：因为 get_db 中已经配置了自动 commit，这里不需要手动 await db.commit()
# 使用 flush 代替手动提交， 将数据推送到数据库以获取自增ID，但不提交事务
await db.flush()
await db.refresh(user) # 刷新对象状态，获取自增ID和数据库默认值
return user # 安全地返回对象（FastAPI 序列化时不会为空）
六、基础增删改查操作
这是最常用的四项操作，都通过会话对象来执行。
增：先创建模型实例，然后用 add 加入会话，最后 commit 提交。提交后可用 refresh 获取数据库生成的默认值（如自增 ID）。
查：用 query 链式调用 filter 指定条件，再调用 first() 取第一条或 all() 取所有。
改：先查到要修改的对象，直接修改它的属性，再 commit 即可。ORM 会自动追踪属性的变化。
删：查到对象后，用 delete 标记删除，然后 commit 提交。10
核心思路是：一切操作先发生在 Python 对象上，commit 时才同步到数据库。
# ============================================================
# 增删改查 —— 全部通过会话对象 db 执行
# ============================================================
# ============================================================
# 一、增（Create）
# 1. 创建模型实例（此时只在 Python 内存中，未进数据库）
# 2. add 加入会话的待提交队列
# 3. commit 真正写入数据库
# 4. refresh 从数据库回读，拿到自增 ID 等数据库生成的值
# ============================================================
new_user = User(username="张三", email="zhangsan@test.com", age=25)
db.add(new_user) # 加入待提交队列，还没写入数据库
db.commit() # 真正执行 INSERT 语句，写入数据库
db.refresh(new_user) # 回读数据库生成的值，比如 new_user.id 现在是自增后的 ID
# ============================================================
# 二、查（Read）
# query(模型类) 指定查哪张表
# filter() 加条件过滤
# first() 取第一条，查不到返回 None
# all() 取所有，返回列表
# ============================================================
# 查单条
user = db.query(User).filter(User.email == "zhangsan@test.com").first()
# 查所有
users = db.query(User).filter(User.age > 18).all()
# 按主键查（更快的简写）
user = db.query(User).get(1)
# ============================================================
# 三、改（Update）
# 1. 先查到要改的对象
# 2. 直接修改属性值（ORM 会自动追踪变化）
# 3. commit 生成 UPDATE 语句同步到数据库
# 无需手动调用 update 方法
# ============================================================
user = db.query(User).filter(User.id == 1).first()
if user:
user.age = 30 # 直接改属性
user.email = "new@test.com"
db.commit() # ORM 自动对比变化，生成 UPDATE 语句
# ============================================================
# 四、删（Delete）
# 1. 先查到要删的对象
# 2. delete 标记为待删除
# 3. commit 执行 DELETE 语句
# ============================================================
user = db.query(User).filter(User.id == 1).first()
if user:
db.delete(user) # 标记删除
db.commit() # 真正从数据库删除
# ============================================================
# 核心流程总结：
# Python 对象（内存） → add / 改属性 / delete → commit → 数据库
# commit 之前，一切变化只在内存中，不影响数据库
# 如果中途异常，没 commit 的改动会自动丢弃，天然安全
# ============================================================
提交与刷新
await db.flush() # 刷新数据到数据库，但不提交事务
# 把内存中的修改推送到数据库（为了拿到 生成自增ID 和触发数据库默认值）
await db.refresh(new_user) # 按需执行：显式地从数据库拉取最新状态到内存（为了保证Pydantic序列化时，不报错）
# 比如 new_user.id 现在是自增后的 ID
await db.commit() # 提交事务，真正执行 INSERT 语句，写入数据库 【数据库连接中，已设置提交】
# 注：这三个 也是执行流程顺序
1.flush() 和 refresh()：放在路由函数中，仅在新增或更新，且【需要返回最新完整对象】时，调用
这两步，在异步中很重要，当执行完 await db.commit() 后，SQLAlchemy 为了保证数据的一致性
会默认将当前 Session中所有已加载的ORM对象标记为 Expired（过期）
如果没有 refresh() 显式地从数据库重新拉取最新状态，Pydantic 在读取属性时会触发隐式的懒加载
在异步环境下，会直接抛出 MissingGreenlet 异常，或 导致返回给前端的数据是空的 {}
# 若不想写 await db.refresh(db_user)，需要在 get_db 依赖中配置
async_session = async_sessionmaker(
engine,
class_=AsyncSession,
expire_on_commit=False # 关键：关闭自动过期
)
2.commit() 和 close()：放在 get_db 依赖的 finally 块中，作为全局的事务兜底
异步操作
# 增删改查 —— 全部通过会话对象 db 执行
# ============================================================
# 一、增（Create）
# 1. 创建模型实例（此时只在 Python 内存中，未进数据库）
# 2. add 加入会话的待提交队列
# 3. commit 真正写入数据库
# 4. refresh 从数据库回读，拿到自增 ID 等数据库生成的值
from pydantic import BaseModel, EmailStr, ConfigDict
# 请求模型：用于接收前端传来的数据，自动进行类型校验
class UserCreate(BaseModel):
name: str
email: EmailStr # 自动校验是否为合法的邮箱格式 但需要安装邮箱扩展 pip install 'pydantic[email]'
age: int | None = None # 可选字段
# 响应模型：用于格式化返回给前端的数据，隐藏敏感信息
class UserResponse(BaseModel):
id: int
name: str
email: str
age: int | None = None
class Config:
# 关键：允许从 SQLAlchemy ORM 对象中读取数据
from_attributes = True
# 它告诉Pydantic：传入的不再是字典，而是一个带有属性的ORM对象（如user.name），请通过属性名去提取数据
# 新版本写法 - 设置：允许从ORM对象读取属性
model_config = ConfigDict(from_attributes=True)
@app.post("/users/", response_model=UserResponse, status_code=201)
async def create_user(
user_data: UserCreate, # FastAPI 会自动将 JSON Body 解析为 UserCreate对象
db: AsyncSession = Depends(get_db)
):
new_user = User(**user_data.model_dump()) # 使用 ** 解包传入数据，非常优雅
db.add(new_user) # 加入待提交队列，还没写入数据库
# 刷新对象状态，确保返回给前端的是数据库中的最新数据
# await db.flush()
# await db.refresh(new_user)
# 注：因为在async_sessionmaker中，配置了关闭自动过期，直接提交 不刷新也可以
# await db.commit() # 有get_db 兜底 提交事务，不用再手动提交事务
return new_user
# ============================================================
# 二、查（Read）
# 核心语句：
# 1. await db.execute(select(模型类).filter(条件)) # 返回一个ORM对象
await db.execute(select(模型类).where(条件)) # 完全等效 官方更推荐
# 从ORM对象中，提取数据：
# 注意：提取数据 不能直接挂在 .execute() 后面，必须先拿到result之后【因为这是异步操作，不是马上返回结果】，再提取 ！！！
.all() 返回原始的 Row 对象列表 # Row对象类似于Python的元组，但支持通过列名进行索引。
# 查询模型类：select(User) 返回: [Row(User(id=1)), Row(User(id=2))] 包裹着ORM对象的Row对象列表
# 查询多字段：select(User.id, User.name) 返回：[Row(id=1, name='Alice'), Row(id=2, name='Bob')] 元组列表
.scalars().all() 提取纯净的实体或单列值 # 取所有【只提取出每行的第一个元素（标量值）】，返回列表
# 查询模型类：select(User) 返回: [User(id=1), User(id=2)] 纯净的ORM对象列表
# 查询多字段：select(User.id, User.name) 返回：['Alice', 'Bob'] 会丢弃name字段，只保留id
.mappings().all() 返回字典列表，更适合转JSON
# 查询多字段：select(User.id, User.name) 返回：[{'id': 1, 'name': 'Alice'}, {'id': 2, 'name': 'Bob'}] 字典列表
.scalars().first() 取第一条，查不到返回 None # 查询非唯一字段
.scalar_one_or_none() 查询一条，查不到返回 None # 查询唯一字段
# 本质区别：
.scalar_one_or_none() 是根据唯一字段，最多一个结果，多个会抛异常
.first() 是多个结果取一个， 底层sql: 通常会在SQL末尾自动追加 LIMIT 1
.scalar() # 用于提取单个标量值 常用于 聚合值 或 字段的单列值
# COUNT(*) 或 SELECT name WHERE id=1
# 2. await db.get(模型类, 主键值) # 根据主键查询，直接返回数据
# 查单条
result = await db.execute(select(User).where(User.email == "zhangsan@test.com"))
user = result.scalars().first()
# 查所有
result = await db.execute(select(User))
users = result.scalars().all()
# 按主键查（更快的简写）
user = await db.get(User, 1)
# ============================================================
# 三、改（Update）
# 方式一：查询对象，修改对象 【可全覆盖或 局部修改】
1. 先查到要改的对象
2. 直接修改属性值（ORM 会自动追踪变化）
3. commit 生成 UPDATE 语句同步到数据库
# 无需手动调用 update 方法
from fastapi import FastAPI, Depends, HTTPException
# 更新专用模型：所有字段都设为 Optional，方便前端按需传值
class UserUpdate(BaseModel):
name: str | None = None
email: EmailStr | None = None
age: int | None = None
@app.put("/users/{user_id}", response_model=UserResponse)
async def update_user(
user_id: int,
user_data: UserUpdate, # 接收前端传来的部分更新数据
db: AsyncSession = Depends(get_db)
):
# 1. 查询目标用户是否存在
user = await db.get(User, user_id)
if not user:
raise HTTPException(status_code=404, detail="User not found")
# 2. 核心: 提取前端实际传递的字段（忽略未传递的字段，局部更新！！！）
# exclude_unset=True 排除未显式设置的字段 但不能排除 name=None
# exclude_none=True 排除值为None的字段 不管是默认值，还是前端显示赋值为None的
update_data = user_data.model_dump(exclude_unset=True, exclude_none=True)
# 3. 动态赋值：修改数据库对象的属性
for field, value in update_data.items():
setattr(user, field, value)
# 4.因为在async_sessionmaker中，配置了关闭自动过期，直接提交 不刷新也可以
# await db.commit() # 有get_db 兜底 提交事务，不用再手动提交事务
return user
# 方式二：使用 Core 层的 字段局部更新
核心语句： await db.execute(update(模型类).where(条件).values(修改字段值))
async def update_user_by_map(db: AsyncSession, user_id: int, update_data: dict):
# 直接根据 key-value 形式更新对应字段
sql = update(User).where(User.id == user_id).values(**update_data)
cursor_result = await db.execute(sql)
await db.commit()
# 返回受影响的行数
return cursor_result.rowcount
# ============================================================
# 四、删（Delete）
# 1. 先查到要删的对象
# 2. delete 标记为待删除
# 3. commit 执行 DELETE 语句
user = await db.get(User, user_id)
if user:
await db.delete(user) # 标记删除 注意 需要await
# db.commit() # 有get_db 兜底 提交事务，不用再手动提交事务
# ============================================================
# 核心流程总结：
# Python 对象（内存） → add / 改属性 / delete → commit → 数据库
# commit 之前，一切变化只在内存中，不影响数据库
# 如果中途异常，没 commit 的改动会自动丢弃，天然安全
# ============================================================
七、复杂条件查询
除了简单的等值过滤，SQLAlchemy 还支持丰富的查询表达。
你可以组合多个条件，比如 “年龄大于 18 且城市是北京”；也可以做模糊匹配，比如 “名字中含‘张’”；还能进行聚合统计，比如 “每个城市各有多少用户”。对于多表查询，如果模型之间定义了关联关系，可以直接用 join 跨表过滤，就像操作单个对象一样自然。
from sqlalchemy import or_, and_, func, between
# ============================================================
# 一、多条件组合
# ============================================================
# AND 连接：年龄大于18 且 城市是北京
users = db.query(User).filter(
and_(User.age > 18, User.city == "北京")
).all()
# OR 连接：城市是北京 或 上海
users = db.query(User).filter(
or_(User.city == "北京", User.city == "上海")
).all()
# 链式 filter 也等同于 AND
users = db.query(User).filter(User.age > 18).filter(User.city == "北京").all()
# 区间查询：年龄在 20 到 30 之间
users = db.query(User).filter(between(User.age, 20, 30)).all()
# IN 查询：城市在给定列表中
users = db.query(User).filter(User.city.in_(["北京", "上海", "广州"])).all()
# ============================================================
# 二、模糊匹配
# ============================================================
# 名字中含"张"
users = db.query(User).filter(User.name.like("%张%")).all()
# 名字以"张"开头
users = db.query(User).filter(User.name.like("张%")).all()
# 邮箱以 @gmail.com 结尾
users = db.query(User).filter(User.email.like("%@gmail.com")).all()
# ============================================================
# 三、聚合统计
# ============================================================
# 统计用户总数
count = db.query(func.count(User.id)).scalar()
# 按城市分组，统计每个城市的用户数
result = db.query(User.city, func.count(User.id)).group_by(User.city).all()
# 返回：[("北京", 15), ("上海", 12), ("广州", 8)]
# 平均年龄
avg_age = db.query(func.avg(User.age)).scalar()
# ============================================================
# 四、多表关联查询
# 前提：User 和 Post 已通过 relationship 和 ForeignKey 建立关联
# ============================================================
# 方式一：join + filter，跨表过滤
# 查所有"张三"写的文章
posts = db.query(Post).join(User).filter(User.name == "张三").all()
# 方式二：直接用 relationship 属性，更自然
# 先拿到用户，再通过 .posts 拿到他的所有文章
user = db.query(User).filter(User.name == "张三").first()
posts = user.posts # 这就是他写的所有文章
# 反向查：这篇文章的作者是谁
post = db.query(Post).filter(Post.title == "某文章标题").first()
author = post.author # 直接拿到 User 对象
异步操作
from sqlalchemy import or_, and_, func, between
# ============================================================
# 一、多条件组合
# AND 连接：年龄大于18 且 城市是北京
方式1： and_() 函数 # 官方推荐，条件不需要加括号
方式2： await db.execute(select(User).where( (条件1) & (条件2))
# 注意：每个条件必须用括号()
result = await db.execute(select(User).where(
User.age > 18 & User.city == "北京"
)
result = await db.execute(select(User).where(
and_(User.age > 18, User.city == "北京")
)
)
users = result.scalars().all()
# OR 连接：城市是北京 或 上海
方式1： or_() 函数 # 官方推荐，条件不需要加括号
方式2：await db.execute(select(User).where(条件1 | 条件2)
result = await db.execute(select(User).where(
(User.city == "北京") | (User.city == "上海")
)
result = await db.execute(select(User).where(
or_(User.city == "北京", User.city == "上海")
)
)
users = result.scalars().all()
# 链式 where 也等同于 AND
result = await db.execute(select(User).where(User.age > 18).where(User.city == "北京"))
# 区间查询：年龄在 20 到 30 之间
result = await db.execute(select(User).where(between(User.age, 20, 30)))
# IN 查询：城市在给定列表中
result = await db.execute(select(User).where(User.city.in_(["北京", "上海", "广州"])))
users = result.scalars().all()
# ============================================================
# 二、模糊匹配
# 名字中含"张"
result = await db.execute(select(User).where(User.name.like("%张%"))).scalars().all()
# 名字以"张"开头
result = await db.execute(select(User).where(User.name.like("张%"))).scalars().all()
# 邮箱以 @gmail.com 结尾
result = await db.execute(select(User).where(User.name.like("%@gmail.co"))).scalars().all()
users = result.scalars().all()
# ============================================================
# 三、聚合统计
func.聚合方法(模型类.属性)
# 统计用户总数
result = await db.execute(select(func.count(User.id)))
count = result.scalar()
# 按城市分组，统计每个城市的用户数
result = await db.execute(
select(User.city, func.count(User.id).label("user_count")
).group_by(User.city)
rows = result.all()
# 返回：[("北京", 15), ("上海", 12), ("广州", 8)]
# ============================================================
# 四、分页查询
# 核心公式是 skip = (page 当前页码 - 1) * page_size 每页数量
select(模型类).offset(skip 跳过的记录数).limit(page_size 返回的记录数)
result = await db.execute(
select(User).offset(skip).limit(page_size)
)
users = result.scalars().all()
# ============================================================
# 五、多表关联查询
# 前提：User 和 Post 已通过 relationship 和 ForeignKey 建立关联
# 方式一：join + where，跨表查询 【只需要部分字段：推荐】
# 查所有"张三"写的文章
# 构建一个 SQL Statement 对象（SQL 语句） stmt: statement的缩写
stmt = (
select(Post)
.join(User, Post.author_id == User.id) # 显式关联 User 表
.where(User.username == "张三") # 跨表条件过滤
)
stmt = (
select(Post)
.join(User) # SQLAlchemy 会自动根据外键找到关联条件
.where(User.username == "张三")
)
result = await db.execute(stmt)
return result.scalars().all()
# 方式二：利用 relationship 属性，预加载关联数据（子查询） 【完整的ORM对象：推荐】
在异步环境中，绝对不能直接访问 .posts 触发懒加载（会报 MissingGreenlet 错误）
必须使用 selectinload 显式预加载
# 正向关联：先拿到用户，再通过 .posts 拿到他的所有文章
stmt = (
select(User)
.options(selectinload(User.posts)) # 核心：显式预加载 posts 集合
.where(User.username == "张三")
)
result = await db.execute(stmt)
user = result.scalar_one_or_none()
if user:
# 此时可以安全、无感地直接访问 user.posts，不会触发额外查询
return user.posts
return []
# 反向关联：获取某篇文章及其作者信息
post = db.query(Post).filter(Post.title == "某文章标题").first()
stmt = (
select(Post)
.options(selectinload(Post.author)) # 预加载作者对象
.where(Post.title == "某文章标题")
)
result = await db.execute(stmt)
post = result.scalar_one_or_none()
if post:
# 安全访问 post.author.name
return post
return None
八、原生 SQL 执行
当 ORM 的表达能力不够用时，可以直接写 SQL。SQLAlchemy 提供了 text() 函数来包裹原生 SQL 字符串，并通过字典方式传入参数，能有效防止 SQL 注入。这种方法保留了 ORM 的连接管理和事务控制，同时让你在复杂场景下直接掌控 SQL 逻辑，是 ORM 能力的有力补充。
from sqlalchemy import text
# ============================================================
# 原生 SQL 执行 —— text() 包裹 SQL，字典传参防注入
# ============================================================
# ============================================================
# 一、查询操作
# ============================================================
# 简单查询
sql = "SELECT * FROM users WHERE age > :age"
rows = db.execute(text(sql), {"age": 18}).fetchall()
# rows 是列表，每行是一个 Row 对象，可按列名取值
# 遍历结果
for row in rows:
print(row.id, row.name, row.email)
# 取单条
sql = "SELECT * FROM users WHERE id = :user_id"
row = db.execute(text(sql), {"user_id": 1}).fetchone()
# 多条件参数化
sql = """
SELECT * FROM users
WHERE age > :min_age
AND city = :city
"""
rows = db.execute(text(sql), {"min_age": 20, "city": "北京"}).fetchall()
# 聚合查询
sql = "SELECT city, COUNT(*) as cnt FROM users GROUP BY city"
rows = db.execute(text(sql)).fetchall()
for row in rows:
print(row.city, row.cnt)
# ============================================================
# 二、写操作（增删改）
# 注意：execute 后必须手动 commit，和 ORM 方式一样
# ============================================================
# 插入
sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)"
db.execute(text(sql), {"name": "王五", "email": "wangwu@test.com", "age": 28})
db.commit() # 必须提交
# 更新
sql = "UPDATE users SET age = :age WHERE id = :user_id"
db.execute(text(sql), {"age": 30, "user_id": 1})
db.commit()
# 删除
sql = "DELETE FROM users WHERE id = :user_id"
db.execute(text(sql), {"user_id": 5})
db.commit()
# ============================================================
# 三、复杂查询示例（ORM 难表达的 SQL）
# ============================================================
# 子查询
sql = """
SELECT * FROM users
WHERE id IN (
SELECT user_id FROM posts
WHERE created_at > :since
)
"""
rows = db.execute(text(sql), {"since": "2025-01-01"}).fetchall()
# 联表查询
sql = """
SELECT u.name, p.title, p.created_at
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.city = :city
ORDER BY p.created_at DESC
"""
rows = db.execute(text(sql), {"city": "北京"}).fetchall()
九、事务管理
事务能保证一组操作要么全部成功，要么全部撤销，这是确保数据一致性的关键。
SQLAlchemy 默认在 commit 时提交事务。如果中间发生异常，需要手动调用 rollback 回滚。更推荐的方式是使用 with 语句开启事务，代码块结束时自动提交，有异常则自动回滚，代码更简洁也更安全。
# ============================================================
# 事务管理 —— 保证一组操作同生共死
# ============================================================
# ============================================================
# 场景示例：用户下单
# 需要同时做两件事：1. 扣减库存 2. 创建订单
# 任何一步失败，另一条也必须撤销，不能出现库存扣了但订单没生成
# ============================================================
# ============================================================
# 方式一：手动提交 / 回滚（原始写法）
# ============================================================
def create_order_manual(user_id, product_id, quantity):
db = SessionLocal()
try:
# 步骤1：扣减库存
product = db.query(Product).filter(Product.id == product_id).first()
product.stock -= quantity
# 步骤2：创建订单
order = Order(user_id=user_id, product_id=product_id, quantity=quantity)
db.add(order)
# 都成功，提交
db.commit()
except Exception:
# 任何一步出错，回滚所有改动
db.rollback()
raise # 把异常继续往上抛
finally:
db.close()
# ============================================================
# 方式二：with 语句自动管理（推荐）
# session.begin() 开启事务
# 代码块正常结束 → 自动 commit
# 代码块抛出异常 → 自动 rollback
# 不用手写 try/except/finally
# ============================================================
def create_order_with(user_id, product_id, quantity):
with SessionLocal() as db: # 自动关闭会话
with db.begin(): # 开启事务，自动提交或回滚
# 步骤1：扣减库存
product = db.query(Product).filter(Product.id == product_id).first()
product.stock -= quantity
# 步骤2：创建订单
order = Order(user_id=user_id, product_id=product_id, quantity=quantity)
db.add(order)
# 到这里事务已自动提交，无需手动 commit
# ============================================================
# 方式三：更简洁的嵌套 with（一个 with 同时管会话和事务）
# ============================================================
def create_order_compact(user_id, product_id, quantity):
with SessionLocal() as db, db.begin(): # 逗号并列，一个缩进层级
# 扣库存
product = db.query(Product).filter(Product.id == product_id).first()
product.stock -= quantity
# 建订单
order = Order(user_id=user_id, product_id=product_id, quantity=quantity)
db.add(order)
# 离开 with 块时，自动提交并关闭会话
# ============================================================
# 对比说明
# ============================================================
| 写法 | 优点 | 缺点 |
|---|---|---|
手动 try/except |
流程清晰，可自定义异常处理 | 代码啰嗦，容易忘写 rollback 或 close |
with db.begin() |
异常自动回滚，不会忘 | 需要额外嵌一层 with |
with SessionLocal(), db.begin() |
一行搞定，最简洁 | 无法在中间插入自定义异常处理 |
浙公网安备 33010602011771号