Отношения многие ко многим

Соотношение «многие ко многим» между двумя таблицами достигается путем добавления таблицы ассоциации так, чтобы у нее было два внешних ключа — по одному от первичного ключа каждой таблицы. Кроме того, классы, отображаемые на две таблицы, имеют атрибут с набором объектов других таблиц ассоциаций, назначенный в качестве вторичного атрибута функции Relations ().

Для этого мы создадим базу данных 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. Вторичному атрибуту функции отношения назначается ссылка в качестве значения.

Аналогично, класс Employee имеет атрибут отделов, связанный с классом Department. Вторичному атрибуту функции отношения назначается ссылка в качестве значения.

Все эти три таблицы создаются при выполнении следующего оператора:

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, как показано на скриншотах ниже:

Затем мы создаем три объекта класса Department и три объекта класса Employee, как показано ниже:

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 в коллекцию Employees объекта Department. Точно так же мы можем добавить объекты Department в атрибут коллекции департаментов объектов Employee.

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

Согласно данным, приведенным в нашем примере, вывод будет отображаться как показано ниже: