声明式的表配置¶

Table Configuration with Declarative

中文

如声明式映射中所介绍的，声明式风格包括同时生成映射的 Table 对象的能力，或直接容纳 Table 或其他 FromClause 对象。

以下示例假设一个声明式基类，如:

from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass

以下所有示例都说明了从上述 Base 继承的类。使用装饰器进行声明性映射（无声明性基类）中介绍的装饰器风格在以下所有示例中也完全支持，声明式基类的旧形式也是如此，包括由 declarative_base() 生成的基类。

英文

As introduced at 声明式映射, the Declarative style includes the ability to generate a mapped Table object at the same time, or to accommodate a Table or other FromClause object directly.

The following examples assume a declarative base class as:

from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass

All of the examples that follow illustrate a class inheriting from the above Base. The decorator style introduced at 使用装饰器进行声明性映射（无声明性基类） is fully supported with all the following examples as well, as are legacy forms of Declarative Base including base classes generated by declarative_base().

使用命令式表的声明式（又名混合声明式）¶

Declarative with Imperative Table (a.k.a. Hybrid Declarative)

中文

声明式映射还可以使用预先存在的 Table 对象，或者其他任意的 FromClause 构造（例如 Join 或 Subquery），这些构造是单独构建的。

这被称为“混合声明式(hybrid declarative)”映射，因为类使用声明式样式进行所有涉及映射器配置的操作，但映射的 Table 对象是单独生成的，并直接传递给声明式过程:

from sqlalchemy import Column, ForeignKey, Integer, String, Table
from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass


# 直接构建Table。 Base.metadata 集合通常是MetaData的一个不错选择，但可以使用任何MetaData集合。

user_table = Table(
    "user",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
    Column("fullname", String),
    Column("nickname", String),
)


# 使用此表构建User类。
class User(Base):
    __table__ = user_table

以上，Table 对象是使用使用元数据描述数据库中描述的方法构建的。然后可以直接应用于声明式映射的类。此形式不使用 __tablename__ 和 __table_args__ 声明类属性。上面的配置通常更易读为内联定义:

class User(Base):
    __table__ = Table(
        "user",
        Base.metadata,
        Column("id", Integer, primary_key=True),
        Column("name", String),
        Column("fullname", String),
        Column("nickname", String),
    )

上述样式的一个自然结果是 __table__ 属性本身在类定义块中定义。因此，可以在后续属性中立即引用，例如下面的示例，该示例说明在多态映射器配置中引用 type 列:

class Person(Base):
    __table__ = Table(
        "person",
        Base.metadata,
        Column("id", Integer, primary_key=True),
        Column("name", String(50)),
        Column("type", String(50)),
    )

    __mapper_args__ = {
        "polymorphic_on": __table__.c.type,
        "polymorphic_identity": "person",
    }

当要映射非 Table 构造（例如 Join 或 Subquery 对象）时，也使用“命令式表(imperative table)”形式。下面是一个示例:

from sqlalchemy import func, select

subq = (
    select(
        func.count(orders.c.id).label("order_count"),
        func.max(orders.c.price).label("highest_order"),
        orders.c.customer_id,
    )
    .group_by(orders.c.customer_id)
    .subquery()
)

customer_select = (
    select(customers, subq)
    .join_from(customers, subq, customers.c.id == subq.c.customer_id)
    .subquery()
)


class Customer(Base):
    __table__ = customer_select

有关映射到非 Table 构造的背景，请参见将一个类映射到多个表和将一个类映射到任意子查询部分。

当类本身使用替代形式的属性声明（例如Python数据类）时，“命令式表(imperative table)”形式特别有用。有关详细信息，请参见将 ORM 映射应用于现有数据类（旧式数据类使用）部分。

参见

使用元数据描述数据库

将 ORM 映射应用于现有数据类（旧式数据类使用）

英文

Declarative mappings may also be provided with a pre-existing Table object, or otherwise a Table or other arbitrary FromClause construct (such as a Join or Subquery) that is constructed separately.

This is referred to as a “hybrid declarative” mapping, as the class is mapped using the declarative style for everything involving the mapper configuration, however the mapped Table object is produced separately and passed to the declarative process directly:

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass


# construct a Table directly.  The Base.metadata collection is
# usually a good choice for MetaData but any MetaData
# collection may be used.

user_table = Table(
    "user",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
    Column("fullname", String),
    Column("nickname", String),
)


# construct the User class using this table.
class User(Base):
    __table__ = user_table

Above, a Table object is constructed using the approach described at 使用元数据描述数据库. It can then be applied directly to a class that is declaratively mapped. The __tablename__ and __table_args__ declarative class attributes are not used in this form. The above configuration is often more readable as an inline definition:

class User(Base):
    __table__ = Table(
        "user",
        Base.metadata,
        Column("id", Integer, primary_key=True),
        Column("name", String),
        Column("fullname", String),
        Column("nickname", String),
    )

A natural effect of the above style is that the __table__ attribute is itself defined within the class definition block. As such it may be immediately referenced within subsequent attributes, such as the example below which illustrates referring to the type column in a polymorphic mapper configuration:

class Person(Base):
    __table__ = Table(
        "person",
        Base.metadata,
        Column("id", Integer, primary_key=True),
        Column("name", String(50)),
        Column("type", String(50)),
    )

    __mapper_args__ = {
        "polymorphic_on": __table__.c.type,
        "polymorphic_identity": "person",
    }

The “imperative table” form is also used when a non-Table construct, such as a Join or Subquery object, is to be mapped. An example below:

from sqlalchemy import func, select

subq = (
    select(
        func.count(orders.c.id).label("order_count"),
        func.max(orders.c.price).label("highest_order"),
        orders.c.customer_id,
    )
    .group_by(orders.c.customer_id)
    .subquery()
)

customer_select = (
    select(customers, subq)
    .join_from(customers, subq, customers.c.id == subq.c.customer_id)
    .subquery()
)


class Customer(Base):
    __table__ = customer_select

For background on mapping to non-Table constructs see the sections 将一个类映射到多个表 and 将一个类映射到任意子查询.

The “imperative table” form is of particular use when the class itself is using an alternative form of attribute declaration, such as Python dataclasses. See the section 将 ORM 映射应用于现有数据类（旧式数据类使用） for detail.

参见

使用元数据描述数据库

将 ORM 映射应用于现有数据类（旧式数据类使用）

用于映射表列的备用属性名称¶

Alternate Attribute Names for Mapping Table Columns

中文

显式命名声明性映射列部分说明了如何使用 mapped_column() 为生成的 Column 对象提供单独于其映射的属性名称的特定名称。

使用命令式表配置时，我们已经有 Column 对象。要将它们映射到备用名称，我们可以将 Column 直接分配给所需的属性:

user_table = Table(
    "user",
    Base.metadata,
    Column("user_id", Integer, primary_key=True),
    Column("user_name", String),
)


class User(Base):
    __table__ = user_table

    id = user_table.c.user_id
    name = user_table.c.user_name

上述 User 映射将通过 User.id 和 User.name 属性引用 "user_id" 和 "user_name" 列，与显式命名声明性映射列中演示的方式相同。

上述映射的一个警告是，直接内联链接到 Column 在使用 PEP 484 类型工具时不会正确键入。一种解决此问题的策略是将 Column 对象应用于 column_property() 函数中；虽然 Mapper 已经自动为其内部使用生成了此属性对象，但通过在类声明中命名它，类型工具将能够将属性与 Mapped 注释匹配:

from sqlalchemy.orm import column_property
from sqlalchemy.orm import Mapped


class User(Base):
    __table__ = user_table

    id: Mapped[int] = column_property(user_table.c.user_id)
    name: Mapped[str] = column_property(user_table.c.user_name)

参见

显式命名声明性映射列 - 应用到声明式表(Declarative Table)

英文

The section 显式命名声明性映射列 illustrated how to use mapped_column() to provide a specific name for the generated Column object separate from the attribute name under which it is mapped.

When using Imperative Table configuration, we already have Column objects present. To map these to alternate names we may assign the Column to the desired attributes directly:

user_table = Table(
    "user",
    Base.metadata,
    Column("user_id", Integer, primary_key=True),
    Column("user_name", String),
)


class User(Base):
    __table__ = user_table

    id = user_table.c.user_id
    name = user_table.c.user_name

The User mapping above will refer to the "user_id" and "user_name" columns via the User.id and User.name attributes, in the same way as demonstrated at 显式命名声明性映射列.

One caveat to the above mapping is that the direct inline link to Column will not be typed correctly when using PEP 484 typing tools. A strategy to resolve this is to apply the Column objects within the column_property() function; while the Mapper already generates this property object for its internal use automatically, by naming it in the class declaration, typing tools will be able to match the attribute to the Mapped annotation:

from sqlalchemy.orm import column_property
from sqlalchemy.orm import Mapped


class User(Base):
    __table__ = user_table

    id: Mapped[int] = column_property(user_table.c.user_id)
    name: Mapped[str] = column_property(user_table.c.user_name)

参见

显式命名声明性映射列 - applies to Declarative Table

为命令式表应用加载、持久性和映射选项列¶

Applying Load, Persistence and Mapping Options for Imperative Table Columns

中文

为声明性映射列设置加载和持久性选项部分回顾了在使用 mapped_column() 构造和声明式表配置时如何设置加载和持久化选项。使用命令式表配置时，我们已经有现有的 Column 对象被映射。为了将这些 Column 对象与特定于ORM映射的其他参数一起映射，我们可以使用 column_property() 和 deferred() 构造来关联列的其他参数。选项包括：

延迟列加载 - deferred() 函数是调用 column_property() 并将 column_property.deferred 参数设置为 True 的简写；此构造默认使用延迟列加载建立 Column。在下面的示例中， User.bio 列默认不会加载，而是仅在访问时加载:

from sqlalchemy.orm import deferred

user_table = Table(
    "user",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
    Column("bio", Text),
)


class User(Base):
    __table__ = user_table

    bio = deferred(user_table.c.bio)

参见

使用【列延迟】限制加载哪些列 - 延迟列加载的完整描述

活动历史 - column_property.active_history 确保在更改属性值时，先前的值将已加载并成为在检查属性历史时的 AttributeState.history 集合的一部分。这可能会产生额外的SQL语句:

from sqlalchemy.orm import column_property

user_table = Table(
    "user",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("important_identifier", String),
)


class User(Base):
    __table__ = user_table

    important_identifier = column_property(
        user_table.c.important_identifier, active_history=True
    )

参见

column_property() 构造对于将类映射到如连接和选择等替代FROM子句的情况也很重要。有关这些情况的更多背景信息，请参见：

对于使用 mapped_column() 的声明式表配置，大多数选项可以直接使用；有关示例，请参见为声明性映射列设置加载和持久性选项部分。

英文

The section 为声明性映射列设置加载和持久性选项 reviewed how to set load and persistence options when using the mapped_column() construct with Declarative Table configuration. When using Imperative Table configuration, we already have existing Column objects that are mapped. In order to map these Column objects along with additional parameters that are specific to the ORM mapping, we may use the column_property() and deferred() constructs in order to associate additional parameters with the column. Options include:

deferred column loading - The deferred() function is shorthand for invoking column_property() with the column_property.deferred parameter set to True; this construct establishes the Column using deferred column loading by default. In the example below, the User.bio column will not be loaded by default, but only when accessed:
```
from sqlalchemy.orm import deferred

user_table = Table(
    "user",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
    Column("bio", Text),
)


class User(Base):
    __table__ = user_table

    bio = deferred(user_table.c.bio)
```

参见

使用【列延迟】限制加载哪些列 - full description of deferred column loading

active history - The column_property.active_history ensures that upon change of value for the attribute, the previous value will have been loaded and made part of the AttributeState.history collection when inspecting the history of the attribute. This may incur additional SQL statements:

from sqlalchemy.orm import deferred

user_table = Table(
    "user",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("important_identifier", String),
)


class User(Base):
    __table__ = user_table

    important_identifier = column_property(
        user_table.c.important_identifier, active_history=True
    )

参见

The column_property() construct is also important for cases where classes are mapped to alternative FROM clauses such as joins and selects. More background on these cases is at:

For Declarative Table configuration with mapped_column(), most options are available directly; see the section 为声明性映射列设置加载和持久性选项 for examples.

声明式的表配置¶

带有 `mapped_column()` 的声明表¶

使用带注释的声明表（ `mapped_column()` 的类型注释形式）¶

`mapped_column()` 从 `Mapped` 注释中派生数据类型和可空性¶

自定义类型映射¶

类型映射内的联合类型¶

支持类型别名类型（由 PEP 695 定义）和 NewType¶

将多种类型配置映射到 Python 类型¶

将整个列声明映射到 Python 类型¶

在类型映射中使用 Python `Enum` 或 pep-586 `Literal` 类型¶

原生枚举和命名¶

更改默认枚举的配置¶

将特定的 `enum.Enum` 或 `typing.Literal` 链接到其他数据类型¶

`mapped_column()` 中的数据类功能¶

访问表和元数据¶

声明性表配置¶

使用声明性表的显式架构名称¶

为声明性映射列设置加载和持久性选项¶

显式命名声明性映射列¶

将其他列附加到现有的声明性映射类¶

使用命令式表的声明式（又名混合声明式）¶

用于映射表列的备用属性名称¶

为命令式表应用加载、持久性和映射选项列¶

使用反射表进行声明式映射¶

使用 DeferredReflection¶

使用 Automap¶

从反射表自动执行列命名方案¶

映射到一组显式主键列¶

映射表列的子集¶

声明式的表配置¶

带有 mapped_column() 的声明表¶

使用带注释的声明表（ mapped_column() 的类型注释形式）¶

mapped_column() 从 Mapped 注释中派生数据类型和可空性¶

自定义类型映射¶

类型映射内的联合类型¶

支持类型别名类型（由 PEP 695 定义）和 NewType¶

将多种类型配置映射到 Python 类型¶

将整个列声明映射到 Python 类型¶

在类型映射中使用 Python Enum 或 pep-586 Literal 类型¶

原生枚举和命名¶

更改默认枚举的配置¶

将特定的 enum.Enum 或 typing.Literal 链接到其他数据类型¶

mapped_column() 中的数据类功能¶

访问表和元数据¶

声明性表配置¶

使用声明性表的显式架构名称¶

为声明性映射列设置加载和持久性选项¶

显式命名声明性映射列¶

将其他列附加到现有的声明性映射类¶

使用命令式表的声明式（又名混合声明式）¶

用于映射表列的备用属性名称¶

为命令式表应用加载、持久性和映射选项列¶

使用反射表进行声明式映射¶

使用 DeferredReflection¶

使用 Automap¶

从反射表自动执行列命名方案¶

映射到一组显式主键列¶

映射表列的子集¶

带有 `mapped_column()` 的声明表¶

使用带注释的声明表（ `mapped_column()` 的类型注释形式）¶

`mapped_column()` 从 `Mapped` 注释中派生数据类型和可空性¶

在类型映射中使用 Python `Enum` 或 pep-586 `Literal` 类型¶

将特定的 `enum.Enum` 或 `typing.Literal` 链接到其他数据类型¶

`mapped_column()` 中的数据类功能¶