python - 继承后sqlalchemy.exc.AmbiguousForeignKeysError

Question

我正在使用 sqlacodegen 从我的数据库中反射(reflect)一堆表。我收到以下错误:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'Employee' and 'Sales'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

这是我的表格的简化版本。我在文档中读到我应该使用 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 == Employee.EmployeeId 继承 OldEmployeeID - 这意味着您的 Sales 主键和 Employee 主键允许不同。