Nhiều mối quan hệ

Many to Many relationshipgiữa hai bảng có thể đạt được bằng cách thêm một bảng liên kết sao cho nó có hai khóa ngoại - một từ khóa chính của mỗi bảng. Hơn nữa, các lớp ánh xạ tới hai bảng có một thuộc tính với tập hợp các đối tượng của các bảng kết hợp khác được gán làm thuộc tính phụ của hàm quan hệ ().

Với mục đích này, chúng ta sẽ tạo một cơ sở dữ liệu SQLite (mycollege.db) với hai bảng - phòng ban và nhân viên. Ở đây, chúng tôi giả định rằng một nhân viên là một bộ phận của nhiều bộ phận và một bộ phận có nhiều hơn một nhân viên. Điều này tạo thành mối quan hệ nhiều-nhiều.

Định nghĩa của các lớp Nhân viên và Phòng ban được ánh xạ tới phòng ban và bảng nhân viên như sau:

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

Bây giờ chúng ta định nghĩa một lớp Liên kết. Nó được liên kết với bảng liên kết và chứa các thuộc tính Department_id và worker_id tương ứng tham chiếu đến các khóa chính của phòng ban và bảng nhân viên.

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)

Ở đây, chúng ta phải lưu ý rằng lớp Phòng ban có thuộc tính nhân viên liên quan đến lớp nhân viên. Thuộc tính phụ của hàm quan hệ được gán một liên kết làm giá trị của nó.

Tương tự, lớp Nhân viên có thuộc tính phòng ban liên quan đến lớp Phòng ban. Thuộc tính phụ của hàm quan hệ được gán một liên kết làm giá trị của nó.

Tất cả ba bảng này được tạo khi câu lệnh sau được thực thi:

Base.metadata.create_all(engine)

Bảng điều khiển Python phát ra các truy vấn CREATE TABLE sau:

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

Chúng tôi có thể kiểm tra điều này bằng cách mở mycollege.db bằng SQLiteStudio như được hiển thị trong ảnh chụp màn hình được cung cấp bên dưới:

Tiếp theo, chúng ta tạo ba đối tượng của lớp Phòng ban và ba đối tượng của lớp Nhân viên như hình dưới đây:

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

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

Mỗi bảng có một thuộc tính collection có phương thức append (). Chúng ta có thể thêm các đối tượng Nhân viên vào bộ sưu tập Nhân viên của đối tượng Bộ phận. Tương tự, chúng ta có thể thêm đối tượng Phòng ban vào thuộc tính bộ sưu tập phòng ban của đối tượng Nhân viên.

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

Tất cả những gì chúng ta phải làm bây giờ là thiết lập một đối tượng phiên, thêm tất cả các đối tượng vào nó và thực hiện các thay đổi như hình dưới đây -

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

Các câu lệnh SQL sau sẽ được phát ra trên bảng điều khiển 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))

Để kiểm tra ảnh hưởng của các thao tác trên, hãy sử dụng SQLiteStudio và xem dữ liệu trong các bảng liên kết, nhân viên và phòng ban -

Để hiển thị dữ liệu, hãy chạy câu lệnh truy vấn sau:

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

Theo dữ liệu được điền trong ví dụ của chúng tôi, đầu ra sẽ được hiển thị như bên dưới:

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