Lesson 5 - Updating, Deleting, and Transactions

Welcome to Modifying Data Safely#

Reading data is only half the job — sooner or later you need to change it. In this lesson you update rows one at a time and in bulk, delete rows you no longer need, and — most importantly — learn how a transaction protects you when something goes wrong halfway through a multi-step change.

By the end of this lesson, you will be able to:

• Update a single row’s values with update()
• Apply a conditional update to every matching row at once
• Delete rows with delete()
• Wrap related changes in a transaction that rolls back automatically on error
• Use a savepoint to undo part of a transaction without losing the rest

Let’s start changing data.

Updating Rows#

A single update#

update() works like select(): build it, narrow it with .where(), and specify new values with .values().

from sqlalchemy import create_engine, MetaData, Table, select, update

engine = create_engine("sqlite:///company.db")
metadata = MetaData()
employees = Table("employees", metadata, autoload_with=engine)
departments = Table("departments", metadata, autoload_with=engine)

with engine.connect() as conn:
    stmt = update(employees).where(employees.c.name == "TAFT").values(salary=28000)
    result = conn.execute(stmt)
    print("rows matched:", result.rowcount)
    conn.commit()

Output:

rows matched: 1

result.rowcount confirms exactly one row changed — useful for catching a .where() clause that is broader (or narrower) than you intended, before you find out the hard way.

A conditional bulk update#

.values() accepts an expression, not just a literal, which lets you update every matching row relative to its own current value in a single statement:

stmt = update(employees).where(employees.c.department_id == 3).values(
    salary=employees.c.salary * 1.05
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    print("rows matched:", result.rowcount)
    conn.commit()

with engine.connect() as conn:
    for row in conn.execute(select(employees.c.name, employees.c.salary).where(employees.c.department_id == 3)):
        print(row)

Output:

rows matched: 3
('HARDING', 54600.00)
('TAFT', 29400.00)
('HOOVER', 28350.00)

employees.c.salary * 1.05 compiles to salary = salary * 1.05 in the generated SQL — the database computes each new value from that row’s own current salary, in one round trip, rather than you looping over rows in Python and issuing one UPDATE per employee.

Deleting Rows#

delete() follows the same shape:

from sqlalchemy import delete

with engine.connect() as conn:
    result = conn.execute(delete(employees).where(employees.c.name == "HOOVER"))
    print("rows deleted:", result.rowcount)
    conn.commit()

with engine.connect() as conn:
    print("HOOVER present?", conn.execute(select(employees.c.name).where(employees.c.name == "HOOVER")).first())

Output:

rows deleted: 1
HOOVER present? None

Transactions: All or Nothing#

Every example above committed on its own, but real changes are often a group: give a raise and log the change and update a summary table. If any one of those fails, you usually want none of them to stick. That guarantee is a transaction.

Open one with conn.begin() as a context manager. If the block finishes normally, SQLAlchemy commits; if an exception escapes the block, it rolls back automatically — you never have to call rollback() yourself:

from sqlalchemy.exc import IntegrityError

with engine.connect() as conn:
    try:
        with conn.begin():
            conn.execute(update(employees).where(employees.c.name == "GRANT").values(salary=33000))
            conn.execute(departments.insert().values(id=99, name="Finance"))  # "Finance" already exists
    except IntegrityError:
        print("Transaction rolled back: duplicate department name violated the unique constraint.")

with engine.connect() as conn:
    print("GRANT salary after rollback:", conn.execute(select(employees.c.salary).where(employees.c.name == "GRANT")).scalar())

Output:

Transaction rolled back: duplicate department name violated the unique constraint.
GRANT salary after rollback: 32000.00

The department insert fails because "Finance" already exists and name is unique=True — exactly the constraint you defined in Lesson 2. Because both statements ran inside the same conn.begin() block, GRANT’s raise is rolled back along with the failed insert. Without the transaction, GRANT would have kept the new salary even though the operation as a whole failed — precisely the inconsistency transactions exist to prevent.

Savepoints: Rolling Back Part of a Transaction#

Sometimes you want to protect one step inside a larger transaction, without losing everything that came before it. A savepoint, created with conn.begin_nested(), gives you that finer-grained control:

with engine.connect() as conn:
    with conn.begin():
        conn.execute(update(employees).where(employees.c.name == "POLK").values(salary=26000))

        savepoint = conn.begin_nested()
        try:
            conn.execute(departments.insert().values(id=1, name="Duplicate"))  # id 1 already exists
        except IntegrityError:
            savepoint.rollback()
            print("Savepoint rolled back; POLK's raise is still part of the outer transaction.")

with engine.connect() as conn:
    print("POLK salary:", conn.execute(select(employees.c.salary).where(employees.c.name == "POLK")).scalar())

Output:

Savepoint rolled back; POLK's raise is still part of the outer transaction.
POLK salary: 26000.00

The failed insert only rolls back to the savepoint, not to the start of the outer transaction — POLK’s raise survives when the outer with conn.begin(): block commits. Use a savepoint whenever one step in a larger operation is expected to sometimes fail in a way you can recover from, without discarding the steps around it.

Practice Exercises#

Continue using company.db, the employees and departments tables.

Exercise 1: Give Engineering a Raise#

Write a conditional bulk update that gives every employee in department_id=2 (Engineering) a 3% raise, then print their new salaries.

# Your code here

Exercise 2: Delete a Department Safely#

Try to delete the "Sales" department directly from departments while employees still reference it, and observe the IntegrityError this raises (since department_id is a foreign key). Catch the error and print a friendly message instead of letting the program crash.

# Your code here

Exercise 3: Two Updates, One Transaction#

Inside a single conn.begin() block, give "ADAMS" a raise to 36000 and set "MONROE"’s job to "SENIOR ENGINEER". Confirm both changes committed together.

# Your code here

Summary#

You updated rows with update(), both a single targeted row and a conditional bulk update expressed relative to each row’s current value, and removed rows with delete(). You then wrapped related changes in a transaction using conn.begin(), watching it roll back automatically when an IntegrityError interrupted it, and used conn.begin_nested() to create a savepoint that rolled back only one failed step while preserving the rest of the transaction.

Key Concepts#

• update(table).where(...).values(...) — updates matching rows; .values() can reference the row’s own current columns.
• delete(table).where(...) — deletes matching rows; omitting .where() deletes everything.
• result.rowcount — confirms how many rows an update or delete actually affected.
• Transaction — a group of statements that commit together or roll back together; opened with conn.begin() as a context manager.
• IntegrityError — raised when a change would violate a constraint like unique or a foreign key.
• Savepoint (conn.begin_nested()) — a checkpoint inside a transaction that can be rolled back on its own, without discarding the rest of the transaction.

Why This Matters#

Every real application eventually needs to make more than one change look like it happened all at once — transferring money between two accounts, or updating an order and its inventory count together. Transactions are what make that guarantee possible, and the difference between forgetting one and using one is the difference between a database that stays consistent under failure and one that silently corrupts itself the first time something goes wrong mid-operation.

Continue Building Your Skills#

You can now read, write, and safely modify data with Core. In the next lesson you will meet SQLAlchemy’s other major layer — the ORM — where these same tables become Python classes with attributes and relationships you can navigate directly.