SQLAlchemy | Multiple One-To-One and One-To-Many relationships

Question

I've got two models: Game and Player. I want to have a list of all players in the Game Model, as well as one of these players in a different field. It's a flask server and a sqlite database.

Here is my Player Model:

class Player(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # other fields...
    
    game_id = db.Column(db.Integer, db.ForeignKey('game.id'))
    game = db.relationship('Game', back_populates='players', foreign_keys=game_id)

Here is my Game Model:

class Game(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # other fields...

    current_president_id = db.Column(db.Integer, db.ForeignKey('player.id'))
    current_president = db.relationship("Player", foreign_keys=current_president_id)

    # other one-to-one relationships to the Player Model, exactly like the first one...

    players = db.relationship('Player', back_populates='game', foreign_keys=Player.game_id)

I made the two models based on this Response. But I still get the same Warning:

SAWarning: Cannot correctly sort tables; there are unresolvable cycles between tables "game, player", which is usually caused by mutually dependent foreign key constraints.  Foreign key constraints involving these tables will not be considered; this warning may raise an error in a future release.

Like the Warning says, when using this configuration I then get at some point:

sqlalchemy.exc.CircularDependencyError: Circular dependency detected. (SaveUpdateState(<Game at 0x23009e00160>), SaveUpdateState(<Player at 0x23009e141c0>), ProcessState(ManyToOneDP(Game.current_president), <Game at 0x23009e00160>, delete=False), ProcessState(OneToManyDP(Game.players), <Game at 0x23009e00160>, delete=False))

I have no idea what to do, I tried to read through the documentation and tested many other configurations, but nothing works. All help greatly appreciated. Thanks!

Tibor Völcker · Accepted Answer · 2021-01-05T18:30:55.690

So I tried to learn SQLAlchemy a bit deeper, and found a solution. First you set the use_alter Flag to True in the one-to-one relationship:

current_president_id = db.Column(db.Integer, db.ForeignKey('player.id', use_alter=True))

This makes the Warning go away. But you still need to be careful now. Because the Player model has a Column referencing the Game.id and the Game model has a Column referencing the Player.id you cannot declare the relationships in one commit:

g, p1, p2 = Game(), Player(), Player()
db.session.add_all([g, p1, p2])
db.session.commit()
g.players = [p1, p2]
g.current_president = p3
db.session.commit()

This will raise the CircularDependencyError. But this will not:

g, p1, p2 = Game(), Player(), Player()
db.session.add_all([g, p1, p2])
db.session.commit()
g.players = [p1, p2]
db.session.commit()
g.current_president = p3
db.session.commit()

EDIT:

Use use_alter=True in the relationship to prevent any CircularDependencyErrors. This way, when you add a Game with Players and the relationship in one commit, the relationship will be set in a second 'ALTER' statement to prevent the circular dependency.

SQLAlchemy | Multiple One-To-One and One-To-Many relationships

1 Answers1

EDIT: