Flask by Example: SQLAlchemy Relationship Mapping
Defining relationships between tables (One-to-Many, Many-to-Many) is a core feature of SQLAlchemy. It allows you to navigate between related records using simple Python attributes.
Code
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
# One-to-Many: One User has Many Posts
# 'Post' is the name of the related class
# backref creates a 'author' attribute on the Post model
posts = db.relationship('Post', backref='author', lazy=True)
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100))
# Foreign Key links to User.id
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
# Usage:
# u = User(name='John')
# p1 = Post(title='My First Post', author=u)
# # Note: we can pass the user object to 'author' because of backref
#
# db.session.add(u)
# db.session.add(p1)
# db.session.commit()
#
# # Accessing relationships
# print(u.posts) # Returns a list of Post objects
# print(p1.author.name) # Returns 'John'Explanation
Relationships allow you to link different models together, such as a One-to-Many relationship between Users and Posts. You use db.ForeignKey on the child model to store the parent's ID, creating the link at the database level.
On the parent model, you use db.relationship to define a high-level property for accessing related objects. This allows you to work with related data using standard Python lists and objects.
The backref argument is a convenience that automatically adds a property to the related class (e.g., post.author). This avoids the need to manually define the relationship on both sides, keeping your code DRY (Don't Repeat Yourself).
Code Breakdown
db.relationship is a Python-level construct. It doesn't affect the database schema directly but tells SQLAlchemy how to join tables and retrieve related data.db.ForeignKey('user.id') creates the actual database constraint. Note that we use the table name user (lowercase) here, not the class name.lazy=True (or 'select') means the related data is loaded from the database only when you access the property. This is efficient but can lead to the "N+1 query problem" if not careful.