CODEWITHSUNDEEP
pythonpython-3.xsqlalchemy
Cyril N.
Cyril N.

Reputation: 39899

SQLAlchemy with multiple session, how to avoid "Object is already attached to session X"?

I have a special configuration where I've created two SQLAlchemy Sessions from the sessionmaker :

db.read = sessionmaker(autoflush=False)
db.write = sessionmaker()

When calling db.write.add(obj), I sometimes have the following error (but not always):

sqlalchemy.exc.InvalidRequestError: Object '<User at 0x7f3fa9ebee50>' is already attached to session '14' (this is '15')

Is there a way to do the following :

"If session is db.write, then call db.write.add(obj) Otherwise, add obj to the db.write session in order to save it, and (important), update both the object in db.write session AND db.read session"

(I've managed to save to via write, but then, obj.id is empty in db.read)

Thank you for your help

Upvotes: 2

Views: 3495

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55924

We can approximate the desired behaviour by extending Session.add to detect and handle the case when an object is already in another session, and by adding an event listener to reinstate the object into the read session once it has been committed. It isn't possible for an object to exist in two sessions simultaneously: if it's preferable that the object remains in the write session the listener and the _prev_session attribute are not required.

import weakref

import sqlalchemy as sa
from sqlalchemy import orm

...

class CarefulSession(orm.Session):
    def add(self, object_):
        # Get the session that contains the object, if there is one.
        object_session = orm.object_session(object_)
        if object_session and object_session is not self:
            # Remove the object from the other session, but keep
            # a reference so we can reinstate it.
            object_session.expunge(object_)
            object_._prev_session = weakref.ref(object_session)
        return super().add(object_)

# The write session must use the Session subclass; the read 
# session need not.
WriteSession = orm.sessionmaker(engine, class_=CarefulSession)


@sa.event.listens_for(WriteSession, 'after_commit')
def receive_after_commit(session):
    """Reinstate objects into their previous sessions."""
    objects = filter(lambda o: hasattr(o, '_prev_session'), session.identity_map.values())
    for object_ in objects:
        prev_session = object_._prev_session()
        if prev_session:
            session.expunge(object_)
            prev_session.add(object_)
            delattr(object_, '_prev_session')

A complete implementation might add an after_rollback listener, and perhaps initialise _prev_session in the base model's __init__ to avoid the del/has attr calls.

Upvotes: 3

Related Questions