sqlalchemy_many_to_many_relationship_with_association_table.py

1from typing import List
2
3from sqlalchemy import Column
4from sqlalchemy import ForeignKey
5from sqlalchemy import Table
6from sqlalchemy.orm import DeclarativeBase
7from sqlalchemy.orm import Mapped
8from sqlalchemy.orm import mapped_column
9from sqlalchemy.orm import relationship
10
11class Base(DeclarativeBase):
12    pass
13
14# association table
15association_table = Table(
16    "association_table",
17    Base.metadata,
18    Column("left_id", ForeignKey("left_table.id"), primary_key=True),
19    Column("right_id", ForeignKey("right_table.id"), primary_key=True),
20)
21
22class Parent(Base):
23    __tablename__ = "left_table"
24
25    id: Mapped[int] = mapped_column(primary_key=True)
26    children: Mapped[List["Child"]] = relationship(
27        secondary=association_table, back_populates="parents"
28    )
29
30class Child(Base):
31    __tablename__ = "right_table"
32
33    id: Mapped[int] = mapped_column(primary_key=True)
34    parents: Mapped[List["Parent"]] = relationship(
35        secondary=association_table, back_populates="children"
36    )