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 |
+-------+---------+-------+---------+
See Question&Answers more detail:
os