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

Need a 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

Need a 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

Need a 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

Need a hint?

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