Many to Many dating among tables is accomplished by including an affiliation table such that it has two overseas keys - one from every table’s primary key. Moreover, classes mapping to the 2 tables have an characteristic with a group of items of different affiliation tables assigned as secondary characteristic of dating() function.
For this purpose, we will create a SQLite database (mycollege.Db) with tables - branch and employee. Here, we anticipate that an worker is part of a couple of branch, and a department has more than one worker. This constitutes many-to-many courting.
Definition of Employee and Department instructions mapped to branch and employee table is as follows −
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')
We now define a Link elegance. It is linked to hyperlink table and carries department_id and employee_id attributes respectively referencing to number one keys of department and worker desk.
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)
Here, we need to make a be aware that Department magnificence has personnel attribute associated with Employee class. The relationship feature’s secondary characteristic is assigned a link as its fee.
Similarly, Employee magnificence has departments characteristic associated with Department elegance. The relationship function’s secondary characteristic is assigned a hyperlink as its fee.
All these 3 tables are created when the following assertion is performed −
Base.metadata.create_all(engine)
The Python console emits following CREATE TABLE queries −
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)
)
We can check this by using commencing mycollege.Db the usage of SQLiteStudio as proven within the screenshots given below −
Next we create three gadgets of Department magnificence and three objects of Employee elegance as proven under −
d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")
e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")
Each desk has a set characteristic having append() method. We can upload Employee gadgets to Employees series of Department item. Similarly, we will upload Department items to departments series attribute of Employee gadgets.
e1.departments.append(d1)
e2.departments.append(d3)
d1.employees.append(e3)
d2.employees.append(e2)
d3.employees.append(e1)
e3.departments.append(d2)
All we should do now could be to set up a session item, add all objects to it and commit the adjustments as shown beneath −
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()
Following SQL statements might be emitted on Python console −
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))
To take a look at the impact of above operations, use SQLiteStudio and consider records in department, employee and link tables −
To show the information, run the subsequent question statement −
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))
As in line with the records populated in our instance, output may be displayed as beneath −
Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: Tony