问题描述
我正在尝试设置一个 postgresql 表,该表有两个指向另一个表中相同主键的外键.
Am trying to setup a postgresql table that has two foreign keys that point to the same primary key in another table.
运行脚本时出现错误
sqlalchemy.exc.AmbiguousForeignKeysError:无法确定关系 Company.stakeholder 上父/子表之间的连接条件 - 有多个外键路径链接表.指定foreign_keys"参数,提供应被视为包含对父表的外键引用的列的列表.
这是 SQLAlchemy 中的确切错误文档 但当我复制他们提供的解决方案时,错误并没有消失.我可能做错了什么?
That is the exact error in the SQLAlchemy Documentation yet when I replicate what they have offered as a solution the error doesn't go away. What could I be doing wrong?
#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
.
I have seen similar questions here but some of the answers recommend one uses a primaryjoin
yet in the documentation it states that you don't need the primaryjoin
in this situation.
推荐答案
尝试从外键中删除引号并将它们设为列表.来自 关系配置:处理的官方文档多个连接路径
Tried removing quotes from the foreign_keys and making them a list. From official documentation on Relationship Configuration: Handling Multiple Join Paths
在 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()
这篇关于一个映射类中的 SQLAlchemy 多个外键指向同一个主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!