Molte a molte relazioni
Many to Many relationshiptra due tabelle si ottiene aggiungendo una tabella di associazione in modo che abbia due chiavi esterne, una dalla chiave primaria di ciascuna tabella. Inoltre, le classi che mappano alle due tabelle hanno un attributo con una raccolta di oggetti di altre tabelle di associazione assegnati come attributo secondario della funzione relationship ().
A questo scopo, creeremo un database SQLite (mycollege.db) con due tabelle: reparto e dipendente. In questo caso, si presume che un dipendente faccia parte di più di un dipartimento e che un dipartimento abbia più di un dipendente. Ciò costituisce una relazione molti-a-molti.
La definizione delle classi Employee e Department mappate alla tabella Department e Employee è la seguente:
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')
Definiamo ora una classe Link. È collegato alla tabella dei collegamenti e contiene gli attributi department_id e employee_id che fanno rispettivamente riferimento alle chiavi primarie della tabella del reparto e del dipendente.
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)
Qui, dobbiamo prendere nota che la classe Department ha l'attributo Employee correlato alla classe Employee. All'attributo secondario della funzione di relazione viene assegnato un collegamento come valore.
Allo stesso modo, la classe Employee ha l'attributo departments relativo alla classe Department. All'attributo secondario della funzione di relazione viene assegnato un collegamento come valore.
Tutte queste tre tabelle vengono create quando viene eseguita la seguente istruzione:
Base.metadata.create_all(engine)
La console Python emette le seguenti query 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)
)
Possiamo verificarlo aprendo mycollege.db usando SQLiteStudio come mostrato negli screenshot riportati di seguito -
Successivamente creiamo tre oggetti della classe Department e tre oggetti della classe Employee come mostrato di seguito:
d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")
e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")
Ogni tabella ha un attributo di raccolta con il metodo append (). È possibile aggiungere oggetti Employee alla raccolta Employees dell'oggetto Department. Allo stesso modo, possiamo aggiungere oggetti Department all'attributo di raccolta departments degli oggetti 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)
Tutto quello che dobbiamo fare ora è impostare un oggetto di sessione, aggiungervi tutti gli oggetti e confermare le modifiche come mostrato di seguito -
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()
Le seguenti istruzioni SQL verranno emesse sulla 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))
Per verificare l'effetto delle operazioni di cui sopra, utilizzare SQLiteStudio e visualizzare i dati nelle tabelle di reparto, dipendente e collegamento -
Per visualizzare i dati, eseguire la seguente istruzione di query:
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))
Secondo i dati inseriti nel nostro esempio, l'output verrà visualizzato come di seguito:
Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: Tony