sqlalchemy Magic

I was writing a plugin for CTFd and I was faced with an interesting problem: how the hell do I add a column (attribue) to a parent table without modifying that table (or model object)???
I was trying to assign an extra attribute to the Teams model; a one-to-many relationship between bracket and team so I could have Teams.chal_bracket and Bracket.teams, but again without modifying the Teams model.
I had actually tried overriding the Teams model and also adding a row on the fly, but neither of those worked. I ended up with the solution below:

# secondary table for team<->bracket associations
tb = db.Table("team_bracket",
              db.Column("bracket_id", db.Integer, db.ForeignKey("bracket.id")),
              db.Column("team_id", db.Integer, db.ForeignKey("teams.id"))

class Bracket(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, index=True, unique=True)
    hidden = db.Column(db.Boolean)
    # super hacked up way to get the chal_bracket attribute on the parent
    # model class (Teams) without actually modifying it
    teams = db.relationship("Teams", backref=db.backref("chal_bracket", uselist=False),
                            secondary=tb, primaryjoin=id == tb.c.bracket_id,
                            secondaryjoin=Teams.id == tb.c.team_id)

Breaking this down:

  • The table tb defines the table team_bracket, which associates a team and a bracket by id
  • The Bracket class, which represents a database table and has an attribute teams
  • The teams attribute has a backref that allows access to the bracket of a team using the Teams.chal_bracket attribue. The attribute is back-populated by sqlalchemy internally; this means the table isn't changed, but sqlalchemy does the work for you! The uselist=False argument is used so that team.chal_bracket returns just the bracket object and not a list of length 1 with the bracket object in it.
  • The teams attribute also defines two joins: a primaryjoin that links the id of the object to the bracket id and a secondaryjoin that links the team id to the team_id of the object. This makes it so that you can get all of the teams associated with a bracket by just doing Bracket.teams and also get the bracket associated with a team by doing Teams.chal_bracket.

Normally you would have to define a relationship in the parent as follows:

class Teams(db.Model):
    chal_bracket_id = db.Column(db.Integer, db.ForeignKey("bracket.id"))
    chal_bracket = db.Relationship("Bracket")

But because of this hack you don't need to modify the parent model to accomplish the exact same thing.
Pretty cool.