Relacje wiele do wielu

Many to Many relationshipmiędzy dwiema tabelami uzyskuje się poprzez dodanie tabeli asocjacyjnej, która ma dwa klucze obce - po jednym z klucza podstawowego każdej tabeli. Ponadto klasy odwzorowujące te dwie tabele mają atrybut ze zbiorem obiektów innych tabel asocjacyjnych przypisany jako drugorzędny atrybut funkcji relations ().

W tym celu stworzymy bazę danych SQLite (mycollege.db) z dwiema tabelami - dział i pracownik. W tym przypadku zakładamy, że pracownik jest częścią więcej niż jednego działu, a dział ma więcej niż jednego pracownika. To stanowi relację wiele do wielu.

Definicja klas Pracownik i Dział odwzorowanych na dział i tabelę pracowników jest następująca -

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

Teraz zdefiniujemy klasę Link. Jest on powiązany z tabelą linków i zawiera atrybuty id_działów i identyfikator_pracownika odnoszące się odpowiednio do kluczy podstawowych działu i tabeli pracowników.

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)

W tym miejscu należy zaznaczyć, że klasa Department ma atrybut workers powiązany z klasą Employee. Drugorzędnemu atrybutowi funkcji relacji przypisano łącze jako wartość.

Podobnie klasa Employee ma atrybut działów powiązany z klasą działu. Drugorzędnemu atrybutowi funkcji relacji przypisano łącze jako wartość.

Wszystkie te trzy tabele są tworzone po wykonaniu poniższej instrukcji -

Base.metadata.create_all(engine)

Konsola Pythona emituje następujące zapytania 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)
)

Możemy to sprawdzić, otwierając mycollege.db za pomocą SQLiteStudio, jak pokazano na zrzutach ekranu podanych poniżej -

Następnie tworzymy trzy obiekty klasy Dział i trzy obiekty klasy Pracownik, jak pokazano poniżej -

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

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

Każda tabela ma atrybut kolekcji z metodą append (). Możemy dodawać obiekty Employee do kolekcji Employees obiektu Department. Podobnie możemy dodać obiekty działu do atrybutu kolekcji działów obiektów pracownika.

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

Wszystko, co musimy teraz zrobić, to skonfigurować obiekt sesji, dodać do niego wszystkie obiekty i zatwierdzić zmiany, jak pokazano poniżej -

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

Następujące instrukcje SQL zostaną wyemitowane na konsoli Pythona -

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

Aby sprawdzić efekt powyższych operacji, użyj SQLiteStudio i przejrzyj dane w tabelach działu, pracowników i linków -

Aby wyświetlić dane, uruchom następującą instrukcję zapytania -

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

Zgodnie z danymi podanymi w naszym przykładzie, dane wyjściowe zostaną wyświetlone jak poniżej -

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