KAHIBARO
Discord Login Register ☰
☰

12.7. Updating Records

Table of Contents

Understanding Record Updates with an ORM

Updating records is one of the four core database operations, often grouped under CRUD, which stands for Create, Read, Update, Delete. In this chapter, you will learn how to update data when you use an ORM, with a focus on the mental model and typical patterns rather than on one specific framework.

You already know what an ORM is and how to create and read records from earlier chapters. Here we focus on what is unique about updating.

The Basic Update Flow

Although ORMs differ in syntax, most follow the same logical steps when you want to update something:

  1. Load the record from the database into an object.
  2. Modify attributes on that object.
  3. Mark changes for saving by telling the ORM you want to persist them.
  4. Commit the transaction so the changes are actually written to the database.

In pseudo code, a typical update looks like this:

python 
# 1. Read the record
user = session.get(User, user_id)
# 2. Change some fields
user.email = "new_email@example.com"
user.is_active = True
# 3. Tell the ORM to save changes (often implicit)
session.add(user)
# 4. Commit
session.commit()

In many ORMs, step 3 is optional once the object is attached to the session. The important idea is that the ORM tracks changes on loaded objects and writes them to the database when you commit.

Updating a Single Record

Updating a single record usually looks like updating a normal object in memory.

Step 1: Load the record

First get the record you want to change:

python 
user = session.get(User, 42)  # 42 is the primary key

If the record might not exist, you must handle that case:

python 
user = session.get(User, 42)
if user is None:
    # handle "not found", for example:
    raise ValueError("User not found")

Step 2: Modify fields

You change attributes just like normal Python attributes:

python 
user.name = "Alice Updated"
user.email = "alice.updated@example.com"

You can also use setattr if you want to update fields programmatically:

python 
for field, value in {"name": "Alice Updated", "email": "alice.updated@example.com"}.items():
    setattr(user, field, value)

Step 3: Commit the changes

Once you are done changing attributes:

python 
session.commit()

The ORM generates an UPDATE statement similar to:

sql 
UPDATE users
SET name = 'Alice Updated',
    email = 'alice.updated@example.com'
WHERE id = 42;

The ORM only does this when you call commit (or sometimes flush).

Partial Updates

You do not need to set every field on the record. You can update just one or two attributes.

Examples:

python 
# Update only the email
user.email = "new@example.com"
session.commit()
# Update only a boolean flag
user.is_active = False
session.commit()

The ORM tracks which fields actually changed. Good ORMs generate SQL that updates only the modified fields. This improves performance and avoids accidental overwrites.

Sometimes you receive only some fields from an API request. A common pattern is:

python 
def update_user_from_payload(user, data: dict):
    # data might look like {"name": "Alice", "is_active": True}
    for key, value in data.items():
        if hasattr(user, key):
            setattr(user, key, value)

Then:

python 
user = session.get(User, user_id)
if user is None:
    raise ValueError("User not found")
update_user_from_payload(user, payload_dict)
session.commit()

This maps naturally to HTTP PATCH semantics in REST APIs.

Bulk Updates

Sometimes you need to update many rows at once, for example when you deactivate all users who have not logged in for a year.

With an ORM you have two main strategies:

1. Load every record and update in Python

python 
old_users = (
    session.query(User)
    .filter(User.last_login < one_year_ago)
    .all()
)
for user in old_users:
    user.is_active = False
session.commit()

Pros:

Cons:

2. Use a bulk update query

Many ORMs support a method that directly translates to a SQL UPDATE query without loading each record:

python 
session.query(User).filter(User.last_login < one_year_ago).update(
    {User.is_active: False}
)
session.commit()

This generates one SQL statement:

sql 
UPDATE users
SET is_active = FALSE
WHERE last_login < :one_year_ago;

Pros:

Cons:

Important rule: Use bulk updates for performance, but remember they usually skip per object validation and events. Only use them when you are sure about the effect of the raw SQL.

Handling Not Found and Optional Updates

When you try to update something that may not exist, you must handle the missing case gracefully.

Example: Update or create error

python 
user = session.get(User, user_id)
if user is None:
    # Decide what to do: error or create new
    raise ValueError(f"User with id {user_id} not found")
user.email = new_email
session.commit()

In a web API, you usually convert this to an HTTP 404 response.

Optional field updates

Sometimes the input contains None for fields. You must decide what None means:

One common pattern is to distinguish between "field is missing from the input" and "field is present with value null". For example:

python 
def update_user(user, data):
    if "name" in data:
        user.name = data["name"]     # even if None
    if "email" in data:
        user.email = data["email"]

If "name" is absent from data, you leave user.name as it is.

Concurrency and Lost Updates

Imagine two requests update the same user at nearly the same time.

  1. Request A loads the user.
  2. Request B loads the same user.
  3. Request A sets email = "a@example.com" and commits.
  4. Request B sets name = "New Name" and commits.

If request B updates all fields from its in memory object, it might overwrite the email change from request A. This is called a lost update.

There are two common strategies to protect against this.

1. Let the database handle it with transactions

You can use transactions and isolation levels so that updates are serialized. Some ORMs also support "select for update" queries:

python 
user = (
    session.query(User)
    .filter(User.id == user_id)
    .with_for_update()
    .one()
)

This locks the row until the transaction commits.

2. Optimistic locking with a version field

You can add a version column that increments on every update.

Example table:

ColumnType
idinteger
nametext
emailtext
versioninteger

Workflow:

  1. Load the user, including version.
  2. When updating, send version back.
  3. The ORM generates an update that checks the version:
sql 
   UPDATE users
   SET name = :name,
       email = :email,
       version = version + 1
   WHERE id = :id AND version = :version;
  1. If no row is updated (row count is 0), it means someone else already changed it. You can then raise a concurrency error.

Many ORMs have built in support for this pattern.

Important rule: In concurrent systems, always think about lost updates. Use transactions, locks, or version columns to protect critical data.

Updates and Transactions

Every update should be part of a transaction. In most ORMs, one session.commit() corresponds to committing a transaction.

You can combine several operations:

python 
# Start transaction
user = session.get(User, user_id)
order = session.get(Order, order_id)
user.last_order_id = order.id
order.status = "processed"
# Both updates are committed together
session.commit()

If anything fails before the commit, you call session.rollback() and the database discards all partial changes.

This is important for maintaining consistent data.

Updating Related Records

When you have relationships, you can update related records through the object graph. This uses the ORM features you learned in the relationships chapter.

Example: Update a one to many relationship

Suppose a User has many Address entries:

python 
user = session.get(User, user_id)
# Update the first address line
addr = user.addresses[0]
addr.line1 = "New Street 123"
session.commit()

The ORM notices that an Address object changed and generates an UPDATE for the addresses table.

Replacing an entire collection

To replace all existing addresses with a new list:

python 
user.addresses = [
    Address(line1="Street 1", city="Paris"),
    Address(line1="Street 2", city="Berlin"),
]
session.commit()

Depending on configuration, the ORM might:

You must understand the relationship settings in your ORM so you know what exactly happens when you reassign collections.

Avoiding Common Mistakes

1. Forgetting to commit

If you change attributes but do not call commit, nothing is persisted.

python 
user = session.get(User, user_id)
user.email = "new@example.com"
# No commit, so no SQL UPDATE is sent

In a request handling function, make sure you either:

2. Updating detached objects

Sometimes you create or load an object outside the current session. Then you try to commit:

python 
user = User(id=42, email="new@example.com")  # not loaded from session
session.add(user)
session.commit()

If the ORM thinks this is a new object, it might try to INSERT instead of UPDATE. To update an existing row without loading it, many ORMs have a special method, for example "merge", or you use a query based update.

Better, load the object from the session first, then modify:

python 
user = session.get(User, 42)
user.email = "new@example.com"
session.commit()

3. Accidentally overwriting data

If you convert an entire request body to a model and assign it directly, you may overwrite fields that were not part of the request or that the user should not control.

Instead, explicitly choose which fields can be updated:

python 
allowed_fields = ["name", "email"]
for field in allowed_fields:
    if field in data:
        setattr(user, field, data[field])

This is called a whitelist approach and is safer.

When to Use ORM Updates vs Raw SQL

Most of the time, ORM based updates are enough and make your code clearer. However, sometimes raw SQL is better.

Use ORM style updates when:

Use raw SQL or bulk operations when:

You can always mix approaches in one application. The key is to be consistent within a given use case and to document when you bypass the ORM.

Summary

With these patterns, you can safely and predictably change data through your ORM, which is essential for building reliable backend applications.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3694 / 24h
Impressum / Imprint
Datenschutz / Data Policy