遗传后sqlalchemy.exc.AmbiguousForeignKeysError

Question

我使用sqlacodegen从我的数据库中反射一堆表。我得到了以下错误：

sqlalchemy.exc.AmbiguousForeignKeysError:无法确定'Employee‘和’Sales‘之间的连接；表之间有多个外键约束关系。请显式指定此联接的“on子句”。

这是我的表的简化版本。我在文档中看到，我应该使用foreign_keys参数来解决外键目标之间的歧义。虽然，我认为这个问题是因为继承。谁能帮我弄清楚这是怎么回事。

# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Employee(Base):
    __tablename__ = 'Employee'

    EmployeeId = Column(Integer, primary_key=True)


class Sales(Employee):
    __tablename__ = 'Sales'

    EmployeeID = Column(ForeignKey('Employee.EmployeeId'), primary_key=True)
    OldemployeeID = Column(ForeignKey('Employee.EmployeeId'))
    employee = relationship('Employee', foreign_keys=[EmployeeID])
    old_employee = relationship("Employee", foreign_keys=[OldemployeeID])

Answer 1

当表之间有多个可能继承的路径(Sales.EmployeeID或Sales.OldEmployeeID)时，SqlAlchemy不知道要使用哪个路径，需要使用inherit_condition显式地告诉它路径。例如，要由EmployeeID继承

class Sales(Employee):
    ...
    __mapper_args__ = { "inherit_condition": EmployeeID == Employee.EmployeeId }

例如，您还可以通过输入OldEmployeeID继承OldEmployeeID == Employee.EmployeeId --这意味着您的Sales主键和Employee主键都允许不同。

Answer 2

只需在EmployeeID和OldemployeeID上使用backref和Integer即可。否则，您将得到另一个错误。

class Sales(Employee):
    __tablename__ = 'Sales'

    EmployeeID = Column(Integer, ForeignKey('Employee.EmployeeId'), primary_key=True)
    OldemployeeID = Column(Integer, ForeignKey('Employee.EmployeeId'))
    employee = relationship('Employee', foreign_keys=[EmployeeID], backref='Employee')
    old_employee = relationship("Employee", foreign_keys=[OldemployeeID], backref='Employee')