多對多關係

---

---

兩個表之間的多對多關係是透過新增一個關聯表來實現的，該關聯表有兩個外部索引鍵——一個來自每個表的主鍵。此外，對映到這兩個表的類具有一個屬性，該屬性包含來自其他關聯表的物件的集合，這些物件被分配為relationship()函式的 secondary 屬性。

為此，我們將建立一個 SQLite 資料庫 (mycollege.db)，其中包含兩個表——department 和 employee。在這裡，我們假設一個員工可以屬於多個部門，一個部門可以有多個員工。這構成了多對多關係。

對映到 department 和 employee 表的 Employee 和 Department 類的定義如下：

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 類。它連結到 link 表，分別包含 department_id 和 employee_id 屬性，這些屬性分別引用 department 和 employee 表的主鍵。

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 類相關的 employees 屬性。relationship 函式的 secondary 屬性被分配了一個連結作為其值。

類似地，Employee 類具有與 Department 類相關的 departments 屬性。relationship 函式的 secondary 屬性被分配了一個連結作為其值。

執行以下語句時將建立所有這三個表：

Base.metadata.create_all(engine)

Python 控制檯會發出以下 CREATE TABLE 查詢：

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 來檢查這一點，如下面的螢幕截圖所示：

接下來，我們建立三個 Department 類的物件和三個 Employee 類的物件，如下所示：

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 物件的 departments 集合屬性中。

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

現在我們要做的就是設定一個會話物件，將所有物件新增到其中並提交更改，如下所示：

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()

以下 SQL 語句將在 Python 控制檯上發出：

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 並檢視 department、employee 和 link 表中的資料：

要顯示資料，請執行以下查詢語句：

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))

根據我們示例中填充的資料，輸出將顯示如下：

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