CRUD - Update Record

Updating a record is straightforward:

1. query the row
2. modify attributes
3. commit

Example

python

with app.app_context():
    user = User.query.filter_by(username="ravi").first()
    if user:
        user.username = "ravik"
        db.session.commit()

Common patterns

• Update timestamps on every change
• Validate before saving (especially on user-editable fields)

Handling missing rows

If a record doesn’t exist, decide your behavior:

• return 404 for web routes
• return 400 or 404 for APIs

Example in a route:

python

user = User.query.get_or_404(user_id)
user.username = new_username
db.session.commit()

Partial updates

In APIs, you often implement PATCH semantics.

Be careful to only update provided fields and validate them.

Updating is assignment plus commit

There is no update() call for a loaded object. You change the attribute and the session notices:

update.py

row = db.session.scalar(sa.select(User).where(User.name == "ada"))
row.age = 37
 
db.session.dirty        # IdentitySet([<User 1>])   <- the session is tracking it
db.session.commit()     # issues the UPDATE

Measured: db.session.dirty was non-empty before the commit, and the stored age was 37 afterwards.

diagram Diagram mermaid

  flowchart TD
  L["load an object"] --> M["mutate an attribute"]
  M --> D["session marks it dirty"]
  D --> C["commit()"]
  C --> U["UPDATE ... SET age=37 WHERE id=1"]
  D --> R["rollback()"]
  R --> X["the change is discarded"]

The unit-of-work pattern is what makes this work: the session compares each tracked object against the values it loaded, and writes only the columns that actually changed.

Bulk updates skip the objects entirely

bulk.py

result = db.session.execute(
    sa.update(User).where(User.age < 18).values(active=False)
)
db.session.commit()
result.rowcount        # how many rows the database matched

This is one statement, so it is far faster than loading rows and mutating them. The trade-off is that it bypasses the ORM: objects already in the session keep their old values, and any Python-level defaults or validation on the model do not run.

After a bulk update, loaded objects are stale

Measured: an object loaded with views = 1, then a raw UPDATE note SET views = 99, still reported views = 1. The session returns the instance it already has, from its identity map — it does not re-query.

refresh.py

db.session.expire(obj)      # next attribute access re-reads from the database
# obj.views -> 99
db.session.expire_all()     # same, for every object in the session

Update-or-create

upsert.py

user = db.session.scalar(sa.select(User).where(User.name == name))
if user is None:
    user = User(name=name)
    db.session.add(user)
user.age = age
db.session.commit()

Correct for a single writer, and racy under concurrency — two requests can both find None. A unique constraint turns the race into an IntegrityError you can catch and retry, which is the difference between a duplicate row and a handled collision.

See it move

sketch Loaded, mutated, committed p5.js Pause Restart

The session tracks a loaded object and writes only what changed. A bulk update bypasses it, leaving loaded objects stale until they are expired.

Check yourself

pch.quizTag pch.quizDefaultTitle

1. How do you update a loaded ORM object?

   A call obj.update(age=37) B assign to the attribute and commit; the session tracks the change and issues the UPDATE C call db.session.update(obj) D delete the row and insert a new one
   pch.quizShowAnswer

   B — assign to the attribute and commit; the session tracks the change and issues the UPDATE — Measured db.session.dirty holding the object after the assignment. The unit-of-work pattern compares against the loaded values and writes only the columns that changed.

2. An object is loaded with views = 1, then a raw UPDATE sets views = 99. What does obj.views report?

   A 99, because the session re-queries on access B 1, because the session returns the instance from its identity map C None, because the value is invalidated D it raises a StaleDataError
   pch.quizShowAnswer

   B — 1, because the session returns the instance from its identity map — Measured 1. Call db.session.expire(obj) — or expire_all() — so the next attribute access re-reads from the database, which then measured 99.

3. What is the trade-off of sa.update(...) as a bulk statement?

   A it cannot use a WHERE clause B it is much faster but bypasses the ORM, so loaded objects go stale and model-level defaults or validation do not run C it only works inside a transaction D it always updates every row
   pch.quizShowAnswer

   B — it is much faster but bypasses the ORM, so loaded objects go stale and model-level defaults or validation do not run — One statement replaces load-and-mutate, which is a large win on many rows. The cost is that nothing at the Python level participates.