SQLAlchemy 将一个映射类中的多个外键映射到同一主键

我尝试设置一个 postgreql 表，该表有两个外键，它们指向另一个表中的同一个主键。

当我运行脚本时，我得到了错误

Sqlalchemy.exc.AmbiguousForeignKeysError: 无法确定关系公司上的父表/子表之间的连接条件-有多个外键路径连接这些表。指定‘ foreign _ keys’参数，提供一个列列表，这些列应被视为包含对父表的外键引用。

这正是 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')

我在这里看到过类似的问题，但是一些答案建议使用 primaryjoin，但是在文档中它说在这种情况下不需要 primaryjoin。

78677

小开

最佳答案

尝试从 foreign _ key 中删除引号并将它们制作成一个列表

在版本0.8中更改: relationship()可以解决仅以 foreign_keys论点为基础的国外关键目标; 在这种情况下不再需要 primaryjoin参数。

下面的自包含代码与 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()

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