Relationships in Databases in Python0%

Relationships in Databases in Python

Advanced8 min read•Updated: 2026-10-01

Relationships in Databases with SQLAlchemy

Relational database systems gain their expressive power from table associations: One-to-Many (1:N), Many-to-One (N:1), Many-to-Many (M:N), and One-to-One (1:1). In SQLAlchemy 2.0, relationships are configured using ForeignKey constraints paired with high-level relationship() property descriptors, enabling bidirectional navigation and automated cascading lifecycles.

1. One-to-Many & Many-to-One Relationships

Consider a standard domain model: an Author writes multiple Articles.

┌────────────────────────┐                   ┌────────────────────────┐
│         Author         │ 1               N │        Article         │
├────────────────────────┤───────────────────├────────────────────────┤
│ id: int (PK)           │                   │ id: int (PK)           │
│ name: str              │                   │ title: str             │
│ articles: List[Article]│◄─ back_populates ─┤ author_id: int (FK)    │
└────────────────────────┘                   │ author: Author         │
                                             └────────────────────────┘

The Role of `back_populates`

Always use back_populates instead of legacy backref. back_populates explicitly documents the relationship on both classes, providing full IDE autocompletion, static type checking, and clean bidirectional synchronization.

2. Many-to-Many Relationships (M:N)

In a Many-to-Many association (e.g. Students enrolled in multiple Courses), relational databases require an intermediate Association Table containing foreign keys referencing both primary keys:

┌──────────────┐         ┌─────────────────────────┐         ┌──────────────┐
│   Student    │ 1     N │   student_course_link   │ N     1 │    Course    │
├──────────────┤─────────├─────────────────────────┤─────────├──────────────┤
│ id (PK)      │         │ student_id (FK) (PK)    │         │ id (PK)      │
│ name         │         │ course_id (FK) (PK)     │         │ title        │
└──────────────┘         └─────────────────────────┘         └──────────────┘

3. The N+1 Query Problem & Eager Loading Strategies

By default, SQLAlchemy loads relationship collections lazily—it issues a separate SELECT query only when the attribute is first accessed in Python:

1# The N+1 Problem:
2# 1 query to fetch 100 authors
3authors = session.scalars(select(Author)).all()
4for a in authors:
5    # 100 additional queries executed sequentially! Total: 101 queries!
6    print(a.articles)

Solving with Eager Loading (`selectinload` & `joinedload`)

The sqlalchemy.orm module provides relationship loaders to eliminate the N+1 problem:

selectinload: Issues a single SELECT ... WHERE id IN (...) query to fetch all related items in bulk. (Best for 1:N collections).
joinedload: Emits an SQL LEFT OUTER JOIN to fetch both parent and child rows in one query. (Best for N:1 and 1:1 scalar references).

1from sqlalchemy.orm import selectinload
2 
3# Solved: Exactly 2 queries executed total, regardless of author count
4stmt = select(Author).options(selectinload(Author.articles))
5authors = session.scalars(stmt).all()
6 
7for a in authors:
8    print(f"Author: {a.name} -> Articles: {[art.title for art in a.articles]}")

4. Cascading Deletions: `all, delete-orphan`

Configuring cascade="all, delete-orphan" ensures database cleanliness:

1
When an Author is deleted, all their associated Article records are deleted automatically.
2
If an Article is removed from author.articles.remove(art), the orphaned article record is deleted from the database instead of lingering with a NULL foreign key.

5. Architectural Summary Table

Relationship	Prerequisite	SQLAlchemy Syntax	Recommended Eager Loader
One-to-Many (1:N)	`ForeignKey` on child table	`relationship(back_populates="...", cascade="all, delete-orphan")`	`selectinload()`
Many-to-One (N:1)	`ForeignKey` on this table	`relationship(back_populates="...")`	`joinedload()`
Many-to-Many (M:N)	Association `Table`	`relationship(secondary=link_table, back_populates="...")`	`selectinload()`
One-to-One (1:1)	Unique `ForeignKey`	`Mapped[Child] = relationship(...)` (scalar type)	`joinedload()`

⚠️ Common Advanced Mistakes & Gotchas

1. The N+1 Query Problem

Iterating over child relationships in a loop with default lazy loading executes 1 query for the parent plus N separate queries for the children. Use selectinload() or joinedload() to eager-load relationships in bulk.

2. Dangling Foreign Keys

When deleting parent records, configure cascade="all, delete-orphan" on relationships to prevent orphaned foreign keys in child tables.

💻 Try It Yourself: One-to-Many Relationship with Cascading Delete

Scenario

Model an Author with many Book instances in SQLAlchemy. Verify that deleting an author automatically cascades and purges all their associated books.

Complete Solution

1from sqlalchemy import create_engine, ForeignKey, select
2from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
3 
4class Base(DeclarativeBase):
5    pass
6 
7class Author(Base):
8    __tablename__ = "authors"
9    id: Mapped[int] = mapped_column(primary_key=True)
10    name: Mapped[str]
11    books: Mapped[list["Book"]] = relationship(back_populates="author", cascade="all, delete-orphan")
12 
13class Book(Base):
14    __tablename__ = "books"
15    id: Mapped[int] = mapped_column(primary_key=True)
16    title: Mapped[str]
17    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
18    author: Mapped["Author"] = relationship(back_populates="books")
19 
20engine = create_engine("sqlite:///:memory:")
21Base.metadata.create_all(engine)
22 
23with Session(engine) as session:
24    author = Author(name="Arthur Conan Doyle", books=[Book(title="Study in Scarlet"), Book(title="Sign of Four")])
25    session.add(author)
26    session.commit()
27    
28    # Cascade delete
29    session.delete(author)
30    session.commit()
31    remaining_books = session.scalars(select(Book)).all()
32    print(f"Remaining books after author deletion: {len(remaining_books)}")

Expected Output

1Remaining books after author deletion: 0

Interactive Quiz• Knowledge Check

Practice Quiz

1.

What problem occurs when accessing lazy-loaded relationship attributes inside a loop over $N$ parent objects? A. The N+1 Query Problem: $1$ query fetches the parents, followed by $N$ separate queries fetching each parent's children. B. A DeadlockError on the primary key. C. All records are deleted automatically. D. Sockets are permanently closed.

Answer: A

Explanation:Lazy loading queries child objects on demand upon attribute access. In a loop over $N$ parents, this causes $1 + N$ queries, creating severe database latency.

2.

Which eager loading strategy emits a SELECT ... WHERE parent_id IN (...) query to efficiently load One-to-Many collections in bulk? A. lazyload() B. selectinload() C. subqueryload() D. noload()

Answer: B

Explanation:selectinload() loads related collections using an efficient IN query that loads all child records corresponding to parent primary keys in a single second query.

3.

What is the effect of specifying cascade="all, delete-orphan" on a parent model's relationship()? A. Child objects are deleted when their parent is deleted, and children removed from the parent's collection are also deleted from the database. B. Child objects are converted into JSON strings. C. Deleting the parent is blocked by a ForeignKeyViolation. D. Foreign keys are automatically set to -1.

Answer: A

Explanation:delete-orphan instructs SQLAlchemy to delete child records from the database if their parent is deleted or if they are detached from the parent's relationship collection.

4.

How is a Many-to-Many relationship configured between two models in SQLAlchemy? A. By placing two foreign keys on the same table. B. By defining an intermediate Association Table and referencing it via relationship(secondary=association_table). C. By using Python's multiprocessing.Queue. D. By duplicating all table columns.

Answer: B

Explanation:Many-to-Many relationships require an association table containing foreign keys to both models, passed to the secondary argument of relationship().

5.

Why is back_populates preferred over backref in modern SQLAlchemy 2.0? A. backref only works on PostgreSQL. B. back_populates requires explicit declarations on both participating classes, improving code clarity, IDE autocompletion, and static type checking. C. backref runs slower in SQLite. D. back_populates creates automated database indexes.

Answer: B

Explanation:Explicitly defining relationships on both classes using back_populates ensures strict type annotations and clarity, avoiding the implicit attribute creation caused by legacy backref.

🚀 What's Next?

In the next lesson, we will continue your advanced Python journey with Transactions and Rollbacks (9: Advanced Database Handling).

👉 Continue to Next Lesson: Transactions and Rollbacks →

Next Lesson

Transactions and Rollbacks

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SQLAlchemy ORM Basics	Transactions and Rollbacks

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Transactions and Rollbacks

Relationships in Databases in Python

Advanced8 min read•Updated: 2026-10-01

Study Materials Cheatsheet

Relationships in Databases with SQLAlchemy

1. One-to-Many & Many-to-One Relationships

Consider a standard domain model: an Author writes multiple Articles.

┌────────────────────────┐                   ┌────────────────────────┐
│         Author         │ 1               N │        Article         │
├────────────────────────┤───────────────────├────────────────────────┤
│ id: int (PK)           │                   │ id: int (PK)           │
│ name: str              │                   │ title: str             │
│ articles: List[Article]│◄─ back_populates ─┤ author_id: int (FK)    │
└────────────────────────┘                   │ author: Author         │
                                             └────────────────────────┘

The Role of `back_populates`

2. Many-to-Many Relationships (M:N)

┌──────────────┐         ┌─────────────────────────┐         ┌──────────────┐
│   Student    │ 1     N │   student_course_link   │ N     1 │    Course    │
├──────────────┤─────────├─────────────────────────┤─────────├──────────────┤
│ id (PK)      │         │ student_id (FK) (PK)    │         │ id (PK)      │
│ name         │         │ course_id (FK) (PK)     │         │ title        │
└──────────────┘         └─────────────────────────┘         └──────────────┘

3. The N+1 Query Problem & Eager Loading Strategies

By default, SQLAlchemy loads relationship collections lazily—it issues a separate SELECT query only when the attribute is first accessed in Python:

1# The N+1 Problem:
2# 1 query to fetch 100 authors
3authors = session.scalars(select(Author)).all()
4for a in authors:
5    # 100 additional queries executed sequentially! Total: 101 queries!
6    print(a.articles)

Solving with Eager Loading (`selectinload` & `joinedload`)

The sqlalchemy.orm module provides relationship loaders to eliminate the N+1 problem:

selectinload: Issues a single SELECT ... WHERE id IN (...) query to fetch all related items in bulk. (Best for 1:N collections).
joinedload: Emits an SQL LEFT OUTER JOIN to fetch both parent and child rows in one query. (Best for N:1 and 1:1 scalar references).

1from sqlalchemy.orm import selectinload
2 
3# Solved: Exactly 2 queries executed total, regardless of author count
4stmt = select(Author).options(selectinload(Author.articles))
5authors = session.scalars(stmt).all()
6 
7for a in authors:
8    print(f"Author: {a.name} -> Articles: {[art.title for art in a.articles]}")

4. Cascading Deletions: `all, delete-orphan`

Configuring cascade="all, delete-orphan" ensures database cleanliness:

1
When an Author is deleted, all their associated Article records are deleted automatically.
2
If an Article is removed from author.articles.remove(art), the orphaned article record is deleted from the database instead of lingering with a NULL foreign key.

5. Architectural Summary Table

Relationship	Prerequisite	SQLAlchemy Syntax	Recommended Eager Loader
One-to-Many (1:N)	`ForeignKey` on child table	`relationship(back_populates="...", cascade="all, delete-orphan")`	`selectinload()`
Many-to-One (N:1)	`ForeignKey` on this table	`relationship(back_populates="...")`	`joinedload()`
Many-to-Many (M:N)	Association `Table`	`relationship(secondary=link_table, back_populates="...")`	`selectinload()`
One-to-One (1:1)	Unique `ForeignKey`	`Mapped[Child] = relationship(...)` (scalar type)	`joinedload()`

⚠️ Common Advanced Mistakes & Gotchas

1. The N+1 Query Problem

2. Dangling Foreign Keys

When deleting parent records, configure cascade="all, delete-orphan" on relationships to prevent orphaned foreign keys in child tables.

💻 Try It Yourself: One-to-Many Relationship with Cascading Delete

Scenario

Model an Author with many Book instances in SQLAlchemy. Verify that deleting an author automatically cascades and purges all their associated books.

Complete Solution

1from sqlalchemy import create_engine, ForeignKey, select
2from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
3 
4class Base(DeclarativeBase):
5    pass
6 
7class Author(Base):
8    __tablename__ = "authors"
9    id: Mapped[int] = mapped_column(primary_key=True)
10    name: Mapped[str]
11    books: Mapped[list["Book"]] = relationship(back_populates="author", cascade="all, delete-orphan")
12 
13class Book(Base):
14    __tablename__ = "books"
15    id: Mapped[int] = mapped_column(primary_key=True)
16    title: Mapped[str]
17    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
18    author: Mapped["Author"] = relationship(back_populates="books")
19 
20engine = create_engine("sqlite:///:memory:")
21Base.metadata.create_all(engine)
22 
23with Session(engine) as session:
24    author = Author(name="Arthur Conan Doyle", books=[Book(title="Study in Scarlet"), Book(title="Sign of Four")])
25    session.add(author)
26    session.commit()
27    
28    # Cascade delete
29    session.delete(author)
30    session.commit()
31    remaining_books = session.scalars(select(Book)).all()
32    print(f"Remaining books after author deletion: {len(remaining_books)}")

Expected Output

1Remaining books after author deletion: 0

Interactive Quiz• Knowledge Check

Practice Quiz

1.

Answer: A

Explanation:Lazy loading queries child objects on demand upon attribute access. In a loop over $N$ parents, this causes $1 + N$ queries, creating severe database latency.

2.

Answer: B

Explanation:selectinload() loads related collections using an efficient IN query that loads all child records corresponding to parent primary keys in a single second query.

3.

Answer: A

Explanation:delete-orphan instructs SQLAlchemy to delete child records from the database if their parent is deleted or if they are detached from the parent's relationship collection.

4.

Answer: B

Explanation:Many-to-Many relationships require an association table containing foreign keys to both models, passed to the secondary argument of relationship().

5.

Answer: B

Explanation:Explicitly defining relationships on both classes using back_populates ensures strict type annotations and clarity, avoiding the implicit attribute creation caused by legacy backref.

🚀 What's Next?

In the next lesson, we will continue your advanced Python journey with Transactions and Rollbacks (9: Advanced Database Handling).

👉 Continue to Next Lesson: Transactions and Rollbacks →

Next Lesson

Transactions and Rollbacks

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SQLAlchemy ORM Basics	Transactions and Rollbacks

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Relationships in Databases with SQLAlchemy

1. One-to-Many & Many-to-One Relationships

The Role of back_populates

2. Many-to-Many Relationships (M:N)

3. The N+1 Query Problem & Eager Loading Strategies

Solving with Eager Loading (selectinload & joinedload)

4. Cascading Deletions: all, delete-orphan

5. Architectural Summary Table

⚠️ Common Advanced Mistakes & Gotchas

1. The N+1 Query Problem

2. Dangling Foreign Keys

💻 Try It Yourself: One-to-Many Relationship with Cascading Delete

Scenario

Complete Solution

Expected Output

Practice Quiz

1.

2.

3.

4.

5.

🚀 What's Next?

Transactions and Rollbacks

Related Lessons

Practice Quiz

Loading Page Content...

Relationships in Databases with SQLAlchemy

1. One-to-Many & Many-to-One Relationships

The Role of back_populates

2. Many-to-Many Relationships (M:N)

3. The N+1 Query Problem & Eager Loading Strategies

Solving with Eager Loading (selectinload & joinedload)

4. Cascading Deletions: all, delete-orphan

5. Architectural Summary Table

⚠️ Common Advanced Mistakes & Gotchas

1. The N+1 Query Problem

2. Dangling Foreign Keys

💻 Try It Yourself: One-to-Many Relationship with Cascading Delete

Scenario

Complete Solution

Expected Output

Practice Quiz

1.

2.

3.

4.

5.

🚀 What's Next?

Transactions and Rollbacks

Related Lessons

Practice Quiz

The Role of `back_populates`

Solving with Eager Loading (`selectinload` & `joinedload`)

4. Cascading Deletions: `all, delete-orphan`

The Role of `back_populates`

Solving with Eager Loading (`selectinload` & `joinedload`)

4. Cascading Deletions: `all, delete-orphan`