SQLAlchemy DetachedInstanceError with regular attribute (not a relation)

I had a similar problem with the DetachedInstanceError: Instance <> is not bound to a Session;

The situation was quite simple, I pass the session and the record to be updated to my function and it would merge the record and commit it to the database. In the first sample I would get the error, as I was lazy and thought that I could just return the merged object so my operating record would be updated (ie its is_modified value would be false). It did return the updated record and is_modified was now false but subsequent uses threw the error. I think this was compounded because of related child records but not entirely sure of that.

        def EditStaff(self, session, record):
            try:
                    r = session.merge(record)
                    session.commit()
                    return r
            except:
                    return False

After much googling and reading about sessions etc, I realized that since I had captured the instance r before the commit and returned it, when that same record was sent back to this function for another edit/commit it had lost its session.

So to fix this I just query the database for the record just updated and return it to keep it in session and mark its is_modified value back to false.

        def EditStaff(self, session, record):
            try:
                    session.merge(record)
                    session.commit()
                    r = self.GetStaff(session, record)
                    return r
            except:
                    return False

Setting the expire_on_commit=False also avoided the error as mentioned above, but I don't think it actually addresses the error, and could lead to many other issues IMO.

We were getting similar errors, even with expire_on_commit set to False. In the end it was actually caused by having two sessionmakers that were both getting used to make sessions in different requests. I don't really understand what was going on, but if you see this exception with expire_on_commit=False, make sure you don't have two sessionmakers initialized.

I found the root cause while trying to narrow down the code that caused the exception. I placed the same attribute access code at different places after session close and found that it definitely doesn't cause any issue immediately after the close of query session. It turns out the problem starts appearing after closing a fresh session that is opened to update the object. Once I understood that the state of the object is unusable after a session close, I was able to find this thread that discussed this same issue. Two solutions that come out of the thread are:

• Keep a session open (which is obvious)
• Specify expire_on_commit=False to sessionmaker().

The 3rd option is to manually set expire_on_commit to False on the session once it is created, something like: session.expire_on_commit = False. I verified that this solves my issue.