Using UPDATE and DELETE Statements

So far we’ve covered , so that we can get some data into our database, and then spent a lot of time on Select which handles the broad range of usage patterns used for retrieving data from the database. In this section we will cover the and Delete constructs, which are used to modify existing rows as well as delete existing rows. This section will cover these constructs from a Core-centric perspective.

ORM Readers - As was the case mentioned at , the Update and operations when used with the ORM are usually invoked internally from the Session object as part of the process.

However, unlike Insert, the and Delete constructs can also be used directly with the ORM, using a pattern known as “ORM-enabled update and delete”; for this reason, familiarity with these constructs is useful for ORM use. Both styles of use are discussed in the sections and Deleting ORM Objects using the Unit of Work pattern.

The update() function generates a new instance of which represents an UPDATE statement in SQL, that will update existing data in a table.

Like the insert() construct, there is a “traditional” form of , which emits UPDATE against a single table at a time and does not return any rows. However some backends support an UPDATE statement that may modify multiple tables at once, and the UPDATE statement also supports RETURNING such that columns contained in matched rows may be returned in the result set.

A basic UPDATE looks like:

The Update.values() method controls the contents of the SET elements of the UPDATE statement. This is the same method shared by the construct. Parameters can normally be passed using the column names as keyword arguments.

UPDATE supports all the major SQL forms of UPDATE, including updates against expressions, where we can make use of Column expressions:


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

To support UPDATE in an “executemany” context, where many parameter sets will be invoked against the same statement, the construct may be used to set up bound parameters; these replace the places that literal values would normally go:

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

Other techniques which may be applied to UPDATE include:

Some databases such as PostgreSQL and MySQL support a syntax “UPDATE FROM” where additional tables may be stated directly in a special FROM clause. This syntax will be generated implicitly when additional tables are located in the WHERE clause of the statement:

>>> update_stmt = (
...     update(user_table)
...     .where(user_table.c.id == address_table.c.user_id)
...     .where(address_table.c.email_address == "patrick@aol.com")
... )
>>> 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

There is also a MySQL specific syntax that can UPDATE multiple tables. This requires we refer to Table objects in the VALUES clause in order to refer to additional tables:

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

Another MySQL-only behavior is that the order of parameters in the SET clause of an UPDATE actually impacts the evaluation of each expression. For this use case, the Update.ordered_values() method accepts a sequence of tuples so that this order may be controlled :

[2]

While Python dictionaries are as of Python 3.7, the Update.ordered_values() method still provides an additional measure of clarity of intent when it is essential that the SET clause of a MySQL UPDATE statement proceed in a specific way.

The delete() function generates a new instance of which represents a DELETE statement in SQL, that will delete rows from a table.

The delete() statement from an API perspective is very similar to that of the construct, traditionally returning no rows but allowing for a RETURNING variant on some database backends.

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

Like , Delete supports the use of correlated subqueries in the WHERE clause as well as backend-specific multiple table syntaxes, such as DELETE FROM..USING on MySQL:

>>> delete_stmt = (
...     .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

Both Update and support the ability to return the number of rows matched after the statement proceeds, for statements that are invoked using Core Connection, i.e. . Per the caveats mentioned below, this value is available from the CursorResult.rowcount attribute:

Tip

The class is a subclass of Result which contains additional attributes that are specific to the DBAPI cursor object. An instance of this subclass is returned when a statement is invoked via the method. When using the ORM, the Session.execute() method returns an object of this type for all INSERT, UPDATE, and DELETE statements.

Facts about :

CursorResult.rowcount is not necessarily available for an UPDATE or DELETE statement that uses RETURNING.
For an execution, CursorResult.rowcount may not be available either, which depends highly on the DBAPI module in use as well as configured options. The attribute indicates if this value will be available for the current backend in use.
Some drivers, particularly third party dialects for non-relational databases, may not support CursorResult.rowcount at all. The will indicate this.
“rowcount” is used by the ORM unit of work process to validate that an UPDATE or DELETE statement matched the expected number of rows, and is also essential for the ORM versioning feature documented at .

Like the construct, Update and also support the RETURNING clause which is added by using the Update.returning() and methods. When these methods are used on a backend that supports RETURNING, selected columns from all rows that match the WHERE criteria of the statement will be returned in the Result object as rows that can be iterated:

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