多対多の関係
Many to Many relationship2つのテーブル間は、2つの外部キー（各テーブルの主キーから1つ）を持つように関連付けテーブルを追加することによって実現されます。さらに、2つのテーブルにマッピングされるクラスには、relationship（）関数の2次属性として割り当てられた他の関連付けテーブルのオブジェクトのコレクションを持つ属性があります。
この目的のために、departmentとemployeeの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属性が含まれています。
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コンソールは次のCREATETABLEクエリを発行します-
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（）メソッドを持つコレクション属性があります。DepartmentオブジェクトのEmployeesコレクションにEmployeeオブジェクトを追加できます。同様に、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を使用して、部門、従業員、およびリンクテーブルのデータを表示します-
データを表示するには、次のクエリステートメントを実行します-
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