Does SQLAlchemy support caching?

Question

Does SQLAlchemy support some kind of caching so if I repeatedly run the same query it returns the response from cache instead of querying the database? Is this cache automatically cleared when the DB is updated?

Or what's the best way to implement this on a CherryPy + SQLAlchemy setup?

Alice

Answer 1

We have a pretty comprehensive caching solution, as an example in conjunction with embedded hooks, in 0.6. It's a recipe to subclass Query, make it aware of Beaker, and allow control of query caching for explicit queries as well as lazy loaders via query options.

I'm running it in production now. The example itself is in the dist and the intro documentation is at http://www.sqlalchemy.org/docs/orm/examples.html#beaker-caching .

UPDATE: Beaker has now been replaced with dogpile caching: http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.dogpile_caching

Flannel

Answer 2

SQLAlchemy supports two types of caches:

1. Caching the result set so that repeatedly running the same query hits the cache instead of the database. It uses dogpile which supports many different backends, including memcached, redis, and basic flat files.

   Docs are here: http://docs.sqlalchemy.org/en/latest/orm/examples.html#module-examples.dogpile_caching

2. Caching the query object so that Python interpreter doesn't have to manually re-assemble the query string every time. These queries are called baked queries and the cache is called baked. Basically it caches all the actions sqlalchemy takes BEFORE hitting the database--it does not cut down on database calls. Initial benchmarks show speedups of up to 40% in query generation time at the tradeoff of a slight increase in code verbosity.

   Docs are here: http://docs.sqlalchemy.org/en/latest/orm/extensions/baked.html

Mortie

Does the caching of the result set not use the Identity map? I heard of dogpile for the first time in this context. Or is it somehow related to the Identity map? – Profess

dogpile caching hung our system. changing to "just using redis" worked fine. – Elicia

Answer 3

Not an answer to your second question, but from the comments in this link indicates that SQLAlchemy does not support cacheing : http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html

Raven said...

Does SQLAlchemy do any kind of internal caching?

For example, if you ask for the same data twice (or an obvious subset
of the initially requested data) will the database be hit once or twice?

I recently wrote a caching database abstraction layer for an
application and (while fun) it was a fair bit of work to get it to a
minimally functional state. If SQLAlchemy did that I would seriously
consider jumping on the bandwagon.

I've found things in the docs that imply something like this might be
going on, but nothing explicit.
4:36 PM

Jonathan Ellis said...

No; the author of SA [rightly, IMO] considers caching a separate concern.

What you saw in the docs is probably the SA identity map, which makes it so 
if you load an instance in  two different places, they will refer
to the same object. But the database will still be queried twice, so it is
not a cache in the sense you mean.

Ja

This link mail-archive.com/[email protected]/msg15667.html suggests/shows that the subsequent query doesn't use a query but returns the instance from the identity map but this only works if you are querying using the primary key. – Almire

Answer 4

Or use an application-level cache via weak references dicts (weakref.WeakValueDictionary), see an example here : http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject

Reputation