One-to-Many and Many-to-Many
26 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Tables point at each other. An order belongs to a customer, and a student takes many courses. A foreign key stores the link in the database, and relationship() lets you follow it in Python as a plain attribute.
One customer, many orders
Foreign key is a column whose value must match the primary key of a row in another table. It is how one row points to another.
Each order must say which customer it belongs to. That is a foreign key column on the many side. The relationship() on both classes lets you move between the two objects without writing a join.
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) orders: Mapped[list["Order"]] = relationship(back_populates="customer")
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) customer: Mapped["Customer"] = relationship(back_populates="orders")ForeignKey is the database link. relationship is the Python link. back_populates tells each side the name of the other, so they stay in step.
Use the list, not the id
Relationship is a Python attribute, made by relationship(), that holds the related object or list of objects and keeps them in step with the foreign key.
You do not set customer_id yourself. Add an order to the customer's list, or set the order's customer. SQLAlchemy fills the foreign key when it flushes, and updates the other side at once.
asha = Customer(name="Asha")order = Order(total=250)asha.orders.append(order)print(order.customer is asha)session.add(asha)session.flush()print(order.customer_id)Example 1 · The foreign key is filled for you
from sqlalchemy import create_engine, select, func, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
with Session(engine) as s: asha = Customer(name="Asha") order = Order(total=250) asha.orders.append(order) print(order.customer is asha, order.customer_id) s.add(asha) s.flush() print(order.customer_id) s.commit() print(asha.orders[0].total)True None 1 250
What this code does
- Appending to the list sets
order.customerat once, but the id is still empty. - At the flush, SQLAlchemy copies the customer id into
customer_id.
Example 2 · Delete-orphan removes dropped children
from sqlalchemy import create_engine, select, func, String, ForeignKeyfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
class Customer(Base): __tablename__ = "customer" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) orders: Mapped[list["Order"]] = relationship( back_populates="customer", cascade="all, delete-orphan" )
class Order(Base): __tablename__ = "orders" id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.id")) total: Mapped[int] customer: Mapped["Customer"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
def orders_count(s): return s.scalar(select(func.count()).select_from(Order))
with Session(engine) as s: c = Customer(name="Asha", orders=[Order(total=100), Order(total=200)]) s.add(c) s.commit() print(orders_count(s)) c.orders.pop(0) s.commit() print(orders_count(s)) s.delete(c) s.commit() print(orders_count(s))2 1 0
What this code does
- Removing an order from the list deletes its row, because it has no parent now.
- Deleting the customer deletes the remaining order too.
The order points back at Asha straight away, before any SQL. The cascade all, delete-orphan saves the order with its customer and deletes an order removed from the list.
Many-to-many needs a middle table
Association table is a table with only foreign keys that links two tables in a many-to-many relationship. Each row is one pair.
A student takes many courses, and a course has many students. One foreign key cannot hold that. A third table stores one row for each pair, and secondary= names it.
enrolment = Table( "enrolment", Base.metadata, Column("student_id", ForeignKey("student.id"), primary_key=True), Column("course_id", ForeignKey("course.id"), primary_key=True),)
class Student(Base): courses: Mapped[list["Course"]] = relationship( secondary=enrolment, back_populates="students")Example 3 · Many-to-many through an association table
from sqlalchemy import create_engine, select, func, String, ForeignKey, Table, Columnfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
enrolment = Table( "enrolment", Base.metadata, Column("student_id", ForeignKey("student.id"), primary_key=True), Column("course_id", ForeignKey("course.id"), primary_key=True),)
class Student(Base): __tablename__ = "student" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) courses: Mapped[list["Course"]] = relationship(secondary=enrolment, back_populates="students")
class Course(Base): __tablename__ = "course" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] = mapped_column(String(60)) students: Mapped[list["Student"]] = relationship(secondary=enrolment, back_populates="courses")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
with Session(engine) as s: py, sql = Course(title="Python"), Course(title="SQL") asha = Student(name="Asha", courses=[py, sql]) ravi = Student(name="Ravi", courses=[sql]) s.add_all([asha, ravi]) s.commit() print([c.title for c in asha.courses]) print([st.name for st in sql.students]) print(s.scalar(select(func.count()).select_from(enrolment)))['Python', 'SQL'] ['Asha', 'Ravi'] 3
What this code does
- Both sides see the same links, because
back_populatesjoins them. - Three pairs were stored in the association table: two for Asha and one for Ravi.
Example 4 · Query across the link with join
from sqlalchemy import create_engine, select, func, String, ForeignKey, Table, Columnfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
class Base(DeclarativeBase): pass
enrolment = Table( "enrolment", Base.metadata, Column("student_id", ForeignKey("student.id"), primary_key=True), Column("course_id", ForeignKey("course.id"), primary_key=True),)
class Student(Base): __tablename__ = "student" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(String(50)) courses: Mapped[list["Course"]] = relationship(secondary=enrolment, back_populates="students")
class Course(Base): __tablename__ = "course" id: Mapped[int] = mapped_column(primary_key=True) title: Mapped[str] = mapped_column(String(60)) students: Mapped[list["Student"]] = relationship(secondary=enrolment, back_populates="courses")
engine = create_engine("sqlite://")Base.metadata.create_all(engine)
with Session(engine) as s: py, sql = Course(title="Python"), Course(title="SQL") s.add_all([Student(name="Asha", courses=[py, sql]), Student(name="Ravi", courses=[sql])]) s.commit() stmt = ( select(Student.name, func.count(Course.id)) .join(Student.courses) .group_by(Student.name) .order_by(Student.name) ) print([tuple(r) for r in s.execute(stmt)])[('Asha', 2), ('Ravi', 1)]What this code does
join(Student.courses)uses the relationship to build the join through the association table.- The database counts the courses of each student, so no objects need loading.
The two primary keys together make the pair unique, so a student cannot join the same course twice. If the link needs its own data, such as a join date, make it a full model instead.
Next step
Check your understanding
11 quick questions