Обновление и удаление строк с помощью Core¶

Конструкция SQL-выражения update()¶

Функция update() генерирует новый экземпляр Update, который представляет собой оператор UPDATE в SQL, обновляющий существующие данные в таблице.

Как и конструкция insert(), существует «традиционная» форма update(), которая выполняет UPDATE для одной таблицы за раз и не возвращает никаких строк. Однако некоторые бэкенды поддерживают оператор UPDATE, который может изменять несколько таблиц одновременно, и оператор UPDATE также поддерживает RETURNING, так что столбцы, содержащиеся в сопоставленных строках, могут быть возвращены в наборе результатов.

Базовый UPDATE выглядит следующим образом:

>>> from sqlalchemy import update
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == "patrick")
...     .values(fullname="Patrick the Star")
... )
>>> print(stmt)
UPDATE user_account SET fullname=:fullname WHERE user_account.name = :name_1

Метод Update.values() управляет содержимым элементов SET оператора UPDATE. Это тот же метод, который используется конструкцией Insert. Параметры обычно передаются с использованием имен столбцов в качестве аргументов ключевых слов.

UPDATE поддерживает все основные SQL-формы UPDATE, включая обновления по выражениям, где мы можем использовать выражения Column:

>>> stmt = update(user_table).values(fullname="Username: " + user_table.c.name)
>>> print(stmt)
UPDATE user_account SET fullname=(:name_1 || user_account.name)

Для поддержки UPDATE в контексте «executemany», где много наборов параметров будут вызваны против одного и того же оператора, конструкция bindparam() может быть использована для установки связанных параметров; они заменяют места, куда обычно помещаются буквальные значения:

>>> from sqlalchemy import bindparam
>>> stmt = (
...     update(user_table)
...     .where(user_table.c.name == bindparam("oldname"))
...     .values(name=bindparam("newname"))
... )
>>> with engine.begin() as conn:
...     conn.execute(
...         stmt,
...         [
...             {"oldname": "jack", "newname": "ed"},
...             {"oldname": "wendy", "newname": "mary"},
...             {"oldname": "jim", "newname": "jake"},
...         ],
...     )
BEGIN (implicit)
UPDATE user_account SET name=? WHERE user_account.name = ?
[...] (('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim'))
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

Другие методы, которые могут быть применены к UPDATE, включают:

>>> scalar_subq = (
...     select(address_table.c.email_address)
...     .where(address_table.c.user_id == user_table.c.id)
...     .order_by(address_table.c.id)
...     .limit(1)
...     .scalar_subquery()
... )
>>> update_stmt = update(user_table).values(fullname=scalar_subq)
>>> print(update_stmt)
UPDATE user_account SET fullname=(SELECT address.email_address
FROM address
WHERE address.user_id = user_account.id ORDER BY address.id
LIMIT :param_1)

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(fullname="Pat")
... )
>>> print(update_stmt)
UPDATE user_account SET fullname=:fullname FROM address
WHERE user_account.id = address.user_id AND address.email_address = :email_address_1

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
...     .values(
...         {user_table.c.fullname: "Pat", address_table.c.email_address: "pat@aol.com"}
...     )
... )
>>> from sqlalchemy.dialects import mysql
>>> print(update_stmt.compile(dialect=mysql.dialect()))
UPDATE user_account, address
SET address.email_address=%s, user_account.fullname=%s
WHERE user_account.id = address.user_id AND address.email_address = %s

>>> update_stmt = update(some_table).ordered_values(
...     (some_table.c.y, 20), (some_table.c.x, some_table.c.y + 10)
... )
>>> print(update_stmt)
UPDATE some_table SET y=:y, x=(some_table.y + :y_1)

Конструкция SQL-выражения delete()¶

Функция delete() генерирует новый экземпляр Delete, который представляет собой оператор DELETE в SQL, который удаляет строки из таблицы.

Оператор delete() с точки зрения API очень похож на конструкцию update(), традиционно возвращающую отсутствие строк, но допускающую вариант RETURNING в некоторых бэкендах баз данных.

>>> from sqlalchemy import delete
>>> stmt = delete(user_table).where(user_table.c.name == "patrick")
>>> print(stmt)
DELETE FROM user_account WHERE user_account.name = :name_1

>>> delete_stmt = (
...     delete(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
... )
>>> from sqlalchemy.dialects import mysql
>>> print(delete_stmt.compile(dialect=mysql.dialect()))
DELETE FROM user_account USING user_account, address
WHERE user_account.id = address.user_id AND address.email_address = %s

Получение количества затронутых строк из UPDATE, DELETE¶

И Update, и Delete поддерживают возможность возвращать количество строк, сопоставленных после выполнения оператора, для операторов, которые вызываются с помощью Core Connection, т.е. Connection.execute(). Согласно оговоркам, упомянутым ниже, это значение доступно из атрибута CursorResult.rowcount:

>>> with engine.begin() as conn:
...     result = conn.execute(
...         update(user_table)
...         .values(fullname="Patrick McStar")
...         .where(user_table.c.name == "patrick")
...     )
...     print(result.rowcount)
BEGIN (implicit)
UPDATE user_account SET fullname=? WHERE user_account.name = ?
[...] ('Patrick McStar', 'patrick')
1
COMMIT

Факты о CursorResult.rowcount:

• Возвращаемое значение - это количество строк, сопоставленных в пункте WHERE оператора. Не имеет значения, был ли ряд действительно изменен или нет.

• CursorResult.rowcount не обязательно доступен для оператора UPDATE или DELETE, который использует RETURNING.

• Для выполнения executemany, CursorResult.rowcount также может быть недоступен, что сильно зависит от используемого модуля DBAPI, а также от настроенных опций. Атрибут CursorResult.supports_sane_multi_rowcount указывает, будет ли это значение доступно для текущего используемого бэкенда.

• Некоторые драйверы, особенно сторонние диалекты для нереляционных баз данных, могут вообще не поддерживать CursorResult.rowcount. На это указывает CursorResult.supports_sane_rowcount.

• «rowcount» используется процессом ORM unit of work для проверки того, что оператор UPDATE или DELETE соответствует ожидаемому количеству строк, а также необходим для функции версионности ORM, документированной в Настройка счетчика версий.

Использование RETURNING с UPDATE, DELETE¶

Как и конструкция Insert, Update и Delete также поддерживают условие RETURNING, которое добавляется с помощью методов Update.returning() и Delete.returning(). При использовании этих методов на бэкенде, поддерживающем RETURNING, выбранные столбцы из всех строк, соответствующих критериям WHERE оператора, будут возвращены в объект Result в виде строк, которые можно итерировать:

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.name == "patrick")
...     .values(fullname="Patrick the Star")
...     .returning(user_table.c.id, user_table.c.name)
... )
>>> print(update_stmt)
UPDATE user_account SET fullname=:fullname
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name
>>> delete_stmt = (
...     delete(user_table)
...     .where(user_table.c.name == "patrick")
...     .returning(user_table.c.id, user_table.c.name)
... )
>>> print(delete_stmt)
DELETE FROM user_account
WHERE user_account.name = :name_1
RETURNING user_account.id, user_account.name

Дополнительная литература по UPDATE, DELETE¶