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ä, Nov 14 '18 at 11:24
@IljaEverilä This was ask *nearly 8* years ago. I am pretty sure SQLAlchemy did change in that time⸮ — Sardathrion - against SE abuse, Oct 15 '19 at 10:08
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ä, Oct 16 '19 at 10:33
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ä, Oct 16 '19 at 10:41
@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, Oct 16 '19 at 12:34

score 95 · Accepted Answer · edited Jul 18 '16 at 09:51

95

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)

edited Jul 18 '16 at 09:51

Zitrax

19,036
20
88
110

answered Nov 24 '11 at 09:06

Pavel Repin

30,663
1
34
41

29

But... but... "The close() method issues a expunge_all(), and releases any transactional/connection resources." This statement is on the page you referenced, back at least as far as version 0.6. – Oddthinking Nov 16 '13 at 11:51
12

As the above comment notes, this answer is not entirely correct. Simply closing a session should leave the attributes stale, but usable. `Session.commit()` and `Session.rollback()` expire ORM loaded state, given default configuration, and expunging before a commit prevents the expiry, leaving the attributes in their stale state. – Ilja Everilä Nov 14 '18 at 11:13
6

I also found this does not help. I expunge and or close and doesn't help. I understand its stale and not connected to db, so what! I want to lookup, and close. db session asap to give connection back to pool and keep working with the "stale" object just to return to api response as json, very weird this has to throw an exception. – bjm88 Jan 29 '21 at 14:39

score 46 · Answer 2 · edited Sep 03 '19 at 10:58

46

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...

edited Sep 03 '19 at 10:58

SuperShoot

9,880
2
38
55

answered May 10 '18 at 12:14

Antash

878
6
15

This worked for me : session_maker = sessionmaker(bind=engine, expire_on_commit=False). But it is worth going through https://groups.google.com/g/sqlalchemy/c/uYIawg4SUQQ before using it. – Ravindra Nov 02 '22 at 13:31

score 3 · Answer 3 · edited Sep 03 '19 at 11:00

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

edited Sep 03 '19 at 11:00

SuperShoot

9,880
2
38
55

answered Sep 05 '14 at 21:39

kolypto

31,774
17
105
99

8

In any case, you shouldn't be using `map` if you don't care about the result. – ThiefMaster Apr 09 '15 at 12:16

SQLAlchemy, get object not bound to a Session

3 Answers3

Linked

Related