How does a sqlalchemy object get detached?

Question

I have code structured something like this:

project
--app
----utils
------util.py
----__init__.py
----models.py
--tests
----__init__.py

Within tests/__init__.py I have code that initializes the application (flask if that matters) and database session by importing it all from app/__init__.py. I can create an instances of models, query, and access backrefs fine within tests/__init__.py. Code of the following form works fine:

objs = SomeModel.query.all()
for o in objs:
    o.backref

However, if I do something like:

from utils.util import some_function
objs = SomeModel.query.all()
for o in objs:
    some_function(o)

where some_function just accesses a backref

def some_function(obj):
    obj.backref

I get an error like DetachedInstanceError: Parent instance <SomeModel at 0x2c1fe10> is not bound to a Session; lazy load operation of attribute 'backref' cannot proceed

Reading the sqlalchemy docs suggests that I need to re-associate the object to a database sesssion. I did that and it looks like it works (i.e. running the function doesn't fail with the previous error):

import db_session
def some_function(obj):
    db_session.add(obj)
    obj.backref

So when exactly does an object get detached? It seems like just passing the object to a function in another module detaches it from a session. Does the object not know about the sqlalchemy session it is associated with? I'm trying to avoid doing db_session.add(obj) which seems like a lot of boilerplate code.

Answer 1

I came across this while working on my own similar question about attribute expiration & instance detachment. univerio gave a me a great answer, and from what I've been learning, I might be able to shed some light on your question.

In my case I was creating, committing or rolling back, and then closing a Session all within the scope of a single with...as... clause, then trying to access the instance I saved (the obj in your example) immediately afterward but outside the scope of that with clause. What happened was the Session was closed before I tried to reference the saved object. By default in SQLAlchemy, persisted attributes/objects cannot be accessed without an active Session, unless explicitly told to allow it. This is to "protect" code from accidentally or unknowingly using outdated/incorrect data, by forcing the application to query/retrieve the updated data first, which requires an associated Session. So in my case, leaving the Session open after committing meant the object could use that Session to query the database in case the record had been modified since it was first written.

In your case, the Session being used to get the objects via objs = SomeModel.query.all() is being closed or disconnected after the query but before obj.backref is called (though I'm not sure how; I don't know what SomeModel is, exactly, I'm assuming is a construct from Flask which incorporates a Session in its background). So obj no longer has a connection to the database and thus is "detached". By adding it to db_session you allow obj to reestablish connectivity to its source database, through which it can query to check for updated attributes, and so it's no longer detached.

Finally, it's worth mentioning that the DetachedInstanceError can be avoided by specifying the original Session to which obj was associated not to expire attributes automatically. By not expiring the obj, the error wouldn't be thrown, but obj would still be detached, meaning when you called obj.backref the returned value may be incorrect/outdated. You asked about detaching in your question, but expiration is a related, but not identical, concept.

Aside -- how to set the obj not to expire: Either at the Session's initialization

my_session = sessionmaker(expire_on_commit=False

the sessionmaker's initialization

my_sessionmaker = sqlalchemy.orm.sessionmaker(expire_on_commit=False)

or even after the Session is already instantiated

my_session.expire_on_commit = False

Answer 2

I had a similar issue while trying to implement an upsert operation:

objs = [SomeModel(id=1, value=2), SomeModel(id=2, value=4)]

for obj in objs:
    fltr_obj = session.query(SomeModel).where(SomeModel.id == obj.id)
    if fltr_obj.first() is None:
        # add if the object does not exist
        session.add(fltr)
    else:
        # update if the object exists
        fltr_obj.update({"id": obj.id, "value": obj.value})

session.commit()

and I got detachedInstanceError. I have fixed this error by replacing the latter with

objs = [SomeModel(id=1, value=2), SomeModel(id=2, value=4)]
for obj in objs:
    session.merge(obj)

session.commit()