Обновление и удаление строк с помощью Core¶
До сих пор мы рассмотрели конструкцию Insert
, чтобы мы могли получить некоторые данные в нашу базу данных, а затем потратили много времени на Select
, которая обрабатывает широкий спектр шаблонов использования, применяемых для получения данных из базы данных. В этом разделе мы рассмотрим конструкции Update
и Delete
, которые используются для изменения существующих строк, а также для удаления существующих строк. В этом разделе мы рассмотрим эти конструкции с точки зрения Core-центризма.
ORM Readers - Как уже упоминалось в Вставка строк с помощью ядра, операции Update
и Delete
при использовании с ORM обычно вызываются внутри объекта Session
как часть процесса unit of work.
Однако, в отличие от Insert
, конструкции Update
и Delete
могут также использоваться непосредственно с ORM, используя шаблон, известный как «обновление и удаление с поддержкой ORM»; по этой причине знакомство с этими конструкциями полезно для использования ORM. Оба стиля использования обсуждаются в разделах Обновление объектов ORM и Удаление объектов ORM.
Конструкция 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, включают:
UPDATE… FROM¶
Некоторые базы данных, такие как PostgreSQL и MySQL, поддерживают синтаксис «UPDATE FROM», в котором дополнительные таблицы могут быть указаны непосредственно в специальном предложении FROM. Этот синтаксис будет сформирован неявно, когда дополнительные таблицы находятся в пункте WHERE оператора:
>>> 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
Существует также специфический для MySQL синтаксис, позволяющий выполнять UPDATE нескольких таблиц. Это требует, чтобы мы ссылались на объекты Table
в предложении VALUES для того, чтобы ссылаться на дополнительные таблицы:
>>> 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
Обновления, упорядоченные по параметрам¶
Другое поведение, характерное только для MySQL, заключается в том, что порядок параметров в предложении SET в UPDATE фактически влияет на оценку каждого выражения. Для этого случая метод Update.ordered_values()
принимает последовательность кортежей, так что этот порядок можно контролировать [2]:
>>> 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
Удаление нескольких таблиц¶
Как и Update
, Delete
поддерживает использование коррелированных подзапросов в предложении WHERE, а также специфические для бэкенда синтаксисы множественных таблиц, такие как DELETE FROM..USING
на MySQL:
>>> 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
является подклассом класса Result
, который содержит дополнительные атрибуты, специфичные для объекта DBAPI cursor
. Экземпляр этого подкласса возвращается при вызове оператора через метод Connection.execute()
. При использовании ORM метод Session.execute()
возвращает объект этого типа для всех операторов INSERT, UPDATE и DELETE.
Факты о 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¶
См.также
Документация по API для UPDATE / DELETE:
Update
Delete
UPDATE и DELETE с поддержкой ORM: