Python Academy · Lesson

Relationships and Joins

Model related tables.

Relationships and Joins is a free Python Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Related Tables

Real data is connected: an author has many books, an order has many items. SQLAlchemy models these links with relationships and foreign keys.

# Author 1 --- many --- Book
# A foreign key on Book points back to Author
print('One-to-many is the most common link')

Foreign Keys

A foreign key column stores the primary key of a related row. Declare it with ForeignKey('table.column').

from sqlalchemy import ForeignKey
from sqlalchemy.orm import Mapped, mapped_column
# author_id: Mapped[int] = mapped_column(ForeignKey('authors.id'))
print('ForeignKey links child to parent')

Defining the Parent

The parent model uses relationship() to hold a collection of children.

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from typing import List
class Base(DeclarativeBase):
    pass
class Author(Base):
    __tablename__ = 'authors'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    books: Mapped[List['Book']] = relationship(back_populates='author')
print('Author.books holds related Book objects')

Defining the Child

The child model has the foreign key column plus a relationship() pointing back to the parent.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
    pass
class Book(Base):
    __tablename__ = 'books'
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey('authors.id'))
    author: Mapped['Author'] = relationship(back_populates='books')
print('Book.author points to its parent')

back_populates

back_populates ties the two relationships together so that updating one side automatically reflects on the other.

# author.books.append(book) also sets book.author = author
# Both sides stay in sync automatically
print('back_populates keeps both sides consistent')

Navigating Relationships

Once linked, you traverse the relationship like normal attributes: author.books is a list, book.author is the parent object.

# for book in author.books:
#     print(book.title)
# print(book.author.name)
print('Navigate with plain attribute access')

Building a Graph

Append children to a parent's collection. Adding the parent to the session cascades to the children.

# a = Author(name='Rowling')
# a.books.append(Book(title='Book 1'))
# session.add(a)
# session.commit()  # author and book both saved
print('Cascade saves the whole object graph')

Implicit Joins

Accessing book.author triggers SQLAlchemy to load the related row automatically (lazy loading) behind the scenes.

# Accessing book.author runs a SELECT if not loaded yet
# This is lazy loading
print('Lazy loading fetches related rows on access')

Explicit Joins

For filtering across tables, use join() in a select. This generates a SQL JOIN.

from sqlalchemy import select
# stmt = select(Book).join(Author).where(Author.name == 'Rowling')
# books = session.scalars(stmt).all()
print('join(Author) filters across the relationship')

Eager Loading

To avoid many small queries, load related rows up front with joinedload or selectinload.

from sqlalchemy import select
from sqlalchemy.orm import selectinload
# stmt = select(Author).options(selectinload(Author.books))
print('selectinload preloads children in one extra query')

Many-to-Many

For many-to-many links, an association table connects both sides. The relationship() uses secondary= to point at it.

# students <-> courses via a 'enrollments' association table
# courses: Mapped[List['Course']] = relationship(secondary=enrollments)
print('secondary= models many-to-many')

Quick Check

Test your relationship knowledge.

Recap

You modeled related tables.

ForeignKey stores the link on the child column
relationship() with back_populates connects both sides
Navigate links as plain attributes; lazy loading fetches on access
join() filters across tables; selectinload loads eagerly; secondary= handles many-to-many

Frequently asked questions

Is the “Relationships and Joins” lesson free?

Yes — the full text of “Relationships and Joins” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.

What will I learn in “Relationships and Joins”?

Model related tables. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Python Academy?

No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Relationships and Joins” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Python Academy lesson?

Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Python Academy