Many-to-many relationships in Flask - Mini Project: Build & Apply

Many-to-many relationships

📖 Scenario: You are building a simple Flask app to manage books and authors. Each book can have many authors, and each author can write many books. This is a many-to-many relationship.

🎯 Goal: Create the data models and setup the many-to-many relationship between Book and Author using Flask-SQLAlchemy.

📋 What You'll Learn

Create a Flask app with SQLAlchemy

Define Book and Author models

Create an association table called book_author

Setup many-to-many relationship using db.relationship and secondary argument

💡 Why This Matters

🌍 Real World

Many web apps need to model complex relationships like books and authors, students and courses, or tags and posts.

💼 Career

Understanding many-to-many relationships is essential for backend developers working with databases and Flask.

1

Create the Flask app and SQLAlchemy setup

Create a Flask app instance called app and initialize SQLAlchemy with db = SQLAlchemy(app).

Flask

# Create Flask app and SQLAlchemy instance
# Your code here

Hint

Use Flask(__name__) to create the app and pass it to SQLAlchemy.

2

Create the association table book_author

Create a table called book_author using db.Table with columns book_id and author_id as foreign keys to book.id and author.id respectively.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

# Create association table book_author
# Your code here

Hint

Use db.Table with two db.Column entries for the foreign keys.

3

Define Book and Author models with relationships

Create two models: Book and Author. Each should have an id column as primary key and a title or name column respectively. Add a authors relationship in Book using db.relationship with secondary=book_author and a books relationship in Author similarly.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

book_author = db.Table('book_author',
    db.Column('book_id', db.Integer, db.ForeignKey('book.id'), primary_key=True),
    db.Column('author_id', db.Integer, db.ForeignKey('author.id'), primary_key=True)
)

# Define Book and Author models with relationships
# Your code here

Hint

Use db.relationship with secondary=book_author and back_populates to link both sides.

4

Create the database tables

Call db.create_all() to create the tables in the database.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

book_author = db.Table('book_author',
    db.Column('book_id', db.Integer, db.ForeignKey('book.id'), primary_key=True),
    db.Column('author_id', db.Integer, db.ForeignKey('author.id'), primary_key=True)
)

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    authors = db.relationship('Author', secondary=book_author, back_populates='books')

class Author(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    books = db.relationship('Book', secondary=book_author, back_populates='authors')

# Create all tables
# Your code here

Hint

Use with app.app_context(): before calling db.create_all().

Practice

1. What is the main purpose of using an association table in a many-to-many relationship in Flask SQLAlchemy?

easy

A. To link two tables that can have multiple related records each

B. To store user login information securely

C. To create a one-to-one relationship between models

D. To optimize database queries by caching results

Solution

1. Step 1: Understand many-to-many relationships

   Many-to-many means each record in one table can relate to many records in another table, and vice versa.

2. Step 2: Role of association table

   An association table connects these two tables by holding pairs of related record IDs, enabling the many-to-many link.

3. Final Answer:

   To link two tables that can have multiple related records each -> Option A

4. Quick Check:

   Association table = link many-to-many tables [OK]

Hint: Association tables connect two models with many links [OK]

Common Mistakes:

• Thinking association tables store user credentials
• Confusing many-to-many with one-to-one relationships
• Assuming association tables optimize queries automatically

2. Which of the following is the correct way to define an association table in Flask SQLAlchemy?

easy

A. association = db.Model('association', db.Column('left_id', db.Integer), db.Column('right_id', db.Integer))

B. association = db.Model('association', db.ForeignKey('left.id'), db.ForeignKey('right.id'))

C. association = db.Table('association', db.Column('left_id', db.String), db.Column('right_id', db.String))

D. association = db.Table('association', db.Column('left_id', db.Integer, db.ForeignKey('left.id')), db.Column('right_id', db.Integer, db.ForeignKey('right.id')))

Solution

1. Step 1: Identify correct association table syntax

   Association tables use db.Table with columns having foreign keys to related tables.

2. Step 2: Check options for correct use of db.Table and foreign keys

   association = db.Table('association', db.Column('left_id', db.Integer, db.ForeignKey('left.id')), db.Column('right_id', db.Integer, db.ForeignKey('right.id'))) correctly uses db.Table and defines columns with foreign keys to 'left.id' and 'right.id'.

3. Final Answer:

   association = db.Table('association', db.Column('left_id', db.Integer, db.ForeignKey('left.id')), db.Column('right_id', db.Integer, db.ForeignKey('right.id'))) -> Option D

4. Quick Check:

   Association table = db.Table + foreign keys [OK]

Hint: Use db.Table with foreign keys for association tables [OK]

Common Mistakes:

• Using db.Model instead of db.Table for association tables
• Missing foreign key constraints in columns
• Using wrong data types like String for IDs

3. Given the following models, what will print(user.groups[0].name) output?

association = db.Table('association',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('group_id', db.Integer, db.ForeignKey('group.id'))
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    groups = db.relationship('Group', secondary=association, back_populates='users')

class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    users = db.relationship('User', secondary=association, back_populates='groups')

user = User(name='Alice')
group = Group(name='Admins')
user.groups.append(group)
print(user.groups[0].name)

medium

A. None

B. Admins

C. Alice

D. Error: AttributeError

Solution

1. Step 1: Understand relationship setup

   User and Group are linked many-to-many via association table. User.groups holds Group objects.

2. Step 2: Analyze code behavior

   User 'Alice' has 'Admins' group appended. Accessing user.groups[0].name returns 'Admins'.

3. Final Answer:

   Admins -> Option B

4. Quick Check:

   user.groups[0].name = 'Admins' [OK]

Hint: Access related objects via relationship lists [OK]

Common Mistakes:

• Confusing user name with group name
• Expecting None if not committed to DB
• Assuming error due to missing session commit

4. Identify the error in this many-to-many relationship setup:

association = db.Table('association',
    db.Column('user_id', db.Integer),
    db.Column('group_id', db.Integer)
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    groups = db.relationship('Group', secondary=association, back_populates='users')

class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    users = db.relationship('User', secondary=association, back_populates='groups')

medium

A. Incorrect use of back_populates attribute

B. Missing primary key in User model

C. Missing foreign key constraints in association table columns

D. Using db.Model instead of db.Table for association

Solution

1. Step 1: Check association table columns

   Columns lack foreign key constraints linking to User and Group tables.

2. Step 2: Importance of foreign keys

   Foreign keys are required to enforce relationships and enable SQLAlchemy to join tables properly.

3. Final Answer:

   Missing foreign key constraints in association table columns -> Option C

4. Quick Check:

   Foreign keys required in association table [OK]

Hint: Always add foreign keys in association tables [OK]

Common Mistakes:

• Omitting foreign keys in association table
• Confusing back_populates usage
• Thinking primary key is missing in User model

5. You want to add a role attribute to the association between User and Group models to store the user's role in each group. Which approach correctly implements this in Flask SQLAlchemy?

hard

A. Create a new model class for the association with user_id, group_id, and role columns

B. Add a role column to the association table defined with db.Table

C. Add role as a property in the User model

D. Add role as a property in the Group model

Solution

1. Step 1: Understand limitation of db.Table

   db.Table is a simple table without a model class, so it cannot hold extra attributes like 'role'.

2. Step 2: Use association object pattern

   Create a new model class representing the association with foreign keys and extra columns like 'role'. This allows storing additional data per relationship.

3. Final Answer:

   Create a new model class for the association with user_id, group_id, and role columns -> Option A

4. Quick Check:

   Extra data in many-to-many needs association model class [OK]

Hint: Use association model class for extra fields in many-to-many [OK]

Common Mistakes:

• Trying to add extra columns to db.Table association
• Adding role to User or Group instead of association
• Not using a model class for complex associations