How to create many-to-many relationships with associated properties in SQLAlchemy

I once had a scenario where one business could be managed by multiple users, and each user could manage multiple businesses. To further complicate matters, only one user could be the Administrator of a business, which should ideally also be represented by the relationship.

Finding out how to represent that relationship was tricky as I couldn't find it in the SQLAlchemy docs, and StackOverflow was a mixed bag. This is what I pieced together:

from datetime import datetime as dt
from app import db
from sqlalchemy import Table, Column, Boolean, DateTime, Integer, \
    SmallInteger, String, ForeignKey
from sqlalchemy.orm import relationship
from app import constants


# Establish many-to-many relationship between users and (business) businesses
class UserBusiness(db.Model):
    __tablename__ = 'userbusiness'
    user_id       = Column(Integer, ForeignKey('users.id'), primary_key=True)
    business_id   = Column(Integer, ForeignKey('businesses.id'),
                           primary_key=True)
    administrator = Column(Boolean, default=False)
    business      = relationship('Business', backref='users')
    user          = relationship('User', backref='businesses')


class User(db.Model):
    __tablename__ = 'users'
    id            = Column(Integer, primary_key=True)
    email         = Column(String(256), unique=True)
    name          = Column(String(128))
    password      = Column(String(256))
    last_auth     = Column(DateTime, default=dt.utcnow)
    status        = Column(SmallInteger, default=constants.NEW)
    # timestamps
    update_date   = Column(DateTime, default=dt.utcnow, onupdate=dt.utcnow)
    insert_date   = Column(DateTime, default=dt.utcnow)

    def __init__(self, email=None, name=None, password=None):
        self.email = email
        self.name = name
        self.password = password


class Business(db.Model):
    __tablename__ = 'businesses'
    id            = Column(Integer, primary_key=True)
    name          = Column(String(256))
    status        = Column(SmallInteger, default=constants.NEW)
    # timestamps
    update_date   = Column(DateTime, default=dt.utcnow, onupdate=dt.utcnow)
    insert_date   = Column(DateTime, default=dt.utcnow)

    def __init__(self, name=None):
        self.name = name

In a Flask application, this would be contained your models.py file. Python's __repr()__ and other common functions omitted for clarity.

To record a new User-Business relationship, first create the UserBusiness relationship, then add in the User and Business objects (if this were Flask, this'd likely be in views.py):

ub = UserBusiness(user=g.user, administrator=True)
ub.business = Business(name='Acme Widgets Ltd')
db.session.add(ub)
db.session.commit()

To get a list of Businesses a user manages, you can do something like the following:

businesses = [ub.business for ub in g.user.businesses]

ub here represents the UserBusiness relation. You could then extend it to only get businesses which the user is an administrator of:

businesses = [ub.business for ub in g.user.businesses if ub.administrator]

It's all beautifully simple once you've done it successfully once or twice...