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

Many to Many relationshipмежду двумя таблицами достигается путем добавления таблицы ассоциаций, в которой есть два внешних ключа - по одному от первичного ключа каждой таблицы. Более того, классы, отображаемые в две таблицы, имеют атрибут с набором объектов других ассоциативных таблиц, назначенный как вторичный атрибут функции 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')

Теперь мы определяем класс 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 имеет атрибут отделов, связанный с классом отдела. Вторичному атрибуту функции отношений в качестве значения присваивается ссылка.

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

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

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

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