Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 37 ยท Relationships and Loading

Many-to-Many Relationships and Association Objects

Model many-to-many links in SQLAlchemy with a secondary table, then upgrade to an association object when the link needs its own columns. Runnable examples.

Students take many courses and each course has many students. Posts have many tags and each tag marks many posts. A relational database cannot store this directly in either table, so it uses a third table with two foreign keys: the association table. SQLAlchemy gives you two ways to use it, and picking the right one depends on one question: does the link itself carry data?

Plain many-to-many with secondary

If the link has no data of its own, you declare the association table with Core's Table and pass it to relationship(secondary=...). You never create a Python class for it. The ORM inserts and deletes rows in the association table for you as you add and remove items from the collections.

Python Posts and tags with secondary

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The composite primary key on the association table (post_id, tag_id) is important: it stops the same pair from being stored twice. Adding the same tag to a post twice would raise an IntegrityError at flush time.

erDiagram
    POST ||--o{ POST_TAG : has
    TAG ||--o{ POST_TAG : has
    POST { int id PK
           string title }
    TAG { int id PK
          string name }
    POST_TAG { int post_id PK, FK
               int tag_id PK, FK }

Two one-to-many links meeting in the middle make a many-to-many.

Because the relationship knows the join path, filtering is short:

Python
# Posts tagged "sql"
select(Post).where(Post.tags.any(Tag.name == "sql"))

# Posts that have ALL of two tags: two EXISTS conditions
select(Post).where(
    Post.tags.any(Tag.name == "sql"),
    Post.tags.any(Tag.name == "python"),
)

# Tag usage counts via an explicit join
from sqlalchemy import func
select(Tag.name, func.count(Post.id)).join(Tag.posts).group_by(Tag.name)

Suppose an enrollment has a grade and a date. That data belongs to the pair (student, course), so it needs a column on the association table, and secondary cannot expose it. The fix is to promote the association table to a full mapped class with two many-to-one relationships. This is called the association object pattern.

Python Enrollment as an association object

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The cost is one more hop: to list Maya's courses you go through enrollment.course. You can hide the hop with an association proxy.

association_proxy: hiding the middle object

sqlalchemy.ext.associationproxy.association_proxy creates a shortcut attribute that reads through the association object. The classic use is a list of plain values that behaves like a list of strings while really storing child rows.

Python association_proxy shortcuts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Which one should I use?

Situation Use
Just "A is related to B", no extra columns secondary= table
The link has attributes (grade, quantity, created_at, role) association object
The link has attributes now or might soon association object (switching later means a migration and code changes)
You want list-of-values ergonomics association object plus association_proxy

Do not mix the two patterns on the same pair of tables with writable relationships. If you map the association table as a class and use it as secondary=, both paths will try to insert the same row. If you need both, mark the secondary one viewonly=True.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the secondary argument of relationship() take?

    secondary points at the association table. SQLAlchemy inserts and deletes its rows when you change the collections.
  2. 2. When must you use an association object instead of secondary?

    A secondary table cannot expose extra columns. Mapping the association table as a class gives you attributes on the link.
  3. 3. Why does the association table usually have a composite primary key of both foreign keys?

    A pair-based primary key makes duplicate links impossible at the database level.
  4. 4. What does association_proxy give you?

    It exposes a target attribute of the related objects directly, so you can treat child rows like a list of plain values.

Summary

  • A many-to-many link needs an association table with two foreign keys.
  • relationship(secondary=table) is the simplest form: no extra class, rows maintained automatically.
  • Use an association object (a mapped class with two many-to-one relationships) when the link has its own data.
  • association_proxy hides the middle object when you only care about one attribute of it.
  • A composite primary key on the association table prevents duplicate links.

Next: Loading Strategies