Python 一个映射类中的 SQLAlchemy 多个外键指向同一个主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22355890/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLAlchemy multiple foreign keys in one mapped class to the same primary key
提问by lukik
Am trying to setup a postgresql table that has two foreign keys that point to the same primary key in another table.
我正在尝试设置一个 postgresql 表，该表有两个指向另一个表中相同主键的外键。
When I run the script I get the error
当我运行脚本时出现错误
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Company.stakeholder - 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.
sqlalchemy.exc.AmbiguousForeignKeysError：无法确定关系 Company.stakeholder 上父/子表之间的连接条件 - 有多个外键路径链接表。指定“foreign_keys”参数，提供应被视为包含对父表的外键引用的列的列表。
That is the exact error in the SQLAlchemy Documentationyet when I replicate what they have offered as a solution the error doesn't go away. What could I be doing wrong?
这是SQLAlchemy 文档中的确切错误，但是当我复制他们提供的解决方案时，错误并没有消失。我可能做错了什么？
#The business case here is that a company can be a stakeholder in another company.
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False)
class Stakeholder(Base):
__tablename__ = 'stakeholder'
id = Column(Integer, primary_key=True)
company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
company = relationship("Company", foreign_keys='company_id')
stakeholder = relationship("Company", foreign_keys='stakeholder_id')
I have seen similar questions here but some of the answersrecommend one uses a primaryjoinyet in the documentation it states that you don't need the primaryjoinin this situation.
我在这里看到了类似的问题，但一些答案建议使用 aprimaryjoin尚未在文档中指出primaryjoin在这种情况下您不需要。
采纳答案by van
Tried removing quotes from the foreign_keys and making them a list. From official documentation on Relationship Configuration: Handling Multiple Join Paths
尝试从外键中删除引号并将它们设为列表。来自官方文档Relationship Configuration: Handling Multiple Join Paths
Changed in version 0.8:
relationship()can resolve ambiguity between foreign key targets on the basis of theforeign_keysargument alone; theprimaryjoinargument is no longer needed in this situation.
在 0.8 版更改：
relationship()可以foreign_keys仅根据参数解决外键目标之间的歧义；该primaryjoin参数不再需要在这种情况下。
Self-contained code below works with sqlalchemy>=0.9:
下面的自包含代码适用于sqlalchemy>=0.9：
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(u'sqlite:///:memory:', echo=True)
session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()
#The business case here is that a company can be a stakeholder in another company.
class Company(Base):
__tablename__ = 'company'
id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False)
class Stakeholder(Base):
__tablename__ = 'stakeholder'
id = Column(Integer, primary_key=True)
company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
company = relationship("Company", foreign_keys=[company_id])
stakeholder = relationship("Company", foreign_keys=[stakeholder_id])
Base.metadata.create_all(engine)
# simple query test
q1 = session.query(Company).all()
q2 = session.query(Stakeholder).all()
回答by Gringo Suave
The latest documentation:
最新文档：
The form of foreign_keys=in the documentation produces a NameError, not sure how it is expected to work when the class hasn't been created yet. With some hacking I was able to succeed with this:
foreign_keys=文档中的形式产生了一个 NameError，不确定在类尚未创建时它是如何工作的。通过一些黑客攻击，我能够成功：
company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
company = relationship("Company", foreign_keys='Stakeholder.company_id')
stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
stakeholder = relationship("Company",
foreign_keys='Stakeholder.stakeholder_id')
In other words:
换句话说：
… foreign_keys='CurrentClass.thing_id')
