python - SQLAlchemy, get object not bound to a Session

Question

I am trying to get an collection of objects out of a database and pass it to another process that is not connected to the database. My code looks like the one below but I keep getting:

sqlalchemy.exc.UnboundExecutionError: Instance <MyClass at 0x8db7fec> is not bound to a Session; attribute refresh operation cannot proceed

When I try to look at the elements of my list outside of the get_list() method.

def get_list (obj):
    sesson = Session()
    lst = session.query(MyClass).all()
    session.close()
    return lst

However, if I use this:

def get_list_bis (obj)
    session = Session()
    return session.query(MyClass).all()

I am able to use the elements but worry about the state of the session since it was not closed.

What am I missing here?

I'm fairly certain that there was more to the original code, unless SQLA has changed since this was written, because simply closing a session would expunge remaining objects and not expire attributes. Committing or rollbacking on the other hand does expire attrs. — Ilja Everilä
@IljaEverilä This was ask nearly 8 years ago. I am pretty sure SQLAlchemy did change in that time⸮ — Sardathrion - against SE abuse
Just to be sure I tested on 0.5 (11 years old), but cannot reproduce. Closing does not expire attributes, even back then, and as noted in the comments to the accepted answer Session.close() does (and did back then) expunge_all() implicitly. — Ilja Everilä
But, perhaps what happened was that there was a lazily loaded relationship attribute that was accessed after closing the session, which would then cause the above error. This is why it'd help in SQLA questions, if the models in question were a part of the question. — Ilja Everilä
@IljaEverilä That is possible. However, I am not sure I still have that code around. Like I said, it was years ago. ☹ — Sardathrion - against SE abuse

Pavel Repin Pavel Repin · Accepted Answer · 2011-11-24T09:06:12

If you want a bunch of objects produced by querying a session to be usable outside the scope of the session, you need to expunge them for the session.

In your first function example, you will need to add a line:

session.expunge_all()

before

session.close()

More generally, let's say the session is not closed right away, like in the first example. Perhaps this is a session that is kept active during entire duration of a web request or something like that. In such cases, you don't want to do expunge_all. You will want to be more surgical:

for item in lst:
    session.expunge(item)

python - SQLAlchemy, get object not bound to a Session

3 Answers