Setting up many-to-many relationship in flask sqlalchemy

Question

I am trying to make a table of games and each game should point to two players or users.

This is the code I have right now. I am using SQLAlchemy

UserGame = db.Table('UserGame',
    db.Column('game_id',db.Integer,db.ForeignKey('game.id')),
    db.Column('user_id',db.Integer,db.ForeignKey('user.id')),
    )

class User(db.Model):
    __tablename__ = 'User'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)
    games = db.relationship('Game', secondary=UserGame, backref='players')

    def __init__(self, username, email):
        self.username = username
        self.email = email
    def __repr__(self):
        return '' % self.username

class Game(db.Model):
    __tablename__ = 'Game'
    id = db.Column(db.Integer, primary_key=True)
    player1 = db.Column(db.Integer,db.ForeignKey('User.id')
    player2 = db.Column(db.Integer,db.ForeignKey('User.id')
    ended = db.Column(db.Boolean)
    winner_username = db.Column(db.String(80),db.ForeignKey('User.username'))

However, when I try to create a user, I get the following error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child     tables on relationship User.games - there are no foreign keys linking these tables via secondary table 'UserGame'.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.

Any ideas?

Jan Vlcinsky · Accepted Answer

M to N leads to Association class

As soon as you find M:N relationship, you are about to use so called association class describing this relationship.

In your case, you have relationship between two players and the meaning of the association is "a game they play together".

Minimal association class describes:

relationship between two instances of some classes
no additional data are bound to given association

There might be ternary or even more complex associations, but these are rather rare.

In some cases, and this is the case of Game here, we might add some additional data to that association (like result, who was the winner etc.).

There is no conceptual problem in describing association between two instances of the same class.

Modeling Association class in sqlalchemy

There are three methods for modelling association classes in sqlalchemy

Secondary table

In sqlalchemy, simple M:N relationship is often modelled by so called secondary table. Unfortunately, this is not the case here as we need to attach additional information to known associations.

Association class

This is the way to go in your case. Code will follow.

Association proxy

There are some advanced techniques, using association_proxy, which might hide the association class instance, but we do not need this (in fact, we want to see the concept of the game in the model).

model.py - used classes

Following example models teams, which might play together a game. Each game is played at host and the other team is considered a guest. We record with each game a score.

This example shall be easy to modify for your scenario.

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Team(Base):
    __tablename__ = "team"
    id = Column(Integer, primary_key=True)
    name = Column(String(40))

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

    def __repr__(self):
        return "".format(self=self)

class Game(Base):
    __tablename__ = "game"
    host_id = Column(Integer, ForeignKey("team.id"), primary_key=True)
    guest_id = Column(Integer, ForeignKey("team.id"), primary_key=True)
    host_score = Column(Integer)
    guest_score = Column(Integer)

    def __init__(self, host, guest, host_score, guest_score):
        self.host = host
        self.guest = guest
        self.host_score = host_score
        self.guest_score = guest_score
    host = relationship(Team, lazy="joined", foreign_keys="Game.host_id", backref="games_ashost")
    guest = relationship(Team, lazy="joined", foreign_keys="Game.guest_id", backref="games_asguest")

    def __repr__(self):
        templ = "" 
        return templ.format(self=self)

Apart from setting up the class Game, it is important to defile host and guest meatinformation for this class. We use relationship for this purpose.

As we are associating two instances of the same class, sqlalchemy has difficulties to find what class attributes to link with host and which with guest. For this purpose, the relationship must use foreign_keys, which might go in form of an array or a string of foreign keys to use. (this was for me the most difficult part as examples do not show such example).

backref in relationship adds new attribute to partnering class, so backref="games_ashost will add for each Team an attribute Team().games_ashost.

Playing with the model

Import what is needed

>>> from model import *
>>> from sqlalchemy import *
>>> from sqlalchemy.orm import Session

Prepare engine and session

>>> engine = create_engine("sqlite://")
>>> Base.metadata.create_all(engine)
>>> session = Session(engine)

Create teams alfa, beta, gama, delta

>>> alfa = Team("alfa")
>>> beta = Team("beta")
>>> gama = Team("gama")
>>> delta = Team("delta")
>>> alfa

>>> beta

>>> gama

>>> delta

Store them in database

>>> session.add_all([alfa, beta, gama, delta])
>>> session.commit()

Instantiate games (getween Alfa and Beta and Alfa and Gama) and store them in the database

>>> game_ab = Game(alfa, beta, 0, 0)
>>> game_ab

>>> game_ag = Game(alfa, gama, 0, 0)
>>> session.add_all([game_ab, game_ag])
>>> session.commit()

Explore available properties of teams

>>> alfa.games_ashost
[, ]
>>> alfa.games_asguest
[]
>>> beta.games_ashost
[]
>>> beta.games_asguest
[]
>>> gama.games_ashost
[]
>>> gama.games_asguest
[]
>>> gama

Explore properties of games

>>> game_ab.host

>>> game_ab.guest