Selecting specific columns with SQLAlchemy inheritance

Question

Using the example from the SQLAlchemy documentation for inheritance, how would I query the Employee class for only the id and engineer_info columns of all derived classes, including ones which don't have the engineer_info column?

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type,
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_info = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_data = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

score 0 · Answer 1 · answered May 21 '20 at 21:05

It looks like you can do this if you explicitly join the employee table to engineer table in a left/outer join, like:

session.query(Employee.id, Engineer.engineer_info).join(Engineer, isouter=True).all()

or:

session.query(Employee.id, Engineer.engineer_info).outerjoin(Engineer).all()

For future searchability: See also SQLAlchemy inheritance filter on all columns which I found by googling "sqlalchemy left join subclass"

Selecting specific columns with SQLAlchemy inheritance

1 Answers1