如何获取针对同一型号的多个外键?(模糊ForeignKeysError)

Question

我有两个简单的模型，比如

class GameModel(db.Model):
__tablename__ = 'games'
id = db.Column(db.Integer, primary_key=True)
home_team = db.Column(db.Integer, db.ForeignKey("teams.team_id"))
away_team = db.Column(db.Integer, db.ForeignKey("teams.team_id"))

class TeamModel(db.Model):
__tablename__ = "teams"
id = db.Column(db.Integer, primary_key=True)
team_id = db.Column(db.Integer, nullable=False, unique=True)
games = db.relationship("GameModel", lazy="joined", backref="game")

当我迁移时，我得到一个错误

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship T
eamModel.games - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing 
a list of those columns which should be counted as containing a foreign key reference to the parent table.

如何正确地将这两张表连接在一起

Answer 1

您需要维护home_games和away_games的单独列表，然后将它们组合以返回games:的完整列表

import datetime
import sqlalchemy as sa
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import declarative_base, relationship
connection_uri = (
"mssql+pyodbc://@localhost:49242/myDb?driver=ODBC+Driver+17+for+SQL+Server"
)
engine = sa.create_engine(
connection_uri,
future=True,
echo=False,
)
Base = declarative_base()

class Game(Base):
__tablename__ = "game"
id = sa.Column(sa.Integer, primary_key=True)
when = sa.Column(sa.Date, nullable=False)
home_team_id = sa.Column(sa.Integer, sa.ForeignKey("team.id"))
away_team_id = sa.Column(sa.Integer, sa.ForeignKey("team.id"))
home_team = relationship(
"Team", foreign_keys=[home_team_id], back_populates="home_games"
)
away_team = relationship(
"Team", foreign_keys=[away_team_id], back_populates="away_games"
)
def __repr__(self):
return f"<Game(when='{self.when}')>"

class Team(Base):
__tablename__ = "team"
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(50))
home_games = relationship(
Game, foreign_keys=[Game.home_team_id], back_populates="home_team"
)
away_games = relationship(
Game, foreign_keys=[Game.away_team_id], back_populates="away_team"
)
@hybrid_property
def games(self):
return self.home_games + self.away_games
def __repr__(self):
return f"<Team(name='{self.name}')>"

# <just for testing>
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
# </just for testing>
with sa.orm.Session(engine, future=True) as session:
t_a = Team(name="Team_A")
t_b = Team(name="Team_B")
g1 = Game(when=datetime.date(2021, 1, 1), home_team=t_a, away_team=t_b)
g2 = Game(when=datetime.date(2022, 2, 2), home_team=t_b, away_team=t_a)
session.add_all([t_a, t_b, g1, g2])
print(t_a.home_games)  # [<Game(when='2021-01-01')>]
print(t_a.away_games)  # [<Game(when='2022-02-02')>]
print(t_a.games)  # [<Game(when='2021-01-01')>, <Game(when='2022-02-02')>]