Many to Many courting between tables is accomplished with the aid of adding an affiliation desk such that it has two overseas keys - one from each desk’s number one key. Moreover, instructions mapping to the 2 tables have an characteristic with a group of objects of different association tables assigned as secondary attribute of relationship() function.
For this reason, we shall create a SQLite database (mycollege.Db) with two tables - department and employee. Here, we expect that an employee is part of multiple branch, and a department has a couple of worker. This constitutes many-to-many courting.
Definition of Employee and Department classes mapped to department and worker 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 outline a Link magnificence. It is related to hyperlink desk and carries department_id and employee_id attributes respectively referencing to number one keys of branch and employee 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 should make a word that Department class has employees characteristic associated with Employee elegance. The relationship feature’s secondary characteristic is assigned a hyperlink as its price.
Similarly, Employee elegance has departments attribute related to Department elegance. The dating function’s secondary characteristic is assigned a link as its price.
All those 3 tables are created when the following assertion is done −
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 take a look at this by beginning mycollege.Db using SQLiteStudio as proven in the screenshots given underneath −
Next we create three items of Department elegance and three objects of Employee magnificence as proven below −
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 group characteristic having append() method. We can add Employee items to Employees series of Department object. Similarly, we are able to upload Department objects to departments series attribute of Employee items.
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 ought to do now's to installation a session object, add all items to it and commit the changes as proven underneath −
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 could 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 test the impact of above operations, use SQLiteStudio and consider information in department, employee and hyperlink tables −
To show the records, run the following question announcement −
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 step with the information populated in our example, output will 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