列 INSERT/UPDATE 默认值¶

Column INSERT/UPDATE Defaults

中文

列的INSERT和UPDATE默认值指的是在对某行执行INSERT或UPDATE语句时，如果 没有为该列提供值 ，则为该行的特定列创建 默认值 的函数。也就是说，如果一个表有一个名为“timestamp”的列，并且一个INSERT语句没有包含该列的值，则INSERT默认值会创建一个新值，例如当前时间，该值将用作插入“timestamp”列的值。如果语句确实包含该列的值，那么默认值则不会生效。

列默认值可以是服务器端函数或常量值，这些值与模式一起在 DDL 中定义，或者作为SQL表达式直接在由SQLAlchemy发出的INSERT或UPDATE语句中呈现；它们也可以是客户端Python函数或常量值，这些值在数据传递到数据库之前由SQLAlchemy调用。

备注

列默认值处理程序不应与拦截和修改INSERT和UPDATE语句的传入值的构造混淆，这些值确实在语句调用时提供。这被称为 数据编组 (data marshalling) ，即应用程序在将列值发送到数据库之前以某种方式修改列值。SQLAlchemy提供了一些实现此目的的方法，包括使用自定义数据类型， SQL执行事件以及在ORM中使用自定义验证器和属性事件。列默认值仅在SQL DML 语句中 没有值存在 时调用。

SQLAlchemy提供了一系列关于默认值生成函数的功能，这些函数在INSERT和UPDATE语句期间针对不存在的值执行。选项包括：

在INSERT和UPDATE操作期间使用的标量值作为默认值
在INSERT和UPDATE操作期间执行的Python函数
嵌入在INSERT语句中的SQL表达式（或在某些情况下提前执行）
嵌入在UPDATE语句中的SQL表达式
在INSERT期间使用的服务器端默认值
在UPDATE期间使用的服务器端触发器标记

所有插入/更新默认值的一般规则是，如果没有为特定列传递值作为 execute() 参数，则默认值生效；否则，使用给定的值。

英文

Column INSERT and UPDATE defaults refer to functions that create a default value for a particular column in a row as an INSERT or UPDATE statement is proceeding against that row, in the case where no value was provided to the INSERT or UPDATE statement for that column. That is, if a table has a column called “timestamp”, and an INSERT statement proceeds which does not include a value for this column, an INSERT default would create a new value, such as the current time, that is used as the value to be INSERTed into the “timestamp” column. If the statement does include a value for this column, then the default does not take place.

Column defaults can be server-side functions or constant values which are defined in the database along with the schema in DDL, or as SQL expressions which are rendered directly within an INSERT or UPDATE statement emitted by SQLAlchemy; they may also be client-side Python functions or constant values which are invoked by SQLAlchemy before data is passed to the database.

备注

A column default handler should not be confused with a construct that intercepts and modifies incoming values for INSERT and UPDATE statements which are provided to the statement as it is invoked. This is known as data marshalling, where a column value is modified in some way by the application before being sent to the database. SQLAlchemy provides a few means of achieving this which include using custom datatypes, SQL execution events and in the ORM custom validators as well as attribute events. Column defaults are only invoked when there is no value present for a column in a SQL DML statement.

SQLAlchemy provides an array of features regarding default generation functions which take place for non-present values during INSERT and UPDATE statements. Options include:

Scalar values used as defaults during INSERT and UPDATE operations
Python functions which execute upon INSERT and UPDATE operations
SQL expressions which are embedded in INSERT statements (or in some cases execute beforehand)
SQL expressions which are embedded in UPDATE statements
Server side default values used during INSERT
Markers for server-side triggers used during UPDATE

The general rule for all insert/update defaults is that they only take effect if no value for a particular column is passed as an execute() parameter; otherwise, the given value is used.

标量默认值¶

Scalar Defaults

中文

最简单的默认值类型是用于列的默认值的标量值:

Table("mytable", metadata_obj, Column("somecolumn", Integer, default=12))

如上所示，值 “12” 将在插入时作为列的值绑定，如果没有提供其他值。

标量值也可以与 UPDATE 语句相关联，尽管这并不常见（因为 UPDATE 语句通常需要动态的默认值）:

Table("mytable", metadata_obj, Column("somecolumn", Integer, onupdate=25))

英文

The simplest kind of default is a scalar value used as the default value of a column:

Table("mytable", metadata_obj, Column("somecolumn", Integer, default=12))

Above, the value “12” will be bound as the column value during an INSERT if no other value is supplied.

A scalar value may also be associated with an UPDATE statement, though this is not very common (as UPDATE statements are usually looking for dynamic defaults):

Table("mytable", metadata_obj, Column("somecolumn", Integer, onupdate=25))

Python 执行函数¶

Python-Executed Functions

中文

Column.default 和 Column.onupdate 关键字参数也接受 Python 函数。当没有为该列提供其他值时，这些函数将在插入或更新时调用，并且返回的值将用作列的值。下面演示了一个粗略的“序列”，它将一个递增的计数器分配给主键列:

# 一个递增的函数
i = 0

def mydefault():
    global i
    i += 1
    return i

t = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer, primary_key=True, default=mydefault),
)

需要注意的是，对于真正的“递增序列”行为，通常应该使用数据库的内置功能，这些功能可能包括序列对象或其他自增能力。对于主键列，SQLAlchemy 通常会自动使用这些能力。有关详细信息，请参见 Column 的 API 文档，包括 Column.autoincrement 标志，以及本章后面关于 Sequence 部分的标准主键生成技术。

为了演示 onupdate，我们将 Python 的 datetime 函数 now 分配给 Column.onupdate 属性:

import datetime

t = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    # 定义 'last_updated' 为 datetime.now() 填充
    Column("last_updated", DateTime, onupdate=datetime.datetime.now),
)

英文

The Column.default and Column.onupdate keyword arguments also accept Python functions. These functions are invoked at the time of insert or update if no other value for that column is supplied, and the value returned is used for the column’s value. Below illustrates a crude “sequence” that assigns an incrementing counter to a primary key column:

# a function which counts upwards
i = 0


def mydefault():
    global i
    i += 1
    return i


t = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer, primary_key=True, default=mydefault),
)

It should be noted that for real “incrementing sequence” behavior, the built-in capabilities of the database should normally be used, which may include sequence objects or other autoincrementing capabilities. For primary key columns, SQLAlchemy will in most cases use these capabilities automatically. See the API documentation for Column including the Column.autoincrement flag, as well as the section on Sequence later in this chapter for background on standard primary key generation techniques.

To illustrate onupdate, we assign the Python datetime function now to the Column.onupdate attribute:

import datetime

t = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    # define 'last_updated' to be populated with datetime.now()
    Column("last_updated", DateTime, onupdate=datetime.datetime.now),
)

When an update statement executes and no value is passed for last_updated, the datetime.datetime.now() Python function is executed and its return value used as the value for last_updated. Notice that we provide now as the function itself without calling it (i.e. there are no parenthesis following) - SQLAlchemy will execute the function at the time the statement executes.

上下文敏感默认函数¶

Context-Sensitive Default Functions

中文

当执行更新语句且没有为 last_updated 提供值时， datetime.datetime.now() Python 函数将被执行，并且其返回值将作为 last_updated 的值。请注意，我们提供的是函数本身，而不是调用它（即后面没有括号）——SQLAlchemy 将在语句执行时调用该函数。

Column.default 和 Column.onupdate 使用的 Python 函数也可以利用当前语句的上下文来确定一个值。语句的 context 是一个内部 SQLAlchemy 对象，它包含关于正在执行的语句的所有信息，包括其源表达式、与其关联的参数以及游标。在默认值生成方面，典型的使用案例是访问行上正在插入或更新的其他值。要访问上下文，请提供一个接受单个 context 参数的函数:

def mydefault(context):
    return context.get_current_parameters()["counter"] + 12

t = Table(
    "mytable",
    metadata_obj,
    Column("counter", Integer),
    Column("counter_plus_twelve", Integer, default=mydefault, onupdate=mydefault),
)

上述默认值生成函数会在所有没有提供 counter_plus_twelve 值的 INSERT 和 UPDATE 语句中执行，并且该值将是执行中 counter 列的值加上数字 12。

对于使用“executemany”样式执行的单个语句，例如通过 Connection.execute() 传递多个参数集，用户定义的函数将为每个参数集调用一次。对于多值 Insert 构造（例如，通过 Insert.values() 方法设置多个 VALUES 子句），用户定义的函数也将为每个参数集调用一次。

当调用该函数时，可以从上下文对象（DefaultExecutionContext 的子类）中访问特殊方法 DefaultExecutionContext.get_current_parameters()。该方法返回一个列键到值的字典，表示 INSERT 或 UPDATE 语句的完整参数集。在多值 INSERT 构造的情况下，与单独的 VALUES 子句对应的参数子集将从完整的参数字典中隔离并单独返回。

英文

The Python functions used by Column.default and Column.onupdate may also make use of the current statement’s context in order to determine a value. The context of a statement is an internal SQLAlchemy object which contains all information about the statement being executed, including its source expression, the parameters associated with it and the cursor. The typical use case for this context with regards to default generation is to have access to the other values being inserted or updated on the row. To access the context, provide a function that accepts a single context argument:

def mydefault(context):
    return context.get_current_parameters()["counter"] + 12


t = Table(
    "mytable",
    metadata_obj,
    Column("counter", Integer),
    Column("counter_plus_twelve", Integer, default=mydefault, onupdate=mydefault),
)

The above default generation function is applied so that it will execute for all INSERT and UPDATE statements where a value for counter_plus_twelve was otherwise not provided, and the value will be that of whatever value is present in the execution for the counter column, plus the number 12.

For a single statement that is being executed using “executemany” style, e.g. with multiple parameter sets passed to Connection.execute(), the user-defined function is called once for each set of parameters. For the use case of a multi-valued Insert construct (e.g. with more than one VALUES clause set up via the Insert.values() method), the user-defined function is also called once for each set of parameters.

When the function is invoked, the special method DefaultExecutionContext.get_current_parameters() is available from the context object (an subclass of DefaultExecutionContext). This method returns a dictionary of column-key to values that represents the full set of values for the INSERT or UPDATE statement. In the case of a multi-valued INSERT construct, the subset of parameters that corresponds to the individual VALUES clause is isolated from the full parameter dictionary and returned alone.

客户端调用的 SQL 表达式¶

Client-Invoked SQL Expressions

中文

Column.default 和 Column.onupdate 关键字也可以传递 SQL 表达式，在大多数情况下，这些表达式会以内联方式呈现在 INSERT 或 UPDATE 语句中:

t = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    # 定义 'create_date' 默认值为 now()
    Column("create_date", DateTime, default=func.now()),
    # 定义 'key' 从 'keyvalues' 表中提取默认值
    Column(
        "key",
        String(20),
        default=select(keyvalues.c.key).where(keyvalues.c.type="type1"),
    ),
    # 定义 'last_modified' 在更新时使用 current_timestamp SQL 函数
    Column("last_modified", DateTime, onupdate=func.utc_timestamp()),
)

如上所示， create_date 列将在插入时使用 now() SQL 函数的结果填充（根据后台数据库，通常编译为 NOW() 或 CURRENT_TIMESTAMP）， key 列将使用来自另一个表的 SELECT 子查询的结果填充。last_modified 列将在该表执行 UPDATE 语句时使用 SQL UTC_TIMESTAMP() MySQL 函数的值填充。

备注

使用 SQL 函数时，使用 func 构造，我们“调用”命名的函数，例如像 func.now() 这样的括号。这不同于当我们指定一个 Python 可调用对象作为默认值（如 datetime.datetime）时，我们传递函数本身，但不调用它。在 SQL 函数的情况下，调用 func.now() 返回 SQL 表达式对象，它将把“NOW”函数呈现为 SQL 中的内容。

由 Column.default 和 Column.onupdate 指定的默认值和更新 SQL 表达式在 INSERT 或 UPDATE 语句执行时由 SQLAlchemy 显式调用，通常会以内联方式呈现在 DML 语句中，除了某些特殊情况，具体情况见下文。这与“服务器端”默认值不同，后者是表的 DDL 定义的一部分，例如作为 “CREATE TABLE” 语句的一部分，这些值可能更常见。有关服务器端默认值，请参见下一节服务器调用的 DDL 显式默认表达式。

当 Column.default 指定的 SQL 表达式用于主键列时，在某些情况下，SQLAlchemy 必须“预执行” 默认生成的 SQL 函数，这意味着它会在一个单独的 SELECT 语句中调用该函数，然后将生成的值作为参数传递给 INSERT。这仅发生在 INSERT 语句的主键列需要返回该主键值的情况下，其中 RETURNING 或 cursor.lastrowid 可能不能使用。指定 insert.inline 标志的 Insert 构造将始终以内联方式呈现默认表达式。

当语句使用单个参数集执行时（即它不是“executemany”风格的执行），返回的 CursorResult 将包含一个集合，可以通过 CursorResult.postfetch_cols() 访问，该集合包含所有使用内联执行默认值的 Column 对象。类似地，所有绑定到语句的参数，包括所有预执行的 Python 和 SQL 表达式，都包含在 CursorResult.last_inserted_params 或 CursorResult.last_updated_params 集合中。 CursorResult.inserted_primary_key 集合包含插入行的主键值列表（一个列表，使得单列和复合列主键在同一格式下表示）。

英文

The Column.default and Column.onupdate keywords may also be passed SQL expressions, which are in most cases rendered inline within the INSERT or UPDATE statement:

t = Table(
    "mytable",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    # define 'create_date' to default to now()
    Column("create_date", DateTime, default=func.now()),
    # define 'key' to pull its default from the 'keyvalues' table
    Column(
        "key",
        String(20),
        default=select(keyvalues.c.key).where(keyvalues.c.type="type1"),
    ),
    # define 'last_modified' to use the current_timestamp SQL function on update
    Column("last_modified", DateTime, onupdate=func.utc_timestamp()),
)

Above, the create_date column will be populated with the result of the now() SQL function (which, depending on backend, compiles into NOW() or CURRENT_TIMESTAMP in most cases) during an INSERT statement, and the key column with the result of a SELECT subquery from another table. The last_modified column will be populated with the value of the SQL UTC_TIMESTAMP() MySQL function when an UPDATE statement is emitted for this table.

备注

When using SQL functions with the func construct, we “call” the named function, e.g. with parenthesis as in func.now(). This differs from when we specify a Python callable as a default such as datetime.datetime, where we pass the function itself, but we don’t invoke it ourselves. In the case of a SQL function, invoking func.now() returns the SQL expression object that will render the “NOW” function into the SQL being emitted.

Default and update SQL expressions specified by Column.default and Column.onupdate are invoked explicitly by SQLAlchemy when an INSERT or UPDATE statement occurs, typically rendered inline within the DML statement except in certain cases listed below. This is different than a “server side” default, which is part of the table’s DDL definition, e.g. as part of the “CREATE TABLE” statement, which are likely more common. For server side defaults, see the next section 服务器调用的 DDL 显式默认表达式.

When a SQL expression indicated by Column.default is used with primary key columns, there are some cases where SQLAlchemy must “pre-execute” the default generation SQL function, meaning it is invoked in a separate SELECT statement, and the resulting value is passed as a parameter to the INSERT. This only occurs for primary key columns for an INSERT statement that is being asked to return this primary key value, where RETURNING or cursor.lastrowid may not be used. An Insert construct that specifies the insert.inline flag will always render default expressions inline.

When the statement is executed with a single set of parameters (that is, it is not an “executemany” style execution), the returned CursorResult will contain a collection accessible via CursorResult.postfetch_cols() which contains a list of all Column objects which had an inline-executed default. Similarly, all parameters which were bound to the statement, including all Python and SQL expressions which were pre-executed, are present in the CursorResult.last_inserted_params() or CursorResult.last_updated_params() collections on CursorResult. The CursorResult.inserted_primary_key collection contains a list of primary key values for the row inserted (a list so that single-column and composite-column primary keys are represented in the same format).

服务器调用的 DDL 显式默认表达式¶

Server-invoked DDL-Explicit Default Expressions

中文

SQL 表达式默认值的一种变体是 Column.server_default，它会在 Table.create() 操作期间放置在 CREATE TABLE 语句中：

t = Table(
    "test",
    metadata_obj,
    Column("abc", String(20), server_default="abc"),
    Column("created_at", DateTime, server_default=func.sysdate()),
    Column("index_value", Integer, server_default=text("0")),
)

上述表的创建调用将生成：

CREATE TABLE test (
    abc varchar(20) default 'abc',
    created_at datetime default sysdate,
    index_value integer default 0
)

上面的示例演示了 Column.server_default 的两种典型用法，即 SQL 函数（上述示例中的 SYSDATE）以及服务器端常量值（上述示例中的整数“0”）。建议使用 text() 构造来处理任何字面 SQL 值，而不是传递原始值，因为 SQLAlchemy 通常不会对这些值执行任何引用或转义。

像客户端生成的表达式一样，Column.server_default 通常可以容纳 SQL 表达式，但是预计这些表达式通常是简单的函数和表达式，而不是嵌入式 SELECT 之类的复杂情况。

英文

A variant on the SQL expression default is the Column.server_default, which gets placed in the CREATE TABLE statement during a Table.create() operation:

t = Table(
    "test",
    metadata_obj,
    Column("abc", String(20), server_default="abc"),
    Column("created_at", DateTime, server_default=func.sysdate()),
    Column("index_value", Integer, server_default=text("0")),
)

A create call for the above table will produce:

CREATE TABLE test (
    abc varchar(20) default 'abc',
    created_at datetime default sysdate,
    index_value integer default 0
)

The above example illustrates the two typical use cases for Column.server_default, that of the SQL function (SYSDATE in the above example) as well as a server-side constant value (the integer “0” in the above example). It is advisable to use the text() construct for any literal SQL values as opposed to passing the raw value, as SQLAlchemy does not typically perform any quoting or escaping on these values.

Like client-generated expressions, Column.server_default can accommodate SQL expressions in general, however it is expected that these will usually be simple functions and expressions, and not the more complex cases like an embedded SELECT.

标记隐式生成的值、时间戳和触发的列¶

Marking Implicitly Generated Values, timestamps, and Triggered Columns

中文

根据其他服务器端数据库机制生成新值的列，例如在某些平台上看到的 TIMESTAMP 列中的数据库特定自动生成行为，以及在 INSERT 或 UPDATE 时调用的自定义触发器生成新值，可以使用 FetchedValue 作为标记进行标识:

from sqlalchemy.schema import FetchedValue

t = Table(
    "test",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("abc", TIMESTAMP, server_default=FetchedValue()),
    Column("def", String(20), server_onupdate=FetchedValue()),
)

:class:.FetchedValue` 标记不会影响 CREATE TABLE 的渲染 DDL。它标记该列为将在 INSERT 或 UPDATE 语句过程中由数据库填充的新值，对于支持的数据库，它可用于指示该列应作为语句的 RETURNING 或 OUTPUT 子句的一部分。 SQLAlchemy ORM 等工具随后使用此标记，以便在此类操作之后知道如何获取该列的值。特别地，:meth:.ValuesBase.return_defaults` 方法可以与 Insert 或 Update 构造一起使用，指示这些值应返回。

有关在 ORM 中使用 FetchedValue 的详细信息，请参见获取服务器生成的默认值。

警告

Column.server_onupdate 指令当前不会生成 MySQL 的 “ON UPDATE CURRENT_TIMESTAMP()” 子句。请参见 MySQL/MariaDB 的 explicit_defaults_for_timestamp 在更新当前时间戳时渲染了解如何生成此子句的背景信息。

参见

获取服务器生成的默认值

英文

Columns which generate a new value on INSERT or UPDATE based on other server-side database mechanisms, such as database-specific auto-generating behaviors such as seen with TIMESTAMP columns on some platforms, as well as custom triggers that invoke upon INSERT or UPDATE to generate a new value, may be called out using FetchedValue as a marker:

from sqlalchemy.schema import FetchedValue

t = Table(
    "test",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("abc", TIMESTAMP, server_default=FetchedValue()),
    Column("def", String(20), server_onupdate=FetchedValue()),
)

The FetchedValue indicator does not affect the rendered DDL for the CREATE TABLE. Instead, it marks the column as one that will have a new value populated by the database during the process of an INSERT or UPDATE statement, and for supporting databases may be used to indicate that the column should be part of a RETURNING or OUTPUT clause for the statement. Tools such as the SQLAlchemy ORM then make use of this marker in order to know how to get at the value of the column after such an operation. In particular, the ValuesBase.return_defaults() method can be used with an Insert or Update construct to indicate that these values should be returned.

For details on using FetchedValue with the ORM, see 获取服务器生成的默认值.

警告

The Column.server_onupdate directive does not currently produce MySQL’s “ON UPDATE CURRENT_TIMESTAMP()” clause. See MySQL/MariaDB 的 explicit_defaults_for_timestamp 在更新当前时间戳时渲染 for background on how to produce this clause.

参见

获取服务器生成的默认值

计算列 (GENERATED ALWAYS AS)¶

Computed Columns (GENERATED ALWAYS AS)

中文

Computed 构造允许 Column 在 DDL 中声明为“GENERATED ALWAYS AS”列，即其值由数据库服务器计算得出。该构造接受一个 SQL 表达式，通常通过字符串或 text() 构造文本声明，类似于 CheckConstraint。然后，SQL 表达式由数据库服务器解释，以确定该列在一行中的值。

示例:

from sqlalchemy import Table, Column, MetaData, Integer, Computed

metadata_obj = MetaData()

square = Table(
    "square",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("side", Integer),
    Column("area", Integer, Computed("side * side")),
    Column("perimeter", Integer, Computed("4 * side")),
)

当在 PostgreSQL 12 后端运行时，square 表的 DDL 将如下所示：

CREATE TABLE square (
    id SERIAL NOT NULL,
    side INTEGER,
    area INTEGER GENERATED ALWAYS AS (side * side) STORED,
    perimeter INTEGER GENERATED ALWAYS AS (4 * side) STORED,
    PRIMARY KEY (id)
)

无论该值是在 INSERT 和 UPDATE 时持久化，还是在查询时计算，都是数据库的实现细节；前者称为“存储”，后者称为“虚拟”。一些数据库实现支持两者，但有些只支持其中之一。可以通过可选的 Computed.persisted 标志指定为 True 或 False，以指示是否应在 DDL 中渲染“STORED”或“VIRTUAL”关键字，但如果目标后端不支持该关键字，则会引发错误；如果未设置该标志，将使用目标后端的有效默认值。

Computed 构造是 FetchedValue 对象的子类，它将自身设置为目标 Column 的“服务器默认值”和“服务器更新时默认值”生成器，这意味着在生成 INSERT 和 UPDATE 语句时，它将被视为默认生成列，同时，在使用 ORM 时，它将作为生成列被提取。这包括它将成为数据库 RETURNING 子句的一部分，对于支持 RETURNING 且生成的值需要被立即提取的数据库。

备注

使用 Computed 构造定义的 Column 不能存储任何超出服务器应用于该列的值；当前，当向这样的列传递一个值以在 INSERT 或 UPDATE 中写入时，SQLAlchemy 的行为是该值将被忽略。

“GENERATED ALWAYS AS”当前已知被以下数据库支持：

MySQL 5.7 及更高版本
MariaDB 10.x 系列及更高版本
PostgreSQL 从版本 12 开始
Oracle 数据库 - 需要注意的是 RETURNING 在 UPDATE 中无法正确工作（如果包含计算列的 UPDATE..RETURNING 被渲染时，会发出警告）
Microsoft SQL Server
SQLite 从版本 3.31 开始

当 Computed 与不支持的后端一起使用时，如果目标方言不支持它，则在尝试渲染该构造时会引发 CompileError。否则，如果方言支持它，但使用的特定数据库服务器版本不支持它，则在发出 DDL 到数据库时会引发 DBAPIError 的子类，通常是 OperationalError。

参见

Computed

英文

The Computed construct allows a Column to be declared in DDL as a “GENERATED ALWAYS AS” column, that is, one which has a value that is computed by the database server. The construct accepts a SQL expression typically declared textually using a string or the text() construct, in a similar manner as that of CheckConstraint. The SQL expression is then interpreted by the database server in order to determine the value for the column within a row.

Example:

from sqlalchemy import Table, Column, MetaData, Integer, Computed

metadata_obj = MetaData()

square = Table(
    "square",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("side", Integer),
    Column("area", Integer, Computed("side * side")),
    Column("perimeter", Integer, Computed("4 * side")),
)

The DDL for the square table when run on a PostgreSQL 12 backend will look like:

CREATE TABLE square (
    id SERIAL NOT NULL,
    side INTEGER,
    area INTEGER GENERATED ALWAYS AS (side * side) STORED,
    perimeter INTEGER GENERATED ALWAYS AS (4 * side) STORED,
    PRIMARY KEY (id)
)

Whether the value is persisted upon INSERT and UPDATE, or if it is calculated on fetch, is an implementation detail of the database; the former is known as “stored” and the latter is known as “virtual”. Some database implementations support both, but some only support one or the other. The optional Computed.persisted flag may be specified as True or False to indicate if the “STORED” or “VIRTUAL” keyword should be rendered in DDL, however this will raise an error if the keyword is not supported by the target backend; leaving it unset will use a working default for the target backend.

The Computed construct is a subclass of the FetchedValue object, and will set itself up as both the “server default” and “server onupdate” generator for the target Column, meaning it will be treated as a default generating column when INSERT and UPDATE statements are generated, as well as that it will be fetched as a generating column when using the ORM. This includes that it will be part of the RETURNING clause of the database for databases which support RETURNING and the generated values are to be eagerly fetched.

备注

A Column that is defined with the Computed construct may not store any value outside of that which the server applies to it; SQLAlchemy’s behavior when a value is passed for such a column to be written in INSERT or UPDATE is currently that the value will be ignored.

“GENERATED ALWAYS AS” is currently known to be supported by:

MySQL version 5.7 and onwards
MariaDB 10.x series and onwards
PostgreSQL as of version 12
Oracle Database - with the caveat that RETURNING does not work correctly with UPDATE (a warning will be emitted to this effect when the UPDATE..RETURNING that includes a computed column is rendered)
Microsoft SQL Server
SQLite as of version 3.31

When Computed is used with an unsupported backend, if the target dialect does not support it, a CompileError is raised when attempting to render the construct. Otherwise, if the dialect supports it but the particular database server version in use does not, then a subclass of DBAPIError, usually OperationalError, is raised when the DDL is emitted to the database.

参见

Computed

标识列 (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)¶

Identity Columns (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

在 1.4 版本加入.

中文

Identity 构造允许 Column 声明为身份列，并在 DDL 中呈现为 “GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY”。身份列的值由数据库服务器自动生成，使用递增（或递减）序列。该构造与 Sequence 大多数选项共享，用于控制数据库行为。

示例:

from sqlalchemy import Table, Column, MetaData, Integer, Identity, String

metadata_obj = MetaData()

data = Table(
    "data",
    metadata_obj,
    Column("id", Integer, Identity(start=42, cycle=True), primary_key=True),
    Column("data", String),
)

在 PostgreSQL 12 后端运行时，data 表的 DDL 将如下所示：

CREATE TABLE data (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE) NOT NULL,
    data VARCHAR,
    PRIMARY KEY (id)
)

如果语句中没有为 id 列提供值，数据库将在插入时自动为 id 列生成一个值，从 42 开始。身份列还可以要求数据库生成该列的值，忽略语句中传递的值或根据后端抛出错误。要激活此模式，可以在 Identity 构造中将 Identity.always 参数设置为 True。更新前面的示例以包含此参数将生成以下 DDL：

CREATE TABLE data (
    id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 42 CYCLE) NOT NULL,
    data VARCHAR,
    PRIMARY KEY (id)
)

Identity 构造是 FetchedValue 对象的子类，并将其设置为目标 Column 的“服务器默认值”生成器，这意味着在生成 INSERT 语句时，它将作为默认生成列，并且在使用 ORM 时，它将作为生成列被提取。这包括它将成为数据库 RETURNING 子句的一部分，对于支持 RETURNING 且生成的值需要被立即提取的数据库。

Identity 构造当前已知被以下数据库支持：

PostgreSQL 从版本 10 开始。
Oracle 数据库从版本 12 开始。它还支持传递 always=None 启用默认生成模式，并且支持参数 on_null=True，用于指定与 “BY DEFAULT” 身份列一起使用的 “ON NULL”。
Microsoft SQL Server。 MSSQL 使用自定义语法，仅支持 start 和 increment 参数，忽略所有其他参数。

当 Identity 与不支持的后端一起使用时，它将被忽略，使用默认的 SQLAlchemy 自动递增列逻辑。

当 Column 同时指定 Identity 和将 Column.autoincrement 设置为 False 时，将引发错误。

参见

Identity

英文

The Identity construct allows a Column to be declared as an identity column and rendered in DDL as “GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY”. An identity column has its value automatically generated by the database server using an incrementing (or decrementing) sequence. The construct shares most of its option to control the database behaviour with Sequence.

Example:

from sqlalchemy import Table, Column, MetaData, Integer, Identity, String

metadata_obj = MetaData()

data = Table(
    "data",
    metadata_obj,
    Column("id", Integer, Identity(start=42, cycle=True), primary_key=True),
    Column("data", String),
)

The DDL for the data table when run on a PostgreSQL 12 backend will look like:

CREATE TABLE data (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE) NOT NULL,
    data VARCHAR,
    PRIMARY KEY (id)
)

The database will generate a value for the id column upon insert, starting from 42, if the statement did not already contain a value for the id column. An identity column can also require that the database generates the value of the column, ignoring the value passed with the statement or raising an error, depending on the backend. To activate this mode, set the parameter Identity.always to True in the Identity construct. Updating the previous example to include this parameter will generate the following DDL:

CREATE TABLE data (
    id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 42 CYCLE) NOT NULL,
    data VARCHAR,
    PRIMARY KEY (id)
)

The Identity construct is a subclass of the FetchedValue object, and will set itself up as the “server default” generator for the target Column, meaning it will be treated as a default generating column when INSERT statements are generated, as well as that it will be fetched as a generating column when using the ORM. This includes that it will be part of the RETURNING clause of the database for databases which support RETURNING and the generated values are to be eagerly fetched.

The Identity construct is currently known to be supported by:

PostgreSQL as of version 10.
Oracle Database as of version 12. It also supports passing always=None to enable the default generated mode and the parameter on_null=True to specify “ON NULL” in conjunction with a “BY DEFAULT” identity column.
Microsoft SQL Server. MSSQL uses a custom syntax that only supports the start and increment parameters, and ignores all other.

When Identity is used with an unsupported backend, it is ignored, and the default SQLAlchemy logic for autoincrementing columns is used.

An error is raised when a Column specifies both an Identity and also sets Column.autoincrement to False.

参见

Identity

默认对象 API¶

Default Objects API

Object Name	Description
ColumnDefault	A plain default value on a column.
Computed	Defines a generated column, i.e. “GENERATED ALWAYS AS” syntax.
DefaultClause	A DDL-specified DEFAULT column value.
DefaultGenerator	Base class for column default values.
FetchedValue	A marker for a transparent database-side default.
Identity	Defines an identity column, i.e. “GENERATED { ALWAYS \| BY DEFAULT } AS IDENTITY” syntax.
Sequence	Represents a named database sequence.

class sqlalchemy.schema.Computed¶

Defines a generated column, i.e. “GENERATED ALWAYS AS” syntax.

The Computed construct is an inline construct added to the argument list of a Column object:

from sqlalchemy import Computed

Table(
    "square",
    metadata_obj,
    Column("side", Float, nullable=False),
    Column("area", Float, Computed("side * side")),
)

See the linked documentation below for complete details.

参见

计算列 (GENERATED ALWAYS AS)

Members

__init__(), copy()

Class signature

class sqlalchemy.schema.Computed (sqlalchemy.schema.FetchedValue, sqlalchemy.schema.SchemaItem)

method sqlalchemy.schema.Computed.__init__(sqltext: _DDLColumnArgument, persisted: bool | None = None) → None¶

Construct a GENERATED ALWAYS AS DDL construct to accompany a Column.

参数:

sqltext¶ –
A string containing the column generation expression, which will be used verbatim, or a SQL expression construct, such as a text() object. If given as a string, the object is converted to a text() object.

警告

The Computed.sqltext argument to Computed 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.
persisted¶ –
Optional, controls how this column should be persisted by the database. Possible values are:
- None, the default, it will use the default persistence defined by the database.
- True, will render GENERATED ALWAYS AS ... STORED, or the equivalent for the target database if supported.
- False, will render GENERATED ALWAYS AS ... VIRTUAL, or the equivalent for the target database if supported.
Specifying True or False may raise an error when the DDL is emitted to the target database if the database does not support that persistence option. Leaving this parameter at its default of None is guaranteed to succeed for all databases that support GENERATED ALWAYS AS.

method sqlalchemy.schema.Computed.copy(*, target_table: Table | None = None, **kw: Any) → Computed¶: 自 1.4 版本弃用: The Computed.copy() method is deprecated and will be removed in a future release.

class sqlalchemy.schema.ColumnDefault¶

A plain default value on a column.

This could correspond to a constant, a callable function, or a SQL clause.

ColumnDefault is generated automatically whenever the default, onupdate arguments of Column are used. A ColumnDefault can be passed positionally as well.

For example, the following:

Column("foo", Integer, default=50)

Is equivalent to:

Column("foo", Integer, ColumnDefault(50))

Class signature

class sqlalchemy.schema.ColumnDefault (sqlalchemy.schema.DefaultGenerator, abc.ABC)

class sqlalchemy.schema.DefaultClause¶

A DDL-specified DEFAULT column value.

DefaultClause is a FetchedValue that also generates a “DEFAULT” clause when “CREATE TABLE” is emitted.

DefaultClause is generated automatically whenever the server_default, server_onupdate arguments of Column are used. A DefaultClause can be passed positionally as well.

For example, the following:

Column("foo", Integer, server_default="50")

Is equivalent to:

Column("foo", Integer, DefaultClause("50"))

Class signature

class sqlalchemy.schema.DefaultClause (sqlalchemy.schema.FetchedValue)

class sqlalchemy.schema.DefaultGenerator¶

Base class for column default values.

This object is only present on column.default or column.onupdate. It’s not valid as a server default.

Class signature

class sqlalchemy.schema.DefaultGenerator (sqlalchemy.sql.expression.Executable, sqlalchemy.schema.SchemaItem)

class sqlalchemy.schema.FetchedValue¶

A marker for a transparent database-side default.

Use FetchedValue when the database is configured to provide some automatic default for a column.

E.g.:

Column("foo", Integer, FetchedValue())

Would indicate that some trigger or default generator will create a new value for the foo column during an INSERT.

参见

标记隐式生成的值、时间戳和触发的列

Class signature

class sqlalchemy.schema.FetchedValue (sqlalchemy.sql.expression.SchemaEventTarget)

class sqlalchemy.schema.Sequence¶

Represents a named database sequence.

The Sequence object represents the name and configurational parameters of a database sequence. It also represents a construct that can be “executed” by a SQLAlchemy Engine or Connection, rendering the appropriate “next value” function for the target database and returning a result.

The Sequence is typically associated with a primary key column:

some_table = Table(
    "some_table",
    metadata,
    Column(
        "id",
        Integer,
        Sequence("some_table_seq", start=1),
        primary_key=True,
    ),
)

When CREATE TABLE is emitted for the above Table, if the target platform supports sequences, a CREATE SEQUENCE statement will be emitted as well. For platforms that don’t support sequences, the Sequence construct is ignored.

参见

定义序列

CreateSequence

DropSequence

Members

__init__(), create(), drop(), next_value()

Class signature

class sqlalchemy.schema.Sequence (sqlalchemy.schema.HasSchemaAttr, sqlalchemy.schema.IdentityOptions, sqlalchemy.schema.DefaultGenerator)

Construct a Sequence object.

参数:

name¶ – the name of the sequence.
start¶ –
the starting index of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “START WITH” clause. If None, the clause is omitted, which on most platforms indicates a starting value of 1.

在 2.0 版本发生变更: The Sequence.start parameter is required in order to have DDL emit “START WITH”. This is a reversal of a change made in version 1.4 which would implicitly render “START WITH 1” if the Sequence.start were not included. See The Sequence construct reverts to not having any explicit default “start” value; impacts MS SQL Server for more detail.
increment¶ – the increment value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “INCREMENT BY” clause. If None, the clause is omitted, which on most platforms indicates an increment of 1.
minvalue¶ – the minimum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “MINVALUE” clause. If None, the clause is omitted, which on most platforms indicates a minvalue of 1 and -2^63-1 for ascending and descending sequences, respectively.
maxvalue¶ – the maximum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “MAXVALUE” clause. If None, the clause is omitted, which on most platforms indicates a maxvalue of 2^63-1 and -1 for ascending and descending sequences, respectively.
nominvalue¶ – no minimum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “NO MINVALUE” clause. If None, the clause is omitted, which on most platforms indicates a minvalue of 1 and -2^63-1 for ascending and descending sequences, respectively.
nomaxvalue¶ – no maximum value of the sequence. This value is used when the CREATE SEQUENCE command is emitted to the database as the value of the “NO MAXVALUE” clause. If None, the clause is omitted, which on most platforms indicates a maxvalue of 2^63-1 and -1 for ascending and descending sequences, respectively.
cycle¶ – allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. This value is used when the CREATE SEQUENCE command is emitted to the database as the “CYCLE” clause. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. If cycle=False (the default) any calls to nextval after the sequence has reached its maximum value will return an error.
schema¶ – optional schema name for the sequence, if located in a schema other than the default. The rules for selecting the schema name when a MetaData is also present are the same as that of Table.schema.
cache¶ – optional integer value; number of future values in the sequence which are calculated in advance. Renders the CACHE keyword understood by Oracle Database and PostgreSQL.
order¶ –
optional boolean value; if True, renders the ORDER keyword, understood by Oracle Database, indicating the sequence is definitively ordered. May be necessary to provide deterministic ordering using Oracle RAC.

自 2.1 版本弃用: This parameter is supported only by Oracle Database, use oracle_order instead.
data_type¶ –
The type to be returned by the sequence, for dialects that allow us to choose between INTEGER, BIGINT, etc. (e.g., mssql).

在 1.4.0 版本加入.
optional¶ – boolean value, when True, indicates that this Sequence object only needs to be explicitly generated on backends that don’t provide another way to generate primary key identifiers. Currently, it essentially means, “don’t create this sequence on the PostgreSQL backend, where the SERIAL keyword creates a sequence for us automatically”.
quote¶ – boolean value, when True or False, explicitly forces quoting of the Sequence.name on or off. When left at its default of None, normal quoting rules based on casing and reserved words take place.
quote_schema¶ – Set the quoting preferences for the schema name.
metadata¶ –
optional MetaData object which this Sequence will be associated with. A Sequence that is associated with a MetaData gains the following capabilities:
- The Sequence will inherit the MetaData.schema parameter specified to the target MetaData, which affects the production of CREATE / DROP DDL, if any.
- The Sequence.create() and Sequence.drop() methods automatically use the engine bound to the MetaData object, if any.
- The MetaData.create_all() and MetaData.drop_all() methods will emit CREATE / DROP for this Sequence, even if the Sequence is not associated with any Table / Column that’s a member of this MetaData.
The above behaviors can only occur if the Sequence is explicitly associated with the MetaData via this parameter.

参见

将序列与元数据关联 - full discussion of the Sequence.metadata parameter.
for_update¶ – Indicates this Sequence, when associated with a Column, should be invoked for UPDATE statements on that column’s table, rather than for INSERT statements, when no value is otherwise present for that column in the statement.

method sqlalchemy.schema.Sequence.create(bind: _CreateDropBind, checkfirst: bool = True) → None¶: Creates this sequence in the database.

method sqlalchemy.schema.Sequence.drop(bind: _CreateDropBind, checkfirst: bool = True) → None¶: Drops this sequence from the database.

method sqlalchemy.schema.Sequence.next_value() → Function[int]¶: Return a next_value function element which will render the appropriate increment function for this Sequence within any SQL expression.

class sqlalchemy.schema.Identity¶

Defines an identity column, i.e. “GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY” syntax.

The Identity construct is an inline construct added to the argument list of a Column object:

from sqlalchemy import Identity

Table(
    "foo",
    metadata_obj,
    Column("id", Integer, Identity()),
    Column("description", Text),
)

See the linked documentation below for complete details.

在 1.4 版本加入.

参见

标识列 (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

Members

__init__(), copy(), on_null

Class signature

class sqlalchemy.schema.Identity (sqlalchemy.schema.IdentityOptions, sqlalchemy.schema.FetchedValue, sqlalchemy.schema.SchemaItem)

Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL construct to accompany a Column.

See the Sequence documentation for a complete description of most parameters.

备注

MSSQL supports this construct as the preferred alternative to generate an IDENTITY on a column, but it uses non standard syntax that only support Identity.start and Identity.increment. All other parameters are ignored.

参数:

always¶ – A boolean, that indicates the type of identity column. If False is specified, the default, then the user-specified value takes precedence. If True is specified, a user-specified value is not accepted ( on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or similar, may be specified in an INSERT to override the sequence value). Some backends also have a default value for this parameter, None can be used to omit rendering this part in the DDL. It will be treated as False if a backend does not have a default value.
on_null¶ –
Set to True to specify ON NULL in conjunction with a always=False identity column. This option is only supported on some backends, like Oracle Database.

自 2.1 版本弃用: This parameter is supported only by Oracle Database, use oracle_on_null instead.
start¶ – the starting index of the sequence.
increment¶ – the increment value of the sequence.
minvalue¶ – the minimum value of the sequence.
maxvalue¶ – the maximum value of the sequence.
nominvalue¶ – no minimum value of the sequence.
nomaxvalue¶ – no maximum value of the sequence.
cycle¶ – allows the sequence to wrap around when the maxvalue or minvalue has been reached.
cache¶ – optional integer value; number of future values in the sequence which are calculated in advance.
order¶ –
optional boolean value; if true, renders the ORDER keyword.

自 2.1 版本弃用: This parameter is supported only by Oracle Database, use oracle_order instead.

method sqlalchemy.schema.Identity.copy(**kw: Any) → Identity¶: 自 1.4 版本弃用: The Identity.copy() method is deprecated and will be removed in a future release.

attribute sqlalchemy.schema.Identity.on_null¶: Alias of the dialect_kwargs 'oracle_on_null'.

自 2.1 版本弃用: The ‘on_null’ attribute is deprecated.

列 INSERT/UPDATE 默认值¶

标量默认值¶

Python 执行函数¶

上下文敏感默认函数¶

客户端调用的 SQL 表达式¶

服务器调用的 DDL 显式默认表达式¶

标记隐式生成的值、时间戳和触发的列¶

定义序列¶

将序列与 SERIAL 列关联¶

独立执行序列¶

将序列与元数据关联¶

将序列关联为服务器端默认值¶

计算列 (GENERATED ALWAYS AS)¶

标识列 (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)¶

默认对象 API¶