Viele zu viele Beziehungen

Many to Many relationshipZwischen zwei Tabellen wird erreicht, indem eine Zuordnungstabelle hinzugefügt wird, die zwei Fremdschlüssel enthält - einen aus dem Primärschlüssel jeder Tabelle. Darüber hinaus haben Klassen, die den beiden Tabellen zugeordnet sind, ein Attribut mit einer Sammlung von Objekten anderer Zuordnungstabellen, die als sekundäres Attribut der Beziehung () zugewiesen sind.

Zu diesem Zweck erstellen wir eine SQLite-Datenbank (mycollege.db) mit zwei Tabellen - Abteilung und Mitarbeiter. Hier nehmen wir an, dass ein Mitarbeiter Teil von mehr als einer Abteilung ist und eine Abteilung mehr als einen Mitarbeiter hat. Dies ist eine Viele-zu-Viele-Beziehung.

Die Definition der Mitarbeiter- und Abteilungsklassen, die der Abteilungs- und Mitarbeitertabelle zugeordnet sind, lautet wie folgt:

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

Wir definieren jetzt eine Link-Klasse. Es ist mit der Verknüpfungstabelle verknüpft und enthält die Attribute department_id und employee_id, die auf Primärschlüssel der Abteilung und der Mitarbeitertabelle verweisen.

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)

Hier müssen wir notieren, dass die Abteilungsklasse Mitarbeiterattribute hat, die sich auf die Mitarbeiterklasse beziehen. Dem sekundären Attribut der Beziehungsfunktion wird ein Link als Wert zugewiesen.

In ähnlicher Weise verfügt die Employee-Klasse über Abteilungsattribute, die sich auf die Abteilungsklasse beziehen. Dem sekundären Attribut der Beziehungsfunktion wird ein Link als Wert zugewiesen.

Alle diese drei Tabellen werden erstellt, wenn die folgende Anweisung ausgeführt wird:

Base.metadata.create_all(engine)

Die Python-Konsole gibt folgende CREATE TABLE-Abfragen aus:

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

Wir können dies überprüfen, indem wir mycollege.db mit SQLiteStudio öffnen, wie in den folgenden Screenshots gezeigt -

Als nächstes erstellen wir drei Objekte der Abteilungsklasse und drei Objekte der Mitarbeiterklasse, wie unten gezeigt -

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

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

Jede Tabelle verfügt über ein Auflistungsattribut mit der Methode append (). Wir können Mitarbeiterobjekte zur Mitarbeitersammlung des Abteilungsobjekts hinzufügen. Ebenso können wir Abteilungsobjekte zum Abteilungssammlungsattribut von Mitarbeiterobjekten hinzufügen.

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

Jetzt müssen wir nur noch ein Sitzungsobjekt einrichten, alle Objekte hinzufügen und die Änderungen wie unten gezeigt festschreiben.

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

Die folgenden SQL-Anweisungen werden auf der Python-Konsole ausgegeben:

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

Verwenden Sie SQLiteStudio, um die Auswirkungen der oben genannten Vorgänge zu überprüfen und Daten in Abteilungs-, Mitarbeiter- und Verknüpfungstabellen anzuzeigen.

Führen Sie die folgende Abfrageanweisung aus, um die Daten anzuzeigen:

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

Gemäß den in unserem Beispiel angegebenen Daten wird die Ausgabe wie folgt angezeigt:

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