如何使用 Python 和 SQLAlchemy 结合外键映射来获取其他表中的数据

在使用 Python 和 SQLAlchemy 时，结合外键映射可以让你在查询时轻松地获取其他表中的数据。SQLAlchemy 提供了丰富的 ORM（对象关系映射）功能，可以让你通过定义外键关系来查询并获取关联的数据。下面我会演示如何设置外键关系，并通过 SQLAlchemy 查询获取其他表中的数据。

在这里插入图片描述

1、问题背景

在使用 SQLAlchemy 进行对象关系映射时，我们可能需要获取其他表中的数据。例如，我们有一个 Customer 表和一个 Order 表，Customer 表中有 uid、name 和 email 字段，Order 表中有 item_id、item_name 和 customer 字段，customer 字段是 Customer 表的 uid 字段的外键。现在，我们希望从 Order 表中查询订单信息时，同时获取该订单所属客户的姓名和电子邮件地址。

2、解决方案

2.1 双向关系映射

为了实现上述目的，我们需要在 Customer 和 Order 类中分别定义关系属性，使用 relationship() 方法。relationship() 方法的第一个参数指定要映射的类，第二个参数指定反向引用属性。在我们的例子中，Customer 类中的 orders 属性表示该客户的所有订单，Order 类中的 customer 属性表示该订单所属的客户。

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper, relationship, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customers'
    uid = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Cust: %s, Name: %s (Email: %s)" %(self.uid, self.name, self.email)

class Order(Base):
    __tablename__ = 'orders'
    item_id = Column(Integer, primary_key=True)
    item_name = Column(String)
    customer_id = Column(Integer, ForeignKey('customers.uid'))

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Item ID %s: %s, has been ordered by customer no. %s" %(self.item_id, self.item_name, self.customer)

# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

metadata.create_all(engine)
mapper(Customer, customers_table, properties={
    'orders': relationship(Order, backref='customer')
})
mapper(Order, orders_table)

session = Session(engine)
for order in session.query(Order):
    print(order, order.customer)

这样，我们就可以通过 Order 对象获取该订单所属客户的信息。

2.2 单向关系映射

如果我们只需要从 Order 表中获取客户信息，而不需要从 Customer 表中获取订单信息，那么我们可以使用单向关系映射。单向关系映射只需要在 Order 类中定义关系属性，不需要在 Customer 类中定义。

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper, relationship, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customers'
    uid = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Cust: %s, Name: %s (Email: %s)" %(self.uid, self.name, self.email)

class Order(Base):
    __tablename__ = 'orders'
    item_id = Column(Integer, primary_key=True)
    item_name = Column(String)
    customer_id = Column(Integer, ForeignKey('customers.uid'))

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Item ID %s: %s, has been ordered by customer no. %s" %(self.item_id, self.item_name, self.customer)

# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

metadata.create_all(engine)
mapper(Customer, customers_table)
mapper(Order, orders_table, properties={
    'customer': relationship(Customer)
})

session = Session(engine)
for order in session.query(Order):
    print(order, order.customer)

这样，我们就可以通过 Order 对象获取该订单所属客户的信息，但不能通过 Customer 对象获取该客户的所有订单。

2.3 添加另一个外键

如果我们需要在 Order 表中添加另一个外键，例如 product_id 字段，并且希望获取该订单所属产品的信息，那么我们可以在 Order 类中定义一个新的关系属性，使用 relationship() 方法。relationship() 方法的第一个参数指定要映射的类，第二个参数指定反向引用属性。在我们的例子中，Order 类中的 product 属性表示该订单所属的产品。

from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import mapper, relationship, Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customers'
    uid = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Cust: %s, Name: %s (Email: %s)" %(self.uid, self.name, self.email)

class Order(Base):
    __tablename__ = 'orders'
    item_id = Column(Integer, primary_key=True)
    item_name = Column(String)
    customer_id = Column(Integer, ForeignKey('customers.uid'))
    product_id = Column(Integer, ForeignKey('products.pid'))

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Item ID %s: %s, has been ordered by customer no. %s" %(self.item_id, self.item_name, self.customer)

class Product(Base):
    __tablename__ = 'products'
    pid = Column(Integer, primary_key=True)
    product_name = Column(String)

    def __repr__(self):
        return str(self)

    def __str__(self):
        return "Product ID %s: %s" %(self.pid, self.product_name)

# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()

metadata.create_all(engine)
mapper(Customer, customers_table, properties={
    'orders': relationship(Order, backref='customer')
})
mapper(Order, orders_table, properties={
    'customer': relationship(Customer),
    'product': relationship(Product)
})
mapper(Product, products_table)

session = Session(engine)
for order in session.query(Order):
    print(order, order.customer, order.product)