sqlalchemy mapper配置之Joined Table Inheritance

In joined table inheritance, each class along a hierarchy of classes is represented by a distinct table. Querying for a particular subclass in the hierarchy will render as a SQL JOIN along all tables in its inheritance path. If the queried class is the base class, the default behavior is to include only the base table in a SELECT statement. In all cases, the ultimate class to instantiate for a given row is determined by a discriminator column or an expression that works against the base table. When a subclass is loaded only against a base table, resulting objects will have base attributes populated at first; attributes that are local to the subclass will lazy load when they are accessed. Alternatively, there are options which can change the default behavior, allowing the query to include columns corresponding to multiple tables/subclasses up front.

采用连接表继承，继承路径中每个映射类都和数据库中一个独立的表建立关联。当针对继承层次中某个子类发出查询时，sqlalchemy 将自动链接/Join该继承类的继承路径中的所有类。如果查询是针对基类的，SELECT语句的默认行为是只包括基类对应的表。在所有情况下，要为给定行实例化的最终类都是由一个鉴别器列或对基表有效的表达式确定的。 当一个子类仅加载到一个基表时，结果对象将首先填充基类的基本属性; 子类的本地属性则消极加载，只有在被访问时加载进来。

The base class in a joined inheritance hierarchy is configured with additional arguments that will refer to the polymorphic discriminator column as well as the identifier for the base class:

另外，还有一些选项可以更改默认行为，允许查询预先包含与多个表 / 子类对应的列。 联合继承层次结构中的基类配置了额外的参数，这些参数将引用多态鉴别器列以及多态的基类标识符:

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
    }

Above, an additional column type is established to act as the discriminator, configured as such using the mapper.polymorphic_on parameter. This column will store a value which indicates the type of object represented within the row. The column may be of any datatype, though string and integer are the most common.

While a polymorphic discriminator expression is not strictly necessary, it is required if polymorphic loading is desired. Establishing a simple column on the base table is the easiest way to achieve this, however very sophisticated inheritance mappings may even configure a SQL expression such as a CASE statement as the polymorphic discriminator.

We next define Engineer and Manager subclasses of Employee. Each contains columns that represent the attributes unique to the subclass they represent. Each table also must contain a primary key column (or columns), as well as a foreign key reference to the parent table:

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

It is most common that the foreign key constraint is established on the same column or columns as the primary key itself, however this is not required; a column distinct from the primary key may also be made to refer to the parent via foreign key. The way that a JOIN is constructed from the base table to subclasses is also directly customizable, however this is rarely necessary.

With the joined inheritance mapping complete, querying against Employee will return a combination of Employee, Engineerand Manager objects. Newly saved Engineer, Manager, and Employee objects will automatically populate the employee.type column with the correct “discriminator” value in this case "engineer", "manager", or "employee", as appropriate.

class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    employees = relationship("Employee", back_populates="company")

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))
    company_id = Column(ForeignKey('company.id'))
    company = relationship("Company", back_populates="employees")

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

class Manager(Employee):
    # ...

class Engineer(Employee):
    # ...

class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    managers = relationship("Manager", back_populates="company")

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 Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    company_id = Column(ForeignKey('company.id'))
    company = relationship("Company", back_populates="managers")

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

class Engineer(Employee):
    # ...