如何在SQLAlchemy中关联同一父模型的两个外键至不同实例？

问题根源在于Challenge模型通过sender_id和receiver_id两个外键关联User模型，SQLAlchemy无法自动推断每个relationship对应的关联路径，因此抛出多外键路径的错误。

只需在Challenge模型的relationship定义中明确指定对应的外键字段即可解决：

修改后的Challenge模型代码：

class Challenge(db.Model):
    __tablename__ = 'challenges'

    id = Column(Integer, primary_key=True)

    time = Column(Integer)
    player_1_score = Column(Integer, nullable=True)
    player_2_score = Column(Integer, nullable=True)

    sender_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    receiver_id = Column(Integer, ForeignKey('users.id'), nullable=False)

    # 指定关联sender_id外键
    sender = relationship("User", back_populates="sent_challenges", foreign_keys=[sender_id])
    # 指定关联receiver_id外键
    receiver = relationship("User", back_populates="received_challenges", foreign_keys=[receiver_id])

User模型代码无需修改，保持原定义即可：

class User(db.Model):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

    user_name = Column(String(14), nullable=False)
    level = Column(Integer)
    high_score = Column(Integer)
    points = Column(Integer)

    sent_challenges = relationship("Challenge", back_populates="sender", cascade="all, delete")
    received_challenges = relationship("Challenge", back_populates="receiver", cascade="all, delete")

修改后，SQLAlchemy能够明确每个关联对应的外键路径，你可以正常通过user.sent_challenges获取该用户发起的所有挑战，通过user.received_challenges获取收到的所有挑战。

内容的提问来源于stack exchange，提问作者Neuroneer