Using UPDATE and DELETE Statements

ここまでは、データベースにデータを取り込むための Insert について説明しました。そして、データベースからデータを取り出すために使用される広範な使用パターンを処理する Select について多くの時間を費やしました。この節では、既存の行の変更と削除に使用される Update および Delete 構文について説明します。この節では、コア中心の観点からこれらの構文について説明します。

ORM Readers - Using INSERT Statements で述べたように、Update および Delete 操作をORMとともに使用すると、通常 unit of work プロセスの一部として Session オブジェクトから内部的に呼び出されます。

ただし、 Insert とは異なり、 Update および Delete 構文は、”ORM対応の更新と削除”として知られるパターンを使用して、ORMで直接使用することもできます。この理由から、これらの構文に精通していることはORMの使用に役立ちます。どちらの使用方法についても、 Updating ORM Objects using the Unit of Work pattern と Deleting ORM Objects using the Unit of Work pattern のセクションで説明しています。

The update() SQL Expression Construct

update() 関数は、テーブル内の既存のデータを更新するSQLのUPDATE文を表す Update の新しいインスタンスを生成します。

insert() 構文のように、一度に1つのテーブルに対して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() メソッドは、UPDATE文のSET要素の内容を制御します。これは Insert 構文で共有されているメソッドと同じです。パラメータは通常、キーワード引数として列名を使用して渡すことができます。

UPDATEは、式に対する更新を含め、UPDATEの主要なSQL形式をすべてサポートしており、 Column 式を利用することができます:

>>> stmt = update(user_table).values(fullname="Username: " + user_table.c.name)
>>> print(stmt)

UPDATE user_account SET fullname=(:name_1 || user_account.name)

同じ文に対して複数のパラメータセットが呼び出される”executemany”コンテキストでUPDATEをサポートするには、 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に適用できる他のテクニックには、次のものがあります。:

Correlated Updates

UPDATE文は、 correlated subquery を使用することで、他のテーブルの行を利用することができます。副問い合わせは、列式が配置される場所ならどこでも使用できます:

>>> 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..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固有の構文もあります。追加のテーブルを参照するには、VALUES句の Table オブジェクトを参照する必要があります:

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

Parameter Ordered Updates

もう1つのMySQLのみの動作は、UPDATEのSET句のパラメータの順序が、実際には各式の評価に影響することです。このユースケースでは、 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)

The delete() SQL Expression Construct

delete() 関数は、テーブルから行を削除するSQLのDELETE文を表す Delete の新しいインスタンスを生成します。

APIの観点から見ると、 delete() 文は update() 構文のものと非常に似ていて、伝統的には行を返しませんが、いくつかのデータベースバックエンドではRETURNINGバリアントを許可しています。

:<数値>:
>>> from sqlalchemy import delete
>>> stmt = delete(user_table).where(user_table.c.name == "patrick")
>>> print(stmt)
{printsql}DELETE FROM user_account WHERE user_account.name = :name_1

Multiple Table Deletes

Update と同様に、 Delete はWHERE文節での相関サブクエリの使用や、MySQLでの DELETE FROM.USING のようなバックエンド固有の複数テーブル構文をサポートしています:

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

Getting Affected Row Count from 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

Tip

:class`_engine.CursorResult` クラスは :class`_engine.Result` のサブクラスで、DBAPIの cursor オブジェクトに固有の属性が追加されています。 Connection.execute() メソッドによって文が呼び出されると、このサブクラスのインスタンスが返されます。ORMを使用している場合、 Session.execute() メソッドはすべてのINSERT、UPDATE、DELETE文に対してこの型のオブジェクトを返します。

CursorResult.rowcount に関する情報:

  • 返される値は、文のWHERE句によって 一致した 行の数です。行が実際に変更されたかどうかは関係ありません。

  • CursorResult.rowcount は、RETURNINGを使用するUPDATE文やDELETE文、または executemany 実行を使用するUPDATE文やDELETE文では必ずしも使用できません。使用できるかどうかは、使用しているDBAPIモジュールによって異なります。

  • DBAPIがある種の文の行数を決定しない場合、戻り値は -1 になります。

  • SQLAlchemyは、カーソルが閉じられる前にDB APIの cursor.rowcount 値を事前にメモします。これは、DB APIの中には、この属性への事後アクセスをサポートしていないものがあるためです。INSERTやSELECTなど、UPDATEでもDELETEでもない文に対して cursor.rowcount を事前にメモするには、 Connection.execution_options.preserve_rowcount 実行オプションを使用できます。

  • 一部のドライバ、特に非リレーショナルデータベース用のサードパーティのダイアレクトは、 CursorResult.rowcount をまったくサポートしていない可能性があります。 CursorResult.supports_sane_rowcount カーソル属性がこれを示します。

  • “rowcount”は、ORM unit of work プロセスで、UPDATE文またはDELETE文が予期された行数に一致することを検証するために使用されます。また、 Configuring a Version Counter に記載されているORMバージョン管理機能にも不可欠です。

Using RETURNING with UPDATE, DELETE

Insert 構文と同様に、 Update と Delete は、 Update.returning() メソッドと Delete.returning() メソッドを使って追加された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

Further Reading for UPDATE, DELETE

See also

API documentation for UPDATE / DELETE:

ORM-enabled UPDATE and DELETE:

ORM-Enabled INSERT, UPDATE, and DELETE statements - in the ORM Querying Guide