SqlAlchemy 2.0 中文文档(九)(2)https://developer.aliyun.com/article/1560664
单表继承
单表继承将所有子类的所有属性表示为单个表中的内容。具有特定类别属性的特定子类将在表中的列中保留它们,如果行引用不同类型的对象,则列中将为空。
在层次结构中查询特定子类将呈现为针对基表的 SELECT,其中将包括一个 WHERE 子句,该子句将限制行为具有鉴别器列或表达式中存在的特定值或值。
单表继承相对于连接表继承具有简单性的优势;查询效率更高,因为只需要涉及一个表来加载每个表示类的对象。
单表继承配置看起来很像连接表继承,只是基类指定了__tablename__
。基表还需要一个鉴别器列,以便类之间可以区分开来。
即使子类共享所有属性的基本表,当使用 Declarative 时,仍然可以在子类上指定 mapped_column
对象,指示该列仅映射到该子类;mapped_column
将应用于相同的基本 Table
对象:
class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "employee", } class Manager(Employee): manager_data: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "manager", } class Engineer(Employee): engineer_info: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "engineer", }
注意到派生类 Manager 和 Engineer 的映射器省略了 __tablename__
,表明它们没有自己的映射表。此外,还包括一个带有 nullable=True
的 mapped_column()
指令;由于为这些类声明的 Python 类型不包括 Optional[]
,该列通常会被映射为 NOT NULL
,这对于只期望为对应于特定子类的那些行填充的列来说是不合适的。
使用 use_existing_column
解决列冲突
注意在前一节中,manager_name
和 engineer_info
列被“上移”应用到 Employee.__table__
,因为它们在没有自己表的子类上声明。当两个子类想要指定相同列时,就会出现一个棘手的情况,如下所示:
from datetime import datetime class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "employee", } class Engineer(Employee): __mapper_args__ = { "polymorphic_identity": "engineer", } start_date: Mapped[datetime] = mapped_column(nullable=True) class Manager(Employee): __mapper_args__ = { "polymorphic_identity": "manager", } start_date: Mapped[datetime] = mapped_column(nullable=True)
上面,在 Engineer
和 Manager
上声明的 start_date
列将导致错误:
sqlalchemy.exc.ArgumentError: Column 'start_date' on class Manager conflicts with existing column 'employee.start_date'. If using Declarative, consider using the use_existing_column parameter of mapped_column() to resolve conflicts.
上述情况对 Declarative 映射系统提出了一个模棱两可的问题,可以通过在 mapped_column()
上使用 mapped_column.use_existing_column
参数来解决,该参数指示 mapped_column()
查找并使用已经映射的继承超类上的列,如果已经存在,否则映射一个新列:
from sqlalchemy import DateTime class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "employee", } class Engineer(Employee): __mapper_args__ = { "polymorphic_identity": "engineer", } start_date: Mapped[datetime] = mapped_column( nullable=True, use_existing_column=True ) class Manager(Employee): __mapper_args__ = { "polymorphic_identity": "manager", } start_date: Mapped[datetime] = mapped_column( nullable=True, use_existing_column=True )
在上面的例子中,当Manager
被映射时,start_date
列已经存在于Employee
类上,已经由Engineer
映射提供。mapped_column.use_existing_column
参数指示mapped_column()
应该首先在Employee
的映射Table
上查找请求的Column
,如果存在,则保持该现有映射。如果不存在,mapped_column()
将正常映射该列,将其添加为Employee
超类引用的Table
中的列之一。
2.0.0b4 版本中新增:- 添加了mapped_column.use_existing_column
,提供了一种符合 2.0 版本的方式来有条件地映射继承子类上的列。之前的方法结合了declared_attr
和对父类.__table__
的查找仍然有效,但缺乏PEP 484类型支持。
类似的概念可以与混合类一起使用(参见使用混合类组合映射层次结构)来定义一系列特定的列和/或其他可重用混合类中的映射属性:
class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": type, "polymorphic_identity": "employee", } class HasStartDate: start_date: Mapped[datetime] = mapped_column( nullable=True, use_existing_column=True ) class Engineer(HasStartDate, Employee): __mapper_args__ = { "polymorphic_identity": "engineer", } class Manager(HasStartDate, Employee): __mapper_args__ = { "polymorphic_identity": "manager", }
与单表继承的关系
与单表继承完全支持关系。配置方式与连接继承相同;外键属性应该在与关系的“外键”一侧相同的类上:
class Company(Base): __tablename__ = "company" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] employees: Mapped[List[Employee]] = relationship(back_populates="company") class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] company_id: Mapped[int] = mapped_column(ForeignKey("company.id")) company: Mapped[Company] = relationship(back_populates="employees") __mapper_args__ = { "polymorphic_identity": "employee", "polymorphic_on": "type", } class Manager(Employee): manager_data: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "manager", } class Engineer(Employee): engineer_info: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "engineer", }
此外,与连接继承的情况类似,我们可以创建涉及特定子类的关系。在查询时,SELECT 语句将包含一个 WHERE 子句,将类选择限制为该子类或子类:
class Company(Base): __tablename__ = "company" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] managers: Mapped[List[Manager]] = relationship(back_populates="company") class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_identity": "employee", "polymorphic_on": "type", } class Manager(Employee): manager_name: Mapped[str] = mapped_column(nullable=True) company_id: Mapped[int] = mapped_column(ForeignKey("company.id")) company: Mapped[Company] = relationship(back_populates="managers") __mapper_args__ = { "polymorphic_identity": "manager", } class Engineer(Employee): engineer_info: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "engineer", }
在上面的例子中,Manager
类将具有Manager.company
属性;Company
将具有Company.managers
属性,始终针对具有额外 WHERE 子句的employee
加载,该子句将行限制为type = 'manager'
的行。
使用polymorphic_abstract
构建更深层次的层次结构
2.0 版本中新增。
在构建任何继承层次结构时,一个映射类可以设置Mapper.polymorphic_abstract
参数为True
,这表示该类应该被正常映射,但不希望直接实例化,并且不包含Mapper.polymorphic_identity
。然后可以声明这个映射类的子类,这些子类可以包含Mapper.polymorphic_identity
,因此可以被正常使用。这允许一系列子类被一个公共基类引用,该基类在层次结构中被认为是“抽象的”,无论是在查询中还是在relationship()
声明中。这种用法与在 Declarative 中使用 abstract 属性的用法不同,后者将目标类完全未映射,因此不能单独作为映射类使用。Mapper.polymorphic_abstract
可以应用于层次结构中的任何类或类,包括同时在多个级别上应用。
举例来说,假设Manager
和Principal
都被分类到一个超类Executive
下,而Engineer
和Sysadmin
被分类到一个超类Technologist
下。Executive
和Technologist
都不会被实例化,因此没有Mapper.polymorphic_identity
。这些类可以通过Mapper.polymorphic_abstract
进行配置如下:
class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_identity": "employee", "polymorphic_on": "type", } class Executive(Employee): """An executive of the company""" executive_background: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = {"polymorphic_abstract": True} class Technologist(Employee): """An employee who works with technology""" competencies: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = {"polymorphic_abstract": True} class Manager(Executive): """a manager""" __mapper_args__ = {"polymorphic_identity": "manager"} class Principal(Executive): """a principal of the company""" __mapper_args__ = {"polymorphic_identity": "principal"} class Engineer(Technologist): """an engineer""" __mapper_args__ = {"polymorphic_identity": "engineer"} class SysAdmin(Technologist): """a systems administrator""" __mapper_args__ = {"polymorphic_identity": "sysadmin"}
在上面的例子中,新的类Technologist
和Executive
都是普通的映射类,并且指示要添加到超类中的新列executive_background
和competencies
。然而,它们都缺少Mapper.polymorphic_identity
的设置;这是因为不希望直接实例化Technologist
或Executive
;我们总是会有Manager
、Principal
、Engineer
或SysAdmin
中的一个。但是我们可以查询Principal
和Technologist
角色,并且让它们成为relationship()
的目标。下面的示例演示了针对Technologist
对象的 SELECT 语句:
session.scalars(select(Technologist)).all() SELECT employee.id, employee.name, employee.type, employee.competencies FROM employee WHERE employee.type IN (?, ?) [...] ('engineer', 'sysadmin')
Technologist
和Executive
抽象映射类也可以成为relationship()
映射的目标,就像任何其他映射类一样。我们可以扩展上面的例子,包括Company
,有单独的集合Company.technologists
和Company.principals
:
class Company(Base): __tablename__ = "company" id = Column(Integer, primary_key=True) executives: Mapped[List[Executive]] = relationship() technologists: Mapped[List[Technologist]] = relationship() class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) # foreign key to "company.id" is added company_id: Mapped[int] = mapped_column(ForeignKey("company.id")) # rest of mapping is the same name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": "type", } # Executive, Technologist, Manager, Principal, Engineer, SysAdmin # classes from previous example would follow here unchanged
使用上述映射,我们可以分别跨Company.technologists
和Company.executives
使用连接和关系加载技术:
session.scalars( select(Company) .join(Company.technologists) .where(Technologist.competency.ilike("%java%")) .options(selectinload(Company.executives)) ).all() SELECT company.id FROM company JOIN employee ON company.id = employee.company_id AND employee.type IN (?, ?) WHERE lower(employee.competencies) LIKE lower(?) [...] ('engineer', 'sysadmin', '%java%') SELECT employee.company_id AS employee_company_id, employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type, employee.executive_background AS employee_executive_background FROM employee WHERE employee.company_id IN (?) AND employee.type IN (?, ?) [...] (1, 'manager', 'principal')
另请参阅
abstract - 声明性参数,允许声明性类在层次结构中完全取消映射,同时仍然从映射的超类扩展。
加载单一继承映射
单表继承的加载技术与联接表继承的加载技术基本相同,并且在这两种映射类型之间提供了高度的抽象,使得很容易在它们之间进行切换,以及在单个层次结构中混合使用它们(只需从要单继承的子类中省略 __tablename__
)。请参阅 编写继承映射的 SELECT 语句 和 单一继承映射的 SELECT 语句 部分,了解有关继承加载技术的文档,包括在映射器配置时间和查询时间配置要查询的类。
使用 use_existing_column
解决列冲突
在上一节中注意到,manager_name
和engineer_info
列被“上移”,应用于Employee.__table__
,因为它们在没有自己的表的子类上声明。当两个子类想要指定相同列时会出现棘手的情况,如下所示:
from datetime import datetime class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "employee", } class Engineer(Employee): __mapper_args__ = { "polymorphic_identity": "engineer", } start_date: Mapped[datetime] = mapped_column(nullable=True) class Manager(Employee): __mapper_args__ = { "polymorphic_identity": "manager", } start_date: Mapped[datetime] = mapped_column(nullable=True)
在上述代码中,同时在Engineer
和Manager
上声明的start_date
列将导致错误:
sqlalchemy.exc.ArgumentError: Column 'start_date' on class Manager conflicts with existing column 'employee.start_date'. If using Declarative, consider using the use_existing_column parameter of mapped_column() to resolve conflicts.
上述情景对声明性映射系统存在一种模糊性,可以通过在mapped_column()
上使用mapped_column.use_existing_column
参数来解决,该参数指示mapped_column()
查找继承的超类,并使用已经存在的列,如果已经存在,则映射新列:
from sqlalchemy import DateTime class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": "type", "polymorphic_identity": "employee", } class Engineer(Employee): __mapper_args__ = { "polymorphic_identity": "engineer", } start_date: Mapped[datetime] = mapped_column( nullable=True, use_existing_column=True ) class Manager(Employee): __mapper_args__ = { "polymorphic_identity": "manager", } start_date: Mapped[datetime] = mapped_column( nullable=True, use_existing_column=True )
在上文中,当 Manager
被映射时,start_date
列已经存在于 Employee
类中,已经由之前的 Engineer
映射提供。mapped_column.use_existing_column
参数指示给 mapped_column()
,它应该首先查找映射到 Employee
的映射 Table
上的请求的 Column
,如果存在,则保持该现有映射。如果不存在,mapped_column()
将正常映射列,将其添加为 Employee
超类引用的 Table
中的列之一。
新版本 2.0.0b4 中新增:- 添加了 mapped_column.use_existing_column
,它提供了一个与 2.0 兼容的方法,以条件地映射继承子类上的列。先前的方法结合了 declared_attr
与对父类 .__table__
的查找,仍然有效,但缺少了 PEP 484 类型支持。
一个类似的概念可以与 mixin 类一起使用(参见 Composing Mapped Hierarchies with Mixins)来定义来自可重用 mixin 类的特定系列列和/或其他映射属性:
class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_on": type, "polymorphic_identity": "employee", } class HasStartDate: start_date: Mapped[datetime] = mapped_column( nullable=True, use_existing_column=True ) class Engineer(HasStartDate, Employee): __mapper_args__ = { "polymorphic_identity": "engineer", } class Manager(HasStartDate, Employee): __mapper_args__ = { "polymorphic_identity": "manager", }
单表继承的关系
关系在单表继承中得到充分支持。配置方式与联接继承的方式相同;外键属性应位于与关系的“外部”一侧相同的类上:
class Company(Base): __tablename__ = "company" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] employees: Mapped[List[Employee]] = relationship(back_populates="company") class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] company_id: Mapped[int] = mapped_column(ForeignKey("company.id")) company: Mapped[Company] = relationship(back_populates="employees") __mapper_args__ = { "polymorphic_identity": "employee", "polymorphic_on": "type", } class Manager(Employee): manager_data: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "manager", } class Engineer(Employee): engineer_info: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "engineer", }
类似于联接继承的情况,我们也可以创建涉及特定子类的关系。当查询时,SELECT 语句将包含一个 WHERE 子句,将类的选择限制为该子类或子类:
class Company(Base): __tablename__ = "company" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] managers: Mapped[List[Manager]] = relationship(back_populates="company") class Employee(Base): __tablename__ = "employee" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] type: Mapped[str] __mapper_args__ = { "polymorphic_identity": "employee", "polymorphic_on": "type", } class Manager(Employee): manager_name: Mapped[str] = mapped_column(nullable=True) company_id: Mapped[int] = mapped_column(ForeignKey("company.id")) company: Mapped[Company] = relationship(back_populates="managers") __mapper_args__ = { "polymorphic_identity": "manager", } class Engineer(Employee): engineer_info: Mapped[str] = mapped_column(nullable=True) __mapper_args__ = { "polymorphic_identity": "engineer", }
上文中,Manager
类将具有一个 Manager.company
属性;Company
将具有一个 Company.managers
属性,该属性始终加载针对具有附加 WHERE 子句的 employee
,限制行为具有 type = 'manager'
的行。
SqlAlchemy 2.0 中文文档(九)(4)https://developer.aliyun.com/article/1560677