python - Why am I getting AmbiguousForeignKeysError?

Question

I've run into an issue after following the SqlAlchemy guide here.

Given the following simplified module:

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_")

That I am attempting to build a query using:

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)

Why am I getting the following error?

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.

I was pretty sure I'd specified the two foreign key relationships?

---

Update:

I've tried the following combination as I think has been suggested in the comments but got the same error:

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

---

Update 2:

Added some details on what the output should look like:

player table:

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

match_result table:

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

Query output:

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

Answer 1

The issue is with the definition of this relationship match = relationship("MatchResult") for the Player class. If you completely remove this line, and use the below definitions for the relationships, all the queries you mentioned should work as expected:

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)

In fact, the desired select query can also be constructed, but you need to specify the relationships explicitly on JOINs:

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
)

---

I would, however, make few more changes to the model to make it even more user-friednly:

1. add backref to the relationship so that it can be navigated back from the Player
2. add a property to show all the matches of one player for both sides

Model definitions:

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")

This will allow navigating the relationships as per below example:

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