SQLAlchemy将一个映射的类中的多个外键映射到相同的主键 [英] SQLAlchemy multiple foreign keys in one mapped class to the same primary key

问题描述

Am试图建立一个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.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中的确切错误文档，但是当我复制他们提供的解决方案时，错误不会消失.我可能做错了什么?

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.

推荐答案

试图从foreign_keys中删除引号，并使其成为列表.从 Relationship Configuration: Handling Multiple Join Paths 的官方文档中

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自变量.

Changed in version 0.8: relationship() can resolve ambiguity between foreign key targets on the basis of the foreign_keys argument alone; the primaryjoin argument is no longer needed in this situation.

---

下面的自包含代码可与sqlalchemy>=0.9一起使用:

---

Self-contained code below works with 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将一个映射的类中的多个外键映射到相同的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！