Relationships (one-to-many) in Flask

Introduction

A one-to-many relationship connects one item to many others. It helps organize related data clearly.

You want to link a blog author to many blog posts.

You need to connect a customer to multiple orders.

You want to store a category with many products.

You want to track a teacher with many students.

Syntax

Flask

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Child', back_populates='parent')

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id'))
    parent = db.relationship('Parent', back_populates='children')

db.relationship defines the link from one side to many.

db.ForeignKey sets the connection on the many side.

Examples

Author has many posts. Each post links back to one author.

Flask

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    posts = db.relationship('Post', back_populates='author')

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
    author = db.relationship('Author', back_populates='posts')

Category holds many products. Each product belongs to one category.

Flask

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    products = db.relationship('Product', back_populates='category')

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category', back_populates='products')

Sample Program

This example creates an author and two posts linked to that author. It then prints the author's name and the titles of their posts.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    posts = db.relationship('Post', back_populates='author')

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
    author = db.relationship('Author', back_populates='posts')

with app.app_context():
    db.create_all()

    # Create an author
    author = Author(name='Alice')
    db.session.add(author)
    db.session.commit()

    # Create posts linked to author
    post1 = Post(title='First Post', author=author)
    post2 = Post(title='Second Post', author=author)
    db.session.add_all([post1, post2])
    db.session.commit()

    # Print author and their posts
    print(f"Author: {author.name}")
    for post in author.posts:
        print(f"- Post: {post.title}")

Important Notes

Always define db.relationship on the 'one' side and db.ForeignKey on the 'many' side.

Use back_populates to keep both sides connected and easy to access.

Remember to create and commit your database session to save changes.

Summary

One-to-many links one item to many related items.

Use db.relationship and db.ForeignKey together.

This helps organize and access related data easily.

Practice

1. What does a one-to-many relationship in Flask SQLAlchemy represent?

easy

A. One record in a table is linked to many records in another table

B. Many records in a table are linked to one record in another table

C. Two tables have the same number of records

D. One record is linked to exactly one record in another table

Solution

1. Step 1: Understand the meaning of one-to-many

   One-to-many means one item in the first table relates to multiple items in the second table.

2. Step 2: Match the description to options

   One record in a table is linked to many records in another table correctly describes this relationship as one record linked to many records.

3. Final Answer:

   One record in a table is linked to many records in another table -> Option A

4. Quick Check:

   One-to-many = One record linked to many [OK]

Hint: One-to-many means one item connects to many items [OK]

Common Mistakes:

• Confusing one-to-many with many-to-one
• Thinking it means equal number of records
• Assuming one-to-many means one-to-one

2. Which of the following is the correct way to define a one-to-many relationship in Flask SQLAlchemy models?

easy

A. Both B and C together

B. child_id = db.Column(db.Integer, db.ForeignKey('parent.id'))

C. parent = db.relationship('Child', backref='parent', lazy=True)

D. db.relationship('Parent', foreign_key='child.id')

Solution

1. Step 1: Identify relationship and foreign key usage

   In Flask SQLAlchemy, one-to-many needs a relationship on the parent and a foreign key on the child.

2. Step 2: Check options for correct syntax

   parent = db.relationship('Child', backref='parent', lazy=True) shows the relationship on the parent, child_id = db.Column(db.Integer, db.ForeignKey('parent.id')) shows the foreign key on the child. Both are needed together.

3. Final Answer:

   Both B and C together -> Option A

4. Quick Check:

   Relationship + ForeignKey = Correct one-to-many [OK]

Hint: Use relationship on parent and ForeignKey on child [OK]

Common Mistakes:

• Defining only relationship without foreign key
• Using wrong foreign key syntax
• Confusing parent and child roles

3. Given these models, what will author.books return?

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    books = db.relationship('Book', backref='author')

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    author_id = db.Column(db.Integer, db.ForeignKey('author.id'))

# Assume author is an Author instance with 3 related Book records

medium

A. A single Book object

B. A list of Book objects related to the author

C. An integer count of books

D. null, because books is not defined

Solution

1. Step 1: Understand db.relationship returns

   The relationship attribute returns a list-like collection of related objects in one-to-many.

2. Step 2: Apply to author.books

   Since author.books is a relationship to Book, it returns a list of Book objects linked to that author.

3. Final Answer:

   A list of Book objects related to the author -> Option B

4. Quick Check:

   Relationship attribute returns list of related objects [OK]

Hint: Relationship attribute returns list of related items [OK]

Common Mistakes:

• Expecting a single object instead of a list
• Thinking it returns count instead of objects
• Assuming attribute is undefined

4. Identify the error in this Flask SQLAlchemy one-to-many setup:

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Child')

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer)

medium

A. children should be a db.Column, not relationship

B. Relationship should be on Child, not Parent

C. Missing ForeignKey on Child's parent_id column

D. Parent model missing primary key

Solution

1. Step 1: Check Child model's parent_id column

   It lacks the ForeignKey constraint linking to Parent's id.

2. Step 2: Understand relationship requires ForeignKey

   Without ForeignKey, SQLAlchemy cannot link Child to Parent properly.

3. Final Answer:

   Missing ForeignKey on Child's parent_id column -> Option C

4. Quick Check:

   ForeignKey needed on child foreign key column [OK]

Hint: ForeignKey must be on child foreign key column [OK]

Common Mistakes:

• Forgetting ForeignKey on child side
• Placing relationship on wrong model
• Confusing relationship with column

5. You want to query all books written by an author named 'Alice' using Flask SQLAlchemy with one-to-many models Author and Book. Which code snippet correctly achieves this?

hard

A. author = Author.query.filter_by(name='Alice').all()\nbooks = author.books if author else []

B. books = Book.query.filter(author.name == 'Alice').all()

C. books = Book.query.filter_by(author='Alice').all()

D. books = Book.query.join(Author).filter(Author.name == 'Alice').all()

Solution

1. Step 1: Understand querying with relationships

   To filter books by author name, join Book with Author and filter by Author.name.

2. Step 2: Analyze options for correct query

   books = Book.query.join(Author).filter(Author.name == 'Alice').all() uses join and filter correctly to get all books by 'Alice'. author = Author.query.filter_by(name='Alice').all()\nbooks = author.books if author else [] accesses .books on list of authors (from .all()). books = Book.query.filter(author.name == 'Alice').all() uses invalid reference to 'author'. books = Book.query.filter_by(author='Alice').all() filters by author string incorrectly.

3. Final Answer:

   books = Book.query.join(Author).filter(Author.name == 'Alice').all() -> Option D

4. Quick Check:

   Use join and filter on related model for correct query [OK]

Hint: Use join and filter on related model for queries [OK]

Common Mistakes:

• Using .all() instead of .first() when accessing instance relationship
• Referencing related model without qualifier in filter
• Not joining tables before filtering