5 to 5 Relationships in SQLAlchemy

Key Concepts

• Many-to-Many Relationships
• Association Tables
• Backref and Back_Populates
• Cascading Operations
• Querying Relationships

Many-to-Many Relationships

Many-to-many relationships occur when multiple records in one table are associated with multiple records in another table. In SQLAlchemy, this is typically managed using an association table that links the two main tables.

Association Tables

An association table is a middleman table that contains foreign keys pointing to the primary keys of the two related tables. This table does not have its own meaningful data but serves to connect the two main tables.

from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

association_table = Table('association', Base.metadata,
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Left(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    rights = relationship("Right", secondary=association_table, back_populates="lefts")

class Right(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    lefts = relationship("Left", secondary=association_table, back_populates="rights")
    

Backref and Back_Populates

The backref and back_populates arguments in the relationship function are used to define the bidirectional relationship between the two tables. backref automatically creates a back reference in the related class, while back_populates explicitly defines the relationship on both sides.

class Left(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    rights = relationship("Right", secondary=association_table, back_populates="lefts")

class Right(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    lefts = relationship("Left", secondary=association_table, back_populates="rights")
    

Cascading Operations

Cascading operations allow you to define how changes in one table should affect related records in another table. For example, when a record in the "left" table is deleted, you can cascade the deletion to all related records in the "right" table.

class Left(Base):
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    rights = relationship("Right", secondary=association_table, back_populates="lefts", cascade="all, delete")

class Right(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    lefts = relationship("Left", secondary=association_table, back_populates="rights", cascade="all, delete")
    

Querying Relationships

Querying many-to-many relationships involves joining the main tables through the association table. SQLAlchemy provides powerful query tools to filter and retrieve related records efficiently.

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

left_records = session.query(Left).all()
for left in left_records:
    print(f"Left ID: {left.id}")
    for right in left.rights:
        print(f"  Right ID: {right.id}")