Introduction

To update a row in SQLAlchemy, query the object, modify its attributes, and call session.commit(). For bulk updates, use session.query(Model).filter(...).update({...}) which generates a single UPDATE statement without loading objects into Python. SQLAlchemy supports both the ORM pattern (load-modify-commit) and the Core pattern (direct SQL expressions). The ORM approach is simpler for single-row updates; the Core/bulk approach is faster for updating many rows at once.

ORM Update: Query, Modify, Commit

1from sqlalchemy import create_engine
2from sqlalchemy.orm import Session, declarative_base, Mapped, mapped_column
3
4Base = declarative_base()
5
6class User(Base):
7    __tablename__ = 'users'
8    id: Mapped[int] = mapped_column(primary_key=True)
9    name: Mapped[str] = mapped_column()
10    email: Mapped[str] = mapped_column()
11    age: Mapped[int] = mapped_column()
12
13engine = create_engine('sqlite:///app.db')
14
15# Update a single row
16with Session(engine) as session:
17    user = session.query(User).filter_by(id=1).first()
18    if user:
19        user.name = "New Name"
20        user.email = "[email protected]"
21        session.commit()

SQLAlchemy tracks changes to loaded objects automatically. When you modify user.name, the session marks the object as "dirty." commit() generates and executes the UPDATE statement.

Using session.get() for Primary Key Lookup

1with Session(engine) as session:
2    # More efficient for primary key lookups — checks identity map first
3    user = session.get(User, 1)
4    if user:
5        user.age = 30
6        session.commit()

session.get() checks the identity map (in-memory cache) before hitting the database, making it faster than query().filter_by() when you have the primary key.

Bulk Update with filter().update()

1with Session(engine) as session:
2    # Update all users over age 30 — single SQL UPDATE statement
3    rows_updated = session.query(User).filter(User.age > 30).update(
4        {"email": "[email protected]"}
5    )
6    session.commit()
7    print(f"Updated {rows_updated} rows")
8
9    # Update with expressions
10    session.query(User).filter(User.age > 18).update(
11        {User.age: User.age + 1},
12        synchronize_session='evaluate'
13    )
14    session.commit()

filter().update() generates a single SQL statement: UPDATE users SET email='[email protected]' WHERE age > 30. No Python objects are loaded.

SQLAlchemy 2.0 Style Updates

1from sqlalchemy import select, update
2
3# Select-style query (2.0 syntax)
4with Session(engine) as session:
5    stmt = select(User).where(User.id == 1)
6    user = session.execute(stmt).scalar_one_or_none()
7    if user:
8        user.name = "Updated Name"
9        session.commit()
10
11# Bulk update with update() construct (2.0 syntax)
12with Session(engine) as session:
13    stmt = (
14        update(User)
15        .where(User.age < 18)
16        .values(email="[email protected]")
17    )
18    result = session.execute(stmt)
19    session.commit()
20    print(f"Updated {result.rowcount} rows")

Upsert (Insert or Update)

1from sqlalchemy.dialects.sqlite import insert
2
3with Session(engine) as session:
4    stmt = insert(User).values(id=1, name="Alice", email="[email protected]", age=25)
5    stmt = stmt.on_conflict_do_update(
6        index_elements=['id'],
7        set_={'name': stmt.excluded.name, 'email': stmt.excluded.email}
8    )
9    session.execute(stmt)
10    session.commit()

For PostgreSQL, use from sqlalchemy.dialects.postgresql import insert. The dialect-specific on_conflict_do_update generates an INSERT ... ON CONFLICT DO UPDATE statement.

Update with Relationships

1class Post(Base):
2    __tablename__ = 'posts'
3    id: Mapped[int] = mapped_column(primary_key=True)
4    title: Mapped[str] = mapped_column()
5    author_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
6
7with Session(engine) as session:
8    user = session.get(User, 1)
9    if user:
10        # Update the user
11        user.name = "Updated Author"
12
13        # Update related posts through the relationship
14        for post in user.posts:
15            post.title = f"[Updated] {post.title}"
16
17        # Single commit updates both user and posts
18        session.commit()

Common Pitfalls

• Forgetting to call session.commit(): SQLAlchemy does not auto-commit. Modified objects remain "dirty" in the session until you explicitly call commit(). Without it, no SQL is sent to the database and changes are lost when the session closes.
• Using synchronize_session=False without understanding the consequence: filter().update(synchronize_session=False) does not update objects already loaded in the session. If you later access those objects, they still have stale values. Use synchronize_session='evaluate' (default) or 'fetch' to keep the session in sync, or expire all with session.expire_all().
• Catching an exception without rolling back: If an error occurs after modifying objects but before committing, the session is in an inconsistent state. Always use session.rollback() in exception handlers, or use with Session(engine) as session: which auto-rolls-back on exceptions.
• Updating a detached object: Objects loaded in one session are "detached" after that session closes. Modifying a detached object and committing in a new session raises DetachedInstanceError. Use session.merge(obj) to re-attach an object to a new session before updating.
• Bulk update bypassing ORM events and hooks: filter().update() and the update() construct execute SQL directly, skipping before_update events, validation hooks, and __setattr__ overrides. If your model relies on ORM-level hooks, use the load-modify-commit pattern instead.

Summary

• Load an object with session.get() or session.query(), modify its attributes, and call session.commit()
• Use filter().update({...}) for efficient bulk updates that generate a single SQL statement
• Use SQLAlchemy 2.0 update() construct for type-safe, composable bulk updates
• Always commit after changes and use session.rollback() on errors
• Choose between ORM updates (triggers hooks, loads objects) and Core updates (fast, skips hooks) based on your needs