SQLAlchemy关联对象模式实战：订单与商品的多对多关系实现

最新推荐文章于 2025-06-15 15:45:43 发布

原创最新推荐文章于 2025-06-15 15:45:43 发布 · 346 阅读

CC 4.0 BY-SA版权

SQLAlchemy关联对象模式实战：订单与商品的多对多关系实现

sqlalchemy THIS IS NOT THE OFFICIAL REPO - PLEASE SUBMIT PRs ETC AT: http://github.com/sqlalchemy/sqlalchemy 项目地址: https://gitcode.com/gh_mirrors/sql/sqlalchemy

关联对象模式概述

在数据库设计中，多对多关系是一种常见的关系类型。SQLAlchemy提供了关联对象模式(Association Object Pattern)来处理这种关系，特别是当关联本身需要携带额外数据时。本文将通过一个订单系统的示例，详细讲解如何使用SQLAlchemy实现这种模式。

模型定义

基础模型

首先定义三个核心模型：Order(订单)、Item(商品)和OrderItem(订单项关联表)。

from sqlalchemy import Column, Integer, String, Float, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

订单模型(Order)

class Order(Base):
    __tablename__ = "order"
    
    order_id = Column(Integer, primary_key=True)
    customer_name = Column(String(30), nullable=False)
    order_date = Column(DateTime, nullable=False, default=datetime.now())
    order_items = relationship(
        "OrderItem", 
        cascade="all, delete-orphan", 
        backref="order"
    )

关键点：

order_items关系定义了与OrderItem的一对多关系
cascade="all, delete-orphan"确保当从订单中移除订单项时，关联记录会被删除
backref="order"在OrderItem模型中创建反向引用

商品模型(Item)

class Item(Base):
    __tablename__ = "item"
    
    item_id = Column(Integer, primary_key=True)
    description = Column(String(30), nullable=False)
    price = Column(Float, nullable=False)

这是一个简单的商品模型，包含商品ID、描述和价格。

订单项关联模型(OrderItem)

class OrderItem(Base):
    __tablename__ = "orderitem"
    
    order_id = Column(Integer, ForeignKey("order.order_id"), primary_key=True)
    item_id = Column(Integer, ForeignKey("item.item_id"), primary_key=True)
    price = Column(Float, nullable=False)
    
    item = relationship(Item, lazy="joined")

关键点：

使用复合主键(order_id和item_id)来唯一标识每个关联
price字段存储交易时的实际价格，可能与商品的标准价格不同
item关系使用lazy="joined"实现立即加载，提高查询效率

实际应用示例

初始化数据库

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
session = Session(engine)

创建商品目录

tshirt, mug, hat, crowbar = (
    Item("SA T-Shirt", 10.99),
    Item("SA Mug", 6.50),
    Item("SA Hat", 8.99),
    Item("MySQL Crowbar", 16.99),
)
session.add_all([tshirt, mug, hat, crowbar])
session.commit()

创建订单并添加商品

order = Order("john smith")

# 添加订单项，可以指定自定义价格
order.order_items.append(OrderItem(mug))  # 使用商品标准价格
order.order_items.append(OrderItem(crowbar, 10.99))  # 指定折扣价格
order.order_items.append(OrderItem(hat))

session.add(order)
session.commit()

查询订单信息

order = session.query(Order).filter_by(customer_name="john smith").one()
print([
    (order_item.item.description, order_item.price)
    for order_item in order.order_items
])

复杂查询示例

查找购买了"MySQL Crowbar"且享受了折扣的客户：

q = session.query(Order).join(OrderItem).join(Item)
q = q.filter(
    and_(Item.description == "MySQL Crowbar", Item.price > OrderItem.price)
)
print([order.customer_name for order in q])