使用 Mixins 组合映射层次结构

Composing Mapped Hierarchies with Mixins

在使用 Declarative 风格映射类时,一个常见的需求是共享通用功能,例如特定列、表或映射器选项、命名方案或其他映射属性,跨多个类。使用声明式映射时,这种习惯用法通过使用 mixin classes 以及通过增强声明式基类本身来支持。

小技巧

除了混入类,共同的列选项还可以通过使用 PEP 593 Annotated 类型在多个类之间共享;有关这些SQLAlchemy 2.0功能的背景,请参阅 将多种类型配置映射到 Python 类型将整个列声明映射到 Python 类型

下面是一些常见混入习惯用法的示例:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class CommonMixin:
    """定义一系列可以通过将这个类作为混入类应用于映射类的常用元素。"""

    @declared_attr.directive
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

    __table_args__ = {"mysql_engine": "InnoDB"}
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[int] = mapped_column(primary_key=True)


class HasLogRecord:
    """标记与 ``LogRecord`` 类有多对一关系的类。"""

    log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id"))

    @declared_attr
    def log_record(self) -> Mapped["LogRecord"]:
        return relationship("LogRecord")


class LogRecord(CommonMixin, Base):
    log_info: Mapped[str]


class MyModel(CommonMixin, HasLogRecord, Base):
    name: Mapped[str]

上面的示例说明了一个类 MyModel ,它在其基类中包含了两个混入 CommonMixinHasLogRecord ,以及一个补充类 LogRecord ,该类也包含了 CommonMixin ,展示了在混入和基类上支持的各种构造,包括:

  • 使用 mapped_column()MappedColumn 声明的列从混入或基类复制到目标类进行映射;上面通过列属性 CommonMixin.idHasLogRecord.log_record_id 说明了这一点。

  • 声明式指令如 __table_args____mapper_args__ 可以分配给混入或基类,它们将自动对继承混入或基类的任何类生效。上面的示例通过 __table_args____mapper_args__ 属性说明了这一点。

  • 所有声明式指令,包括所有 __tablename____table____table_args____mapper_args__ ,可以使用用户定义的类方法实现,这些方法使用 declared_attr 装饰器(特别是 declared_attr.directive 子成员,稍后会详细介绍)。上面,通过生成一个动态生成 Table 名称的 def __tablename__(cls) 类方法来说明这一点;当应用于 MyModel 类时,表名将生成为 "mymodel" ,当应用于 LogRecord 类时,表名将生成为 "logrecord"

  • 其他ORM属性如 relationship() 可以使用同样用 declared_attr 装饰的用户定义类方法在目标类上生成进行映射。上面,通过生成一个多对一 relationship() 到一个名为 LogRecord 的映射对象来说明这一点。

上面的功能可以通过一个 select() 示例进行演示:

>>> from sqlalchemy import select
>>> print(select(MyModel).join(MyModel.log_record))
SELECT mymodel.name, mymodel.id, mymodel.log_record_id FROM mymodel JOIN logrecord ON logrecord.id = mymodel.log_record_id

小技巧

declared_attr 的示例将尝试说明每个方法示例的正确 PEP 484 注解。使用带有 declared_attr 函数的注解是 完全可选的 ,声明式不会使用这些注解;然而,这些注解对于通过Mypy --strict 类型检查是必要的。

另外,上面说明的 declared_attr.directive 子成员也是可选的,只对 PEP 484 类型工具有意义,因为它在创建覆盖声明指令如 __tablename____mapper_args____table_args__ 的方法时调整预期的返回类型。

在 2.0 版本加入: 作为SQLAlchemy ORM的 PEP 484 类型支持的一部分,向 declared_attr 添加了 declared_attr.directive 以区分 Mapped 属性和声明式配置属性

对于混入和基类的顺序没有固定的约定。正常的Python方法解析规则适用,上面的示例也同样适用于:

class MyModel(Base, HasLogRecord, CommonMixin):
    name: Mapped[str] = mapped_column()

这是因为 Base 在这里没有定义 CommonMixinHasLogRecord 定义的任何变量,即 __tablename____table_args__id 等。如果 Base 确实定义了相同名称的属性,则放在继承列表中第一个的类将确定在新定义的类上使用哪个属性。

小技巧

虽然上面的示例使用基于 Mapped 注解类的 Annotated Declarative Table 形式,混入类也可以完美地与非注解和遗留的声明式形式一起工作,例如直接使用 Column 而不是 mapped_column()

在 2.0 版本发生变更: 对于使用SQLAlchemy 1.4系列的用户,他们可能使用了 mypy plugin,不再需要使用 declarative_mixin() 类装饰器来标记声明式混入,假设不再使用mypy插件。

A common need when mapping classes using the Declarative style is to share common functionality, such as particular columns, table or mapper options, naming schemes, or other mapped properties, across many classes. When using declarative mappings, this idiom is supported via the use of mixin classes, as well as via augmenting the declarative base class itself.

小技巧

In addition to mixin classes, common column options may also be shared among many classes using PEP 593 Annotated types; see 将多种类型配置映射到 Python 类型 and 将整个列声明映射到 Python 类型 for background on these SQLAlchemy 2.0 features.

An example of some commonly mixed-in idioms is below:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class CommonMixin:
    """define a series of common elements that may be applied to mapped
    classes using this class as a mixin class."""

    @declared_attr.directive
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

    __table_args__ = {"mysql_engine": "InnoDB"}
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[int] = mapped_column(primary_key=True)


class HasLogRecord:
    """mark classes that have a many-to-one relationship to the
    ``LogRecord`` class."""

    log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id"))

    @declared_attr
    def log_record(self) -> Mapped["LogRecord"]:
        return relationship("LogRecord")


class LogRecord(CommonMixin, Base):
    log_info: Mapped[str]


class MyModel(CommonMixin, HasLogRecord, Base):
    name: Mapped[str]

The above example illustrates a class MyModel which includes two mixins CommonMixin and HasLogRecord in its bases, as well as a supplementary class LogRecord which also includes CommonMixin, demonstrating a variety of constructs that are supported on mixins and base classes, including:

  • columns declared using mapped_column(), Mapped or Column are copied from mixins or base classes onto the target class to be mapped; above this is illustrated via the column attributes CommonMixin.id and HasLogRecord.log_record_id.

  • Declarative directives such as __table_args__ and __mapper_args__ can be assigned to a mixin or base class, where they will take effect automatically for any classes which inherit from the mixin or base. The above example illustrates this using the __table_args__ and __mapper_args__ attributes.

  • All Declarative directives, including all of __tablename__, __table__, __table_args__ and __mapper_args__, may be implemented using user-defined class methods, which are decorated with the declared_attr decorator (specifically the declared_attr.directive sub-member, more on that in a moment). Above, this is illustrated using a def __tablename__(cls) classmethod that generates a Table name dynamically; when applied to the MyModel class, the table name will be generated as "mymodel", and when applied to the LogRecord class, the table name will be generated as "logrecord".

  • Other ORM properties such as relationship() can be generated on the target class to be mapped using user-defined class methods also decorated with the declared_attr decorator. Above, this is illustrated by generating a many-to-one relationship() to a mapped object called LogRecord.

The features above may all be demonstrated using a select() example:

>>> from sqlalchemy import select
>>> print(select(MyModel).join(MyModel.log_record))
SELECT mymodel.name, mymodel.id, mymodel.log_record_id FROM mymodel JOIN logrecord ON logrecord.id = mymodel.log_record_id

小技巧

The examples of declared_attr will attempt to illustrate the correct PEP 484 annotations for each method example. The use of annotations with declared_attr functions are completely optional, and are not consumed by Declarative; however, these annotations are required in order to pass Mypy --strict type checking.

Additionally, the declared_attr.directive sub-member illustrated above is optional as well, and is only significant for PEP 484 typing tools, as it adjusts for the expected return type when creating methods to override Declarative directives such as __tablename__, __mapper_args__ and __table_args__.

在 2.0 版本加入: As part of PEP 484 typing support for the SQLAlchemy ORM, added the declared_attr.directive to declared_attr to distinguish between Mapped attributes and Declarative configurational attributes

There’s no fixed convention for the order of mixins and base classes. Normal Python method resolution rules apply, and the above example would work just as well with:

class MyModel(Base, HasLogRecord, CommonMixin):
    name: Mapped[str] = mapped_column()

This works because Base here doesn’t define any of the variables that CommonMixin or HasLogRecord defines, i.e. __tablename__, __table_args__, id, etc. If the Base did define an attribute of the same name, the class placed first in the inherits list would determine which attribute is used on the newly defined class.

小技巧

While the above example is using Annotated Declarative Table form based on the Mapped annotation class, mixin classes also work perfectly well with non-annotated and legacy Declarative forms, such as when using Column directly instead of mapped_column().

在 2.0 版本发生变更: For users coming from the 1.4 series of SQLAlchemy who may have been using the mypy plugin, the declarative_mixin() class decorator is no longer needed to mark declarative mixins, assuming the mypy plugin is no longer in use.

增强Base基类

Augmenting the Base

除了使用纯混入外,本节中的大多数技术还可以直接应用于基类,以便将模式应用于从特定基类派生的所有类。下面的示例说明了上一节的一些示例,关于 Base 类:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    """定义一系列可以通过将这个类作为基类应用于映射类的常用元素。"""

    @declared_attr.directive
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

    __table_args__ = {"mysql_engine": "InnoDB"}
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[int] = mapped_column(primary_key=True)


class HasLogRecord:
    """标记与 ``LogRecord`` 类有多对一关系的类。"""

    log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id"))

    @declared_attr
    def log_record(self) -> Mapped["LogRecord"]:
        return relationship("LogRecord")


class LogRecord(Base):
    log_info: Mapped[str]


class MyModel(HasLogRecord, Base):
    name: Mapped[str]

上面, MyModelLogRecord 在继承 Base 时,将会从类名派生其表名,一个名为 id 的主键列,以及由 Base.__table_args__Base.__mapper_args__ 定义的表和映射器参数。

使用遗留 declarative_base()registry.generate_base() 时,可以如下使用 declarative_base.cls 参数来生成等效效果,如下面的非注解示例所示:

# 遗留 declarative_base() 的使用

from sqlalchemy import Integer, String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base:
    """定义一系列可以通过将这个类作为基类应用于映射类的常用元素。"""

    @declared_attr.directive
    def __tablename__(cls):
        return cls.__name__.lower()

    __table_args__ = {"mysql_engine": "InnoDB"}
    __mapper_args__ = {"eager_defaults": True}

    id = mapped_column(Integer, primary_key=True)


Base = declarative_base(cls=Base)


class HasLogRecord:
    """标记与``LogRecord``类有多对一关系的类。"""

    log_record_id = mapped_column(ForeignKey("logrecord.id"))

    @declared_attr
    def log_record(self):
        return relationship("LogRecord")


class LogRecord(Base):
    log_info = mapped_column(String)


class MyModel(HasLogRecord, Base):
    name = mapped_column(String)

In addition to using a pure mixin, most of the techniques in this section can also be applied to the base class directly, for patterns that should apply to all classes derived from a particular base. The example below illustrates some of the previous section’s example in terms of the Base class:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    """define a series of common elements that may be applied to mapped
    classes using this class as a base class."""

    @declared_attr.directive
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

    __table_args__ = {"mysql_engine": "InnoDB"}
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[int] = mapped_column(primary_key=True)


class HasLogRecord:
    """mark classes that have a many-to-one relationship to the
    ``LogRecord`` class."""

    log_record_id: Mapped[int] = mapped_column(ForeignKey("logrecord.id"))

    @declared_attr
    def log_record(self) -> Mapped["LogRecord"]:
        return relationship("LogRecord")


class LogRecord(Base):
    log_info: Mapped[str]


class MyModel(HasLogRecord, Base):
    name: Mapped[str]

Where above, MyModel as well as LogRecord, in deriving from Base, will both have their table name derived from their class name, a primary key column named id, as well as the above table and mapper arguments defined by Base.__table_args__ and Base.__mapper_args__.

When using legacy declarative_base() or registry.generate_base(), the declarative_base.cls parameter may be used as follows to generate an equivalent effect, as illustrated in the non-annotated example below:

# legacy declarative_base() use

from sqlalchemy import Integer, String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base:
    """define a series of common elements that may be applied to mapped
    classes using this class as a base class."""

    @declared_attr.directive
    def __tablename__(cls):
        return cls.__name__.lower()

    __table_args__ = {"mysql_engine": "InnoDB"}
    __mapper_args__ = {"eager_defaults": True}

    id = mapped_column(Integer, primary_key=True)


Base = declarative_base(cls=Base)


class HasLogRecord:
    """mark classes that have a many-to-one relationship to the
    ``LogRecord`` class."""

    log_record_id = mapped_column(ForeignKey("logrecord.id"))

    @declared_attr
    def log_record(self):
        return relationship("LogRecord")


class LogRecord(Base):
    log_info = mapped_column(String)


class MyModel(HasLogRecord, Base):
    name = mapped_column(String)

混合列

Mixing in Columns

列可以在混入中表示,假设使用 Declarative table 风格的配置(而不是 imperative table 配置),这样在混入中声明的列可以被复制为声明过程生成的 Table 的一部分。mapped_column()MappedColumn 三种构造都可以在声明式混入中内联声明:

class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(default=func.now())
    updated_at: Mapped[datetime]


class MyModel(TimestampMixin, Base):
    __tablename__ = "test"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]

如上所示,所有在其类基中包含 TimestampMixin 的声明式类将自动包含一个 created_at 列,该列将时间戳应用于所有行插入,以及一个 updated_at 列,出于示例目的,该列没有默认值(如果有,我们将使用 Column.onupdate 参数,该参数由 mapped_column() 接受)。这些列构造总是 从原始混入或基类中复制(copied from the originating mixin or base class),这样相同的混入/基类可以应用于任意数量的目标类,每个目标类将有自己的列构造。

所有声明式列形式都被混入支持,包括:

  • 注解属性(Annotated attributes) - 是否存在 mapped_column():

    class TimestampMixin:
        created_at: Mapped[datetime] = mapped_column(default=func.now())
        updated_at: Mapped[datetime]
  • mapped_column - 是否存在 Mapped:

    class TimestampMixin:
        created_at = mapped_column(default=func.now())
        updated_at: Mapped[datetime] = mapped_column()
  • Column - 传统声明式形式:

    class TimestampMixin:
        created_at = Column(DateTime, default=func.now())
        updated_at = Column(DateTime)

在上述每种形式中,声明式处理混入类上的基于列的属性,通过创建构造的 副本 ,然后将其应用于目标类。

在 2.0 版本发生变更: 声明式API现在可以适应 Column 对象以及在使用混入时的任何形式的 mapped_column() 构造,而无需使用 declared_attr()。先前限制了带有 ForeignKey 元素的列无法直接在混入中使用的限制已被移除。

Columns can be indicated in mixins assuming the Declarative table style of configuration is in use (as opposed to imperative table configuration), so that columns declared on the mixin can then be copied to be part of the Table that the Declarative process generates. All three of the mapped_column(), Mapped, and Column constructs may be declared inline in a declarative mixin:

class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(default=func.now())
    updated_at: Mapped[datetime]


class MyModel(TimestampMixin, Base):
    __tablename__ = "test"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]

Where above, all declarative classes that include TimestampMixin in their class bases will automatically include a column created_at that applies a timestamp to all row insertions, as well as an updated_at column, which does not include a default for the purposes of the example (if it did, we would use the Column.onupdate parameter which is accepted by mapped_column()). These column constructs are always copied from the originating mixin or base class, so that the same mixin/base class may be applied to any number of target classes which will each have their own column constructs.

All Declarative column forms are supported by mixins, including:

  • Annotated attributes - with or without mapped_column() present:

    class TimestampMixin:
        created_at: Mapped[datetime] = mapped_column(default=func.now())
        updated_at: Mapped[datetime]
  • mapped_column - with or without Mapped present:

    class TimestampMixin:
        created_at = mapped_column(default=func.now())
        updated_at: Mapped[datetime] = mapped_column()
  • Column - legacy Declarative form:

    class TimestampMixin:
        created_at = Column(DateTime, default=func.now())
        updated_at = Column(DateTime)

In each of the above forms, Declarative handles the column-based attributes on the mixin class by creating a copy of the construct, which is then applied to the target class.

在 2.0 版本发生变更: The declarative API can now accommodate

Column objects as well as mapped_column() constructs of any form when using mixins without the need to use declared_attr(). Previous limitations which prevented columns with ForeignKey elements from being used directly in mixins have been removed.

混合关系

Mixing in Relationships

relationship() 创建的关系仅使用 declared_attr 方法提供给声明式混入类,消除了复制关系及其可能的列绑定内容时可能出现的任何歧义。下面的示例结合了一个外键列和关系,以便两个类 FooBar 都可以配置为通过多对一引用一个公共目标类:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class RefTargetMixin:
    target_id: Mapped[int] = mapped_column(ForeignKey("target.id"))

    @declared_attr
    def target(cls) -> Mapped["Target"]:
        return relationship("Target")


class Foo(RefTargetMixin, Base):
    __tablename__ = "foo"
    id: Mapped[int] = mapped_column(primary_key=True)


class Bar(RefTargetMixin, Base):
    __tablename__ = "bar"
    id: Mapped[int] = mapped_column(primary_key=True)


class Target(Base):
    __tablename__ = "target"
    id: Mapped[int] = mapped_column(primary_key=True)

通过上述映射, FooBar 中的每一个都包含一个访问 Target 的关系,通过 .target 属性访问:

>>> from sqlalchemy import select
>>> print(select(Foo).join(Foo.target))
SELECT foo.id, foo.target_id FROM foo JOIN target ON target.id = foo.target_id
>>> print(select(Bar).join(Bar.target))
SELECT bar.id, bar.target_id FROM bar JOIN target ON target.id = bar.target_id

特殊参数如 relationship.primaryjoin 也可以在混入的类方法中使用,这些类方法通常需要引用正在映射的类。对于需要引用本地映射列的方案,在普通情况下,这些列由声明式作为映射类上的属性提供,该类作为 cls 参数传递给装饰的类方法。使用此功能,我们可以例如使用明确的 primaryjoin 重写 RefTargetMixin.target 方法,该方法引用 Targetcls 上的待映射列:

class Target(Base):
    __tablename__ = "target"
    id: Mapped[int] = mapped_column(primary_key=True)


class RefTargetMixin:
    target_id: Mapped[int] = mapped_column(ForeignKey("target.id"))

    @declared_attr
    def target(cls) -> Mapped["Target"]:
        # 说明明确的 'primaryjoin' 参数
        return relationship("Target", primaryjoin=Target.id == cls.target_id)

Relationships created by relationship() are provided with declarative mixin classes exclusively using the declared_attr approach, eliminating any ambiguity which could arise when copying a relationship and its possibly column-bound contents. Below is an example which combines a foreign key column and a relationship so that two classes Foo and Bar can both be configured to reference a common target class via many-to-one:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship


class Base(DeclarativeBase):
    pass


class RefTargetMixin:
    target_id: Mapped[int] = mapped_column(ForeignKey("target.id"))

    @declared_attr
    def target(cls) -> Mapped["Target"]:
        return relationship("Target")


class Foo(RefTargetMixin, Base):
    __tablename__ = "foo"
    id: Mapped[int] = mapped_column(primary_key=True)


class Bar(RefTargetMixin, Base):
    __tablename__ = "bar"
    id: Mapped[int] = mapped_column(primary_key=True)


class Target(Base):
    __tablename__ = "target"
    id: Mapped[int] = mapped_column(primary_key=True)

With the above mapping, each of Foo and Bar contain a relationship to Target accessed along the .target attribute:

>>> from sqlalchemy import select
>>> print(select(Foo).join(Foo.target))
SELECT foo.id, foo.target_id FROM foo JOIN target ON target.id = foo.target_id
>>> print(select(Bar).join(Bar.target))
SELECT bar.id, bar.target_id FROM bar JOIN target ON target.id = bar.target_id

Special arguments such as relationship.primaryjoin may also be used within mixed-in classmethods, which often need to refer to the class that’s being mapped. For schemes that need to refer to locally mapped columns, in ordinary cases these columns are made available by Declarative as attributes on the mapped class which is passed as the cls argument to the decorated classmethod. Using this feature, we could for example rewrite the RefTargetMixin.target method using an explicit primaryjoin which refers to pending mapped columns on both Target and cls:

class Target(Base):
    __tablename__ = "target"
    id: Mapped[int] = mapped_column(primary_key=True)


class RefTargetMixin:
    target_id: Mapped[int] = mapped_column(ForeignKey("target.id"))

    @declared_attr
    def target(cls) -> Mapped["Target"]:
        # illustrates explicit 'primaryjoin' argument
        return relationship("Target", primaryjoin=Target.id == cls.target_id)

混合 column_property() 和其他 MapperProperty

Mixing in column_property() and other MapperProperty classes

relationship() 一样,其他 MapperProperty 子类如 column_property() 在混入中使用时也需要生成类本地副本,因此也在使用 declared_attr 装饰的函数中声明。在函数内,用 mapped_column()MappedColumn 声明的其他普通映射列将从 cls 参数中提供,以便它们可以用于组成新属性,如以下示例中将两列相加:

from sqlalchemy.orm import column_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class SomethingMixin:
    x: Mapped[int]
    y: Mapped[int]

    @declared_attr
    def x_plus_y(cls) -> Mapped[int]:
        return column_property(cls.x + cls.y)


class Something(SomethingMixin, Base):
    __tablename__ = "something"

    id: Mapped[int] = mapped_column(primary_key=True)

如上所示,我们可以在语句中使用 Something.x_plus_y ,其生成完整的表达式:

>>> from sqlalchemy import select
>>> print(select(Something.x_plus_y))
SELECT something.x + something.y AS anon_1 FROM something

小技巧

declared_attr 装饰器使被装饰的可调用对象完全像类方法一样运行。然而,像 Pylance 这样的类型工具可能无法识别这一点,这有时会导致它对函数体内访问 cls 变量发出警告。要解决此问题,可以直接将 @classmethod 装饰器与 declared_attr 结合使用,如:

class SomethingMixin:

x: Mapped[int] y: Mapped[int]

@declared_attr @classmethod def x_plus_y(cls) -> Mapped[int]:

return column_property(cls.x + cls.y)

在 2.0 版本加入:

  • declared_attr 可以适应用 @classmethod 装饰的函数,以帮助 PEP 484 集成在需要时。

Like relationship(), other MapperProperty subclasses such as column_property() also need to have class-local copies generated when used by mixins, so are also declared within functions that are decorated by declared_attr. Within the function, other ordinary mapped columns that were declared with mapped_column(), Mapped, or Column will be made available from the cls argument so that they may be used to compose new attributes, as in the example below which adds two columns together:

from sqlalchemy.orm import column_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class SomethingMixin:
    x: Mapped[int]
    y: Mapped[int]

    @declared_attr
    def x_plus_y(cls) -> Mapped[int]:
        return column_property(cls.x + cls.y)


class Something(SomethingMixin, Base):
    __tablename__ = "something"

    id: Mapped[int] = mapped_column(primary_key=True)

Above, we may make use of Something.x_plus_y in a statement where it produces the full expression:

>>> from sqlalchemy import select
>>> print(select(Something.x_plus_y))
SELECT something.x + something.y AS anon_1 FROM something

小技巧

The declared_attr decorator causes the decorated callable

to behave exactly as a classmethod. However, typing tools like Pylance may not be able to recognize this, which can sometimes cause it to complain about access to the cls variable inside the body of the function. To resolve this issue when it occurs, the @classmethod decorator may be combined directly with declared_attr as:

class SomethingMixin:
    x: Mapped[int]
    y: Mapped[int]

    @declared_attr
    @classmethod
    def x_plus_y(cls) -> Mapped[int]:
        return column_property(cls.x + cls.y)

在 2.0 版本加入: - declared_attr can accommodate a function decorated with @classmethod to help with PEP 484 integration where needed.

使用 Mixins 和基类以及映射继承模式

Using Mixins and Base Classes with Mapped Inheritance Patterns

在处理映射器继承模式时,如文档 映射类继承层次结构 所述,使用 declared_attr 以及混入类或增强类层次结构中映射和未映射的超类时,会有一些额外的功能。

在混入类或基类上定义使用 declared_attr 装饰的函数,以便在映射继承层次结构中的子类中解释时,对于生成由声明式使用的特殊名称(如 __tablename____mapper_args__ )的函数与生成普通映射属性(如 mapped_column()relationship())的函数之间,有一个重要的区别。定义 声明式指令(Declarative directives) 的函数 针对层次结构中的每个子类调用 ,而生成 映射属性(mapped attributes) 的函数 仅针对层次结构中的第一个映射超类调用

这种行为差异的理由是基于以下事实:映射属性已经可以被类继承,例如超类的映射表上的特定列不应也复制到子类中,而特定于特定类或其映射表的元素是不可继承的,例如本地映射的表的名称。

以下两节演示了这两种用例之间的行为差异。

When dealing with mapper inheritance patterns as documented at 映射类继承层次结构, some additional capabilities are present when using declared_attr either with mixin classes, or when augmenting both mapped and un-mapped superclasses in a class hierarchy.

When defining functions decorated by declared_attr on mixins or base classes to be interpreted by subclasses in a mapped inheritance hierarchy, there is an important distinction made between functions that generate the special names used by Declarative such as __tablename__, __mapper_args__ vs. those that may generate ordinary mapped attributes such as mapped_column() and relationship(). Functions that define Declarative directives are invoked for each subclass in a hierarchy, whereas functions that generate mapped attributes are invoked only for the first mapped superclass in a hierarchy.

The rationale for this difference in behavior is based on the fact that mapped properties are already inheritable by classes, such as a particular column on a superclass’ mapped table should not be duplicated to that of a subclass as well, whereas elements that are specific to a particular class or its mapped table are not inheritable, such as the name of the table that is locally mapped.

The difference in behavior between these two use cases is demonstrated in the following two sections.

使用 declared_attr() 继承 TableMapper 参数

Using declared_attr() with inheriting Table and Mapper arguments

一个常见的混入用法是创建一个 def __tablename__(cls) 函数,动态生成映射的 Table 名称。

这个用法可以用于生成继承映射层次结构中的表名,如下面的示例所示,该示例创建了一个混入,使每个类都具有基于类名的简单表名。下面的示例展示了为映射类 PersonPerson 的子类 Engineer 生成表名,但不为 Person 的子类 Manager 生成表名:

from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class Tablename:
    @declared_attr.directive
    def __tablename__(cls) -> Optional[str]:
        return cls.__name__.lower()


class Person(Tablename, Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


class Engineer(Person):
    id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True)

    primary_language: Mapped[str]

    __mapper_args__ = {"polymorphic_identity": "engineer"}


class Manager(Person):
    @declared_attr.directive
    def __tablename__(cls) -> Optional[str]:
        """覆盖 __tablename__ 使 Manager 为单继承到 Person"""

        return None

    __mapper_args__ = {"polymorphic_identity": "manager"}

在上面的示例中, Person 基类和 Engineer 类,由于它们是生成新表名的 Tablename 混入类的子类,将具有生成的 __tablename__ 属性,这表示每个类都应该有自己的 Table 生成并映射到它。对于 Engineer 子类,应用的继承风格是 joined table inheritance,因为它将映射到一个连接到基础 person 表的 engineer 表。继承自 Person 的任何其他子类也将默认应用此继承风格(在此特定示例中,还需要每个子类指定一个主键列;更多内容将在下一节中介绍)。

相比之下, Person 的子类 Manager 覆盖 __tablename__ 类方法以返回 None。这表示声明式不应生成 Table,而是将只使用 Person 映射到的基础 Table。对于 Manager 子类,应用的继承风格是 single table inheritance

上面的示例说明了声明式指令如 __tablename__ 必须 单独应用于每个子类 ,因为每个映射类都需要声明它将映射到哪个 Table,或者是否将自己映射到继承的超类的 Table

如果我们希望 反转 上面展示的默认表方案,使单表继承为默认模式,并且只有在提供了 __tablename__ 指令以覆盖它时才定义连接表继承,我们可以在最顶层的 __tablename__() 方法中使用声明式助手,在这种情况下,这个助手称为 has_inherited_table()。如果超类已经映射到 Table,此函数将返回 True 。我们可以在基类的 __tablename__() 类方法中使用此助手,以便如果已经存在表,则 有条件地 返回 None 作为表名,从而默认情况下为继承子类指示单表继承:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import has_inherited_table
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class Tablename:
    @declared_attr.directive
    def __tablename__(cls):
        if has_inherited_table(cls):
            return None
        return cls.__name__.lower()


class Person(Tablename, Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


class Engineer(Person):
    @declared_attr.directive
    def __tablename__(cls):
        """覆盖 __tablename__ 使 Engineer 为连接继承到 Person"""

        return cls.__name__.lower()

    id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True)

    primary_language: Mapped[str]

    __mapper_args__ = {"polymorphic_identity": "engineer"}


class Manager(Person):
    __mapper_args__ = {"polymorphic_identity": "manager"}

A common recipe with mixins is to create a def __tablename__(cls) function that generates a name for the mapped Table dynamically.

This recipe can be used to generate table names for an inheriting mapper hierarchy as in the example below which creates a mixin that gives every class a simple table name based on class name. The recipe is illustrated below where a table name is generated for the Person mapped class and the Engineer subclass of Person, but not for the Manager subclass of Person:

from typing import Optional

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class Tablename:
    @declared_attr.directive
    def __tablename__(cls) -> Optional[str]:
        return cls.__name__.lower()


class Person(Tablename, Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


class Engineer(Person):
    id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True)

    primary_language: Mapped[str]

    __mapper_args__ = {"polymorphic_identity": "engineer"}


class Manager(Person):
    @declared_attr.directive
    def __tablename__(cls) -> Optional[str]:
        """override __tablename__ so that Manager is single-inheritance to Person"""

        return None

    __mapper_args__ = {"polymorphic_identity": "manager"}

In the above example, both the Person base class as well as the Engineer class, being subclasses of the Tablename mixin class which generates new table names, will have a generated __tablename__ attribute, which to Declarative indicates that each class should have its own Table generated to which it will be mapped. For the Engineer subclass, the style of inheritance applied is joined table inheritance, as it will be mapped to a table engineer that joins to the base person table. Any other subclasses that inherit from Person will also have this style of inheritance applied by default (and within this particular example, would need to each specify a primary key column; more on that in the next section).

By contrast, the Manager subclass of Person overrides the __tablename__ classmethod to return None. This indicates to Declarative that this class should not have a Table generated, and will instead make use exclusively of the base Table to which Person is mapped. For the Manager subclass, the style of inheritance applied is single table inheritance.

The example above illustrates that Declarative directives like __tablename__ are necessarily applied to each subclass individually, as each mapped class needs to state which Table it will be mapped towards, or if it will map itself to the inheriting superclass’ Table.

If we instead wanted to reverse the default table scheme illustrated above, so that single table inheritance were the default and joined table inheritance could be defined only when a __tablename__ directive were supplied to override it, we can make use of Declarative helpers within the top-most __tablename__() method, in this case a helper called has_inherited_table(). This function will return True if a superclass is already mapped to a Table. We may use this helper within the base-most __tablename__() classmethod so that we may conditionally return None for the table name, if a table is already present, thus indicating single-table inheritance for inheriting subclasses by default:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import has_inherited_table
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


class Base(DeclarativeBase):
    pass


class Tablename:
    @declared_attr.directive
    def __tablename__(cls):
        if has_inherited_table(cls):
            return None
        return cls.__name__.lower()


class Person(Tablename, Base):
    id: Mapped[int] = mapped_column(primary_key=True)
    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


class Engineer(Person):
    @declared_attr.directive
    def __tablename__(cls):
        """override __tablename__ so that Engineer is joined-inheritance to Person"""

        return cls.__name__.lower()

    id: Mapped[int] = mapped_column(ForeignKey("person.id"), primary_key=True)

    primary_language: Mapped[str]

    __mapper_args__ = {"polymorphic_identity": "engineer"}


class Manager(Person):
    __mapper_args__ = {"polymorphic_identity": "manager"}

使用 declared_attr() 生成特定于表的继承列

Using declared_attr() to generate table-specific inheriting columns

与使用 declared_attr 处理 __tablename__ 和其他特殊名称的方式相反,当我们混入列和属性(例如关系、列属性等)时,除非将 declared_attr 指令与 declared_attr.cascading 子指令结合使用,否则该函数仅对层次结构中的 基类 调用。如下所示,只有 Person 类会收到名为 id 的列;映射将在 Engineer 上失败,因为没有为其提供主键:

class HasId:
    id: Mapped[int] = mapped_column(primary_key=True)


class Person(HasId, Base):
    __tablename__ = "person"

    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


# 此映射将失败,因为没有主键
class Engineer(Person):
    __tablename__ = "engineer"

    primary_language: Mapped[str]
    __mapper_args__ = {"polymorphic_identity": "engineer"}

在连接表继承中,我们通常希望在每个子类上命名不同的列。然而,在这种情况下,我们可能希望在每个表上都有一个 id 列,并通过外键相互引用。我们可以通过使用 declared_attr.cascading 修饰符来实现这一点,该修饰符指示该函数应该对层次结构中的 每个类 调用,几乎(见下文警告)与 __tablename__ 的方式相同:

class HasIdMixin:
    @declared_attr.cascading
    def id(cls) -> Mapped[int]:
        if has_inherited_table(cls):
            return mapped_column(ForeignKey("person.id"), primary_key=True)
        else:
            return mapped_column(Integer, primary_key=True)


class Person(HasIdMixin, Base):
    __tablename__ = "person"

    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


class Engineer(Person):
    __tablename__ = "engineer"

    primary_language: Mapped[str]
    __mapper_args__ = {"polymorphic_identity": "engineer"}

警告

declared_attr.cascading 功能当前 允许子类用不同的函数或值重写属性。这是 @declared_attr 解析机制中的当前限制,如果检测到这种情况,则会发出警告。此限制仅适用于ORM映射列、关系和其他 MapperProperty 类型的属性。它 适用于声明式指令,如 __tablename____mapper_args__ 等,这些指令在内部的解析方式与 declared_attr.cascading 不同。

In contrast to how __tablename__ and other special names are handled when used with declared_attr, when we mix in columns and properties (e.g. relationships, column properties, etc.), the function is invoked for the base class only in the hierarchy, unless the declared_attr directive is used in combination with the declared_attr.cascading sub-directive. Below, only the Person class will receive a column called id; the mapping will fail on Engineer, which is not given a primary key:

class HasId:
    id: Mapped[int] = mapped_column(primary_key=True)


class Person(HasId, Base):
    __tablename__ = "person"

    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


# this mapping will fail, as there's no primary key
class Engineer(Person):
    __tablename__ = "engineer"

    primary_language: Mapped[str]
    __mapper_args__ = {"polymorphic_identity": "engineer"}

It is usually the case in joined-table inheritance that we want distinctly named columns on each subclass. However in this case, we may want to have an id column on every table, and have them refer to each other via foreign key. We can achieve this as a mixin by using the declared_attr.cascading modifier, which indicates that the function should be invoked for each class in the hierarchy, in almost (see warning below) the same way as it does for __tablename__:

class HasIdMixin:
    @declared_attr.cascading
    def id(cls) -> Mapped[int]:
        if has_inherited_table(cls):
            return mapped_column(ForeignKey("person.id"), primary_key=True)
        else:
            return mapped_column(Integer, primary_key=True)


class Person(HasIdMixin, Base):
    __tablename__ = "person"

    discriminator: Mapped[str]
    __mapper_args__ = {"polymorphic_on": "discriminator"}


class Engineer(Person):
    __tablename__ = "engineer"

    primary_language: Mapped[str]
    __mapper_args__ = {"polymorphic_identity": "engineer"}

警告

The declared_attr.cascading feature currently does not allow for a subclass to override the attribute with a different function or value. This is a current limitation in the mechanics of how @declared_attr is resolved, and a warning is emitted if this condition is detected. This limitation only applies to ORM mapped columns, relationships, and other MapperProperty styles of attribute. It does not apply to Declarative directives such as __tablename__, __mapper_args__, etc., which resolve in a different way internally than that of declared_attr.cascading.

组合来自多个 Mixins 的表/映射器参数

Combining Table/Mapper Arguments from Multiple Mixins

在使用声明式混入指定 __table_args____mapper_args__ 的情况下,您可能希望将多个混入的某些参数与您希望在类本身上定义的参数合并。这里可以使用 declared_attr 装饰器来创建用户定义的整理例程,从多个集合中提取参数:

from sqlalchemy.orm import declarative_mixin, declared_attr


class MySQLSettings:
    __table_args__ = {"mysql_engine": "InnoDB"}


class MyOtherMixin:
    __table_args__ = {"info": "foo"}


class MyModel(MySQLSettings, MyOtherMixin, Base):
    __tablename__ = "my_model"

    @declared_attr.directive
    def __table_args__(cls):
        args = dict()
        args.update(MySQLSettings.__table_args__)
        args.update(MyOtherMixin.__table_args__)
        return args

    id = mapped_column(Integer, primary_key=True)

In the case of __table_args__ or __mapper_args__ specified with declarative mixins, you may want to combine some parameters from several mixins with those you wish to define on the class itself. The declared_attr decorator can be used here to create user-defined collation routines that pull from multiple collections:

from sqlalchemy.orm import declarative_mixin, declared_attr


class MySQLSettings:
    __table_args__ = {"mysql_engine": "InnoDB"}


class MyOtherMixin:
    __table_args__ = {"info": "foo"}


class MyModel(MySQLSettings, MyOtherMixin, Base):
    __tablename__ = "my_model"

    @declared_attr.directive
    def __table_args__(cls):
        args = dict()
        args.update(MySQLSettings.__table_args__)
        args.update(MyOtherMixin.__table_args__)
        return args

    id = mapped_column(Integer, primary_key=True)

使用 Mixins 上的命名约定创建索引和约束

Creating Indexes and Constraints with Naming Conventions on Mixins

使用命名约束(如 IndexUniqueConstraintCheckConstraint ),其中每个对象对于从混入派生的特定表都是唯一的,这需要为每个实际映射的类创建每个对象的单独实例。

一个简单的示例,定义一个命名的、可能是多列的 Index,适用于从混入派生的所有表,使用 Index 的“内联”形式并将其作为 __table_args__ 的一部分建立,使用 declared_attr__table_args__() 设为一个类方法,该方法将为每个子类调用:

class MyMixin:
    a = mapped_column(Integer)
    b = mapped_column(Integer)

    @declared_attr.directive
    def __table_args__(cls):
        return (Index(f"test_idx_{cls.__tablename__}", "a", "b"),)


class MyModelA(MyMixin, Base):
    __tablename__ = "table_a"
    id = mapped_column(Integer, primary_key=True)


class MyModelB(MyMixin, Base):
    __tablename__ = "table_b"
    id = mapped_column(Integer, primary_key=True)

上面的示例将生成两个表 "table_a""table_b" ,索引为 "test_idx_table_a""test_idx_table_b"

通常,在现代SQLAlchemy中我们会使用命名约定,如文档 配置约束命名约定 所述。尽管命名约定在创建新的 Constraint 对象时会自动应用,因这种约定在对象构造时基于特定 Constraint 的父 Table 应用,因此需要为每个继承子类创建一个独特的 Constraint 对象,其自身具有 Table,再次使用 declared_attr__table_args__(),如下所示,使用一个抽象映射基类:

from uuid import UUID

from sqlalchemy import CheckConstraint
from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import UniqueConstraint
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

constraint_naming_conventions = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}


class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=constraint_naming_conventions)


class MyAbstractBase(Base):
    __abstract__ = True

    @declared_attr.directive
    def __table_args__(cls):
        return (
            UniqueConstraint("uuid"),
            CheckConstraint("x > 0 OR y < 100", name="xy_chk"),
        )

    id: Mapped[int] = mapped_column(primary_key=True)
    uuid: Mapped[UUID]
    x: Mapped[int]
    y: Mapped[int]


class ModelAlpha(MyAbstractBase):
    __tablename__ = "alpha"


class ModelBeta(MyAbstractBase):
    __tablename__ = "beta"

上面的映射将生成包括表特定名称的所有约束的DDL,包括主键、CHECK约束、唯一约束:

CREATE TABLE alpha (
    id INTEGER NOT NULL,
    uuid CHAR(32) NOT NULL,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    CONSTRAINT pk_alpha PRIMARY KEY (id),
    CONSTRAINT uq_alpha_uuid UNIQUE (uuid),
    CONSTRAINT ck_alpha_xy_chk CHECK (x > 0 OR y < 100)
)


CREATE TABLE beta (
    id INTEGER NOT NULL,
    uuid CHAR(32) NOT NULL,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    CONSTRAINT pk_beta PRIMARY KEY (id),
    CONSTRAINT uq_beta_uuid UNIQUE (uuid),
    CONSTRAINT ck_beta_xy_chk CHECK (x > 0 OR y < 100)
)

Using named constraints such as Index, UniqueConstraint, CheckConstraint, where each object is to be unique to a specific table descending from a mixin, requires that an individual instance of each object is created per actual mapped class.

As a simple example, to define a named, potentially multicolumn Index that applies to all tables derived from a mixin, use the “inline” form of Index and establish it as part of __table_args__, using declared_attr to establish __table_args__() as a class method that will be invoked for each subclass:

class MyMixin:
    a = mapped_column(Integer)
    b = mapped_column(Integer)

    @declared_attr.directive
    def __table_args__(cls):
        return (Index(f"test_idx_{cls.__tablename__}", "a", "b"),)


class MyModelA(MyMixin, Base):
    __tablename__ = "table_a"
    id = mapped_column(Integer, primary_key=True)


class MyModelB(MyMixin, Base):
    __tablename__ = "table_b"
    id = mapped_column(Integer, primary_key=True)

The above example would generate two tables "table_a" and "table_b", with indexes "test_idx_table_a" and "test_idx_table_b"

Typically, in modern SQLAlchemy we would use a naming convention, as documented at 配置约束命名约定. While naming conventions take place automatically using the MetaData.naming_convention as new Constraint objects are created, as this convention is applied at object construction time based on the parent Table for a particular Constraint, a distinct Constraint object needs to be created for each inheriting subclass with its own Table, again using declared_attr with __table_args__(), below illustrated using an abstract mapped base:

from uuid import UUID

from sqlalchemy import CheckConstraint
from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import UniqueConstraint
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import declared_attr
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

constraint_naming_conventions = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}


class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=constraint_naming_conventions)


class MyAbstractBase(Base):
    __abstract__ = True

    @declared_attr.directive
    def __table_args__(cls):
        return (
            UniqueConstraint("uuid"),
            CheckConstraint("x > 0 OR y < 100", name="xy_chk"),
        )

    id: Mapped[int] = mapped_column(primary_key=True)
    uuid: Mapped[UUID]
    x: Mapped[int]
    y: Mapped[int]


class ModelAlpha(MyAbstractBase):
    __tablename__ = "alpha"


class ModelBeta(MyAbstractBase):
    __tablename__ = "beta"

The above mapping will generate DDL that includes table-specific names for all constraints, including primary key, CHECK constraint, unique constraint:

CREATE TABLE alpha (
    id INTEGER NOT NULL,
    uuid CHAR(32) NOT NULL,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    CONSTRAINT pk_alpha PRIMARY KEY (id),
    CONSTRAINT uq_alpha_uuid UNIQUE (uuid),
    CONSTRAINT ck_alpha_xy_chk CHECK (x > 0 OR y < 100)
)


CREATE TABLE beta (
    id INTEGER NOT NULL,
    uuid CHAR(32) NOT NULL,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    CONSTRAINT pk_beta PRIMARY KEY (id),
    CONSTRAINT uq_beta_uuid UNIQUE (uuid),
    CONSTRAINT ck_beta_xy_chk CHECK (x > 0 OR y < 100)
)