第4-4课：SQLAlchemy关系映射与工程实践
一、本课定位
上一课把一张商品表映射成了Python类，并使用Session完成增删改查。本课进入真实业务中更常见的多表场景：一名客户有多张订单，需要同时查询客户信息和订单信息。
你已经学习过数据库和Java，因此本课不会重新讲解主键、外键和JOIN的基础语法，而是重点说明SQLAlchemy如何表达这些概念，以及它与JPA、MyBatis、MyBatis-Plus之间的差异。
二、本课目标
完成本课后，你能够：
- 使用
ForeignKey建立数据库外键； - 使用
relationship()建立Python对象之间的关系； - 映射一对多和多对一关系；
- 使用
join()完成显式联表查询； - 使用数据传输对象（Data Transfer Object，DTO）承载多表查询结果；
- 使用
selectinload()避免N+1查询； - 使用
func.count()和group_by()完成聚合查询； - 理解Repository与事务边界的基本职责。
三、SQLAlchemy能否实现多表查询
可以。SQLAlchemy主要提供两种多表查询方式。
3.1 查询ORM实体及其关系
statement = (
select(Customer)
.options(selectinload(Customer.orders))
)
customers = session.scalars(statement).all()
查询结果是Customer对象，每个客户可以通过customer.orders访问订单集合。这种方式类似JPA实体关系查询，适合后续业务逻辑需要完整实体对象的场景。
3.2 查询指定列并组装DTO
statement = (
select(Order.order_no, Customer.customer_name, Order.amount)
.join(Customer, Order.customer_id == Customer.id)
)
rows = session.execute(statement).all()
这种方式只查询需要的列，再把结果转换成DTO。它更接近MyBatis中编写联表SQL并映射到DTO或VO。
两者没有绝对优劣：需要修改完整业务实体时使用ORM实体；列表、报表、统计接口通常更适合DTO投影。
四、与Java技术体系对照
| Python与SQLAlchemy | Java中的近似概念 | 说明 |
|---|---|---|
ForeignKey |
数据库外键、JPA @JoinColumn |
定义数据库层面的引用约束 |
relationship() |
JPA @OneToMany、@ManyToOne |
定义对象之间如何导航 |
select()、join() |
MyBatis SQL、JPA Criteria/JPQL | 构造查询 |
Session |
JPA EntityManager |
管理实体状态和事务工作单元 |
@dataclass DTO |
Java DTO/VO/record | 承载查询输出，不负责持久化 |
selectinload() |
ORM批量预加载 | 减少逐条加载关系产生的查询 |
SQLAlchemy不是MyBatis-Plus的完全对应物。它的ORM部分更接近JPA/Hibernate，同时也允许像SQL构造器一样明确选择表、列、连接条件和聚合表达式。
五、ForeignKey与relationship的区别
这是本课最重要的区别。
customer_id: Mapped[int] = mapped_column(
ForeignKey("course_orm_customer.id"),
nullable=False,
)
customer: Mapped[Customer] = relationship(back_populates="orders")
ForeignKey作用在数据库层：它让order.customer_id引用customer.id，数据库可以阻止无效的客户编号。
relationship()作用在Python对象层：它让代码可以写成order.customer或customer.orders。它不会代替数据库外键，也不是数据库中的新列。
简化理解：
customer_id保存关系；ForeignKey约束关系；relationship()方便Python代码使用关系。
六、一对多双向关系
父对象的一方：
orders: Mapped[list["Order"]] = relationship(
back_populates="customer",
cascade="all, delete-orphan",
)
子对象的一方：
customer: Mapped[Customer] = relationship(back_populates="orders")
back_populates明确指出两个属性互为反向关系。当执行下面的代码时，SQLAlchemy能够维护两端对象的一致性：
customer.orders.append(order)
6.1 cascade的含义
示例中的cascade="all, delete-orphan"表示：
- 保存客户时，可以级联保存订单集合中的新订单；
- 订单从所属客户的集合中移除且不再属于其他父对象时，可以将其删除。
级联删除有数据副作用，生产项目中必须结合业务规则决定，不能看到一对多就固定照抄。
七、DTO是否是查询要件
DTO不是联表查询的强制要求。SQLAlchemy可以返回：
- 完整ORM实体；
- 多个ORM实体组成的行；
- 指定字段组成的
Row； - 自己构造的
dataclass、普通类或字典。
本课使用不可变dataclass定义DTO：
@dataclass(frozen=True)
class OrderSummaryDTO:
order_no: str
customer_name: str
amount: Decimal
DTO适合下列场景：
- 页面列表只需要少数字段；
- 返回结果来自多张表，无法自然归属于单个实体；
- 统计、分组和报表查询；
- 希望隔离数据库模型与对外接口模型。
DTO不应该调用session.add()进行持久化，因为它只是查询结果载体，不是ORM映射实体。
八、显式联表查询
statement = (
select(Order.order_no, Customer.customer_name, Order.amount)
.join(Customer, Order.customer_id == Customer.id)
.where(Customer.customer_code.like("ORM-C-%"))
.order_by(Order.order_no)
)
rows = session.execute(statement).all()
执行顺序可以按SQL理解：
select()决定返回哪些列；join()决定关联哪张表以及关联条件；where()限制数据范围；order_by()决定结果顺序；session.execute()执行语句；all()取得全部结果。
这里没有使用字符串拼接，SQLAlchemy会把Python值绑定为SQL参数。
九、N+1查询问题
N+1查询是指：先用1条SQL查询N个客户，随后为了读取每名客户的订单，又追加N条SQL，总计执行N+1条查询。
直接访问延迟加载的关系可能出现这个问题：
customers = session.scalars(select(Customer)).all()
for customer in customers:
print(customer.orders)
本课使用selectinload()预加载：
statement = select(Customer).options(selectinload(Customer.orders))
它通常先查询客户，再使用一条带IN条件的SQL批量查询这些客户的订单，不会为每名客户分别查询一次。
常见关系加载策略还有joinedload()，它通过连接查询加载关系。集合关系使用连接加载时可能扩大结果行数，因此本课先掌握更直观的selectinload()。
十、聚合查询
statement = (
select(Customer.customer_name, func.count(Order.id))
.join(Order, Customer.id == Order.customer_id)
.group_by(Customer.id, Customer.customer_name)
)
func.count()会生成SQL的COUNT()，group_by()生成GROUP BY。统计工作由数据库完成，Python只接收统计结果，不应先查询全部订单再在内存中计数。
十一、Repository与事务边界
Repository（仓储）负责封装数据访问细节，例如查询客户、查询订单摘要。Service（业务服务）负责组织业务流程和决定事务成功或失败。
推荐的职责划分：
Service或调用方：开始事务 → 调用多个Repository方法 → 提交或回滚
Repository：执行查询、增加、修改、删除 → 不擅自commit
这与Java项目中@Transactional通常放在Service层的思路一致。如果每个Repository方法都自行提交，那么一个跨多个数据操作的业务事务就会被割裂。
本课标准示例没有为了展示分层而增加大量类，但其中的数据访问函数都不调用commit()，事务由session_factory.begin()统一管理。
十二、完整示例
本课完整示例位于：
relationship_query_example.py
示例包含：
Customer与Order双向关系；- 外键和级联配置；
- 可重复执行的数据初始化；
- ORM关系对象查询；
- DTO显式联表查询；
- 分组聚合查询；
- 配置异常和数据库异常的分类处理。
十三、安装与配置
如果python-test环境已经安装上一课依赖，不需要重复安装。可以先确认：
conda activate python-test
python -c "import sqlalchemy, psycopg; print(sqlalchemy.__version__); print(psycopg.__version__)"
如未安装，推荐使用当前解释器对应的pip：
python -m pip install -r requirements.txt
也可以使用conda安装：
conda install -c conda-forge sqlalchemy psycopg
注意：激活python-test后不要再写-n base，否则会尝试修改无权限的公共base环境。
复制配置模板：
Copy-Item config.example.toml config.toml
随后只修改本地config.toml。该文件已由项目.gitignore忽略，不使用环境变量，也不要把真实密码写入config.example.toml或Python代码。
十四、运行方法与预期结果
进入本课目录：
cd D:\Code\Python\04_数据库\4_4_SQLAlchemy关系映射与工程实践
conda activate python-test
python relationship_query_example.py
正常情况下会看到类似结果：
关系对象查询：
张三
ORM-O-001|金额：299.00
ORM-O-002|金额：99.00
李四
ORM-O-003|金额：599.00
DTO联表查询：
ORM-O-001|张三|金额：299.00
ORM-O-002|张三|金额：99.00
ORM-O-003|李四|金额：599.00
聚合查询：
张三|订单数量：2
李四|订单数量：1
数据库自动生成的主键可能继续增长，这是序列的正常行为，不代表练习数据发生重复。
十五、关键代码执行顺序
- 读取本地TOML配置；
- 创建
Engine和连接池； - 创建
sessionmaker； create_all()创建不存在的练习表；- 在一个事务中清理本课前缀数据并重新新增；
- 在独立Session中查询关系对象；
- 执行联表查询并构造DTO；
- 执行分组统计；
- 关闭Session并释放Engine连接池。
十六、常见错误
16.1 只写relationship而不写ForeignKey
SQLAlchemy通常需要外键判断两张表如何关联。relationship()不能代替数据库外键。
16.2 Session关闭后触发延迟加载
关系数据尚未加载就关闭Session，之后访问customer.orders可能出现对象已脱离Session的错误。应在Session有效期间使用关系，或提前预加载并转换成DTO。
16.3 循环中产生N+1查询
查询列表后逐个访问延迟加载集合，会产生大量SQL。列表场景应根据需要使用selectinload()或明确的联表DTO查询。
16.4 对DTO执行session.add
只有继承声明式基类并完成表映射的ORM实体才能持久化。DTO没有表映射，只负责传输数据。
16.5 Repository内部随意commit
这会破坏上层业务事务。Repository可以执行flush()以提前同步SQL，但是否提交应由事务调用方决定。
16.6 删除父记录时违反外键约束
需要先删除子记录，或明确配置数据库/ORM级联规则。级联策略必须符合业务要求。
十七、课堂练习
练习要求位于practice.py。你需要独立完成“课程分类—课程”一对多模型，并实现：
- 关系对象查询；
- DTO联表查询；
- 分类课程数量统计；
- 可重复运行的数据初始化；
- 清晰的事务边界。
本课不在练习文件中提供代码骨架。需要帮助时，可以先询问具体概念或把已完成部分交给我验证。
十八、本课小结
ForeignKey负责数据库约束，relationship()负责对象导航；- SQLAlchemy既能查询完整关联实体，也能显式联表并构造DTO；
- DTO不是强制要求，但非常适合列表、报表和跨表结果；
selectinload()可以避免常见的N+1查询；- 聚合应尽量交给数据库完成；
- Repository负责数据访问，事务边界通常由Service或调用方管理。
十九、验收标准
- 能解释
ForeignKey与relationship()的区别； - 能建立一对多双向关系；
- 能使用关联属性查询子对象集合；
- 能使用
join()查询多张表； - 能把指定列转换成DTO；
- 能使用
selectinload()预加载集合； - 能完成分组统计；
- 程序连续运行两次结果一致且没有重复练习数据；
- 配置保存在被Git忽略的本地TOML中。