SQLAlchemy, get object not bound to a Session

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?

Solution 1:

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)

Solution 2:

This often happens due to objects being in expired state, objects get expired for example after committing, then when such expired objects are about to get used the ORM tries to refresh them, but this cannot be done when objects are detached from session (e.g. because that session was closed). This behavior can be managed by creating session with expire_on_commit=False param.

>>> from sqlalchemy import inspect
>>> insp = inspect(my_object)
>>> insp.expired
True  # then it will be refreshed...

Solution 3:

In my case, I was saving a related entity as well, and this recipe helped me to refresh all instances within a session, leveraging the fact that Session is iterable:

map(session.refresh, iter(session))  # call refresh() on every instance

This is extremely ineffective, but works. Should be fine for unit-tests.

Final note: in Python3 map() is a generator and won't do anything. Use real loops of list comprehensions