Sqlalchemy 简明教程

Many to Many Relationships

两个表之间的 Many to Many relationship 通过添加一个关联表来实现，关联表有两个外键 - 一个来自每个表的主键。此外，映射到两个表的类具有一个属性，该属性具有一个集合对象的其他关联表被指定为 relationship() 函数的辅助属性。

Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table’s primary key. Moreover, classes mapping to the two tables have an attribute with a collection of objects of other association tables assigned as secondary attribute of relationship() function.

为此，我们将在 SQLite 数据库 (mycollege.db) 中创建两个表 - 部门和员工。这里，我们假设一个员工属于多个部门，并且一个部门有多个员工。这构成了多对多关系。

For this purpose, we shall create a SQLite database (mycollege.db) with two tables - department and employee. Here, we assume that an employee is a part of more than one department, and a department has more than one employee. This constitutes many-to-many relationship.

映射到部门和员工表的员工类和部门类的定义如下：

Definition of Employee and Department classes mapped to department and employee table is as follows −

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

class Department(Base):
   __tablename__ = 'department'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   employees = relationship('Employee', secondary = 'link')

class Employee(Base):
   __tablename__ = 'employee'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   departments = relationship(Department,secondary='link')

现对 Link 类执行定义。其与关联表相连，并且分别包含分别引用部门键和员工表的主键的 department_id 和 employee_id 属性。

We now define a Link class. It is linked to link table and contains department_id and employee_id attributes respectively referencing to primary keys of department and employee table.

class Link(Base):
   __tablename__ = 'link'
   department_id = Column(
      Integer,
      ForeignKey('department.id'),
      primary_key = True)

employee_id = Column(
   Integer,
   ForeignKey('employee.id'),
   primary_key = True)

在此，我们必须注意，Department 类具有与 Employee 类相关的 employee 属性。关联功能的次要属性被分配为链接作为其值。

Here, we have to make a note that Department class has employees attribute related to Employee class. The relationship function’s secondary attribute is assigned a link as its value.

相似地，Employee 类具有与 Department 类相关的 department 属性。关联功能的次要属性被分配为链接作为其值。

Similarly, Employee class has departments attribute related to Department class. The relationship function’s secondary attribute is assigned a link as its value.

当执行以下语句时，将创建所有这三个表：

All these three tables are created when the following statement is executed −

Base.metadata.create_all(engine)

Python 控制台发出以下 CREATE TABLE 查询：

The Python console emits following CREATE TABLE queries −

CREATE TABLE department (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE employee (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE link (
   department_id INTEGER NOT NULL,
   employee_id INTEGER NOT NULL,
   PRIMARY KEY (department_id, employee_id),
   FOREIGN KEY(department_id) REFERENCES department (id),
   FOREIGN KEY(employee_id) REFERENCES employee (id)
)

我们可以通过使用 SQLiteStudio 打开 mycollege.db 来检查这一点，如下所示：

We can check this by opening mycollege.db using SQLiteStudio as shown in the screenshots given below −

department table
employee table
link table

接下来，我们创建 Department 类的三个对象和 Employee 类的三个对象，如下所示：

Next we create three objects of Department class and three objects of Employee class as shown below −

d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")

e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")

每个表都有具有 append() 方法的集合属性。我们可以将 Employee 对象添加到 Department 对象的 Employees 集合中。相似地，我们可以将 Department 对象添加到 Employee 对象的 department 集合属性中。

Each table has a collection attribute having append() method. We can add Employee objects to Employees collection of Department object. Similarly, we can add Department objects to departments collection attribute of Employee objects.

e1.departments.append(d1)
e2.departments.append(d3)
d1.employees.append(e3)
d2.employees.append(e2)
d3.employees.append(e1)
e3.departments.append(d2)

接下来，我们需要做的就是设置一个会话对象，将所有对象添加到其中并提交修改，如下所示：

All we have to do now is to set up a session object, add all objects to it and commit the changes as shown below −

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(e1)
session.add(e2)
session.add(d1)
session.add(d2)
session.add(d3)
session.add(e3)
session.commit()

在 Python 控制台中会发出以下 SQL 语句：

Following SQL statements will be emitted on Python console −

INSERT INTO department (name) VALUES (?) ('Accounts',)
INSERT INTO department (name) VALUES (?) ('Sales',)
INSERT INTO department (name) VALUES (?) ('Marketing',)
INSERT INTO employee (name) VALUES (?) ('John',)
INSERT INTO employee (name) VALUES (?) ('Graham',)
INSERT INTO employee (name) VALUES (?) ('Tony',)
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3))
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))

要检查上述操作的影响，请使用 SQLiteStudio 并查看部门、员工和链接表中的数据：

To check the effect of above operations, use SQLiteStudio and view data in department, employee and link tables −

department table data
employee table data
link table data

要显示数据，请运行以下查询语句：

To display the data, run the following query statement −

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for x in session.query( Department, Employee).filter(Link.department_id == Department.id,
   Link.employee_id == Employee.id).order_by(Link.department_id).all():
   print ("Department: {} Name: {}".format(x.Department.name, x.Employee.name))

根据我们示例中填充的数据，输出将如下所述显示：

As per the data populated in our example, output will be displayed as below −

Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: Tony