版本:1.1.0b2 |发布日期:2016年7月1日

SQLAlchemy 1.1文档

映射类继承层次结构

SQLAlchemy支持三种形式的继承:单表继承,其中几种类型由单个表格表示,具体表继承,其中每种类型由独立表示表和连接表继承,其中类层次结构在相关表之间分解,每个类由其自己的表示,只包括那些类的本地属性。

最常见的继承形式是单一的和连接的表格,而具体的继承呈现更多的配置上的挑战。

当映射器配置为继承关系时,SQLAlchemy能够加载polymorphically,这意味着一个查询可以返回多个类型的对象。

连接表继承

在连接的表继承中,沿着父类的特定类列表的每个类都由唯一的表来表示。特定实例的全部属性集合表示为沿其继承路径中所有表的连接。在这里,我们首先定义Employee类。该表将包含主键列(或多个列),以及由Employee表示的每个属性的列。在这种情况下,只是name

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
    }

映射表也有一个名为type的列。该列的作用是作为鉴别符,并存储一个值,该值指示行内表示的对象的类型。该列可以是任何数据类型,尽管字符串和整数是最常见的。

警告

Currently, only one discriminator column may be set, typically on the base-most class in the hierarchy. “级联”多态列还不被支持。

只有在需要多态加载时才需要鉴别器列,正如通常情况那样。它不是直接存在于基本映射表上,而是可以在查询类时使用的派生select语句上定义;但是,这是一个更复杂的配置方案。

映射通过__mapper_args__字典接收附加参数。在这里,type列明确表示为鉴别器列,还给出employee多态标识符这是将存储在该类的实例的多态鉴别器列中的值。

我们接下来定义EmployeeEngineerManager子类。每个都包含表示它们表示的子类的唯一属性的列。每个表还必须包含一个主键列(或多个列),而且在大多数情况下还需要对父表进行外键引用:

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',
    }

标准做法是将同一列用于主键的角色以及对父表的外键,并且该列的名称也与父表的名称相同。但是,这两种做法都是可选的。单独的列可以用于主键和父 - 关系,列的名称可以不同于父列,甚至可以在父表和子表之间指定自定义连接条件,而不使用外键。

加入继承主键

连接表继承配置的一个自然结果是任何映射对象的标识都可以完全从基表中确定。这具有明显的优势,所以SQLAlchemy总是将加入继承类的主键列视为基表的唯一键列。In other words, the id columns of both the engineer and manager tables are not used to locate Engineer or Manager objects - only the value in employee.id is considered. engineer.id and manager.id are still of course critical to the proper operation of the pattern overall as they are used to locate the joined row, once the parent row has been determined within a statement.

完成联合继承映射后,查询Employee将返回EmployeeEngineerManager对象的组合。Newly saved Engineer, Manager, and Employee objects will automatically populate the employee.type column with engineer, manager, or employee, as appropriate.

查询表的基本控制

orm.with_polymorphic()函数和Querywith_polymorphic()方法会影响Query从中选择。通常,这样的查询:

session.query(Employee).all()

...仅从employee表中选择。从数据库加载新鲜数据时,我们的连接表设置将仅使用如下所示的SQL从父表进行查询:

SELECT employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type FROM employee []

由于从engineermanager子表中的Employee对象请求了属性,所以会为列中的该相关行,如果数据尚未加载。因此,在访问这些对象之后,您会看到更多的SQL按照以下方式发布:

SELECT manager.id AS manager_id, manager.manager_data AS manager_manager_data FROM manager WHERE ? = manager.id [5] SELECT engineer.id AS engineer_id, engineer.engineer_info AS engineer_engineer_info FROM engineer WHERE ? = engineer.id [2]

这种行为在发布少量项目的搜索(如使用Query.get())时效果很好,因为连接表的全部范围不会被不必要地引入到SQL语句中。但是,当查询大量已知类型很多的行时,可能需要主动加入部分或全部连接的表。with_polymorphic特性提供了这个功能。

告诉我们的查询要多态加载EngineerManager对象,我们可以使用orm.with_polymorphic()函数来创建一个新的别名类选择与每个继承表的外连接相结合的基表:

from sqlalchemy.orm import with_polymorphic

eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])

query = session.query(eng_plus_manager)

上面产生一个查询,它将employee表连接到engineermanager表,如下所示:

query.all()
SELECT employee.id AS employee_id, engineer.id AS engineer_id, manager.id AS manager_id, employee.name AS employee_name, employee.type AS employee_type, engineer.engineer_info AS engineer_engineer_info, manager.manager_data AS manager_manager_data FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id LEFT OUTER JOIN manager ON employee.id = manager.id []

orm.with_polymorphic()返回的实体是一个AliasedClass对象,它可以像任何其他别名一样在Query中使用,包括命名属性对于Employee类中的这些属性。在我们的例子中,eng_plus_manager成为我们用来引用上面三路外连接的实体。它还包括在类列表中命名的每个类的名称空间,以便可以调用特定于这些子类的属性。下面的例子说明了如何根据eng_plus_manager调用特定于Engineer以及Manager的属性:

eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
query = session.query(eng_plus_manager).filter(
                or_(
                    eng_plus_manager.Engineer.engineer_info=='x',
                    eng_plus_manager.Manager.manager_data=='y'
                )
            )

orm.with_polymorphic() accepts a single class or mapper, a list of classes/mappers, or the string '*' to indicate all subclasses:

# join to the engineer table
entity = with_polymorphic(Employee, Engineer)

# join to the engineer and manager tables
entity = with_polymorphic(Employee, [Engineer, Manager])

# join to all subclass tables
entity = with_polymorphic(Employee, '*')

# use the 'entity' with a Query object
session.query(entity).all()

它还接受第三个参数selectable,它取代了自动连接创建,而直接从可选的给定中选择。这个特性通常和后面描述的“具体”继承一起使用,但是可以和任何类型的继承设置一起使用,如果使用专门的SQL来加载多态:

# custom selectable
employee = Employee.__table__
manager = Manager.__table__
engineer = Engineer.__table__
entity = with_polymorphic(
            Employee,
            [Engineer, Manager],
            employee.outerjoin(manager).outerjoin(engineer)
        )

# use the 'entity' with a Query object
session.query(entity).all()

请注意,如果您只需要加载一个子类型,例如Engineer对象,则不需要orm.with_polymorphic(),因为您将针对Engineer

Query.with_polymorphic() has the same purpose as orm.with_polymorphic(), except is not as flexible in its usage patterns in that it only applies to the first full mapping, which then impacts all occurrences of that class or the target subclasses within the Query. 对于简单的情况,可以认为它更简洁:

session.query(Employee).with_polymorphic([Engineer, Manager]).\
    filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q'))

New in version 0.8: orm.with_polymorphic(), an improved version of Query.with_polymorphic() method.

该映射器还接受with_polymorphic作为配置参数,以便自动发布连接样式的加载。这个参数可能是字符串'*',一个类的列表,或者是一个元组,或者是一个元组,后面跟着一个可选的:

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

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

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

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

上面的映射将为Employee对象的每个查询生成一个类似于with_polymorphic('*')的查询。

使用orm.with_polymorphic()Query.with_polymorphic()将覆盖映射器级别的with_polymorphic设置。

sqlalchemy.orm.with_polymorphic(base, classes, selectable=False, flat=False, polymorphic_on=None, aliased=False, innerjoin=False, _use_mapper_path=False, _existing_alias=None)

生成一个AliasedClass构造,它为给定基的后代映射器指定列。

New in version 0.8: orm.with_polymorphic() is in addition to the existing Query method Query.with_polymorphic(), which has the same purpose but is not as flexible in its usage.

使用此方法将确保每个后代映射程序的表都包含在FROM子句中,并允许针对这些表使用filter()标准。结果的实例也会有那些已经加载的列,所以不需要这些列的“post fetch”。

请参阅Basic Control of Which Tables are Queried

参数:
  • base - 要被别名的基类。
  • classes – a single class or mapper, or list of class/mappers, which inherit from the base class. 或者,它也可能是字符串'*',在这种情况下,所有递减的映射类将被添加到FROM子句中。
  • aliased – when True, the selectable will be wrapped in an alias, that is (SELECT * FROM <fromclauses>) AS anon_1. 当使用with_polymorphic()在不支持括号连接的后端(如SQLite和旧版本的MySQL)上创建JOIN的目标时,这可能很重要。
  • 扁平 -
    布尔,将通过传递给
    FromClause.alias() call so that aliases of Join objects don’t include an enclosing SELECT. 这可以在许多情况下导致更高效的查询。对于嵌套JOIN的JOIN将被重写为针对不支持此语法的后端上的别名SELECT子查询的JOIN。

    flat设置为True意味着aliased标志也是True

    版本0.9.0中新增。

    也可以看看

    Join.alias()

  • selectable – a table or select() statement that will be used in place of the generated FROM clause. 如果任何所需的类使用具体的表继承,则此参数是必需的,因为SQLAlchemy当前不能自动在表之间生成UNION。如果使用selectable参数,则必须表示由每个映射类映射的全部表和列。否则,未映射的映射列将导致它们的表直接附加到FROM子句,这通常会导致不正确的结果。
  • polymorphic_on - 用作给定可选项的“鉴别器”列的列。如果没有给定,基类的映射器的polymorphic_on属性将被使用,如果有的话。这对默认情况下没有多态加载行为的映射很有用。
  • innerjoin – if True, an INNER JOIN will be used. 这应该只在查询一个特定的子类型时才被指定

高级控制哪些表被查询

The with_polymorphic functions work fine for simplistic scenarios. 但是,需要对表格渲染进行直接控制,比如当只想渲染子类表而不渲染父表时。

这个用例可以直接使用映射的Table对象来实现。例如,要查询具有特定标准的员工姓名:

engineer = Engineer.__table__
manager = Manager.__table__

session.query(Employee.name).\
    outerjoin((engineer, engineer.c.employee_id==Employee.employee_id)).\
    outerjoin((manager, manager.c.employee_id==Employee.employee_id)).\
    filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q'))

基表,在这种情况下,“雇员”表,并不总是必要的。一个SQL查询总是更有效率,更少的联接。在这里,如果我们只想加载特定于经理或工程师的信息,我们可以指示Query仅使用那些表。FROM子句由Session.query()Query.filter()Query.select_from()方法:

session.query(Manager.manager_data).select_from(manager)

session.query(engineer.c.id).\
        filter(engineer.c.engineer_info==manager.c.manager_data)

创建连接到特定的子类型

of_type()方法是一个帮助器,它允许沿着relationship()路径构建连接,同时将标准缩小到特定的子类。假设employees表代表与Company对象关联的员工集合。我们将在employees表和一个新表companies中添加一个company_id列:

class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    employees = relationship("Employee",
                    backref='company',
                    cascade='all, delete-orphan')

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(20))
    company_id = Column(Integer, ForeignKey('company.id'))
    __mapper_args__ = {
        'polymorphic_on':type,
        'polymorphic_identity':'employee',
        'with_polymorphic':'*'
    }

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'}

When querying from Company onto the Employee relationship, the join() method as well as the any() and has() operators will create a join from company to employee, without including engineer or manager in the mix. 如果我们希望具有专门针对Engineer类的标准,则可以使用of_type()运算符告诉这些方法连接或子查询表示子类的连接表:

session.query(Company).\
    join(Company.employees.of_type(Engineer)).\
    filter(Engineer.engineer_info=='someinfo')

这个的一个长手势版本将涉及到在2元组中可选的完整目标:

employee = Employee.__table__
engineer = Engineer.__table__

session.query(Company).\
    join((employee.join(engineer), Company.employees)).\
    filter(Engineer.engineer_info=='someinfo')

of_type() accepts a single class argument. 通过连接到上面的显式连接,或者使用orm.with_polymorphic()函数创建一个多态可选:

manager_and_engineer = with_polymorphic(
                            Employee, [Manager, Engineer],
                            aliased=True)

session.query(Company).\
    join(manager_and_engineer, Company.employees).\
    filter(
        or_(manager_and_engineer.Engineer.engineer_info=='someinfo',
            manager_and_engineer.Manager.manager_data=='somedata')
    )

Above, we use the aliased=True argument with orm.with_polymorhpic() so that the right hand side of the join between Company and manager_and_engineer is converted into an aliased subquery. 某些后端(如SQLite和较早版本的MySQL)无法处理以下形式的FROM子句:

FROM x JOIN (y JOIN z ON <onclause>) ON <onclause>

使用aliased=True来代替它:

FROM x JOIN (SELECT * FROM y JOIN z ON <onclause>) AS anon_1 ON <onclause>

上面的连接也可以通过结合of_type()和多态构造来更加简洁地表达:

manager_and_engineer = with_polymorphic(
                            Employee, [Manager, Engineer],
                            aliased=True)

session.query(Company).\
    join(Company.employees.of_type(manager_and_engineer)).\
    filter(
        or_(manager_and_engineer.Engineer.engineer_info=='someinfo',
            manager_and_engineer.Manager.manager_data=='somedata')
    )

当嵌入式标准根据子类时,any()has()运算符也可以与of_type()

session.query(Company).\
        filter(
            Company.employees.of_type(Engineer).
                any(Engineer.engineer_info=='someinfo')
            ).all()

请注意,any()has()都是相关EXISTS查询的简写。用手建立一个看起来像:

session.query(Company).filter(
    exists([1],
        and_(Engineer.engineer_info=='someinfo',
            employees.c.company_id==companies.c.company_id),
        from_obj=employees.join(engineers)
    )
).all()

The EXISTS subquery above selects from the join of employees to engineers, and also specifies criterion which correlates the EXISTS subselect back to the parent companies table.

New in version 0.8: of_type() accepts orm.aliased() and orm.with_polymorphic() constructs in conjunction with Query.join(), any() and has().

急切地加载特定或多形的子类型

joinedload()subqueryload()contains_eager()和其他加载相关的选项也支持使用of_type()下面我们加载Company行,同时热切地加载相关的Engineer对象,同时查询employeeengineer

session.query(Company).\
    options(
        subqueryload(Company.employees.of_type(Engineer)).
        subqueryload("machines")
        )
    )

Query.join()一样,of_type()也可以用于加载和orm.with_polymorphic()同时,可以加载所有引用子类型的所有子属性:

manager_and_engineer = with_polymorphic(
                            Employee, [Manager, Engineer],
                            aliased=True)

session.query(Company).\
    options(
        joinedload(Company.employees.of_type(manager_and_engineer))
        )
    )

New in version 0.8: joinedload(), subqueryload(), contains_eager() and related loader options support paths that are qualified with of_type(), supporting single target types as well as orm.with_polymorphic() targets.

上述查询的另一个选项是分别陈述两个子类型; joinedload()指令应该检测到这一点,并自动创建上面的with_polymorphic结构:

session.query(Company).\
    options(
        joinedload(Company.employees.of_type(Manager)),
        joinedload(Company.employees.of_type(Engineer)),
        )
    )

版本1.0中的新增内容当多重重叠的of_type()指令遇到时,像joinedload()这样的热切加载器将创建一个多态实体。

单表继承

单个表继承是基类的属性以及所有子类在一个表内表示的地方。每个属性都映射到基类和所有子类,表中存在一列;对应于单个子类的列是可空的。除了只有一个表外,这个配置看起来很像连接表继承。在这种情况下,需要一个type列,因为没有其他的方法来区分类。该表仅在基本映射器中指定;对于继承类,将它们的table参数保留为空:

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

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

class Manager(Employee):
    __mapper_args__ = {
        'polymorphic_identity':'manager'
    }

class Engineer(Employee):
    __mapper_args__ = {
        'polymorphic_identity':'engineer'
    }

请注意,派生类Manager和Engineer的映射器省略了__tablename__,表示它们没有自己的映射表。

具体表继承

这种继承形式将每个类映射到不同的表。由于具体继承有更多的概念开销,首先我们将说明这些表看起来像Core表元数据:

employees_table = Table(
    'employee', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
)

managers_table = Table(
    'manager', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('manager_data', String(50)),
)

engineers_table = Table(
    'engineer', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('engineer_info', String(50)),
)

注意在这种情况下没有type列;对于多态加载,为了在查询期间“制造”这些信息将需要额外的步骤。

使用经典的映射,我们可以独立映射我们的三个类,而不需要任何关系。从Employee继承的EngineerManager这一事实对经典映射没有任何影响:

class Employee(object):
    pass

class Manager(Employee):
    pass

class Engineer(Employee):
    pass

mapper(Employee, employees_table)
mapper(Manager, managers_table)
mapper(Engineer, engineers_table)

但是,当使用Declarative时,Declarative会假设类之间的继承映射,因为它们已经处于继承关系中。所以要声明地映射我们的三个类,我们必须在__mapper_args__中包含orm.mapper.concrete参数:

class Employee(Base):
    __tablename__ = 'employee'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))

class Manager(Employee):
    __tablename__ = 'manager'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    manager_data = Column(String(50))

    __mapper_args__ = {
        'concrete': True
    }

class Engineer(Employee):
    __tablename__ = 'engineer'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    engineer_info = Column(String(50))

    __mapper_args__ = {
        'concrete': True
    }

应该注意两个关键点:

  • We must define all columns explicitly on each subclass, even those of the same name. Employee.name这样的列是not复制到由ManagerEngineer映射的表中。
  • while the Engineer and Manager classes are mapped in an inheritance relationship with Employee, they still do not include polymorphic loading.

具体多态加载

要多态地加载,需要orm.mapper.with_polymorphic参数,以及一个可选的指示如何加载行。多态加载对于具体的继承来说是效率最低的,所以如果我们寻求这种加载方式的话,虽然可能不太推荐。在具体继承的情况下,就意味着我们必须建立三个表的联盟。

首先用经典的映射来说明这一点,SQLAlchemy包含一个辅助函数来创建这个名为polymorphic_union()的UNION,它将把所有不同的列映射到具有相同数字和列名的选择结构中,还为每个子查询生成一个虚拟的type列。在所有三个表都被声明之后,函数被称为,然后与映射器结合:

from sqlalchemy.orm import polymorphic_union

pjoin = polymorphic_union({
    'employee': employees_table,
    'manager': managers_table,
    'engineer': engineers_table
}, 'type', 'pjoin')

employee_mapper = mapper(Employee, employees_table,
                                    with_polymorphic=('*', pjoin),
                                    polymorphic_on=pjoin.c.type,
                                    polymorphic_identity='employee')
manager_mapper = mapper(Manager, managers_table,
                                    inherits=employee_mapper,
                                    concrete=True,
                                    polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, engineers_table,
                                    inherits=employee_mapper,
                                    concrete=True,
                                    polymorphic_identity='engineer')

选择后,多态联合会产生一个像这样的查询:

session.query(Employee).all()
SELECT pjoin.id AS pjoin_id, pjoin.name AS pjoin_name, pjoin.type AS pjoin_type, pjoin.manager_data AS pjoin_manager_data, pjoin.engineer_info AS pjoin_engineer_info FROM ( SELECT employee.id AS id, employee.name AS name, CAST(NULL AS VARCHAR(50)) AS manager_data, CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS type FROM employee UNION ALL SELECT manager.id AS id, manager.name AS name, manager.manager_data AS manager_data, CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS type FROM manager UNION ALL SELECT engineer.id AS id, engineer.name AS name, CAST(NULL AS VARCHAR(50)) AS manager_data, engineer.engineer_info AS engineer_info, 'engineer' AS type FROM engineer ) AS pjoin

上面的UNION查询需要为每个子表制造“NULL”列,以适应那些不属于映射的列。

为了使用Declarative来映射具体的继承和多态的加载,挑战是在创建映射时准备好多态联合。实现这一目标的一种方法是继续在实际映射类之前定义表元数据,并使用__table__将其指定给每个类:

class Employee(Base):
    __table__ = employee_table
    __mapper_args__ = {
        'polymorphic_on':pjoin.c.type,
        'with_polymorphic': ('*', pjoin),
        'polymorphic_identity':'employee'
    }

class Engineer(Employee):
    __table__ = engineer_table
    __mapper_args__ = {'polymorphic_identity':'engineer', 'concrete':True}

class Manager(Employee):
    __table__ = manager_table
    __mapper_args__ = {'polymorphic_identity':'manager', 'concrete':True}

使用声明式助手类

另一种方法是使用一个特殊的帮助类,它承担推迟Mapper对象生产的相当复杂的任务,直到收集到所有表元数据,并且映射关联的多态联合将能得到的。这可以通过AbstractConcreteBaseConcreteBase类来获得。就我们这里的例子而言,我们使用“混凝土”基础,例如一个Employee行本身可以存在,而不是EngineerManager映射将如下所示:

from sqlalchemy.ext.declarative import ConcreteBase

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

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'concrete':True
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    manager_data = Column(String(40))

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

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    engineer_info = Column(String(40))

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

也可以选择使用所谓的“抽象”基础;我们根本不会有一个employee表,而只会有managerengineer表。The Employee class will never be instantiated directly. 这里的变化是基本映射器直接映射到可选择的“多态联合”,它不再包含employee表。在经典的映射中,这是:

from sqlalchemy.orm import polymorphic_union

pjoin = polymorphic_union({
    'manager': managers_table,
    'engineer': engineers_table
}, 'type', 'pjoin')

employee_mapper = mapper(Employee, pjoin,
                                    with_polymorphic=('*', pjoin),
                                    polymorphic_on=pjoin.c.type)
manager_mapper = mapper(Manager, managers_table,
                                    inherits=employee_mapper,
                                    concrete=True,
                                    polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, engineers_table,
                                    inherits=employee_mapper,
                                    concrete=True,
                                    polymorphic_identity='engineer')

使用声明助手,AbstractConcreteBase助手可以产生这个;映射将是:

from sqlalchemy.ext.declarative import AbstractConcreteBase

class Employee(AbstractConcreteBase, Base):
    pass

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    manager_data = Column(String(40))

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

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    engineer_info = Column(String(40))

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

也可以看看

声明式参考文档中的Concrete Table Inheritance

使用与继承的关系

连接表和单表继承方案都会产生可用于relationship()函数的映射。也就是说,可以将父对象映射到多态的子对象。类似地,继承映射器可以在任何级别都有relationship()对象,它们被继承到每个子类。关系的唯一要求是父母和孩子之间有表格关系。一个例子是以下对连接表继承的例子的修改,它在EmployeeCompany之间设置了一个双向关系:

employees_table = Table('employees', metadata,
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('company_id', Integer, ForeignKey('companies.company_id'))
)

companies = Table('companies', metadata,
   Column('company_id', Integer, primary_key=True),
   Column('name', String(50)))

class Company(object):
    pass

mapper(Company, companies, properties={
   'employees': relationship(Employee, backref='company')
})

与具体继承的关系

在具体的继承场景中,映射关系更具挑战性,因为不同的类不共享表。在这种情况下,如果每个子表都包含一个到父项的外键,那么如果可以从父项到子项构建联接条件,则可以建立从父项到子项的关系:

companies = Table('companies', metadata,
   Column('id', Integer, primary_key=True),
   Column('name', String(50)))

employees_table = Table('employees', metadata,
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('company_id', Integer, ForeignKey('companies.id'))
)

managers_table = Table('managers', metadata,
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('manager_data', String(50)),
    Column('company_id', Integer, ForeignKey('companies.id'))
)

engineers_table = Table('engineers', metadata,
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('engineer_info', String(50)),
    Column('company_id', Integer, ForeignKey('companies.id'))
)

mapper(Employee, employees_table,
                with_polymorphic=('*', pjoin),
                polymorphic_on=pjoin.c.type,
                polymorphic_identity='employee')

mapper(Manager, managers_table,
                inherits=employee_mapper,
                concrete=True,
                polymorphic_identity='manager')

mapper(Engineer, engineers_table,
                inherits=employee_mapper,
                concrete=True,
                polymorphic_identity='engineer')

mapper(Company, companies, properties={
    'employees': relationship(Employee)
})

The big limitation with concrete table inheritance is that relationship() objects placed on each concrete mapper do not propagate to child mappers. 如果要在所有具体的映射器上设置相同的relationship()对象,则必须手动配置它们。To configure back references in such a configuration the back_populates keyword may be used instead of backref, such as below where both A(object) and B(A) bidirectionally reference C:

ajoin = polymorphic_union({
        'a':a_table,
        'b':b_table
    }, 'type', 'ajoin')

mapper(A, a_table, with_polymorphic=('*', ajoin),
    polymorphic_on=ajoin.c.type, polymorphic_identity='a',
    properties={
        'some_c':relationship(C, back_populates='many_a')
})
mapper(B, b_table,inherits=A, concrete=True,
    polymorphic_identity='b',
    properties={
        'some_c':relationship(C, back_populates='many_a')
})
mapper(C, c_table, properties={
    'many_a':relationship(A, collection_class=set,
                                back_populates='some_c'),
})

声明式使用继承

声明使得继承配置更直观。请参阅Inheritance Configuration上的文档。