多対多の関係

2つのテーブル間の多対多の関係*は、2つの外部キー（各テーブルの主キーから1つ）を持つように関連付けテーブルを追加することで実現されますさらに、2つのテーブルにマッピングするクラスには、relationship（）関数の2次属性として割り当てられた他の関連付けテーブルのオブジェクトのコレクションを持つ属性があります。

この目的のために、部門と従業員という2つのテーブルを持つSQLiteデータベース（mycollege.db）を作成します。ここでは、従業員は複数の部門の一部であり、部門には複数の従業員がいると想定しています。これは、多対多の関係を構成します。

部門および従業員テーブルにマッピングされる従業員および部門クラスの定義は次のとおりです-

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属性が含まれています。それぞれ、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属性があることに注意する必要があります。関係関数の2次属性には、値としてリンクが割り当てられます。

同様に、Employeeクラスには、Departmentクラスに関連するdepartments属性があります。関係関数の2次属性には、値としてリンクが割り当てられます。

これらの3つのテーブルはすべて、次のステートメントが実行されるときに作成されます-

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クラスの3つのオブジェクトとEmployeeクラスの3つのオブジェクトを作成します-

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コレクションに追加できます。同様に、Employeeオブジェクトのdepartmentsコレクション属性にDepartmentオブジェクトを追加できます。

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を使用して、部門、従業員、およびリンクテーブルのデータを表示します-

部門テーブルデータ

従業員テーブルデータ

リンクテーブルデータ

データを表示するには、次のクエリステートメントを実行します-

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

Sqlalchemy-orm-many-to-many-relationships

多対多の関係