Many to Many Relationships

Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table’s primary key. Moreover, classes mapping to the two tables have an attribute with a collection of objects of other association tables assigned as secondary attribute of relationship() function.

For this purpose, we shall create a SQLite database (mycollege.db) with two tables - department and employee. Here, we assume that an employee is a part of more than one department, and a department has more than one employee. This constitutes many-to-many relationship.

Definition of Employee and Department classes mapped to department and employee table is as follows −

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine( sqpte:///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 =  pnk )
   
class Employee(Base):
   __tablename__ =  employee 
   id = Column(Integer, primary_key = True)
   name = Column(String)
   departments = relationship(Department,secondary= pnk )

We now define a Link class. It is pnked to pnk table and contains department_id and employee_id attributes respectively referencing to primary keys of department and employee table.

class Link(Base):
   __tablename__ =  pnk 
   department_id = Column(
      Integer, 
      ForeignKey( department.id ), 
      primary_key = True)

employee_id = Column(
   Integer, 
   ForeignKey( employee.id ), 
   primary_key = True)

Here, we have to make a note that Department class has employees attribute related to Employee class. The relationship function’s secondary attribute is assigned a pnk as its value.

Similarly, Employee class has departments attribute related to Department class. The relationship function’s secondary attribute is assigned a pnk as its value.

All these three tables are created when the following statement is executed −

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 pnk (
   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 opening mycollege.db using SQLiteStudio as shown in the screenshots given below −

Next we create three objects of Department class and three objects of Employee class as shown 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 table has a collection attribute having append() method. We can add Employee objects to Employees collection of Department object. Similarly, we can add Department objects to departments collection attribute of Employee objects.

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 have to do now is to set up a session object, add all objects to it and commit the changes as shown below −

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 will 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 pnk (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3))
INSERT INTO pnk (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))

To check the effect of above operations, use SQLiteStudio and view data in department, employee and pnk tables −

To display the data, run the following query 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 per the data populated in our example, output will be displayed as below −

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