TopolearnKnowing when to modify objects and when to issue one bulk statement is the difference between clean code and thousands of needless queries.
With the ORM you update rows by changing attributes on loaded objects. The Session notices the change (the object becomes "dirty") and emits an UPDATE for just the modified columns at the next flush or commit. There's no save() call.
To remove a row, call session.delete(obj); the DELETE runs at flush. Relationship cascades (covered later) can delete related objects too.
Loading every object just to change one field is wasteful for many rows. For bulk changes, execute a DML statement through the session:
session.execute(update(User).where(User.active == False).values(status="archived"))session.execute(delete(User).where(User.last_login < cutoff))
These run one SQL statement regardless of row count. Objects already loaded in the session are kept in sync where possible: by default the ORM evaluates the WHERE clause in Python against loaded objects (synchronize_session="auto"). The result's rowcount tells you how many rows matched.
Bulk statements skip per-object Python logic such as ORM-level cascades and attribute events, so use them for data changes, not when your model logic must run. Either way, the changes are only permanent after commit().
Example
from sqlalchemy import create_engine, select, update, delete
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
active: Mapped[bool] = mapped_column(default=True)
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s:
s.add_all([User(name="ada"), User(name="bob"), User(name="cy")])
s.commit()
ada = s.get(User, 1)
ada.name = "Ada" # dirty: UPDATE at next flush
s.delete(s.get(User, 3)) # DELETE at next flush
r = s.execute(update(User).where(User.name == "bob").values(active=False))
print(r.rowcount) # 1
s.commit()
print(s.execute(select(User.name, User.active)).all())
# [('Ada', True), ('bob', False)]Remember
- Change attributes on loaded objects; the Session emits UPDATE at flush
- session.delete(obj) removes a row at flush
- update()/delete() statements change many rows in one SQL statement
- Bulk statements bypass per-object ORM logic like cascades
Watch out for
- Looping over 100,000 loaded objects to set a flag instead of one update() statement.