Sqlalchemy Can't Join Two Tables With Two Foreign Keys Between Them

The code below does not work due to this line owner_id = Column(Integer, ForeignKey('employees.employee_id')) in Manager class. SQLAlchemy generates error message: AmbiguousForei

Solution 1:

SQLAlchemy is confused about how to join Manager to Employee because you have multiple foreign keys between the two tables, employee_id and owner_id. In this case, you need to specify the inherit_condition to the mapper explicitly:

classManager(Employee):
    __tablename__ = 'managers'

    employee_id = Column(Integer, ForeignKey('employees.employee_id'),
                         primary_key=True)
    manager_data = Column(String(50))

    owner_id = Column(Integer, ForeignKey('employees.employee_id'))

    __mapper_args__ = {'polymorphic_identity': 'manager', 'inherit_condition': employee_id == Employee.employee_id}

Solution 2:

You are both extending parent model as well as defining relationship to the same parent model.

simply use one way, you could simply created relations and have all your models extend base class

classEmployee(Base):
    __tablename__ = 'employees'

    employee_id = Column(Integer, primary_key=True)
    ...
classOwner(Base):
    __tablename__ = 'owners'
    __mapper_args__ = {'polymorphic_identity': 'owner'}
    owner_id = Column(Integer, primary_key=True)
    ...
    ...
classManager(Base):
    __tablename__ = 'managers'
    __mapper_args__ = {'polymorphic_identity': 'manager'}

    manager_id = Column(Integer, primary_key=True)
    employee_id = Column(Integer, ForeignKey('employees.employee_id'), primary_key=True)
    owner_id = Column(Integer, ForeignKey('owners.owner_id'))
    ...
    ...

add the rest of relevant field where indicated as ....