SQLAlchemy: use related object when session is closed

user_5308802 0
Viewed 599

I have many models with relational links to each other which I have to use. My code is very complicated so I cannot keep session alive after a query. Instead, I try to preload all the objects:

def db_get_structure():
    with Session(my_engine) as session:
        deps = {x.id: x for x in session.query(Department).all()}
        ...
        return (deps, ...)

def some_logic(id):
    struct = db_get_structure()
    return some_other_logic(struct.deps[id].owner)

However, I get the following error anyway regardless of the fact that all the objects are already loaded:

sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Department at 0x10476e780> is not bound to a Session; lazy load operation of attribute 'owner' cannot proceed

Is it possible to link preloaded objects with each other so that the relations will work after session get closed?

I know about joined queries (.options(joinedload(), but this approach leads to more code lines and bigger DB request, and I think this should be solved simpler, because all the objects are already loaded into Python objects. It's even possible now to request the related objects like struct.deps[struct.deps[id].owner_id], but I think the ORM should do this and provide shorter notation struct.deps[id].owner using some "cached load".

session sqlalchemy orm python
1 Answers

Whenever you access an attribute on a DB entity that has not yet been loaded from the DB, SQLAlchemy will issue an implicit SQL statement to the DB to fetch that data. My guess is that this is what happens when you issue struct.deps[struct.deps[id].owner_id].

If the object in question has been removed from the session it is in a "detached" state and SQLAlchemy protects you from accidentally running into inconsistent data. In order to work with that object again it needs to be "re-attached".

I've done this already fairly often with session.merge:

attached_object = new_session.merge(detached_object)

But this will reconile the object instance with the DB and potentially issue updates to the DB if necessary. The detached_object is taken as "truth".

I believe you can do the reverse (attaching it by reading from the DB instead of writing to it) by using session.refresh(detached_object), but I need to verify this. I'll update the post if I found something.

Both ways have to talk to the DB with at least a select to ensure the data is consistent.

In order to avoid loading, issue session.merge(..., load=False). But this has some very important cavetas. Have a look at the docs of session.merge() for details.

I will need to read up on your link you added concerning your "complicated code". I would like to understand why you need to throw away your session the way you do it. Maybe there is an easier way?

user_1606650
Related