Колонки INSERT/UPDATE по умолчанию¶

Скалярные значения по умолчанию¶

Самый простой вид значения по умолчанию - это скалярное значение, используемое в качестве значения по умолчанию для столбца:

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

Выше, значение «12» будет привязано в качестве значения столбца при INSERT, если не указано другое значение.

Скалярное значение также может быть связано с оператором UPDATE, хотя это не очень распространено (поскольку операторы UPDATE обычно ищут динамические значения по умолчанию):

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

Функции, исполняемые в Python¶

Аргументы ключевых слов Column.default и Column.onupdate также принимают функции Python. Эти функции вызываются во время вставки или обновления, если для столбца не задано другое значение, и возвращаемое значение используется для значения столбца. Ниже показана грубая «последовательность», которая присваивает увеличивающийся счетчик столбцу первичного ключа:

# 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),
)

Следует отметить, что для реального поведения «увеличивающейся последовательности» обычно следует использовать встроенные возможности базы данных, которые могут включать объекты последовательности или другие возможности автоинкрементации. Для столбцов с первичным ключом SQLAlchemy в большинстве случаев будет использовать эти возможности автоматически. Смотрите документацию API для Column, включая флаг Column.autoincrement, а также раздел Sequence далее в этой главе для получения информации о стандартных методах генерации первичных ключей.

Чтобы проиллюстрировать onupdate, мы присваиваем функцию Python datetime now атрибуту Column.onupdate:

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),
)

Когда выполняется оператор обновления и не передается значение для last_updated, выполняется Python-функция datetime.datetime.now(), а ее возвращаемое значение используется в качестве значения для last_updated. Обратите внимание, что мы предоставляем now как саму функцию, не вызывая ее (т.е. после нее нет скобок) - SQLAlchemy выполнит функцию в момент выполнения оператора.

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),
)

Выражения SQL, вызываемые клиентом¶

Ключевые слова Column.default и Column.onupdate также могут передавать SQL-выражения, которые в большинстве случаев отображаются в строку внутри оператора INSERT или UPDATE:

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()),
)

Выше, столбец create_date будет заполнен результатом SQL-функции now() (которая, в зависимости от бэкенда, компилируется в NOW() или CURRENT_TIMESTAMP в большинстве случаев) при выполнении оператора INSERT, а столбец key - результатом подзапроса SELECT из другой таблицы. Столбец last_modified будет заполнен значением функции SQL UTC_TIMESTAMP() MySQL при выполнении оператора UPDATE для этой таблицы.

SQL-выражения по умолчанию и обновления, указанные в Column.default и Column.onupdate, вызываются SQLAlchemy явно при выполнении оператора INSERT или UPDATE, обычно отображаются в строке оператора DML, за исключением некоторых случаев, перечисленных ниже. Это отличается от умолчаний «на стороне сервера», которые являются частью определения DDL таблицы, например, как часть оператора «CREATE TABLE», которые, вероятно, более распространены. Об умолчаниях на стороне сервера см. следующий раздел Выражения по умолчанию DDL-Explicit, вызываемые сервером.

Когда SQL-выражение, обозначенное Column.default, используется с колонками первичного ключа, есть некоторые случаи, когда SQLAlchemy должна «предварительно выполнить» SQL-функцию генерации по умолчанию, то есть она вызывается в отдельном операторе SELECT, а полученное значение передается в качестве параметра в INSERT. Это происходит только для столбцов с первичным ключом для оператора INSERT, которому предлагается вернуть значение этого первичного ключа, где RETURNING или cursor.lastrowid не могут быть использованы. Конструкция Insert, в которой указан флаг insert.inline, всегда выводит выражения по умолчанию в строку.

Когда утверждение выполняется с одним набором параметров (то есть, это не выполнение в стиле «executemany»), возвращаемый CursorResult будет содержать коллекцию, доступную через CursorResult.postfetch_cols(), которая содержит список всех объектов Column, которые имели inline-выполнение по умолчанию. Аналогично, все параметры, которые были связаны с оператором, включая все выражения Python и SQL, которые были предварительно выполнены, присутствуют в коллекциях CursorResult.last_inserted_params() или CursorResult.last_updated_params() на CursorResult. Коллекция CursorResult.inserted_primary_key содержит список значений первичного ключа для вставленной строки (список, чтобы первичные ключи с одним столбцом и составным столбцом были представлены в одинаковом формате).

Выражения по умолчанию DDL-Explicit, вызываемые сервером¶

Вариантом выражения SQL по умолчанию является Column.server_default, которое помещается в оператор CREATE TABLE во время операции Table.create():

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 для приведенной выше таблицы приведет к следующему результату:

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.

Маркировка неявно генерируемых значений, временных меток и триггерных столбцов¶

Колонки, которые генерируют новое значение при INSERT или UPDATE на основе других механизмов базы данных на стороне сервера, таких как специфическое для базы данных поведение автоматической генерации, например, как в колонках 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()),
)

Индикатор FetchedValue не влияет на отображаемый DDL для CREATE TABLE. Вместо этого он помечает столбец как тот, который будет иметь новое значение, заполненное базой данных в процессе выполнения оператора INSERT или UPDATE, а для поддерживающих баз данных может использоваться для указания того, что столбец должен быть частью предложения RETURNING или OUTPUT для оператора. Такие инструменты, как SQLAlchemy ORM, используют этот маркер для того, чтобы знать, как получить значение столбца после такой операции. В частности, метод ValuesBase.return_defaults() можно использовать с конструкцией Insert или Update, чтобы указать, что эти значения должны быть возвращены.

Подробнее об использовании FetchedValue с ORM см. в разделе Получение генерируемых сервером значений по умолчанию.

Определение последовательностей¶

SQLAlchemy представляет последовательности баз данных с помощью объекта Sequence, который считается частным случаем «столбца по умолчанию». Это имеет значение только для баз данных, которые имеют явную поддержку последовательностей, что среди диалектов SQLAlchemy включает PostgreSQL, Oracle, MS SQL Server и MariaDB. В остальных случаях объект Sequence игнорируется.

Sequence может быть помещен в любой столбец в качестве генератора «по умолчанию», который будет использоваться во время операций INSERT, а также может быть настроен на срабатывание во время операций UPDATE, если это необходимо. Чаще всего он используется в сочетании с одним целочисленным столбцом первичного ключа:

table = Table(
    "cartitems",
    metadata_obj,
    Column(
        "cart_id",
        Integer,
        Sequence("cart_id_seq", start=1),
        primary_key=True,
    ),
    Column("description", String(40)),
    Column("createdate", DateTime()),
)

Где выше, таблица cartitems связана с последовательностью с именем cart_id_seq. Излучение MetaData.create_all() для вышеуказанной таблицы будет включать:

CREATE SEQUENCE cart_id_seq START WITH 1

CREATE TABLE cartitems (
  cart_id INTEGER NOT NULL,
  description VARCHAR(40),
  createdate TIMESTAMP WITHOUT TIME ZONE,
  PRIMARY KEY (cart_id)
)

Sequence("cart_id_seq", start=1, schema="some_schema")

Когда конструкции DML Insert вызываются против таблицы cartitems, без явного значения, переданного для столбца cart_id, последовательность cart_id_seq будет использоваться для генерации значения на участвующих бэкендах. Обычно функция последовательности встраивается в оператор INSERT, который комбинируется с RETURNING, чтобы сгенерированное значение можно было вернуть процессу Python:

INSERT INTO cartitems (cart_id, description, createdate)
VALUES (next_val(cart_id_seq), 'some description', '2015-10-15 12:00:15')
RETURNING cart_id

При использовании Connection.execute() для вызова конструкции Insert, вновь созданные идентификаторы первичных ключей, включая, но не ограничиваясь ими, созданные с помощью Sequence, доступны из конструкции CursorResult с помощью атрибута CursorResult.inserted_primary_key.

Когда Sequence связан с Column как его Python-side генератор по умолчанию, Sequence будет также подвержен DDL «CREATE SEQUENCE» и «DROP SEQUENCE», когда подобный DDL будет создан для принадлежащего ему Table, например, при использовании MetaData.create_all() для генерации DDL для серии таблиц.

Параметр Sequence также может быть связан с конструкцией MetaData напрямую. Это позволяет использовать Sequence более чем в одном Table одновременно, а также наследовать параметр MetaData.schema. Подробнее см. в разделе Ассоциирование последовательности с метаданными.

table = Table(
    "cartitems",
    metadata_obj,
    Column(
        "cart_id",
        Integer,
        # use an explicit Sequence where available, but not on
        # PostgreSQL where SERIAL will be used
        Sequence("cart_id_seq", start=1, optional=True),
        primary_key=True,
    ),
    Column("description", String(40)),
    Column("createdate", DateTime()),
)

with my_engine.connect() as conn:
    seq = Sequence("some_sequence")
    nextid = conn.execute(seq)

>>> my_seq = Sequence("some_sequence")
>>> stmt = select(my_seq.next_value())
>>> print(stmt.compile(dialect=postgresql.dialect()))
SELECT nextval('some_sequence') AS next_value_1

seq = Sequence("my_general_seq", metadata=metadata_obj, start=1)

table = Table(
    "cartitems",
    metadata_obj,
    seq,
    Column("description", String(40)),
    Column("createdate", DateTime()),
)

Column(
    "cart_id", Integer, Sequence("cart_id_seq", metadata=metadata_obj), primary_key=True
)

cart_id_seq = Sequence("cart_id_seq", metadata=metadata_obj)
table = Table(
    "cartitems",
    metadata_obj,
    Column(
        "cart_id",
        Integer,
        cart_id_seq,
        server_default=cart_id_seq.next_value(),
        primary_key=True,
    ),
    Column("description", String(40)),
    Column("createdate", DateTime()),
)

class CartItem(Base):
    __tablename__ = "cartitems"

    cart_id_seq = Sequence("cart_id_seq", metadata=Base.metadata)
    cart_id = Column(
        Integer, cart_id_seq, server_default=cart_id_seq.next_value(), primary_key=True
    )
    description = Column(String(40))
    createdate = Column(DateTime)

CREATE TABLE cartitems (
    cart_id INTEGER DEFAULT nextval('cart_id_seq') NOT NULL,
    description VARCHAR(40),
    createdate TIMESTAMP WITHOUT TIME ZONE,
    PRIMARY KEY (cart_id)
)

Вычисляемые столбцы (генерируются ВСЕГДА КАК)¶

Конструкция 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")),
)

DDL для таблицы square при выполнении на бэкенде PostgreSQL 12 будет выглядеть так:

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, чтобы указать, должно ли ключевое слово «STORED» или «VIRTUAL» отображаться в DDL, однако это вызовет ошибку, если ключевое слово не поддерживается целевым бэкендом; если флаг не установлен, будет использоваться рабочее значение по умолчанию для целевого бэкенда.

Конструкция Computed является подклассом объекта FetchedValue и устанавливает себя в качестве генератора «server default» и «server onupdate» для целевого Column, что означает, что он будет рассматриваться как генерирующий столбец по умолчанию при генерации операторов INSERT и UPDATE, а также что он будет извлекаться как генерирующий столбец при использовании ORM. Это включает в себя то, что он будет частью условия RETURNING в базе данных для баз данных, которые поддерживают RETURNING, и сгенерированные значения будут извлекаться с нетерпением.

«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.

Колонки идентичности (генерируются { ВСЕГДА | ПО ОПРЕДЕЛЕНИЮ } КАК ИДЕНТИЧНОСТЬ)¶

Конструкция 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),
)

DDL для таблицы data при выполнении на бэкенде PostgreSQL 12 будет выглядеть так:

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

База данных сгенерирует значение для столбца id при вставке, начиная с 42, если утверждение еще не содержало значения для столбца id. Столбец идентичности может также требовать, чтобы база данных генерировала значение столбца, игнорируя значение, переданное с утверждением, или выдавая ошибку, в зависимости от бэкенда. Чтобы активировать этот режим, установите параметр Identity.always в True в конструкции Identity. Обновление предыдущего примера для включения этого параметра приведет к созданию следующего 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 для указания «ON NULL» в сочетании со столбцом идентификации «BY DEFAULT».

• Microsoft SQL Server. MSSQL использует собственный синтаксис, который поддерживает только параметры start и increment, а все остальные игнорирует.

Когда Identity используется с неподдерживаемым бэкендом, он игнорируется, и используется стандартная логика SQLAlchemy для автоинкрементных колонок.

Возникает ошибка, если Column указывает одновременно Identity, а также устанавливает Column.autoincrement в False.

API объектов по умолчанию¶