SQLAlchemy for Python Developers

# Modifying and Deleting Rows Using Core

In this chapter, we explain the Update and Delete statements used for modifying and deleting existing rows using the Core approach in SQLAlchemy.


# Constructing SQL Expressions with update()

You can write an UPDATE statement as follows.

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

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

The original text discusses bindparam(), but since I haven't seen many use cases for it, it is omitted in this text. If you're curious, please refer to the original content (opens new window).


# Correlated Update

Using a Correlated Subquery (opens new window), you can utilize rows from another table as follows.

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

When updating a table, there are times when you need to set conditions in relation to information from another table. In such cases, you can use it as shown in the example below.

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

# Updating Multiple Tables Simultaneously

You can simultaneously update specific values in multiple tables that meet certain conditions, as shown in the following example.

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

I did not include a summary of the 'Parameter Ordered Updates (opens new window)' section from the original text because I did not understand it. If someone understands this part well, it would be appreciated if you could contribute to this document.


# Constructing SQL Expressions with delete()

You can write a DELETE statement as follows.

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

# Deleting with a JOIN to Another Table

There are cases where you need to delete data that meets specific conditions after joining with another table. (If this is unclear, refer to this article (opens new window) for clarification.) In such cases, you can use it as shown in the example below.

>>> 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 the Number of Rows Affected in UPDATE, DELETE

You can obtain the number of rows processed by a query using the 'Result.rowcount' (opens new window) property, as shown next.

>>> with engine.begin() as conn:
...     result = conn.execute(
...         update(user_table).
...         values(fullname="Patrick McStar").
...         where(user_table.c.name == 'patrick')
...     )
...     print(result.rowcount)  # You can use the rowcount property of the Result object.

1  # The number of rows processed by the query (the same as the number of rows matching the conditions).

# Using RETURNING with UPDATE, DELETE

You can use the RETURNING syntax as follows.

For more on the RETURNING syntax, please see this article (opens new window).

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

← Manipulating Data Using ORM →