[Flask] Relationship Definitions

One to many relationships may be defined as follows:

# Category table (in app/models/category.py)
class Category(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

# Post table (in app/models/post.py)
class Post(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    pub_date = db.Column(db.DateTime, default=datetime.utcnow)

    # One to many relationship
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category', backref=db.backref('posts'))

The backref property specifies the member variable that will be used to access the related posts when working with a Category object.

For example:

chosen_category = Category.get_by_id(5)
posts_in_category = chosen_category.posts

The relationship may also be specified on the other end if you like:

# Category table (in app/models/category.py)
class Category(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    posts = db.relationship('Post', backref=db.backref('category'))

# Post table (in app/models/post.py)
class Post(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    pub_date = db.Column(db.DateTime, default=datetime.utcnow)

    # One to many relationship
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))

Many to many relationships may be defined as follows:

# User table (in app/models/user.py)
class User(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), nullable=False, unique=True)
    email = db.Column(db.String(100), unique=True)

# Relationship table (in app/models/relationships.py)
users_posts = db.Table(
    'users_posts',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('post_id', db.Integer, db.ForeignKey('post.id')))

# Post table (in app/models/post.py)
class Post(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    ...
    # Many to many relationship
    users = db.relationship(
        'User', secondary=users_posts, backref=db.backref('posts')
    )

All many to many relationship tables should be placed in the file relationships.py under the app/models directory.

One to one relationships are achieved using the uselist flag as shown below:

class User(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    profile_id = db.Column(db.Integer, db.ForeignKey('profile.id'))
    profile = db.relationship('Profile', db.backref=('user', uselist=False))

class Profile(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)

Alternatively, the relationship may be reversed:

class User(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    profile = db.relationship('Profile', uselist=False, backref='user')

class Profile(db.Model, CRUDMixin):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

https://flaskage.readthedocs.io/en/latest/database_design.html#the-crud-mixin