定义约束和索引

Defining Constraints and Indexes

中文

本节将讨论SQL 约束 (constraints) 和索引。在SQLAlchemy中，关键类包括 ForeignKeyConstraint 和 Index。

英文

This section will discuss SQL constraints and indexes. In SQLAlchemy the key classes include ForeignKeyConstraint and Index.

定义外键

Defining Foreign Keys

中文

在 SQL 中， 外键 是一种表级结构，它限制该表中的一个或多个列，仅允许这些列的值出现在另一组列中，通常但不总是在另一个表中。我们称这些被限制的列为 外键列，而被它们所引用的列为 被引用列。被引用的列几乎总是其所属表的主键，但也有例外。外键是连接具有相互关系的行对的“关节”，SQLAlchemy 在其几乎所有操作中都赋予这一概念极高的重要性。

在 SQLAlchemy 中，正如在 DDL 中一样，外键约束可以作为表定义中的附加属性来定义，也可以（在单列外键的情况下）选择在单个列的定义中指定。单列外键更为常见，并且可以在列级别通过构造 ForeignKey 对象并将其作为 Column 对象的参数来指定:

user_preference = Table(
    "user_preference",
    metadata_obj,
    Column("pref_id", Integer, primary_key=True),
    Column("user_id", Integer, ForeignKey("user.user_id"), nullable=False),
    Column("pref_name", String(40), nullable=False),
    Column("pref_value", String(100)),
)

如上所示，我们定义了一个新表 user_preference，其中每一行的 user_id 列的值必须存在于 user 表的 user_id 列中。

传递给 ForeignKey 的参数通常是形如 <表名>.<列名> 的字符串，对于位于远程 schema 或“拥有者”中的表，则使用形如 <schema名>.<表名>.<列名> 的格式。它也可以是一个实际的 Column 对象，如下所示，它是从已有的 Table 对象的 c 集合中获取的:

ForeignKey(user.c.user_id)

使用字符串的好处在于， user 和 user_preference 之间的 Python 内部链接仅在首次需要时才会解析，因此表对象可以轻松地分布在多个模块中，并按任意顺序定义。

外键也可以在表级别使用 ForeignKeyConstraint 对象来定义。该对象可以描述单列或多列的外键。多列外键称为 复合外键，几乎总是引用具有复合主键的表。下面我们定义一个具有复合主键的表 invoice:

invoice = Table(
    "invoice",
    metadata_obj,
    Column("invoice_id", Integer, primary_key=True),
    Column("ref_num", Integer, primary_key=True),
    Column("description", String(60), nullable=False),
)

然后是一个引用 invoice 的复合外键表 invoice_item:

invoice_item = Table(
    "invoice_item",
    metadata_obj,
    Column("item_id", Integer, primary_key=True),
    Column("item_name", String(60), nullable=False),
    Column("invoice_id", Integer, nullable=False),
    Column("ref_num", Integer, nullable=False),
    ForeignKeyConstraint(
        ["invoice_id", "ref_num"], ["invoice.invoice_id", "invoice.ref_num"]
    ),
)

需要注意的是，ForeignKeyConstraint 是定义复合外键的唯一方式。尽管我们也可以分别在 invoice_item.invoice_id 和 invoice_item.ref_num 列上放置单个 ForeignKey 对象，但 SQLAlchemy 不会意识到这两个值应该成对匹配——它会被视为两个独立的外键约束，而不是引用两个列的单个复合外键。

英文

A foreign key in SQL is a table-level construct that constrains one or more columns in that table to only allow values that are present in a different set of columns, typically but not always located on a different table. We call the columns which are constrained the foreign key columns and the columns which they are constrained towards the referenced columns. The referenced columns almost always define the primary key for their owning table, though there are exceptions to this. The foreign key is the “joint” that connects together pairs of rows which have a relationship with each other, and SQLAlchemy assigns very deep importance to this concept in virtually every area of its operation.

In SQLAlchemy as well as in DDL, foreign key constraints can be defined as additional attributes within the table clause, or for single-column foreign keys they may optionally be specified within the definition of a single column. The single column foreign key is more common, and at the column level is specified by constructing a ForeignKey object as an argument to a Column object:

user_preference = Table(
    "user_preference",
    metadata_obj,
    Column("pref_id", Integer, primary_key=True),
    Column("user_id", Integer, ForeignKey("user.user_id"), nullable=False),
    Column("pref_name", String(40), nullable=False),
    Column("pref_value", String(100)),
)

Above, we define a new table user_preference for which each row must contain a value in the user_id column that also exists in the user table’s user_id column.

The argument to ForeignKey is most commonly a string of the form <tablename>.<columnname>, or for a table in a remote schema or “owner” of the form <schemaname>.<tablename>.<columnname>. It may also be an actual Column object, which as we’ll see later is accessed from an existing Table object via its c collection:

ForeignKey(user.c.user_id)

The advantage to using a string is that the in-python linkage between user and user_preference is resolved only when first needed, so that table objects can be easily spread across multiple modules and defined in any order.

Foreign keys may also be defined at the table level, using the ForeignKeyConstraint object. This object can describe a single- or multi-column foreign key. A multi-column foreign key is known as a composite foreign key, and almost always references a table that has a composite primary key. Below we define a table invoice which has a composite primary key:

invoice = Table(
    "invoice",
    metadata_obj,
    Column("invoice_id", Integer, primary_key=True),
    Column("ref_num", Integer, primary_key=True),
    Column("description", String(60), nullable=False),
)

And then a table invoice_item with a composite foreign key referencing invoice:

invoice_item = Table(
    "invoice_item",
    metadata_obj,
    Column("item_id", Integer, primary_key=True),
    Column("item_name", String(60), nullable=False),
    Column("invoice_id", Integer, nullable=False),
    Column("ref_num", Integer, nullable=False),
    ForeignKeyConstraint(
        ["invoice_id", "ref_num"], ["invoice.invoice_id", "invoice.ref_num"]
    ),
)

It’s important to note that the ForeignKeyConstraint is the only way to define a composite foreign key. While we could also have placed individual ForeignKey objects on both the invoice_item.invoice_id and invoice_item.ref_num columns, SQLAlchemy would not be aware that these two values should be paired together - it would be two individual foreign key constraints instead of a single composite foreign key referencing two columns.

通过 ALTER 创建/删除外键约束

Creating/Dropping Foreign Key Constraints via ALTER

中文

我们在教程和其他地方看到的外键与 DDL 的行为表明，约束通常以内联方式呈现在 CREATE TABLE 语句中，例如：

CREATE TABLE addresses (
    id INTEGER NOT NULL,
    user_id INTEGER,
    email_address VARCHAR NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES users (id)
)

CONSTRAINT .. FOREIGN KEY 指令用于在 CREATE TABLE 定义中以内联方式创建约束。 MetaData.create_all() 和 MetaData.drop_all() 方法默认采用这种方式，它们会对涉及的所有 Table 对象进行拓扑排序，以按照外键依赖关系的顺序创建和删除表（该排序也可通过 MetaData.sorted_tables 访问器获得）。

当涉及两个或多个外键约束且存在“依赖循环”时，这种方式将不起作用，即一组表互相依赖，前提是后端强制执行外键（除 SQLite 和 MySQL/MyISAM 外，其他数据库均是如此）。因此这些方法会将此类循环中的约束拆分为单独的 ALTER 语句，在除 SQLite 以外的所有后端中执行，因为 SQLite 不支持大多数 ALTER 形式。如下所示：

node = Table(
    "node",
    metadata_obj,
    Column("node_id", Integer, primary_key=True),
    Column("primary_element", Integer, ForeignKey("element.element_id")),
)

element = Table(
    "element",
    metadata_obj,
    Column("element_id", Integer, primary_key=True),
    Column("parent_node_id", Integer),
    ForeignKeyConstraint(
        ["parent_node_id"], ["node.node_id"], name="fk_element_parent_node_id"
    ),
)

当我们在 PostgreSQL 后端上调用 MetaData.create_all() 时， 两个表之间的循环将被解析，约束将被单独创建：

>>> with engine.connect() as conn:
...     metadata_obj.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)

CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id)
)

ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
    FOREIGN KEY(parent_node_id) REFERENCES node (node_id)
ALTER TABLE node ADD FOREIGN KEY(primary_element)
    REFERENCES element (element_id)

当执行 DROP 操作时也会应用相同的逻辑，不过要注意，在 SQL 中执行 DROP CONSTRAINT 需要该约束具有名称。就上面的 'node' 表而言，我们并未为该约束命名，因此系统将尝试仅对具名约束执行 DROP：

>>> with engine.connect() as conn:
...     metadata_obj.drop_all(conn, checkfirst=False)
ALTER TABLE element DROP CONSTRAINT fk_element_parent_node_id
DROP TABLE node
DROP TABLE element

如果循环无法被解析，比如在我们没有为任一约束命名的情况下，则会收到如下错误：

sqlalchemy.exc.CircularDependencyError: Can't sort tables for DROP;
an unresolvable foreign key dependency exists between tables:
element, node.  Please ensure that the ForeignKey and ForeignKeyConstraint
objects involved in the cycle have names so that they can be dropped
using DROP CONSTRAINT.

这个错误只适用于 DROP 情况，因为在 CREATE 情况下可以不指定名称而发出 “ADD CONSTRAINT”；数据库通常会自动分配一个名称。

ForeignKeyConstraint.use_alter 和 ForeignKey.use_alter 关键字参数可用于手动解析依赖循环。我们可以只在 'element' 表中添加该标志，如下所示:

element = Table(
    "element",
    metadata_obj,
    Column("element_id", Integer, primary_key=True),
    Column("parent_node_id", Integer),
    ForeignKeyConstraint(
        ["parent_node_id"],
        ["node.node_id"],
        use_alter=True,
        name="fk_element_parent_node_id",
    ),
)

在我们的 CREATE DDL 中，我们将仅看到此约束的 ALTER 语句，而不是另一个约束：

>>> with engine.connect() as conn:
...     metadata_obj.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)

CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id),
    FOREIGN KEY(primary_element) REFERENCES element (element_id)
)

ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
FOREIGN KEY(parent_node_id) REFERENCES node (node_id)

ForeignKeyConstraint.use_alter 和 ForeignKey.use_alter 与 DROP 操作结合使用时，约束必须具名，否则会引发如下错误：

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint
ForeignKeyConstraint(...); it has no name

参见

配置约束命名约定

sort_tables_and_constraints()

英文

The behavior we’ve seen in tutorials and elsewhere involving foreign keys with DDL illustrates that the constraints are typically rendered “inline” within the CREATE TABLE statement, such as:

CREATE TABLE addresses (
    id INTEGER NOT NULL,
    user_id INTEGER,
    email_address VARCHAR NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES users (id)
)

The CONSTRAINT .. FOREIGN KEY directive is used to create the constraint in an “inline” fashion within the CREATE TABLE definition. The MetaData.create_all() and MetaData.drop_all() methods do this by default, using a topological sort of all the Table objects involved such that tables are created and dropped in order of their foreign key dependency (this sort is also available via the MetaData.sorted_tables accessor).

This approach can’t work when two or more foreign key constraints are involved in a “dependency cycle”, where a set of tables are mutually dependent on each other, assuming the backend enforces foreign keys (always the case except on SQLite, MySQL/MyISAM). The methods will therefore break out constraints in such a cycle into separate ALTER statements, on all backends other than SQLite which does not support most forms of ALTER. Given a schema like:

node = Table(
    "node",
    metadata_obj,
    Column("node_id", Integer, primary_key=True),
    Column("primary_element", Integer, ForeignKey("element.element_id")),
)

element = Table(
    "element",
    metadata_obj,
    Column("element_id", Integer, primary_key=True),
    Column("parent_node_id", Integer),
    ForeignKeyConstraint(
        ["parent_node_id"], ["node.node_id"], name="fk_element_parent_node_id"
    ),
)

When we call upon MetaData.create_all() on a backend such as the PostgreSQL backend, the cycle between these two tables is resolved and the constraints are created separately:

>>> with engine.connect() as conn:
...     metadata_obj.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)

CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id)
)

ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
    FOREIGN KEY(parent_node_id) REFERENCES node (node_id)
ALTER TABLE node ADD FOREIGN KEY(primary_element)
    REFERENCES element (element_id)

In order to emit DROP for these tables, the same logic applies, however note here that in SQL, to emit DROP CONSTRAINT requires that the constraint has a name. In the case of the 'node' table above, we haven’t named this constraint; the system will therefore attempt to emit DROP for only those constraints that are named:

>>> with engine.connect() as conn:
...     metadata_obj.drop_all(conn, checkfirst=False)
ALTER TABLE element DROP CONSTRAINT fk_element_parent_node_id
DROP TABLE node
DROP TABLE element

In the case where the cycle cannot be resolved, such as if we hadn’t applied a name to either constraint here, we will receive the following error:

sqlalchemy.exc.CircularDependencyError: Can't sort tables for DROP;
an unresolvable foreign key dependency exists between tables:
element, node.  Please ensure that the ForeignKey and ForeignKeyConstraint
objects involved in the cycle have names so that they can be dropped
using DROP CONSTRAINT.

This error only applies to the DROP case as we can emit “ADD CONSTRAINT” in the CREATE case without a name; the database typically assigns one automatically.

The ForeignKeyConstraint.use_alter and ForeignKey.use_alter keyword arguments can be used to manually resolve dependency cycles. We can add this flag only to the 'element' table as follows:

element = Table(
    "element",
    metadata_obj,
    Column("element_id", Integer, primary_key=True),
    Column("parent_node_id", Integer),
    ForeignKeyConstraint(
        ["parent_node_id"],
        ["node.node_id"],
        use_alter=True,
        name="fk_element_parent_node_id",
    ),
)

in our CREATE DDL we will see the ALTER statement only for this constraint, and not the other one:

>>> with engine.connect() as conn:
...     metadata_obj.create_all(conn, checkfirst=False)
CREATE TABLE element (
    element_id SERIAL NOT NULL,
    parent_node_id INTEGER,
    PRIMARY KEY (element_id)
)

CREATE TABLE node (
    node_id SERIAL NOT NULL,
    primary_element INTEGER,
    PRIMARY KEY (node_id),
    FOREIGN KEY(primary_element) REFERENCES element (element_id)
)

ALTER TABLE element ADD CONSTRAINT fk_element_parent_node_id
FOREIGN KEY(parent_node_id) REFERENCES node (node_id)

ForeignKeyConstraint.use_alter and ForeignKey.use_alter, when used in conjunction with a drop operation, will require that the constraint is named, else an error like the following is generated:

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint
ForeignKeyConstraint(...); it has no name

参见

配置约束命名约定

sort_tables_and_constraints()

ON UPDATE 和 ON DELETE

ON UPDATE and ON DELETE

中文

大多数数据库支持外键值的 级联 操作，也就是说，当父行被更新时，新的值会传递到子行中；或者当父行被删除时，所有对应的子行会被设为 null 或一并删除。 在数据定义语言（DDL）中，这类行为通过像 “ON UPDATE CASCADE”、”ON DELETE CASCADE” 和 “ON DELETE SET NULL” 这样的短语来指定，对应于外键约束。”ON UPDATE” 或 “ON DELETE” 后的短语也可能因所用数据库的不同而有所变化。 ForeignKey 和 ForeignKeyConstraint 对象支持通过 onupdate 和 ondelete 关键字参数生成这些子句。其值是任意字符串，会被输出在相应的 “ON UPDATE” 或 “ON DELETE” 短语之后:

child = Table(
    "child",
    metadata_obj,
    Column(
        "id",
        Integer,
        ForeignKey("parent.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    ),
)

composite = Table(
    "composite",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("rev_id", Integer),
    Column("note_id", Integer),
    ForeignKeyConstraint(
        ["rev_id", "note_id"],
        ["revisions.id", "revisions.note_id"],
        onupdate="CASCADE",
        ondelete="SET NULL",
    ),
)

请注意，一些数据库后端在级联操作上有特殊要求：

• MySQL / MariaDB - 应使用 InnoDB 存储引擎（在现代数据库中通常是默认选项）

• SQLite - 默认情况下不启用外键约束。 参考 外键支持

参见

有关将 ON DELETE CASCADE 与 ORM relationship() 构造集成的背景知识，请参见以下章节：

使用具有 ORM 关系的外键 ON DELETE 级联

使用具有多对多关系的外键 ON DELETE

PostgreSQL 约束选项 - 指出外键级联操作可用的其他选项，如列列表

外键支持 - 启用 SQLite 外键支持的背景知识

英文

Most databases support cascading of foreign key values, that is the when a parent row is updated the new value is placed in child rows, or when the parent row is deleted all corresponding child rows are set to null or deleted. In data definition language these are specified using phrases like “ON UPDATE CASCADE”, “ON DELETE CASCADE”, and “ON DELETE SET NULL”, corresponding to foreign key constraints. The phrase after “ON UPDATE” or “ON DELETE” may also allow other phrases that are specific to the database in use. The ForeignKey and ForeignKeyConstraint objects support the generation of this clause via the onupdate and ondelete keyword arguments. The value is any string which will be output after the appropriate “ON UPDATE” or “ON DELETE” phrase:

child = Table(
    "child",
    metadata_obj,
    Column(
        "id",
        Integer,
        ForeignKey("parent.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    ),
)

composite = Table(
    "composite",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("rev_id", Integer),
    Column("note_id", Integer),
    ForeignKeyConstraint(
        ["rev_id", "note_id"],
        ["revisions.id", "revisions.note_id"],
        onupdate="CASCADE",
        ondelete="SET NULL",
    ),
)

Note that some backends have special requirements for cascades to function:

• MySQL / MariaDB - the InnoDB storage engine should be used (this is typically the default in modern databases)

• SQLite - constraints are not enabled by default. See 外键支持

参见

For background on integration of ON DELETE CASCADE with ORM relationship() constructs, see the following sections:

使用具有 ORM 关系的外键 ON DELETE 级联

使用具有多对多关系的外键 ON DELETE

PostgreSQL 约束选项 - indicates additional options available for foreign key cascades such as column lists

外键支持 - background on enabling foreign key support with SQLite

UNIQUE 约束

UNIQUE Constraint

中文

唯一约束（Unique Constraint）可以通过在 Column 上使用 unique 关键字以匿名方式创建单列表唯一约束。 带有显式名称的唯一约束和/或多列表唯一约束通过 UniqueConstraint 表级构造来创建。

from sqlalchemy import UniqueConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # 每列匿名唯一约束
    Column("col1", Integer, unique=True),
    Column("col2", Integer),
    Column("col3", Integer),
    # 显式/复合唯一约束。'name' 是可选项。
    UniqueConstraint("col2", "col3", name="uix_1"),
)

英文

Unique constraints can be created anonymously on a single column using the unique keyword on Column. Explicitly named unique constraints and/or those with multiple columns are created via the UniqueConstraint table-level construct.

from sqlalchemy import UniqueConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # per-column anonymous unique constraint
    Column("col1", Integer, unique=True),
    Column("col2", Integer),
    Column("col3", Integer),
    # explicit/composite unique constraint.  'name' is optional.
    UniqueConstraint("col2", "col3", name="uix_1"),
)

CHECK 约束

CHECK Constraint

中文

检查约束（Check Constraint）可以具名或匿名，并可以在列级或表级使用 CheckConstraint 构造进行创建。 检查约束的文本会直接传递给数据库，因此其“数据库无关性”是有限的。列级检查约束通常只应引用所在的那一列，而表级约束可以引用表中的任意列。

请注意，一些数据库（如 MySQL 8.0.16 之前的版本）并不真正支持检查约束。

from sqlalchemy import CheckConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # 每列 CHECK 约束
    Column("col1", Integer, CheckConstraint("col1>5")),
    Column("col2", Integer),
    Column("col3", Integer),
    # 表级 CHECK 约束。'name' 是可选项。
    CheckConstraint("col2 > col3 + 5", name="check1"),
)

mytable.create(engine)
CREATE TABLE mytable (
    col1 INTEGER  CHECK (col1>5),
    col2 INTEGER,
    col3 INTEGER,
    CONSTRAINT check1  CHECK (col2 > col3 + 5)
)

英文

Check constraints can be named or unnamed and can be created at the Column or Table level, using the CheckConstraint construct. The text of the check constraint is passed directly through to the database, so there is limited “database independent” behavior. Column level check constraints generally should only refer to the column to which they are placed, while table level constraints can refer to any columns in the table.

Note that some databases do not actively support check constraints such as older versions of MySQL (prior to 8.0.16).

from sqlalchemy import CheckConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # per-column CHECK constraint
    Column("col1", Integer, CheckConstraint("col1>5")),
    Column("col2", Integer),
    Column("col3", Integer),
    # table level CHECK constraint.  'name' is optional.
    CheckConstraint("col2 > col3 + 5", name="check1"),
)

mytable.create(engine)
CREATE TABLE mytable (
    col1 INTEGER  CHECK (col1>5),
    col2 INTEGER,
    col3 INTEGER,
    CONSTRAINT check1  CHECK (col2 > col3 + 5)
)

PRIMARY KEY 约束

PRIMARY KEY Constraint

中文

任何 Table 对象的主键约束是隐式存在的，基于那些设置了 Column.primary_key 标志的 Column 对象。 PrimaryKeyConstraint 对象提供了对该约束的显式访问方式，并允许直接进行配置:

from sqlalchemy import PrimaryKeyConstraint

my_table = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer),
    Column("version_id", Integer),
    Column("data", String(50)),
    PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
)

参见

PrimaryKeyConstraint - 详细的 API 文档。

英文

The primary key constraint of any Table object is implicitly present, based on the Column objects that are marked with the Column.primary_key flag. The PrimaryKeyConstraint object provides explicit access to this constraint, which includes the option of being configured directly:

from sqlalchemy import PrimaryKeyConstraint

my_table = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer),
    Column("version_id", Integer),
    Column("data", String(50)),
    PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
)

参见

PrimaryKeyConstraint - detailed API documentation.

使用声明性 ORM 扩展时设置约束

Setting up Constraints when using the Declarative ORM Extension

中文

Table 是 SQLAlchemy Core 中用于定义表元数据的构造，这些元数据除了其他用途外，还可作为 SQLAlchemy ORM 中类映射的目标。Declarative 扩展允许根据一组 Column 对象的映射自动创建 Table 对象。

若要将诸如 ForeignKeyConstraint 这样的表级约束对象应用于通过 Declarative 定义的表，可以使用 __table_args__ 属性，详见 Table Configuration。

英文

The Table is the SQLAlchemy Core construct that allows one to define table metadata, which among other things can be used by the SQLAlchemy ORM as a target to map a class. The Declarative extension allows the Table object to be created automatically, given the contents of the table primarily as a mapping of Column objects.

To apply table-level constraint objects such as ForeignKeyConstraint to a table defined using Declarative, use the __table_args__ attribute, described at Table Configuration.

配置约束命名约定

Configuring Constraint Naming Conventions

中文

关系型数据库通常为所有约束和索引分配显式名称。在常见情况下，使用 CREATE TABLE 创建表时，CHECK、UNIQUE 和 PRIMARY KEY 等约束是在表定义中内联产生的，如果没有指定名称，数据库系统通常会自动为这些约束分配名称。而当使用诸如 ALTER TABLE 这样的命令修改已有数据库表时，该命令通常需要为新添加的约束显式指定名称，并且能够指定要删除或修改的现有约束的名称。

可以使用 Constraint.name 参数显式地为约束命名，索引使用 Index.name 参数。对于约束来说，该参数是可选的。也可以通过 Column.unique 和 Column.index 参数使用不带显式名称的方式创建 UniqueConstraint 和 Index 对象。

对已有表和约束进行修改的用例可通过如 Alembic 等模式迁移工具来处理。然而，Alembic 和 SQLAlchemy 当前都不会为未指定名称的约束对象自动生成名称，因此若要修改现有约束，就需要反向工程分析数据库用于自动命名约束的机制，或是在一开始就小心地为所有约束指定名称。

与为所有 Constraint 和 Index 对象显式命名的方式相对，使用事件机制可以构建自动命名方案。这种方法的优点是，可以无需在代码中到处指定名称参数，也能为所有约束和索引提供一致的命名方案；而且这种机制同样适用于通过 Column.unique 和 Column.index 参数产生的约束和索引。从 SQLAlchemy 0.9.2 起，基于事件的此类命名方案已被包含，并可通过 MetaData.naming_convention 参数进行配置。

英文

Relational databases typically assign explicit names to all constraints and indexes. In the common case that a table is created using CREATE TABLE where constraints such as CHECK, UNIQUE, and PRIMARY KEY constraints are produced inline with the table definition, the database usually has a system in place in which names are automatically assigned to these constraints, if a name is not otherwise specified. When an existing database table is altered in a database using a command such as ALTER TABLE, this command typically needs to specify explicit names for new constraints as well as be able to specify the name of an existing constraint that is to be dropped or modified.

Constraints can be named explicitly using the Constraint.name parameter, and for indexes the Index.name parameter. However, in the case of constraints this parameter is optional. There are also the use cases of using the Column.unique and Column.index parameters which create UniqueConstraint and Index objects without an explicit name being specified.

The use case of alteration of existing tables and constraints can be handled by schema migration tools such as Alembic. However, neither Alembic nor SQLAlchemy currently create names for constraint objects where the name is otherwise unspecified, leading to the case where being able to alter existing constraints means that one must reverse-engineer the naming system used by the relational database to auto-assign names, or that care must be taken to ensure that all constraints are named.

In contrast to having to assign explicit names to all Constraint and Index objects, automated naming schemes can be constructed using events. This approach has the advantage that constraints will get a consistent naming scheme without the need for explicit name parameters throughout the code, and also that the convention takes place just as well for those constraints and indexes produced by the Column.unique and Column.index parameters. As of SQLAlchemy 0.9.2 this event-based approach is included, and can be configured using the argument MetaData.naming_convention.

为元数据集合配置命名约定

Configuring a Naming Convention for a MetaData Collection

中文

MetaData.naming_convention 是一个字典，其键可以是 Index 类或各个 Constraint 类，也可以是一些字符串代码，如 "fk"、 "pk"、 "ix"、 "ck"、 "uq" ，分别代表外键、主键、索引、检查和唯一约束。字典中的字符串模板将在约束或索引与该 MetaData 对象关联且未指定名称时使用（包括一个可以进一步装饰已有名称的例外情况）。

一个适用于基本场景的命名方案示例如下:

convention = {
    "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",
}

metadata_obj = MetaData(naming_convention=convention)

上述命名方案将为目标 MetaData 集合中的所有约束生成名称。 例如，当我们创建一个未命名的 UniqueConstraint 时，可以看到自动生成的名称:

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False),
...     UniqueConstraint("name"),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

即使我们只是使用 Column.unique 标志，该功能也会生效:

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False, unique=True),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

命名方案方法的一个关键优势在于：名称是在 Python 构建对象时生成的，而不是在发出 DDL 时。这对于使用 Alembic 的 --autogenerate 功能尤为重要，因为生成的新迁移脚本中将会包含显式的命名:

def upgrade():
    op.create_unique_constraint("uq_user_name", "user", ["name"])

上述 "uq_user_name" 字符串即是从 --autogenerate 在元数据中定位到的 UniqueConstraint 对象中获取的。

可用的模板变量包括 %(table_name)s、 %(referred_table_name)s、 %(column_0_name)s、 %(column_0_label)s、 %(column_0_key)s、 %(referred_column_0_name)s 和 %(constraint_name)s，还包括它们的多列版本，如 %(column_0N_name)s、 %(column_0_N_name)s、 %(referred_column_0_N_name)s，这些变量会以带或不带下划线的方式连接所有列名。 MetaData.naming_convention 的文档中提供了对每个模板变量的更多细节说明。

英文

MetaData.naming_convention refers to a dictionary which accepts the Index class or individual Constraint classes as keys, and Python string templates as values. It also accepts a series of string-codes as alternative keys, "fk", "pk", "ix", "ck", "uq" for foreign key, primary key, index, check, and unique constraint, respectively. The string templates in this dictionary are used whenever a constraint or index is associated with this MetaData object that does not have an existing name given (including one exception case where an existing name can be further embellished).

An example naming convention that suits basic cases is as follows:

convention = {
    "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",
}

metadata_obj = MetaData(naming_convention=convention)

The above convention will establish names for all constraints within the target MetaData collection. For example, we can observe the name produced when we create an unnamed UniqueConstraint:

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False),
...     UniqueConstraint("name"),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

This same feature takes effect even if we just use the Column.unique flag:

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("name", String(30), nullable=False, unique=True),
... )
>>> list(user_table.constraints)[1].name
'uq_user_name'

A key advantage to the naming convention approach is that the names are established at Python construction time, rather than at DDL emit time. The effect this has when using Alembic’s --autogenerate feature is that the naming convention will be explicit when a new migration script is generated:

def upgrade():
    op.create_unique_constraint("uq_user_name", "user", ["name"])

The above "uq_user_name" string was copied from the UniqueConstraint object that --autogenerate located in our metadata.

The tokens available include %(table_name)s, %(referred_table_name)s, %(column_0_name)s, %(column_0_label)s, %(column_0_key)s, %(referred_column_0_name)s, and %(constraint_name)s, as well as multiple-column versions of each including %(column_0N_name)s, %(column_0_N_name)s, %(referred_column_0_N_name)s which render all column names separated with or without an underscore. The documentation for MetaData.naming_convention has further detail on each of these conventions.

默认命名约定

The Default Naming Convention

中文

MetaData.naming_convention 的默认值处理了 SQLAlchemy 长期以来的行为， 即为使用 Column.index 参数创建的 Index 对象分配名称:

>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
>>> DEFAULT_NAMING_CONVENTION
immutabledict({'ix': 'ix_%(column_0_label)s'})

英文

The default value for MetaData.naming_convention handles the long-standing SQLAlchemy behavior of assigning a name to a Index object that is created using the Column.index parameter:

>>> from sqlalchemy.sql.schema import DEFAULT_NAMING_CONVENTION
>>> DEFAULT_NAMING_CONVENTION
immutabledict({'ix': 'ix_%(column_0_label)s'})

长名称截断

Truncation of Long Names

中文

当生成的名称（特别是那些使用了多列 token 的名称）超过目标数据库标识符长度限制时 （例如 PostgreSQL 的限制是 63 个字符），该名称将会被确定性地截断，并追加一个 基于原始长名称的 md5 哈希值计算出的 4 位后缀。例如，下面的命名规则会由于列名较长 而生成超长名称:

metadata_obj = MetaData(
    naming_convention={"uq": "uq_%(table_name)s_%(column_0_N_name)s"}
)

long_names = Table(
    "long_names",
    metadata_obj,
    Column("information_channel_code", Integer, key="a"),
    Column("billing_convention_name", Integer, key="b"),
    Column("product_identifier", Integer, key="c"),
    UniqueConstraint("a", "b", "c"),
)

在 PostgreSQL 方言中，名称超过 63 个字符时将被如下方式截断：

CREATE TABLE long_names (
    information_channel_code INTEGER,
    billing_convention_name INTEGER,
    product_identifier INTEGER,
    CONSTRAINT uq_long_names_information_channel_code_billing_conventi_a79e
    UNIQUE (information_channel_code, billing_convention_name, product_identifier)
)

上述后缀 a79e 是基于长名称的 md5 哈希值得出的，并将在每次生成时保持一致， 从而为给定的模式生成一致的名称。

英文

When a generated name, particularly those that use the multiple-column tokens, is too long for the identifier length limit of the target database (for example, PostgreSQL has a limit of 63 characters), the name will be deterministically truncated using a 4-character suffix based on the md5 hash of the long name. For example, the naming convention below will generate very long names given the column names in use:

metadata_obj = MetaData(
    naming_convention={"uq": "uq_%(table_name)s_%(column_0_N_name)s"}
)

long_names = Table(
    "long_names",
    metadata_obj,
    Column("information_channel_code", Integer, key="a"),
    Column("billing_convention_name", Integer, key="b"),
    Column("product_identifier", Integer, key="c"),
    UniqueConstraint("a", "b", "c"),
)

On the PostgreSQL dialect, names longer than 63 characters will be truncated as in the following example:

CREATE TABLE long_names (
    information_channel_code INTEGER,
    billing_convention_name INTEGER,
    product_identifier INTEGER,
    CONSTRAINT uq_long_names_information_channel_code_billing_conventi_a79e
    UNIQUE (information_channel_code, billing_convention_name, product_identifier)
)

The above suffix a79e is based on the md5 hash of the long name and will generate the same value every time to produce consistent names for a given schema.

为命名约定创建自定义标记

Creating Custom Tokens for Naming Conventions

中文

还可以通过在命名约定字典中指定额外的 token 和可调用对象来自定义添加新 token。 例如，如果我们希望使用 GUID 方案为外键约束命名，可以按如下方式实现:

import uuid


def fk_guid(constraint, table):
    str_tokens = (
        [
            table.name,
        ]
        + [element.parent.name for element in constraint.elements]
        + [element.target_fullname for element in constraint.elements]
    )
    guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens).encode("ascii"))
    return str(guid)


convention = {
    "fk_guid": fk_guid,
    "ix": "ix_%(column_0_label)s",
    "fk": "fk_%(fk_guid)s",
}

在上例中，当我们创建一个新的 ForeignKeyConstraint 时， 将获得如下名称:

>>> metadata_obj = MetaData(naming_convention=convention)

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("version", Integer, primary_key=True),
...     Column("data", String(30)),
... )
>>> address_table = Table(
...     "address",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("user_id", Integer),
...     Column("user_version_id", Integer),
... )
>>> fk = ForeignKeyConstraint(["user_id", "user_version_id"], ["user.id", "user.version"])
>>> address_table.append_constraint(fk)
>>> fk.name
fk_0cd51ab5-8d70-56e8-a83c-86661737766d

参见

MetaData.naming_convention - 了解更多用法细节以及可用命名组件的完整列表。

命名约束的重要性 - Alembic 文档中的相关说明。

英文

New tokens can also be added, by specifying an additional token and a callable within the naming_convention dictionary. For example, if we wanted to name our foreign key constraints using a GUID scheme, we could do that as follows:

import uuid


def fk_guid(constraint, table):
    str_tokens = (
        [
            table.name,
        ]
        + [element.parent.name for element in constraint.elements]
        + [element.target_fullname for element in constraint.elements]
    )
    guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens).encode("ascii"))
    return str(guid)


convention = {
    "fk_guid": fk_guid,
    "ix": "ix_%(column_0_label)s",
    "fk": "fk_%(fk_guid)s",
}

Above, when we create a new ForeignKeyConstraint, we will get a name as follows:

>>> metadata_obj = MetaData(naming_convention=convention)

>>> user_table = Table(
...     "user",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("version", Integer, primary_key=True),
...     Column("data", String(30)),
... )
>>> address_table = Table(
...     "address",
...     metadata_obj,
...     Column("id", Integer, primary_key=True),
...     Column("user_id", Integer),
...     Column("user_version_id", Integer),
... )
>>> fk = ForeignKeyConstraint(["user_id", "user_version_id"], ["user.id", "user.version"])
>>> address_table.append_constraint(fk)
>>> fk.name
fk_0cd51ab5-8d70-56e8-a83c-86661737766d

参见

MetaData.naming_convention - for additional usage details as well as a listing of all available naming components.

The Importance of Naming Constraints - in the Alembic documentation.

命名 CHECK 约束

Naming CHECK Constraints

中文

CheckConstraint 对象配置在任意 SQL 表达式之上，该表达式可以包含任意数量的列， 而且通常使用原始 SQL 字符串进行配置。因此，对于 CheckConstraint，一种常见的命名约定是： 假设该对象已有名称，然后再结合其他约定元素进行增强。 一个典型的约定形式为 "ck_%(table_name)s_%(constraint_name)s":

metadata_obj = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)

Table(
    "foo",
    metadata_obj,
    Column("value", Integer),
    CheckConstraint("value > 5", name="value_gt_5"),
)

上述表格将生成名称 ck_foo_value_gt_5：

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value_gt_5 CHECK (value > 5)
)

CheckConstraint 也支持 %(columns_0_name)s token； 我们可以通过在约束表达式中确保使用 Column 或 column() 元素来使用该 token， 方法可以是将约束与表分离定义:

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

foo = Table("foo", metadata_obj, Column("value", Integer))

CheckConstraint(foo.c.value > 5)

也可以通过内联使用 column():

from sqlalchemy import column

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

foo = Table(
    "foo", metadata_obj, Column("value", Integer), CheckConstraint(column("value") > 5)
)

两种方式都会生成名称 ck_foo_value：

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value CHECK (value > 5)
)

“column zero”的名称确定方式是通过扫描表达式中存在的列对象来完成的。 如果表达式中存在多个列，该扫描会使用确定性的搜索逻辑， 但具体结构会决定哪一列被视为“column zero”。

英文

The CheckConstraint object is configured against an arbitrary SQL expression, which can have any number of columns present, and additionally is often configured using a raw SQL string. Therefore a common convention to use with CheckConstraint is one where we expect the object to have a name already, and we then enhance it with other convention elements. A typical convention is "ck_%(table_name)s_%(constraint_name)s":

metadata_obj = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)

Table(
    "foo",
    metadata_obj,
    Column("value", Integer),
    CheckConstraint("value > 5", name="value_gt_5"),
)

The above table will produce the name ck_foo_value_gt_5:

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value_gt_5 CHECK (value > 5)
)

CheckConstraint also supports the %(columns_0_name)s token; we can make use of this by ensuring we use a Column or column() element within the constraint’s expression, either by declaring the constraint separate from the table:

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

foo = Table("foo", metadata_obj, Column("value", Integer))

CheckConstraint(foo.c.value > 5)

or by using a column() inline:

from sqlalchemy import column

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

foo = Table(
    "foo", metadata_obj, Column("value", Integer), CheckConstraint(column("value") > 5)
)

Both will produce the name ck_foo_value:

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value CHECK (value > 5)
)

The determination of the name of “column zero” is performed by scanning the given expression for column objects. If the expression has more than one column present, the scan does use a deterministic search, however the structure of the expression will determine which column is noted as “column zero”.

为布尔、枚举和其他架构类型配置命名

Configuring Naming for Boolean, Enum, and other schema types

中文

SchemaType 类指的是如 Boolean 和 Enum 等类型对象， 它们会为对应的类型生成一个 CHECK 约束。 此类约束的名称可通过传入 name 参数直接指定，例如 Boolean.name:

Table("foo", metadata_obj, Column("flag", Boolean(name="ck_foo_flag")))

命名约定功能也可以与这些类型结合使用，通常会使用包含 %(constraint_name)s 的约定， 并为类型显式指定名称:

metadata_obj = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)

Table("foo", metadata_obj, Column("flag", Boolean(name="flag_bool")))

上述表将生成约束名 ck_foo_flag_bool：

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag_bool CHECK (flag IN (0, 1))
)

SchemaType 类使用了特殊的内部标记机制，使得命名约定仅在 DDL 编译时确定。 在 PostgreSQL 中，由于存在原生 BOOLEAN 类型，Boolean 的 CHECK 约束并不需要； 即使为 CHECK 约束配置了命名约定，也可以安全地定义未命名的 Boolean 类型。 该命名约定仅在运行在不支持原生 BOOLEAN 类型的数据库（如 SQLite 或 MySQL）时才会被启用。

CHECK 约束也可以使用 column_0_name token，该 token 与 SchemaType 搭配良好， 因为这类约束通常只涉及一个列:

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

Table("foo", metadata_obj, Column("flag", Boolean()))

上述结构将生成：

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag CHECK (flag IN (0, 1))
)

英文

The SchemaType class refers to type objects such as Boolean and Enum which generate a CHECK constraint accompanying the type. The name for the constraint here is most directly set up by sending the “name” parameter, e.g. Boolean.name:

Table("foo", metadata_obj, Column("flag", Boolean(name="ck_foo_flag")))

The naming convention feature may be combined with these types as well, normally by using a convention which includes %(constraint_name)s and then applying a name to the type:

metadata_obj = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)

Table("foo", metadata_obj, Column("flag", Boolean(name="flag_bool")))

The above table will produce the constraint name ck_foo_flag_bool:

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag_bool CHECK (flag IN (0, 1))
)

The SchemaType classes use special internal symbols so that the naming convention is only determined at DDL compile time. On PostgreSQL, there’s a native BOOLEAN type, so the CHECK constraint of Boolean is not needed; we are safe to set up a Boolean type without a name, even though a naming convention is in place for check constraints. This convention will only be consulted for the CHECK constraint if we run against a database without a native BOOLEAN type like SQLite or MySQL.

The CHECK constraint may also make use of the column_0_name token, which works nicely with SchemaType since these constraints have only one column:

metadata_obj = MetaData(naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"})

Table("foo", metadata_obj, Column("flag", Boolean()))

The above schema will produce:

CREATE TABLE foo (
    flag BOOL,
    CONSTRAINT ck_foo_flag CHECK (flag IN (0, 1))
)

将命名约定与 ORM 声明性混合使用

Using Naming Conventions with ORM Declarative Mixins

中文

当在 ORM Declarative Mixins 中使用命名约定功能时， 每个实际映射到表的子类必须拥有各自独立的约束对象。 详见 使用 Mixins 上的命名约定创建索引和约束 部分以获取背景信息和示例。

英文

When using the naming convention feature with ORM Declarative Mixins, individual constraint objects must exist for each actual table-mapped subclass. See the section 使用 Mixins 上的命名约定创建索引和约束 for background and examples.

约束 API

Constraints API

Object Name	Description
CheckConstraint	A table- or column-level CHECK constraint.
ColumnCollectionConstraint	A constraint that proxies a ColumnCollection.
ColumnCollectionMixin	A ColumnCollection of Column objects.
Constraint	A table-level SQL constraint.
conv	Mark a string indicating that a name has already been converted by a naming convention.
ForeignKey	Defines a dependency between two columns.
ForeignKeyConstraint	A table-level FOREIGN KEY constraint.
HasConditionalDDL	define a class that includes the HasConditionalDDL.ddl_if() method, allowing for conditional rendering of DDL.
PrimaryKeyConstraint	A table-level PRIMARY KEY constraint.
UniqueConstraint	A table-level UNIQUE constraint.

class sqlalchemy.schema.Constraint

A table-level SQL constraint.

Constraint serves as the base class for the series of constraint objects that can be associated with Table objects, including PrimaryKeyConstraint, ForeignKeyConstraint UniqueConstraint, and CheckConstraint.

Members

__init__(), argument_for(), copy(), ddl_if(), dialect_kwargs, dialect_options, info, kwargs

Class signature

class sqlalchemy.schema.Constraint (sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.HasConditionalDDL, sqlalchemy.schema.SchemaItem)

method sqlalchemy.schema.Constraint.__init__(name: _ConstraintNameArgument = None, deferrable: bool | None = None, initially: str | None = None, info: _InfoType | None = None, comment: str | None = None, _create_rule: Any | None = None, _type_bound: bool = False, **dialect_kw: Any) → None

Create a SQL constraint.

参数:

• name – Optional, the in-database name of this Constraint.

• deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.

• initially – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.

• info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

• comment –

  Optional string that will render an SQL comment on foreign key constraint creation.

  在 2.0 版本加入.

• **dialect_kw – Additional keyword arguments are dialect specific, and passed in the form <dialectname>_<argname>. See the documentation regarding an individual dialect at Dialects for detail on documented arguments.

• _create_rule – used internally by some datatypes that also create constraints.

• _type_bound – used internally to indicate that this constraint is associated with a specific datatype.

classmethod sqlalchemy.schema.Constraint.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

method sqlalchemy.schema.Constraint.copy(**kw: Any) → Self

自 1.4 版本弃用: The Constraint.copy() method is deprecated and will be removed in a future release.

method sqlalchemy.schema.Constraint.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

inherited from the HasConditionalDDL.ddl_if() method of HasConditionalDDL

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

attribute sqlalchemy.schema.Constraint.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.Constraint.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

attribute sqlalchemy.schema.Constraint.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.Constraint.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

class sqlalchemy.schema.ColumnCollectionMixin

A ColumnCollection of Column objects.

This collection represents the columns which are referred to by this object.

class sqlalchemy.schema.ColumnCollectionConstraint

A constraint that proxies a ColumnCollection.

Members

__init__(), argument_for(), columns, contains_column(), copy(), ddl_if(), dialect_kwargs, dialect_options, info, kwargs

Class signature

class sqlalchemy.schema.ColumnCollectionConstraint (sqlalchemy.schema.ColumnCollectionMixin, sqlalchemy.schema.Constraint)

method sqlalchemy.schema.ColumnCollectionConstraint.__init__(*columns: _DDLColumnArgument, name: _ConstraintNameArgument = None, deferrable: bool | None = None, initially: str | None = None, info: _InfoType | None = None, _autoattach: bool = True, _column_flag: bool = False, _gather_expressions: List[_DDLColumnArgument] | None = None, **dialect_kw: Any) → None

参数:

• *columns – A sequence of column names or Column objects.

• name – Optional, the in-database name of this constraint.

• deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.

• initially – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.

• **dialect_kw – other keyword arguments including dialect-specific arguments are propagated to the Constraint superclass.

classmethod sqlalchemy.schema.ColumnCollectionConstraint.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

attribute sqlalchemy.schema.ColumnCollectionConstraint.columns: ReadOnlyColumnCollection[str, Column[Any]]

inherited from the ColumnCollectionMixin.columns attribute of ColumnCollectionMixin

A ColumnCollection representing the set of columns for this constraint.

method sqlalchemy.schema.ColumnCollectionConstraint.contains_column(col: Column[Any]) → bool

Return True if this constraint contains the given column.

Note that this object also contains an attribute .columns which is a ColumnCollection of Column objects.

method sqlalchemy.schema.ColumnCollectionConstraint.copy(*, target_table: Table | None = None, **kw: Any) → ColumnCollectionConstraint

自 1.4 版本弃用: The ColumnCollectionConstraint.copy() method is deprecated and will be removed in a future release.

method sqlalchemy.schema.ColumnCollectionConstraint.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

inherited from the HasConditionalDDL.ddl_if() method of HasConditionalDDL

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

attribute sqlalchemy.schema.ColumnCollectionConstraint.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.ColumnCollectionConstraint.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

attribute sqlalchemy.schema.ColumnCollectionConstraint.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.ColumnCollectionConstraint.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

class sqlalchemy.schema.CheckConstraint

A table- or column-level CHECK constraint.

Can be included in the definition of a Table or Column.

Members

__init__(), argument_for(), columns, contains_column(), copy(), ddl_if(), dialect_kwargs, dialect_options, info, kwargs

Class signature

class sqlalchemy.schema.CheckConstraint (sqlalchemy.schema.ColumnCollectionConstraint)

method sqlalchemy.schema.CheckConstraint.__init__(sqltext: _TextCoercedExpressionArgument[Any], name: _ConstraintNameArgument = None, deferrable: bool | None = None, initially: str | None = None, table: Table | None = None, info: _InfoType | None = None, _create_rule: Any | None = None, _autoattach: bool = True, _type_bound: bool = False, **dialect_kw: Any) → None

Construct a CHECK constraint.

参数:

• sqltext –

  A string containing the constraint definition, which will be used verbatim, or a SQL expression construct. If given as a string, the object is converted to a text() object. If the textual string includes a colon character, escape this using a backslash:

  CheckConstraint(r"foo ~ E'a(?\:b|c)d")

  警告

  The CheckConstraint.sqltext argument to CheckConstraint can be passed as a Python string argument, which will be treated as trusted SQL text and rendered as given. DO NOT PASS UNTRUSTED INPUT TO THIS PARAMETER.

• name – Optional, the in-database name of the constraint.

• deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.

• initially – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.

• info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

classmethod sqlalchemy.schema.CheckConstraint.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

attribute sqlalchemy.schema.CheckConstraint.columns: ReadOnlyColumnCollection[str, Column[Any]]

inherited from the ColumnCollectionMixin.columns attribute of ColumnCollectionMixin

A ColumnCollection representing the set of columns for this constraint.

method sqlalchemy.schema.CheckConstraint.contains_column(col: Column[Any]) → bool

inherited from the ColumnCollectionConstraint.contains_column() method of ColumnCollectionConstraint

Return True if this constraint contains the given column.

Note that this object also contains an attribute .columns which is a ColumnCollection of Column objects.

method sqlalchemy.schema.CheckConstraint.copy(*, target_table: Table | None = None, **kw: Any) → CheckConstraint

自 1.4 版本弃用: The CheckConstraint.copy() method is deprecated and will be removed in a future release.

method sqlalchemy.schema.CheckConstraint.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

inherited from the HasConditionalDDL.ddl_if() method of HasConditionalDDL

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

attribute sqlalchemy.schema.CheckConstraint.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.CheckConstraint.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

attribute sqlalchemy.schema.CheckConstraint.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.CheckConstraint.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

class sqlalchemy.schema.ForeignKey

Defines a dependency between two columns.

ForeignKey is specified as an argument to a Column object, e.g.:

t = Table(
    "remote_table",
    metadata,
    Column("remote_id", ForeignKey("main_table.id")),
)

Note that ForeignKey is only a marker object that defines a dependency between two columns. The actual constraint is in all cases represented by the ForeignKeyConstraint object. This object will be generated automatically when a ForeignKey is associated with a Column which in turn is associated with a Table. Conversely, when ForeignKeyConstraint is applied to a Table, ForeignKey markers are automatically generated to be present on each associated Column, which are also associated with the constraint object.

Note that you cannot define a “composite” foreign key constraint, that is a constraint between a grouping of multiple parent/child columns, using ForeignKey objects. To define this grouping, the ForeignKeyConstraint object must be used, and applied to the Table. The associated ForeignKey objects are created automatically.

The ForeignKey objects associated with an individual Column object are available in the foreign_keys collection of that column.

Further examples of foreign key configuration are in 定义外键.

Members

__init__(), argument_for(), column, copy(), dialect_kwargs, dialect_options, get_referent(), info, kwargs, references(), target_fullname

Class signature

class sqlalchemy.schema.ForeignKey (sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.SchemaItem)

method sqlalchemy.schema.ForeignKey.__init__(column: _DDLColumnReferenceArgument, _constraint: ForeignKeyConstraint | None = None, use_alter: bool = False, name: _ConstraintNameArgument = None, onupdate: str | None = None, ondelete: str | None = None, deferrable: bool | None = None, initially: str | None = None, link_to_name: bool = False, match: str | None = None, info: _InfoType | None = None, comment: str | None = None, _unresolvable: bool = False, **dialect_kw: Any)

Construct a column-level FOREIGN KEY.

The ForeignKey object when constructed generates a ForeignKeyConstraint which is associated with the parent Table object’s collection of constraints.

参数:

• column – A single target column for the key relationship. A Column object or a column name as a string: tablename.columnkey or schema.tablename.columnkey. columnkey is the key which has been assigned to the column (defaults to the column name itself), unless link_to_name is True in which case the rendered name of the column is used.

• name – Optional string. An in-database name for the key if constraint is not provided.

• onupdate –

  Optional string. If set, emit ON UPDATE <value> when issuing DDL for this constraint. Typical values include CASCADE, DELETE and RESTRICT.

  参见

  ON UPDATE 和 ON DELETE

• ondelete –

  Optional string. If set, emit ON DELETE <value> when issuing DDL for this constraint. Typical values include CASCADE, SET NULL and RESTRICT. Some dialects may allow for additional syntaxes.

  参见

  ON UPDATE 和 ON DELETE

• deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.

• initially – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.

• link_to_name – if True, the string name given in column is the rendered name of the referenced column, not its locally assigned key.

• use_alter –

  passed to the underlying ForeignKeyConstraint to indicate the constraint should be generated/dropped externally from the CREATE TABLE/ DROP TABLE statement. See ForeignKeyConstraint.use_alter for further description.

  参见

  ForeignKeyConstraint.use_alter

  通过 ALTER 创建/删除外键约束

• match – Optional string. If set, emit MATCH <value> when issuing DDL for this constraint. Typical values include SIMPLE, PARTIAL and FULL.

• info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

• comment –

  Optional string that will render an SQL comment on foreign key constraint creation.

  在 2.0 版本加入.

• **dialect_kw – Additional keyword arguments are dialect specific, and passed in the form <dialectname>_<argname>. The arguments are ultimately handled by a corresponding ForeignKeyConstraint. See the documentation regarding an individual dialect at Dialects for detail on documented arguments.

classmethod sqlalchemy.schema.ForeignKey.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

attribute sqlalchemy.schema.ForeignKey.column

Return the target Column referenced by this ForeignKey.

If no target column has been established, an exception is raised.

method sqlalchemy.schema.ForeignKey.copy(*, schema: str | None = None, **kw: Any) → ForeignKey

自 1.4 版本弃用: The ForeignKey.copy() method is deprecated and will be removed in a future release.

attribute sqlalchemy.schema.ForeignKey.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.ForeignKey.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

method sqlalchemy.schema.ForeignKey.get_referent(table: FromClause) → Column[Any] | None

Return the Column in the given Table (or any FromClause) referenced by this ForeignKey.

Returns None if this ForeignKey does not reference the given Table.

attribute sqlalchemy.schema.ForeignKey.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.ForeignKey.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

method sqlalchemy.schema.ForeignKey.references(table: Table) → bool

Return True if the given Table is referenced by this ForeignKey.

attribute sqlalchemy.schema.ForeignKey.target_fullname

Return a string based ‘column specification’ for this ForeignKey.

This is usually the equivalent of the string-based “tablename.colname” argument first passed to the object’s constructor.

class sqlalchemy.schema.ForeignKeyConstraint

A table-level FOREIGN KEY constraint.

Defines a single column or composite FOREIGN KEY … REFERENCES constraint. For a no-frills, single column foreign key, adding a ForeignKey to the definition of a Column is a shorthand equivalent for an unnamed, single column ForeignKeyConstraint.

Examples of foreign key configuration are in 定义外键.

Members

__init__(), argument_for(), column_keys, columns, contains_column(), copy(), ddl_if(), dialect_kwargs, dialect_options, elements, info, kwargs, referred_table

Class signature

class sqlalchemy.schema.ForeignKeyConstraint (sqlalchemy.schema.ColumnCollectionConstraint)

method sqlalchemy.schema.ForeignKeyConstraint.__init__(columns: _typing_Sequence[_DDLColumnArgument], refcolumns: _typing_Sequence[_DDLColumnReferenceArgument], name: _ConstraintNameArgument = None, onupdate: str | None = None, ondelete: str | None = None, deferrable: bool | None = None, initially: str | None = None, use_alter: bool = False, link_to_name: bool = False, match: str | None = None, table: Table | None = None, info: _InfoType | None = None, comment: str | None = None, **dialect_kw: Any) → None

Construct a composite-capable FOREIGN KEY.

参数:

• columns – A sequence of local column names. The named columns must be defined and present in the parent Table. The names should match the key given to each column (defaults to the name) unless link_to_name is True.

• refcolumns – A sequence of foreign column names or Column objects. The columns must all be located within the same Table.

• name – Optional, the in-database name of the key.

• onupdate –

  Optional string. If set, emit ON UPDATE <value> when issuing DDL for this constraint. Typical values include CASCADE, DELETE and RESTRICT.

  参见

  ON UPDATE 和 ON DELETE

• ondelete –

  Optional string. If set, emit ON DELETE <value> when issuing DDL for this constraint. Typical values include CASCADE, SET NULL and RESTRICT. Some dialects may allow for additional syntaxes.

  参见

  ON UPDATE 和 ON DELETE

• deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.

• initially – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.

• link_to_name – if True, the string name given in column is the rendered name of the referenced column, not its locally assigned key.

• use_alter –

  If True, do not emit the DDL for this constraint as part of the CREATE TABLE definition. Instead, generate it via an ALTER TABLE statement issued after the full collection of tables have been created, and drop it via an ALTER TABLE statement before the full collection of tables are dropped.

  The use of ForeignKeyConstraint.use_alter is particularly geared towards the case where two or more tables are established within a mutually-dependent foreign key constraint relationship; however, the MetaData.create_all() and MetaData.drop_all() methods will perform this resolution automatically, so the flag is normally not needed.

  参见

  通过 ALTER 创建/删除外键约束

• match – Optional string. If set, emit MATCH <value> when issuing DDL for this constraint. Typical values include SIMPLE, PARTIAL and FULL.

• info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

• comment –

  Optional string that will render an SQL comment on foreign key constraint creation.

  在 2.0 版本加入.

• **dialect_kw – Additional keyword arguments are dialect specific, and passed in the form <dialectname>_<argname>. See the documentation regarding an individual dialect at Dialects for detail on documented arguments.

classmethod sqlalchemy.schema.ForeignKeyConstraint.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

attribute sqlalchemy.schema.ForeignKeyConstraint.column_keys

Return a list of string keys representing the local columns in this ForeignKeyConstraint.

This list is either the original string arguments sent to the constructor of the ForeignKeyConstraint, or if the constraint has been initialized with Column objects, is the string .key of each element.

attribute sqlalchemy.schema.ForeignKeyConstraint.columns: ReadOnlyColumnCollection[str, Column[Any]]

inherited from the ColumnCollectionMixin.columns attribute of ColumnCollectionMixin

A ColumnCollection representing the set of columns for this constraint.

method sqlalchemy.schema.ForeignKeyConstraint.contains_column(col: Column[Any]) → bool

inherited from the ColumnCollectionConstraint.contains_column() method of ColumnCollectionConstraint

Return True if this constraint contains the given column.

Note that this object also contains an attribute .columns which is a ColumnCollection of Column objects.

method sqlalchemy.schema.ForeignKeyConstraint.copy(*, schema: str | None = None, target_table: Table | None = None, **kw: Any) → ForeignKeyConstraint

自 1.4 版本弃用: The ForeignKeyConstraint.copy() method is deprecated and will be removed in a future release.

method sqlalchemy.schema.ForeignKeyConstraint.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

inherited from the HasConditionalDDL.ddl_if() method of HasConditionalDDL

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

attribute sqlalchemy.schema.ForeignKeyConstraint.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.ForeignKeyConstraint.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

attribute sqlalchemy.schema.ForeignKeyConstraint.elements: List[ForeignKey]

A sequence of ForeignKey objects.

Each ForeignKey represents a single referring column/referred column pair.

This collection is intended to be read-only.

attribute sqlalchemy.schema.ForeignKeyConstraint.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.ForeignKeyConstraint.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

attribute sqlalchemy.schema.ForeignKeyConstraint.referred_table

The Table object to which this ForeignKeyConstraint references.

This is a dynamically calculated attribute which may not be available if the constraint and/or parent table is not yet associated with a metadata collection that contains the referred table.

class sqlalchemy.schema.HasConditionalDDL

define a class that includes the HasConditionalDDL.ddl_if() method, allowing for conditional rendering of DDL.

Currently applies to constraints and indexes.

Members

ddl_if()

在 2.0 版本加入.

method sqlalchemy.schema.HasConditionalDDL.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

class sqlalchemy.schema.PrimaryKeyConstraint

A table-level PRIMARY KEY constraint.

The PrimaryKeyConstraint object is present automatically on any Table object; it is assigned a set of Column objects corresponding to those marked with the Column.primary_key flag:

>>> my_table = Table(
...     "mytable",
...     metadata,
...     Column("id", Integer, primary_key=True),
...     Column("version_id", Integer, primary_key=True),
...     Column("data", String(50)),
... )
>>> my_table.primary_key
PrimaryKeyConstraint(
    Column('id', Integer(), table=<mytable>,
           primary_key=True, nullable=False),
    Column('version_id', Integer(), table=<mytable>,
           primary_key=True, nullable=False)
)

The primary key of a Table can also be specified by using a PrimaryKeyConstraint object explicitly; in this mode of usage, the “name” of the constraint can also be specified, as well as other options which may be recognized by dialects:

my_table = Table(
    "mytable",
    metadata,
    Column("id", Integer),
    Column("version_id", Integer),
    Column("data", String(50)),
    PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
)

The two styles of column-specification should generally not be mixed. An warning is emitted if the columns present in the PrimaryKeyConstraint don’t match the columns that were marked as primary_key=True, if both are present; in this case, the columns are taken strictly from the PrimaryKeyConstraint declaration, and those columns otherwise marked as primary_key=True are ignored. This behavior is intended to be backwards compatible with previous behavior.

For the use case where specific options are to be specified on the PrimaryKeyConstraint, but the usual style of using primary_key=True flags is still desirable, an empty PrimaryKeyConstraint may be specified, which will take on the primary key column collection from the Table based on the flags:

my_table = Table(
    "mytable",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("version_id", Integer, primary_key=True),
    Column("data", String(50)),
    PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
)

Members

argument_for(), columns, contains_column(), copy(), ddl_if(), dialect_kwargs, dialect_options, info, kwargs

Class signature

class sqlalchemy.schema.PrimaryKeyConstraint (sqlalchemy.schema.ColumnCollectionConstraint)

classmethod sqlalchemy.schema.PrimaryKeyConstraint.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

attribute sqlalchemy.schema.PrimaryKeyConstraint.columns: ReadOnlyColumnCollection[str, Column[Any]]

inherited from the ColumnCollectionMixin.columns attribute of ColumnCollectionMixin

A ColumnCollection representing the set of columns for this constraint.

method sqlalchemy.schema.PrimaryKeyConstraint.contains_column(col: Column[Any]) → bool

inherited from the ColumnCollectionConstraint.contains_column() method of ColumnCollectionConstraint

Return True if this constraint contains the given column.

Note that this object also contains an attribute .columns which is a ColumnCollection of Column objects.

method sqlalchemy.schema.PrimaryKeyConstraint.copy(*, target_table: Table | None = None, **kw: Any) → ColumnCollectionConstraint

inherited from the ColumnCollectionConstraint.copy() method of ColumnCollectionConstraint

自 1.4 版本弃用: The ColumnCollectionConstraint.copy() method is deprecated and will be removed in a future release.

method sqlalchemy.schema.PrimaryKeyConstraint.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

inherited from the HasConditionalDDL.ddl_if() method of HasConditionalDDL

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

attribute sqlalchemy.schema.PrimaryKeyConstraint.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.PrimaryKeyConstraint.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

attribute sqlalchemy.schema.PrimaryKeyConstraint.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.PrimaryKeyConstraint.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

class sqlalchemy.schema.UniqueConstraint

A table-level UNIQUE constraint.

Defines a single column or composite UNIQUE constraint. For a no-frills, single column constraint, adding unique=True to the Column definition is a shorthand equivalent for an unnamed, single column UniqueConstraint.

Members

__init__(), argument_for(), columns, contains_column(), copy(), ddl_if(), dialect_kwargs, dialect_options, info, kwargs

Class signature

class sqlalchemy.schema.UniqueConstraint (sqlalchemy.schema.ColumnCollectionConstraint)

method sqlalchemy.schema.UniqueConstraint.__init__(*columns: _DDLColumnArgument, name: _ConstraintNameArgument = None, deferrable: bool | None = None, initially: str | None = None, info: _InfoType | None = None, _autoattach: bool = True, _column_flag: bool = False, _gather_expressions: List[_DDLColumnArgument] | None = None, **dialect_kw: Any) → None

inherited from the sqlalchemy.schema.ColumnCollectionConstraint.__init__ method of ColumnCollectionConstraint

参数:

• *columns – A sequence of column names or Column objects.

• name – Optional, the in-database name of this constraint.

• deferrable – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.

• initially – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.

• **dialect_kw – other keyword arguments including dialect-specific arguments are propagated to the Constraint superclass.

classmethod sqlalchemy.schema.UniqueConstraint.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

attribute sqlalchemy.schema.UniqueConstraint.columns: ReadOnlyColumnCollection[str, Column[Any]]

inherited from the ColumnCollectionMixin.columns attribute of ColumnCollectionMixin

A ColumnCollection representing the set of columns for this constraint.

method sqlalchemy.schema.UniqueConstraint.contains_column(col: Column[Any]) → bool

inherited from the ColumnCollectionConstraint.contains_column() method of ColumnCollectionConstraint

Return True if this constraint contains the given column.

Note that this object also contains an attribute .columns which is a ColumnCollection of Column objects.

method sqlalchemy.schema.UniqueConstraint.copy(*, target_table: Table | None = None, **kw: Any) → ColumnCollectionConstraint

inherited from the ColumnCollectionConstraint.copy() method of ColumnCollectionConstraint

自 1.4 版本弃用: The ColumnCollectionConstraint.copy() method is deprecated and will be removed in a future release.

method sqlalchemy.schema.UniqueConstraint.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

inherited from the HasConditionalDDL.ddl_if() method of HasConditionalDDL

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

attribute sqlalchemy.schema.UniqueConstraint.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.UniqueConstraint.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

attribute sqlalchemy.schema.UniqueConstraint.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.UniqueConstraint.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.

function sqlalchemy.schema.conv(value: str, quote: bool | None = None) → Any

Mark a string indicating that a name has already been converted by a naming convention.

This is a string subclass that indicates a name that should not be subject to any further naming conventions.

E.g. when we create a Constraint using a naming convention as follows:

m = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)
t = Table(
    "t", m, Column("x", Integer), CheckConstraint("x > 5", name="x5")
)

The name of the above constraint will be rendered as "ck_t_x5". That is, the existing name x5 is used in the naming convention as the constraint_name token.

In some situations, such as in migration scripts, we may be rendering the above CheckConstraint with a name that’s already been converted. In order to make sure the name isn’t double-modified, the new name is applied using the conv() marker. We can use this explicitly as follows:

m = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
)
t = Table(
    "t",
    m,
    Column("x", Integer),
    CheckConstraint("x > 5", name=conv("ck_t_x5")),
)

Where above, the conv() marker indicates that the constraint name here is final, and the name will render as "ck_t_x5" and not "ck_t_ck_t_x5"

参见

配置约束命名约定

索引

Indexes

中文

索引可以匿名创建（使用自动生成的名称 ix_<column label>）， 方法是在 Column 上使用内联的 index 关键字， 这也会修改 unique 的行为，使其将唯一性应用于索引本身，而不是单独添加一个 UNIQUE 约束。 对于具有指定名称或包含多个列的索引，请使用需要显式指定名称的 Index 构造。

下面演示了一个包含多个 Index 对象的 Table。 “CREATE INDEX” 的 DDL 会在表创建语句之后立即发出：

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # 一个带索引的列，索引名为 "ix_mytable_col1"
    Column("col1", Integer, index=True),
    # 一个唯一索引的列，索引名为 "ix_mytable_col2"
    Column("col2", Integer, index=True, unique=True),
    Column("col3", Integer),
    Column("col4", Integer),
    Column("col5", Integer),
    Column("col6", Integer),
)

# 在 col3 和 col4 上创建一个索引
Index("idx_col34", mytable.c.col3, mytable.c.col4)

# 在 col5 和 col6 上创建一个唯一索引
Index("myindex", mytable.c.col5, mytable.c.col6, unique=True)

mytable.create(engine)
CREATE TABLE mytable (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 INTEGER,
    col5 INTEGER,
    col6 INTEGER
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)
CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
CREATE INDEX idx_col34 ON mytable (col3, col4)

注意，上述示例中 Index 构造是在与其关联的表对象之外定义的， 直接使用了 Column 对象。 Index 也支持在 Table 内部进行“内联”定义， 使用字符串名称来标识列:

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    Column("col1", Integer),
    Column("col2", Integer),
    Column("col3", Integer),
    Column("col4", Integer),
    # 在 col1 和 col2 上创建索引
    Index("idx_col12", "col1", "col2"),
    # 在 col3 和 col4 上创建唯一索引
    Index("idx_col34", "col3", "col4", unique=True),
)

Index 对象也支持其自身的 create() 方法：

i = Index("someindex", mytable.c.col5)
i.create(engine)
CREATE INDEX someindex ON mytable (col5)

英文

Indexes can be created anonymously (using an auto-generated name ix_<column label>) for a single column using the inline index keyword on Column, which also modifies the usage of unique to apply the uniqueness to the index itself, instead of adding a separate UNIQUE constraint. For indexes with specific names or which encompass more than one column, use the Index construct, which requires a name.

Below we illustrate a Table with several Index objects associated. The DDL for “CREATE INDEX” is issued right after the create statements for the table:

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # an indexed column, with index "ix_mytable_col1"
    Column("col1", Integer, index=True),
    # a uniquely indexed column with index "ix_mytable_col2"
    Column("col2", Integer, index=True, unique=True),
    Column("col3", Integer),
    Column("col4", Integer),
    Column("col5", Integer),
    Column("col6", Integer),
)

# place an index on col3, col4
Index("idx_col34", mytable.c.col3, mytable.c.col4)

# place a unique index on col5, col6
Index("myindex", mytable.c.col5, mytable.c.col6, unique=True)

mytable.create(engine)
CREATE TABLE mytable (
    col1 INTEGER,
    col2 INTEGER,
    col3 INTEGER,
    col4 INTEGER,
    col5 INTEGER,
    col6 INTEGER
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)
CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
CREATE INDEX idx_col34 ON mytable (col3, col4)

Note in the example above, the Index construct is created externally to the table which it corresponds, using Column objects directly. Index also supports “inline” definition inside the Table, using string names to identify columns:

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    Column("col1", Integer),
    Column("col2", Integer),
    Column("col3", Integer),
    Column("col4", Integer),
    # place an index on col1, col2
    Index("idx_col12", "col1", "col2"),
    # place a unique index on col3, col4
    Index("idx_col34", "col3", "col4", unique=True),
)

The Index object also supports its own create() method:

i = Index("someindex", mytable.c.col5)
i.create(engine)
CREATE INDEX someindex ON mytable (col5)

功能索引

Functional Indexes

中文

Index 支持由目标后端支持的 SQL 和函数表达式。 若要对列的降序值创建索引，可使用 ColumnElement.desc() 修饰符:

from sqlalchemy import Index

Index("someindex", mytable.c.somecol.desc())

或者，在支持函数索引（如 PostgreSQL）的后端中， 可以使用 lower() 函数创建“大小写不敏感”索引:

from sqlalchemy import func, Index

Index("someindex", func.lower(mytable.c.somecol))

英文

Index supports SQL and function expressions, as supported by the target backend. To create an index against a column using a descending value, the ColumnElement.desc() modifier may be used:

from sqlalchemy import Index

Index("someindex", mytable.c.somecol.desc())

Or with a backend that supports functional indexes such as PostgreSQL, a “case insensitive” index can be created using the lower() function:

from sqlalchemy import func, Index

Index("someindex", func.lower(mytable.c.somecol))

索引 API

Index API

Object Name	Description
Index	A table-level INDEX.

class sqlalchemy.schema.Index

A table-level INDEX.

Defines a composite (one or more column) INDEX.

E.g.:

sometable = Table(
    "sometable",
    metadata,
    Column("name", String(50)),
    Column("address", String(100)),
)

Index("some_index", sometable.c.name)

For a no-frills, single column index, adding Column also supports index=True:

sometable = Table(
    "sometable", metadata, Column("name", String(50), index=True)
)

For a composite index, multiple columns can be specified:

Index("some_index", sometable.c.name, sometable.c.address)

Functional indexes are supported as well, typically by using the func construct in conjunction with table-bound Column objects:

Index("some_index", func.lower(sometable.c.name))

An Index can also be manually associated with a Table, either through inline declaration or using Table.append_constraint(). When this approach is used, the names of the indexed columns can be specified as strings:

Table(
    "sometable",
    metadata,
    Column("name", String(50)),
    Column("address", String(100)),
    Index("some_index", "name", "address"),
)

To support functional or expression-based indexes in this form, the text() construct may be used:

from sqlalchemy import text

Table(
    "sometable",
    metadata,
    Column("name", String(50)),
    Column("address", String(100)),
    Index("some_index", text("lower(name)")),
)

参见

索引 - General information on Index.

PostgreSQL 特定的索引选项 - PostgreSQL-specific options available for the Index construct.

MySQL / MariaDB 特定索引选项 - MySQL-specific options available for the Index construct.

聚集索引支持 - MSSQL-specific options available for the Index construct.

Members

__init__(), argument_for(), create(), ddl_if(), dialect_kwargs, dialect_options, drop(), info, kwargs

Class signature

class sqlalchemy.schema.Index (sqlalchemy.sql.base.DialectKWArgs, sqlalchemy.schema.ColumnCollectionMixin, sqlalchemy.schema.HasConditionalDDL, sqlalchemy.schema.SchemaItem)

method sqlalchemy.schema.Index.__init__(name: str | None, *expressions: _DDLColumnArgument, unique: bool = False, quote: bool | None = None, info: _InfoType | None = None, _table: Table | None = None, _column_flag: bool = False, **dialect_kw: Any) → None

Construct an index object.

参数:

• name – The name of the index

• *expressions – Column expressions to include in the index. The expressions are normally instances of Column, but may also be arbitrary SQL expressions which ultimately refer to a Column.

• unique=False – Keyword only argument; if True, create a unique index.

• quote=None – Keyword only argument; whether to apply quoting to the name of the index. Works in the same manner as that of Column.quote.

• info=None – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

• **dialect_kw – Additional keyword arguments not mentioned above are dialect specific, and passed in the form <dialectname>_<argname>. See the documentation regarding an individual dialect at Dialects for detail on documented arguments.

classmethod sqlalchemy.schema.Index.argument_for(dialect_name, argument_name, default)

inherited from the DialectKWArgs.argument_for() method of DialectKWArgs

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None)

some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

参数:

• dialect_name – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.

• argument_name – name of the parameter.

• default – default value of the parameter.

method sqlalchemy.schema.Index.create(bind: _CreateDropBind, checkfirst: bool = False) → None

Issue a CREATE statement for this Index, using the given Connection or Engine` for connectivity.

参见

MetaData.create_all().

method sqlalchemy.schema.Index.ddl_if(dialect: str | None = None, callable_: DDLIfCallable | None = None, state: Any | None = None) → Self

inherited from the HasConditionalDDL.ddl_if() method of HasConditionalDDL

apply a conditional DDL rule to this schema item.

These rules work in a similar manner to the ExecutableDDLElement.execute_if() callable, with the added feature that the criteria may be checked within the DDL compilation phase for a construct such as CreateTable. HasConditionalDDL.ddl_if() currently applies towards the Index construct as well as all Constraint constructs.

参数:

• dialect – string name of a dialect, or a tuple of string names to indicate multiple dialect types.

• callable_ – a callable that is constructed using the same form as that described in ExecutableDDLElement.execute_if.callable_.

• state – any arbitrary object that will be passed to the callable, if present.

在 2.0 版本加入.

参见

控制 DDL 约束和索引的生成 - background and usage examples

attribute sqlalchemy.schema.Index.dialect_kwargs

inherited from the DialectKWArgs.dialect_kwargs attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

参见

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.schema.Index.dialect_options

inherited from the DialectKWArgs.dialect_options attribute of DialectKWArgs

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

在 0.9.2 版本加入.

参见

DialectKWArgs.dialect_kwargs - flat dictionary form

method sqlalchemy.schema.Index.drop(bind: _CreateDropBind, checkfirst: bool = False) → None

Issue a DROP statement for this Index, using the given Connection or Engine for connectivity.

参见

MetaData.drop_all().

attribute sqlalchemy.schema.Index.info

inherited from the SchemaItem.info attribute of SchemaItem

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as Table and Column.

attribute sqlalchemy.schema.Index.kwargs

inherited from the DialectKWArgs.kwargs attribute of DialectKWArgs

A synonym for DialectKWArgs.dialect_kwargs.