Detaching SQLAlchemy instance so no refresh happens

Question

I want to detach an instance of a class from my session but it should still be available for reading (without emitting a query). I have been scanning through the documentation for days now, but every approach I try leads to the message

DetachedInstanceError: Instance <MyModel at 0x36bb190> is not bound to a Session;
attribute refresh operation cannot proceed

I am working with the zope.sqlalchemy transaction manager in Pyramid. I want my object to be usable after the transaction has been committed. I only need it to read the "cached" value, i.e. those that were in it before the transaction was commited.

The only possible solution I could find was to wrap the class (or the attributes itself) and then track the changes manually (I could do that but it is really ugly and not at all Pythonic).

Is there a way I can prevent SQLAlchemy from trying to refresh these values?

As a fallback I would even be open to just returning None, as long as the above error doesn't get thrown after the transaction as been committed

Answer 1

You can do exactly this (for example for caching) by doing:

session.expunge(obj)

As per sqlalchemy documentation:

http://docs.sqlalchemy.org/en/rel_1_0/orm/session_api.html?highlight=expire#sqlalchemy.orm.session.Session.expunge

This will give you object that is in detached state that you can safely use - you need to remember you will not be able to read properties that would emit another query thus being tied to session - like relationship, this will end up with DetachedInstanceError.

By default a commit will issue an expire_all() which means all objects will refresh their state on read, by expunging them you detach them from the session so there should be no subsequent queries after you commit your transaction.

I would advise against disabling this functionality globally as the other comments suggest as Mike Bayer generally suggest it is a good idea and a sane default for most people that can save you headaches in long run.

Just expunge things explictly when you need them.