CODEWITHSUNDEEP
sqlalchemy
Peter Moore
Peter Moore

Reputation: 2086

Can SQLAlchemy cache updates

I would like to use SQLalchemy to update rows in a potentially large table using the ORM interface. But im not clear about something.

My question is, assuming I have one session doing repeated selects and some updates, are these updates happening in the database or are they sometimes buffered in some ORM cache object?

For example, If I'm repeating a select with a session which yields 10 rows like this:

session.query('User').filter(User.val == 42, User.surname == 'Dent')

.. but I only update the last row of that 10,

  1. Will the ORM always fetch the same 10 rows from DB or will it use cached values?
  2. will any caching behavior change with transaction markers?

Upvotes: 0

Views: 603

Answers (1)

univerio
univerio

Reputation: 20518

It's not clear what you mean by "use cached values for updates", but I'll try to outline the behavior in general:

query = session.query(User).filter(...)

This does not do anything with the database. This constructs a Query object that represents your query.

users = query.all()
# or
for user in query:
    ...

This will always execute a SELECT query to fetch the rows from the database, converts them into in-memory objects, and puts them into the identity map. (This is maybe what you mean by a cache.)

user = users[0]
user.surname = "Bar"

This records the fact that you want to update the surname column to "Bar", but it doesn't actually do anything to the database.

session.flush()

This issues UPDATE queries to the database.

session.commit()

This flushes if there's any pending changes, issues a COMMIT query, and (by default) expires all instances in the identity map so that doing user.surname at this point issues a SELECT query (but just for that row, i.e. SELECT ... WHERE id = ...).

Upvotes: 1

Related Questions