Updating and Deleting Rows with Core¶
So far we’ve covered Insert
, 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 Update
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 Inserting Rows with Core,
the Update
and Delete
operations when used with
the ORM are usually invoked internally from the Session
object as part of the unit of work process.
However, unlike Insert
, the Update
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 Updating ORM Objects and
Deleting ORM Objects.
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select, insert, text
from sqlalchemy.orm import declarative_base, Session
Base = declarative_base()
user_table = Table(
"user_account",
Base.metadata,
Column('id', Integer, primary_key=True),
Column('name', String(30)),
Column('fullname', String)
)
class User(Base):
__table__ = user_table
def __repr__(self):
return f"User({self.name!r}, {self.fullname!r})"
address_table = Table(
"address",
Base.metadata,
Column('id', Integer, primary_key=True),
Column('user_id', ForeignKey('user_account.id'), nullable=False),
Column('email_address', String, nullable=False)
)
class Address(Base):
__table__ = address_table
def __repr__(self):
return f"Address({self.email_address!r})"
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
Base.metadata.create_all(engine)
with engine.begin() as conn:
conn.execute(
insert(user_table),
[
{"id": 1, "name": "spongebob", "fullname": "Spongebob Squarepants"},
{"id": 2, "name": "sandy", "fullname": "Sandy Cheeks"},
{"id": 3, "name": "patrick", "fullname": "Patrick Star"}
]
)
with engine.begin() as conn:
conn.execute(
insert(address_table),
[
{"user_id": 1, "email_address": "spongebob@sqlalchemy.org"},
{"user_id": 2, "email_address": "sandy@sqlalchemy.org"},
{"user_id": 2, "email_address": "sandy@squirrelpower.org"},
]
)
2021-10-04 01:45:41,858 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,858 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user_account")
2021-10-04 01:45:41,859 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,860 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("user_account")
2021-10-04 01:45:41,861 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,861 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("address")
2021-10-04 01:45:41,862 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,862 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("address")
2021-10-04 01:45:41,863 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,864 INFO sqlalchemy.engine.Engine
CREATE TABLE user_account (
id INTEGER NOT NULL,
name VARCHAR(30),
fullname VARCHAR,
PRIMARY KEY (id)
)
2021-10-04 01:45:41,865 INFO sqlalchemy.engine.Engine [no key 0.00073s] ()
2021-10-04 01:45:41,867 INFO sqlalchemy.engine.Engine
CREATE TABLE address (
id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
email_address VARCHAR NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user_account (id)
)
2021-10-04 01:45:41,867 INFO sqlalchemy.engine.Engine [no key 0.00042s] ()
2021-10-04 01:45:41,868 INFO sqlalchemy.engine.Engine COMMIT
2021-10-04 01:45:41,869 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,870 INFO sqlalchemy.engine.Engine INSERT INTO user_account (id, name, fullname) VALUES (?, ?, ?)
2021-10-04 01:45:41,871 INFO sqlalchemy.engine.Engine [generated in 0.00063s] ((1, 'spongebob', 'Spongebob Squarepants'), (2, 'sandy', 'Sandy Cheeks'), (3, 'patrick', 'Patrick Star'))
2021-10-04 01:45:41,872 INFO sqlalchemy.engine.Engine COMMIT
2021-10-04 01:45:41,873 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,874 INFO sqlalchemy.engine.Engine INSERT INTO address (user_id, email_address) VALUES (?, ?)
2021-10-04 01:45:41,875 INFO sqlalchemy.engine.Engine [generated in 0.00064s] ((1, 'spongebob@sqlalchemy.org'), (2, 'sandy@sqlalchemy.org'), (2, 'sandy@squirrelpower.org'))
2021-10-04 01:45:41,882 INFO sqlalchemy.engine.Engine COMMIT
The update() SQL Expression Construct¶
The update()
function generates a new instance of
Update
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
update()
, 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:
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
The values()
method controls the contents of the SET elements
of the UPDATE statement. This is the same method shared by the Insert
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:
stmt = (
update(user_table).
values(fullname="Username: " + user_table.c.name)
)
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 bindparam()
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'},
]
)
2021-10-04 01:45:41,907 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,909 INFO sqlalchemy.engine.Engine UPDATE user_account SET name=? WHERE user_account.name = ?
2021-10-04 01:45:41,909 INFO sqlalchemy.engine.Engine [generated in 0.00061s] (('ed', 'jack'), ('mary', 'wendy'), ('jake', 'jim'))
2021-10-04 01:45:41,910 INFO sqlalchemy.engine.Engine COMMIT
Other techniques which may be applied to UPDATE include:
UPDATE..FROM¶
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').
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
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
Parameter Ordered Updates¶
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 ordered_values()
method accepts a sequence of
tuples so that this order may be controlled 1:
with engine.begin() as conn:
conn.execute(text("CREATE TABLE some_table (x int, y int)"))
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 1, "y": 1}, {"x": 2, "y": 4}]
)
some_table = Table("some_table", Base.metadata, autoload_with=engine)
2021-10-04 01:45:41,949 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,950 INFO sqlalchemy.engine.Engine CREATE TABLE some_table (x int, y int)
2021-10-04 01:45:41,950 INFO sqlalchemy.engine.Engine [generated in 0.00084s] ()
2021-10-04 01:45:41,952 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2021-10-04 01:45:41,952 INFO sqlalchemy.engine.Engine [generated in 0.00080s] ((1, 1), (2, 4))
2021-10-04 01:45:41,953 INFO sqlalchemy.engine.Engine COMMIT
2021-10-04 01:45:41,954 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,955 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("some_table")
2021-10-04 01:45:41,956 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,957 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'
2021-10-04 01:45:41,958 INFO sqlalchemy.engine.Engine [raw sql] ('some_table',)
2021-10-04 01:45:41,959 INFO sqlalchemy.engine.Engine PRAGMA main.foreign_key_list("some_table")
2021-10-04 01:45:41,960 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,961 INFO sqlalchemy.engine.Engine PRAGMA temp.foreign_key_list("some_table")
2021-10-04 01:45:41,961 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,962 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'
2021-10-04 01:45:41,963 INFO sqlalchemy.engine.Engine [raw sql] ('some_table',)
2021-10-04 01:45:41,965 INFO sqlalchemy.engine.Engine PRAGMA main.index_list("some_table")
2021-10-04 01:45:41,966 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,967 INFO sqlalchemy.engine.Engine PRAGMA temp.index_list("some_table")
2021-10-04 01:45:41,967 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,968 INFO sqlalchemy.engine.Engine PRAGMA main.index_list("some_table")
2021-10-04 01:45:41,969 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,969 INFO sqlalchemy.engine.Engine PRAGMA temp.index_list("some_table")
2021-10-04 01:45:41,970 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:41,971 INFO sqlalchemy.engine.Engine SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'
2021-10-04 01:45:41,972 INFO sqlalchemy.engine.Engine [raw sql] ('some_table',)
2021-10-04 01:45:41,974 INFO sqlalchemy.engine.Engine ROLLBACK
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)
with engine.begin() as conn:
conn.execute(update_stmt)
with engine.begin() as conn:
result = conn.execute(select(some_table)).all()
result
2021-10-04 01:45:41,989 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,991 INFO sqlalchemy.engine.Engine UPDATE some_table SET y=?, x=(some_table.y + ?)
2021-10-04 01:45:41,992 INFO sqlalchemy.engine.Engine [generated in 0.00077s] (20, 10)
2021-10-04 01:45:41,993 INFO sqlalchemy.engine.Engine COMMIT
2021-10-04 01:45:41,994 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:41,995 INFO sqlalchemy.engine.Engine SELECT some_table.x, some_table.y
FROM some_table
2021-10-04 01:45:41,996 INFO sqlalchemy.engine.Engine [generated in 0.00072s] ()
2021-10-04 01:45:41,997 INFO sqlalchemy.engine.Engine COMMIT
[(11, 20), (14, 20)]
The delete() SQL Expression Construct¶
The delete()
function generates a new instance of
Delete
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 update()
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
Multiple Table Deletes¶
Like Update
, 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 = (
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¶
Both Update
and Delete
support the ability to
return the number of rows matched after the statement proceeds, for statements
that are invoked using Core Connection
, i.e.
execute()
. Per the caveats mentioned below, this value
is available from the rowcount
attribute:
with engine.begin() as conn:
result = conn.execute(
update(user_table).
values(fullname="Patrick McStar").
where(user_table.c.name == 'patrick')
)
print(result.rowcount)
2021-10-04 01:45:42,022 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:42,024 INFO sqlalchemy.engine.Engine UPDATE user_account SET fullname=? WHERE user_account.name = ?
2021-10-04 01:45:42,025 INFO sqlalchemy.engine.Engine [generated in 0.00048s] ('Patrick McStar', 'patrick')
1
2021-10-04 01:45:42,026 INFO sqlalchemy.engine.Engine COMMIT
Tip
The CursorResult
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
execute()
method. When using the ORM, the
execute()
method returns an object of this type for
all INSERT, UPDATE, and DELETE statements.
Facts about rowcount
:
The value returned is the number of rows matched by the WHERE clause of the statement. It does not matter if the row were actually modified or not.
rowcount
is not necessarily available for an UPDATE or DELETE statement that uses RETURNING.For an executemany execution,
rowcount
may not be available either, which depends highly on the DBAPI module in use as well as configured options. The attributesupports_sane_multi_rowcount
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
rowcount
at all. Thesupports_sane_rowcount
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 Configuring a Version Counter.
Using RETURNING with UPDATE, DELETE¶
Like the Insert
construct, Update
and Delete
also support the RETURNING clause which is added by using the
returning()
and returning()
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 RETURNING user_account.id, user_account.name
Further Reading for UPDATE, DELETE¶
See also
API documentation for UPDATE / DELETE:
ORM-enabled UPDATE and DELETE:
- 1
While Python dictionaries are guaranteed to be insert ordered as of Python 3.7, the
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.