Plusieurs à plusieurs relations

Many to Many relationshipentre deux tables est obtenu en ajoutant une table d'association de telle sorte qu'elle ait deux clés étrangères - une de la clé primaire de chaque table. De plus, les classes mappant aux deux tables ont un attribut avec une collection d'objets d'autres tables d'association assignées comme attribut secondaire de la fonction Relationship ().

Pour cela, nous allons créer une base de données SQLite (mycollege.db) avec deux tables - department et employee. Ici, nous supposons qu'un employé fait partie de plus d'un service et qu'un service a plus d'un employé. Cela constitue une relation plusieurs-à-plusieurs.

La définition des classes d'employé et de service mappées à la table de service et d'employé est la suivante:

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

Nous définissons maintenant une classe Link. Il est lié à la table link et contient les attributs department_id et employee_id faisant respectivement référence aux clés primaires de la table department et employee.

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)

Ici, nous devons noter que la classe Department a un attribut employés lié à la classe Employee. L'attribut secondaire de la fonction de relation reçoit un lien comme valeur.

De même, la classe Employee a un attribut department lié à la classe Department. L'attribut secondaire de la fonction de relation reçoit un lien comme valeur.

Tous ces trois tableaux sont créés lorsque l'instruction suivante est exécutée -

Base.metadata.create_all(engine)

La console Python émet les requêtes CREATE TABLE suivantes -

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

Nous pouvons vérifier cela en ouvrant mycollege.db en utilisant SQLiteStudio comme indiqué dans les captures d'écran ci-dessous -

Ensuite, nous créons trois objets de classe Department et trois objets de classe Employee comme indiqué ci-dessous -

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

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

Chaque table a un attribut de collection ayant la méthode append (). Nous pouvons ajouter des objets Employee à la collection Employees de l'objet Department. De même, nous pouvons ajouter des objets Department à l'attribut de collection Department des objets 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)

Tout ce que nous avons à faire maintenant est de configurer un objet de session, d'y ajouter tous les objets et de valider les modifications comme indiqué ci-dessous -

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

Les instructions SQL suivantes seront émises sur la console 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))

Pour vérifier l'effet des opérations ci-dessus, utilisez SQLiteStudio et affichez les données dans les tables de service, d'employé et de lien -

Pour afficher les données, exécutez l'instruction de requête suivante -

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

Selon les données renseignées dans notre exemple, la sortie sera affichée comme ci-dessous -

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