SQLAlchemy ORM更新缓存疑问：会话中更新是直接落库还是被ORM缓存缓冲？

Great question—this is a super common point of confusion when working with SQLAlchemy's ORM, especially when dealing with larger datasets. Let's break down exactly what's happening:

1. The Session's Identity Map Cache #

When you run a query like session.query(User).filter(User.val == 42, User.surname == 'Dent'), SQLAlchemy:

• Executes the SQL query against the database
• Creates Python objects for each row returned
• Stores these objects in the Session's identity map (a built-in cache)

Any subsequent queries in the same Session targeting the same User records (by primary key) will pull objects directly from this cache instead of hitting the database again. Even re-running the exact filter query will check the cache first before executing new SQL—though note: if other transactions modified the data, you won't see those changes unless you refresh the objects or expire the cache.

2. Updates Are Buffered, Not Immediate #

When you modify one of the cached User objects (e.g., user.val = 100), that change does NOT get written to the database right away. Instead:

• The object is marked as "dirty" in the Session
• The change is buffered in memory until you trigger a flush or commit.

When Do Changes Get Written to the Database? #

• session.commit(): The most common trigger. Committing first flushes all pending changes (generates and runs UPDATE/INSERT/DELETE statements) to the database, then finalizes the transaction. All your buffered updates will be written at this point.
• session.flush(): If you want to push changes to the database without committing the transaction, call flush(). This executes all pending SQL, but the transaction stays open—other sessions won't see these changes until you commit.
• Automatic flushes: SQLAlchemy will sometimes auto-flush pending changes before a query if it determines the query might be affected by unwritten changes. For example, updating a User's val then querying on val will trigger a flush first to ensure accurate results.

3. Your Specific Scenario #

In your case:

• Re-running the query to get 10 User rows will pull from the identity map after the first query (no extra database hits)
• Modifying only some rows marks those specific objects as dirty
• Those updates stay buffered in the Session's memory until you call commit() or flush()—they won't hit the database until then.

Pro Tip for Large Datasets #

If you're working with a very large table, loading hundreds/thousands of objects into the Session's cache can eat up memory. For bulk updates that don't require interacting with Python objects directly, use SQLAlchemy's bulk update method instead:

session.query(User).filter(User.val == 42, User.surname == 'Dent').update({User.val: 100})

This generates a single UPDATE SQL statement that runs directly against the database, no objects loaded into the cache—way more efficient for large-scale changes.

Quick Recap #

• Session caches queried objects in the identity map
• Object modifications are buffered as "dirty" changes
• Changes are written to the database only on commit(), flush(), or automatic flush
• Use bulk updates for large datasets to avoid memory overhead

Hope that clears up your confusion!

内容的提问来源于stack exchange，提问作者Peter Moore