时间戳

首页 > 解决方案 > 为什么我会收到 AmbiguousForeignKeysError？

python - 为什么我会收到 AmbiguousForeignKeysError？

问题描述

遵循此处的 SqlAlchemy 指南后，我遇到了一个问题。

给定以下简化模块：

class _Base():
    id_ = Column(Integer, primary_key=True, autoincrement=True)


Base = declarative_base(cls=_Base)


class BlgMixin():

    @declared_attr
    def __table_args__(cls):
        return {'schema': "belgarath_backup", "extend_existing": True}


class DataAccessLayer():

    def __init__(self):
        conn_string = "mysql+mysqlconnector://root:root@localhost/"
        self.engine = create_engine(conn_string)

    def create_session(self):
        Base.metadata.create_all(self.engine)
        Session = sessionmaker()
        Session.configure(bind=self.engine)
        self.session = Session()


class Player(Base, BlgMixin):
    __tablename__ = "player"

    name_ = Column(String(100))

    match = relationship("MatchResult")


class MatchResult(Base, BlgMixin):
    __tablename__ = "match_result"

    p1_id = Column(Integer, ForeignKey(f"{BlgMixin.__table_args__.get('schema')}.player.id_"))
    p2_id = Column(Integer, ForeignKey(f"{BlgMixin.__table_args__.get('schema')}.player.id_"))

    p1 = relationship("Player", foreign_keys=f"{BlgMixin.__table_args__.get('schema')}.player.id_")
    p2 = relationship("Player", foreign_keys=f"{BlgMixin.__table_args__.get('schema')}.player.id_")

我正在尝试使用以下方法构建查询：

dal = DataAccessLayer()
dal.create_session()

player_1 = aliased(Player)
player_2 = aliased(Player)

matches = dal.session.query(MatchResult.p1_id, player_1.name_, MatchResult.p2_id, player_2.name_)
matches = matches.join(player_1)
matches = matches.join(player_2)

为什么我会收到以下错误？

Could not determine join condition between parent/child tables on relationship Player.match - 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.

我很确定我指定了两个外键关系？

更新：

我已经尝试了以下组合，因为我认为评论中已建议但得到了相同的错误：

p1 = relationship("Player", foreign_keys=[p1_id])
p2 = relationship("Player", foreign_keys=[p2_id])

更新 2：

添加了一些关于输出应该是什么样子的细节：

player桌子：

+-----+-------+
| id_ | name_ |
+-----+-------+
|   1 | foo   |
|   2 | bar   |
|   3 | baz   |
|   4 | zoo   |
+-----+-------+

match_result桌子：

+-----+-------+-------+
| id_ | p1_id | p2_id |
+-----+-------+-------+
|   1 |     1 |     2 |
|   2 |     2 |     1 |
|   3 |     3 |     1 |
|   4 |     1 |     4 |
+-----+-------+-------+

查询输出：

+-------+---------+-------+---------+
| p1_id | p1_name | p2_id | p2_name |
+-------+---------+-------+---------+
|     1 | foo     |     2 | bar     |
|     2 | bar     |     1 | foo     |
|     3 | baz     |     1 | foo     |
|     1 | foo     |     4 | zoo     |
+-------+---------+-------+---------+

标签： pythonsqlalchemy

解决方案

问题在于类的这种关系的match = relationship("MatchResult")定义Player。如果您完全删除此行，并为关系使用以下定义，则您提到的所有查询都应按预期工作：

class Player(Base, BlgMixin):
    __tablename__ = "player"

    name_ = Column(String(100))


class MatchResult(Base, BlgMixin):
    __tablename__ = "match_result"

    p1_id = Column(ForeignKey(Player.id_))
    p2_id = Column(ForeignKey(Player.id_))

    p1 = relationship(Player, foreign_keys=p1_id)
    p2 = relationship(Player, foreign_keys=p2_id)

实际上，也可以构造所需的选择查询，但您需要在 JOIN 上明确指定关系：

player_1 = aliased(Player)
player_2 = aliased(Player)
q = (
    dal.session
    .query(
        MatchResult.p1_id,
        player_1.name_,
        MatchResult.p2_id,
        player_2.name_,
    )
    .join(player_1, MatchResult.p1)  # explicitly specify which relationship/FK to join on
    .join(player_2, MatchResult.p2)  # explicitly specify which relationship/FK to join on
)

但是，我会对该模型进行更多更改，以使其更加用户友好：

  1. 添加backref到关系中，以便可以从Player
  2. 添加一个property以显示双方一名球员的所有比赛

模型定义：

class Player(Base, BlgMixin):
    __tablename__ = "player"

    name_ = Column(String(100))

    @property
    def all_matches(self):
        return self.matches_home + self.matches_away


class MatchResult(Base, BlgMixin):
    __tablename__ = "match_result"

    p1_id = Column(ForeignKey(Player.id_))
    p2_id = Column(ForeignKey(Player.id_))
    
    p1 = relationship(Player, foreign_keys=p1_id, backref="matches_home")
    p2 = relationship(Player, foreign_keys=p2_id, backref="matches_away")

这将允许按照以下示例导航关系：

p1 = session.query(Player).get(1)
print(p1)
for match in p1.all_matches:
    print(" ", match)

推荐阅读