SQLAlchemy get items from the identity map not only by primary key

Question

Is it possible to use a couple of fields not from the primary key to retrieve items (already fetched earlier) from the identity map? For example, I often query a table by (external_id, platform_id) pair, which is a unique key, but not a primary key. And I want to omit unnecessary SQL queries in such cases.

Answer 1

A brief overview of identity_map and get():

An identity map is kept for a lifecycle of a SQLAlchemy's session object i.e. in case of a web-service or a RESTful api the session object's lifecycle is not more than a single request (recommended).

From : http://martinfowler.com/eaaCatalog/identityMap.html

An Identity Map keeps a record of all objects that have been read from the database in a single business transaction. Whenever you want an object, you check the Identity Map first to see if you already have it.

In SQLAlchemy's ORM there's this special query method get(), it first looks into identity_map using the pk (only allowed argument) and returns object from the identity map, actually executing the SQL query and hitting the database.

From the docs:

get(ident)

Return an instance based on the given primary key identifier, or None if not found.

get() is special in that it provides direct access to the identity map of the owning Session. If the given primary key identifier is present in the local identity map, the object is returned directly from this collection and no SQL is emitted, unless the object has been marked fully expired. If not present, a SELECT is performed in order to locate the object.

---

Only get() is using the identity_map - official docs:

It’s somewhat used as a cache, in that it implements the identity map pattern, and stores objects keyed to their primary key. However, it doesn’t do any kind of query caching. This means, if you say session.query(Foo).filter_by(name='bar'), even if Foo(name='bar') is right there, in the identity map, the session has no idea about that. It has to issue SQL to the database, get the rows back, and then when it sees the primary key in the row, then it can look in the local identity map and see that the object is already there. It’s only when you say query.get({some primary key}) that the Session doesn’t have to issue a query.

---

P.S. If you're querying not using pk, you aren't hitting the identity_map in the first place.

---

Few relevant SO questions, helpful to clear the concept:

Forcing a sqlalchemy ORM get() outside identity map

Answer 2

It's possible to access the whole identity map sequentially:

for obj in session.identity_map.values():
    print(obj)

To get an object by arbitrary attributes, you then have to filter for the object type first and then check your attributes.

It's not a lookup in constant time, but can prevent unnecessary queries.

There is the argument, that objects may have been modified by another process and the identity map doesn't hold the current state, but this argument is invalid: If your transaction isolation level is read committed (or less) - and this is often the case, data ALWAYS may have been changed immediately after the query is finished.