Sqlalchemy 简明教程

SQLAlchemy ORM - Building Relationship

本会话描述了如何创建另一个表，该表与我们数据库中已存在的表相关。 customers 表包含客户的主数据。我们现在需要创建一个 invoices 表，该表可能拥有属于某个客户的任意数量的发票。这是一个一对多关系的案例。

This session describes creation of another table which is related to already existing one in our database. The customers table contains master data of customers. We now need to create invoices table which may have any number of invoices belonging to a customer. This is a case of one to many relationships.

使用声明式，我们按照下面给出的方式定义此表及其映射类 Invoices：

Using declarative, we define this table along with its mapped class, Invoices as given below −

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

class Invoice(Base):
   __tablename__ = 'invoices'

   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")
Base.metadata.create_all(engine)

这会向 SQLite 引擎发送一个 CREATE TABLE 查询，如下所示：

This will send a CREATE TABLE query to SQLite engine as below −

CREATE TABLE invoices (
   id INTEGER NOT NULL,
   custid INTEGER,
   invno INTEGER,
   amount INTEGER,
   PRIMARY KEY (id),
   FOREIGN KEY(custid) REFERENCES customers (id)
)

我们可以借助 SQLiteStudio 工具检查是否已在 sales.db 中创建新表。

We can check that new table is created in sales.db with the help of SQLiteStudio tool.

salesdb new table

Invoices 类对 custid 属性应用 ForeignKey 结构。此指令表明此列中的值应被限制为 customers 表中 id 列中的现有值。这是关系数据库的核心特性，并且是将不连接的表集合转换成为具有丰富的重叠关系的“粘合剂”。

Invoices class applies ForeignKey construct on custid attribute. This directive indicates that values in this column should be constrained to be values present in id column in customers table. This is a core feature of relational databases, and is the “glue” that transforms unconnected collection of tables to have rich overlapping relationships.

另一个称为 relationship() 的指令告诉 ORM 应使用属性 Invoice.customer 将 Invoice 类链接到 Customer 类。relationship() 使用两个表之间的外键关系来确定此链接的性质，并确定该关系是一对多的关系。

A second directive, known as relationship(), tells the ORM that the Invoice class should be linked to the Customer class using the attribute Invoice.customer. The relationship() uses the foreign key relationships between the two tables to determine the nature of this linkage, determining that it is many to one.

另一个 relationship() 指令会被放置在 Customer 映射类的 Customer.invoices 属性下。relationship.back_populates 参数被分配为引用互补的属性名称，以便每个 relationship() 能够就以相反的方式表达的相同关系做出明智的决策。一方是 Invoices.customer 引用 Invoices 实例，另一方是 Customer.invoices 引用 Customers 实例列表。

An additional relationship() directive is placed on the Customer mapped class under the attribute Customer.invoices. The parameter relationship.back_populates is assigned to refer to the complementary attribute names, so that each relationship() can make intelligent decision about the same relationship as expressed in reverse. On one side, Invoices.customer refers to Invoices instance, and on the other side, Customer.invoices refers to a list of Customers instances.

该关系函数是 SQLAlchemy ORM 包的 Relationship API 的一部分。它提供两个映射类之间的关系。这对应于父-子或关联表关系。

The relationship function is a part of Relationship API of SQLAlchemy ORM package. It provides a relationship between two mapped classes. This corresponds to a parent-child or associative table relationship.

以下是发现的基本关系模式−

Following are the basic Relationship Patterns found −

One To Many

一对多的关系是指通过子表上的外键来引用父表的关系。relationship() 然后指定在父表上，作为引用子表中一堆项的集合。relationship.back_populates 参数用于在一对多关系中建立双向关系，其中“反向”侧是多对一关系。

A One to Many relationship refers to parent with the help of a foreign key on the child table. relationship() is then specified on the parent, as referencing a collection of items represented by the child. The relationship.back_populates parameter is used to establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one.

Many To One

另一方面，多对一关系将外键放在父表中来引用子表。relationship() 在父表上进行声明，在那里将创建一个新的标量持有属性。这里同样使用 relationship.back_populates 参数实现双向行为。

On the other hand, Many to One relationship places a foreign key in the parent table to refer to the child. relationship() is declared on the parent, where a new scalar-holding attribute will be created. Here again the relationship.back_populates parameter is used for Bidirectionalbehaviour.

One To One

一对一关系本质上就是双向关系。uselist 标志指示在关系的“多”侧放置标量属性而不是集合。要将一对多转换为一对一关系类型，请将 uselist 参数设置为 false。

One To One relationship is essentially a bidirectional relationship in nature. The uselist flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship. To convert one-to-many into one-to-one type of relation, set uselist parameter to false.

Many To Many

多对多关系是通过添加与两个类相关的关联表（通过定义具有外键的属性）来建立的。它由 relationship() 的第二个参数指示。通常，表使用与声明基类关联的元数据对象，以便 ForeignKey 指令可以找到要链接的远程表。每个 relationship() 的 relationship.back_populates 参数建立双向关系。该关系的两侧都包含一个集合。

Many to Many relationship is established by adding an association table related to two classes by defining attributes with their foreign keys. It is indicated by the secondary argument to relationship(). Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link. The relationship.back_populates parameter for each relationship() establishes a bidirectional relationship. Both sides of the relationship contain a collection.