หลายต่อหลายความสัมพันธ์

Many to Many relationshipระหว่างสองตารางทำได้โดยการเพิ่มตารางการเชื่อมโยงเพื่อให้มีคีย์ต่างประเทศสองคีย์ - หนึ่งคีย์จากคีย์หลักของแต่ละตาราง ยิ่งไปกว่านั้นการแมปคลาสกับสองตารางยังมีแอ็ตทริบิวต์ที่มีคอลเลกชันของอ็อบเจ็กต์ของตารางการเชื่อมโยงอื่น ๆ ที่กำหนดให้เป็นแอ็ตทริบิวต์รองของฟังก์ชันความสัมพันธ์ ()

เพื่อจุดประสงค์นี้เราจะสร้างฐานข้อมูล 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')

ตอนนี้เรากำหนดคลาสลิงค์ มีการเชื่อมโยงกับตารางลิงก์และมีแอตทริบิวต์ 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)

ที่นี่เราต้องจดบันทึกไว้ว่าแผนกชั้นมีคุณลักษณะของพนักงานที่เกี่ยวข้องกับระดับพนักงาน แอตทริบิวต์รองของฟังก์ชันความสัมพันธ์ได้รับการกำหนดลิงก์เป็นค่า

ในทำนองเดียวกันระดับพนักงานมีแอตทริบิวต์ของแผนกที่เกี่ยวข้องกับระดับแผนก แอตทริบิวต์รองของฟังก์ชันความสัมพันธ์ได้รับการกำหนดลิงก์เป็นค่า

ตารางทั้งสามนี้ถูกสร้างขึ้นเมื่อดำเนินการคำสั่งต่อไปนี้ -

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

ตรวจสอบได้โดยเปิด mycollege.db โดยใช้ SQLiteStudio ดังที่แสดงในภาพหน้าจอด้านล่าง -

ต่อไปเราจะสร้างออบเจ็กต์สามชิ้นของคลาสแผนกและสามอ็อบเจกต์ของคลาสพนักงานดังที่แสดงด้านล่าง -

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 () เราสามารถเพิ่มวัตถุพนักงานในคอลเลกชันของพนักงานของวัตถุแผนก ในทำนองเดียวกันเราสามารถเพิ่มวัตถุแผนกไปยังแผนกรวบรวมแอตทริบิวต์ของวัตถุพนักงาน

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