为什么我会收到 AmbiguousForeignKeysError? [英] Why am I getting AmbiguousForeignKeysError?
问题描述
我在遵循 SqlAlchemy 指南后遇到了一个问题 此处.
I've run into an issue after following the SqlAlchemy guide here.
给定以下简化模块:
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?
我已经尝试了以下组合，因为我认为在评论中已经建议但得到了同样的错误:
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])
更新 2:
添加了一些关于输出应该是什么样子的细节:
Update 2:
Added some details on what the output should look like:
玩家表:
+-----+-------+
| 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 |
+-------+---------+-------+---------+
推荐答案
双向关系和多个连接路径阻止了 SQLAlchemy 自动确定连接，并且两个表中的关系发出非常相似的错误消息使其难以理解问题出在哪里(以及给定的更改是否在解决问题方面取得了任何进展).我发现最简单的方法是在 Player 中注释掉关系，直到 ResultMatch 正常工作.
The two-way relationship and multiple join paths prevent SQLAlchemy from automatically determining the joins, and the relationships in both tables emit very similar error messages makes it difficult to understand where the problems lie (and whether a given change makes any progress in solving them). I found the simplest approach was to comment out the relationship in Player until ResultMatch was working properly.
对 MatchResult 的更改与问题中引用的多连接路径文档中指定的更改相同.为了使 Player 中的关系起作用，我指定了 主要连接条件，以便 SQLAlchemy 可以确定如何连接到 MatchResult.
The changes to MatchResult are the same as those specified in the multiple join paths docs referenced in the question. To get the relationship in Player to work I specified the primary join condition so that SQLAlchemy could determine how to join to MatchResult.
class Player(Base):
__tablename__ = 'player'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(100))
matches = orm.relationship('MatchResult',
primaryjoin="or_(Player.id == MatchResult.p1_id, Player.id == MatchResult.p2_id)")
class MatchResult(Base):
__tablename__ = 'match_result'
id = sa.Column(sa.Integer, primary_key=True)
p1_id = sa.Column(sa.Integer, sa.ForeignKey('player.id'))
p2_id = sa.Column(sa.Integer, sa.ForeignKey('player.id'))
p1 = orm.relationship("Player", foreign_keys=[p1_id])
p2 = orm.relationship("Player", foreign_keys=[p2_id])
完成这些更改后，无需任何显式别名或连接即可完成基本查询.
Once these changes have been made, basic querying can be done without any explicit aliasing or joins.
ms = session.query(MatchResult)
for r in ms:
print(r.p1_id, r.p1.name, r.p2_id, r.p2.name)
p1 = session.query(Player).filter(Player.name == 'bar').one()
for m in p1.matches:
print(m.p1.name, m.p2.name)
<小时>
以上代码，为了其他读者的清晰和有用，不包括特定于 OP 应用程序的继承、混合和会话管理代码.这个版本包括所有这些.
The above code, for clarity and usefulness to other readers, does not include the inheritance, mixin and session management code that is specific to the OP's application. Thiis version includes all of these.
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy import orm
class _Base():
id_ = sa.Column(sa.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 = sa.create_engine(conn_string)
def create_session(self):
Base.metadata.create_all(self.engine)
Session = orm.sessionmaker()
Session.configure(bind=self.engine)
self.session = Session()
class Player(Base, BlgMixin):
__tablename__ = 'player'
name = sa.Column(sa.String(100))
match = orm.relationship('MatchResult',
primaryjoin="or_(Player.id_ == MatchResult.p1_id, Player.id_ == MatchResult.p2_id)")
class MatchResult(Base, BlgMixin):
__tablename__ = 'match_result'
p1_id = sa.Column(sa.Integer, sa.ForeignKey(f'{BlgMixin.__table_args__.get("schema")}.player.id_'))
p2_id = sa.Column(sa.Integer, sa.ForeignKey(f'{BlgMixin.__table_args__.get("schema")}.player.id_'))
p1 = orm.relationship("Player", foreign_keys=[p1_id])
p2 = orm.relationship("Player", foreign_keys=[p2_id])
dal = DataAccessLayer()
Base.metadata.drop_all(bind=dal.engine)
Base.metadata.create_all(bind=dal.engine)
names = ['foo', 'bar', 'baz', 'zoo']
dal.create_session()
ps = [Player(name=n) for n in names]
dal.session.add_all(ps)
dal.session.flush()
p1, p2, p3, p4 = ps
m1 = MatchResult(p1_id=p1.id_, p2_id=p2.id_)
m2 = MatchResult(p1_id=p2.id_, p2_id=p1.id_)
m3 = MatchResult(p1_id=p3.id_, p2_id=p1.id_)
m4 = MatchResult(p1_id=p1.id_, p2_id=p4.id_)
dal.session.add_all([m1, m2, m3, m4])
dal.session.commit()
ms = dal.session.query(MatchResult)
for r in ms:
print(r.p1_id, r.p1.name, r.p2_id, r.p2.name)
print()
p1 = dal.session.query(Player).filter(Player.name == 'bar').one()
for m in p1.match:
print(m.p1.name, m.p2.name)
dal.session.close()
这篇关于为什么我会收到 AmbiguousForeignKeysError?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
