Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 37 ยท Relationships and Loading

One-to-Many and One-to-One Relationships

Connect ORM classes with relationship(), ForeignKey and back_populates. Build one-to-many and one-to-one links, navigate them as Python attributes and see the SQL.

A database links rows with foreign keys. Your Python code wants to write author.books and book.author. The ORM's relationship() is the bridge: you declare the foreign key once on the "many" side, and SQLAlchemy gives both classes a navigable attribute that loads and saves the related rows for you.

This lesson covers the two simplest shapes, one-to-many (an author has many books) and one-to-one (a user has one profile). Every example runs for real in your browser, against an in-memory SQLite database.

The foreign key and the relationship are two different things

A ForeignKey is a database constraint: it lives on the table and is checked by the database. A relationship() is an ORM feature: it is a Python attribute that knows how to join the two tables. You normally need both. The foreign key sits on the child (the "many" side). The relationship attributes sit on both classes and point at each other with back_populates.

flowchart LR
    A["Author<br/>id, name<br/>books: list of Book"] -- "one" --> B["Book<br/>id, title, author_id (FK)<br/>author: Author"]
    B -. "many" .-> A

The foreign key column lives on Book. Both classes carry a relationship attribute so you can walk the link in either direction.

A one-to-many example

With the typed 2.0 style, the annotation tells SQLAlchemy the shape: Mapped[list["Book"]] is a collection, Mapped["Author"] is a single object.

Python Author and books

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.

You never set author_id yourself. When you append a Book to ada.books, the session works out the foreign key at flush time and fills it in.

back_populates keeps both sides in sync

back_populates="author" on one side and back_populates="books" on the other tell SQLAlchemy that these two attributes are the same link seen from opposite ends. Change one and the other updates in memory, before any SQL is sent.

Python Both sides stay in sync

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.

Mapped[int | None] makes the column nullable, so a player may have no team. Without | None the column is NOT NULL and a player must have one.

If you forget back_populates on one side, the two attributes become two independent relationships. Both will try to write the same foreign key, and SQLAlchemy warns about overlapping relationships. Always name both ends.

Viewing the SQL

Set echo=True on the engine to see what a relationship does. Reading book.author for the first time triggers one extra SELECT ... FROM author WHERE author.id = ?. This is lazy loading, covered in Loading Strategies.

One-to-one

A one-to-one relationship is a one-to-many with a rule: only one child per parent. In the typed style you annotate the parent attribute as a single Mapped["Profile"] instead of a list (the older spelling is uselist=False). Add unique=True on the foreign key so the database enforces the rule too.

Python User and Profile (one-to-one)

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.

Because User.profile is annotated as a single object, SQLAlchemy does not wrap it in a list. Assigning a new profile to u.profile replaces the old one (the old row's foreign key is set to NULL, or the row is deleted if you use a delete cascade, see Cascades).

Declaring the join when it is ambiguous

If two tables are linked by more than one foreign key, SQLAlchemy cannot guess which one a relationship should use. Pass foreign_keys=[...] to say which column drives it. A message might have both a sender_id and a recipient_id pointing at user_account:

Python
class Message(Base):
    __tablename__ = "message"
    id: Mapped[int] = mapped_column(primary_key=True)
    sender_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    recipient_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))

    sender: Mapped["User"] = relationship(foreign_keys=[sender_id])
    recipient: Mapped["User"] = relationship(foreign_keys=[recipient_id])

There is no back_populates here, so the relationships are one-directional. That is fine when you only ever navigate from the message.

Querying across a relationship

You can filter on the related table without writing the join condition by hand. Use join() on the relationship attribute, or has() and any():

Python
# Authors who wrote at least one book with "SQL" in the title
select(Author).join(Author.books).where(Book.title.contains("SQL")).distinct()

# The same idea using EXISTS
select(Author).where(Author.books.any(Book.title.contains("SQL")))

# Books whose author is named Ada (scalar side uses has())
select(Book).where(Book.author.has(Author.name == "Ada"))

any() is for collections and has() is for single objects. Both produce an EXISTS subquery, which never duplicates rows.

Common mistakes

  • Putting the ForeignKey on the wrong side. It always goes on the "many" table.
  • Writing ForeignKey("Author.id") instead of "author.id". It takes table.column as named in the database.
  • Forgetting that relationship() does not create columns. Without the author_id column nothing can be saved.
  • Mutating a collection and expecting the database to change before commit(). Nothing is written until the session flushes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. On which table does the ForeignKey column belong in a one-to-many relationship?

    Each child row stores the id of its parent, so the foreign key column lives on the 'many' table (Book.author_id).
  2. 2. What does back_populates do?

    back_populates names the matching attribute on the other class. Changing one side updates the other before any SQL runs.
  3. 3. How do you make a relationship one-to-one in the typed style?

    A single-object annotation stops SQLAlchemy from using a list, and the unique constraint makes the database enforce one child per parent.
  4. 4. Which query shape finds Authors that have at least one matching Book without duplicating authors?

    any() is for collections and compiles to an EXISTS subquery. has() is its counterpart for single-object relationships.

Summary

  • The foreign key is a database constraint on the child table. relationship() is the Python attribute built on top of it.
  • Use back_populates on both classes so each side mirrors the other.
  • Annotate collections as Mapped[list["Child"]] and single objects as Mapped["Parent"]; add | None for nullable links.
  • One-to-one is a one-to-many with a single-object annotation and a unique foreign key.
  • Pass foreign_keys=[...] when two tables are joined by more than one key.
  • any() and has() filter across a relationship with EXISTS.

Next: Many-to-Many Relationships