SQLAlchemy DetachedInstanceError with regular attribute (not a relation)

I just started using SQLAlchemy and get a DetachedInstanceError and can't find much information on this anywhere. I am using the instance outside a session, so it is natural that SQLAlchemy is unable to load any relations if they are not already loaded, however, the attribute I am accessing is not a relation, in fact this object has no relations at all. I found solutions such as eager loading, but I can't apply to this because this is not a relation. I even tried "touching" this attribute before closing the session, but it still doesn't 开发者_如何学编程prevent the exception. What could be causing this exception for a non-relational property even after it has been successfully accessed once before? Any help in debugging this issue is appreciated. I will meanwhile try to get a reproducible stand-alone scenario and update here.

Update: This is the actual exception message with a few stacks:

  File "/home/hari/bin/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/attributes.py", line 159, in __get__
    return self.impl.get(instance_state(instance), instance_dict(instance))
  File "/home/hari/bin/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/attributes.py", line 377, in get
    value = callable_(passive=passive)
  File "/home/hari/bin/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/state.py", line 280, in __call__
    self.manager.deferred_scalar_loader(self, toload)
  File "/home/hari/bin/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/orm/mapper.py", line 2323, in _load_scalar_attributes
    (state_str(state)))
DetachedInstanceError: Instance <ReportingJob at 0xa41cd8c> is not bound to a Session; attribute refresh operation cannot proceed

The partial model looks like this:

metadata = MetaData()
ModelBase = declarative_base(metadata=metadata)

class ReportingJob(ModelBase):
    __tablename__ = 'reporting_job'

    job_id         = Column(BigInteger, Sequence('job_id_sequence'), primary_key=True)
    client_id      = Column(BigInteger, nullable=True)

And the field client_id is what is causing this exception with a usage like the below:

Query:

    jobs = session \
            .query(ReportingJob) \
            .filter(ReportingJob.job_id == job_id) \
            .all()
    if jobs:
        # FIXME(Hari): Workaround for the attribute getting lazy-loaded.
        jobs[0].client_id
        return jobs[0]

This is what triggers the exception later out of the session scope:

        msg = msg + ", client_id: %s" % job.client_id

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.

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

To throw my cause & solution into the ring, I use flask and flask-sqlalchemy to manage all my session stuff. This is fine when I'm doing things via the site, but when doing things via command line and scripts, you have to ensure that anything that's doing flask-y things has to do it with the flask context.

So, in my situation, I needed to get things from a database (using flask-sqlalchemy), then render them to templates (using flask's render_template), then email them (using flask-mail).

In code, what I'd done was something like,

def render_obj(db_obj):
  with app.app_context():
    return render_template('template_for_my_db_obj.html', db_obj=db_obj

def get_renders():
  my_db_objs = MyDbObj.query.all()

  renders = []
  for day, _db_objs in itertools.groupby(my_db_objs, MyDbObj.get_date):
    renders.extend(list(map(render_obj, _db_obj)))

  return renders

def email_report():
  renders = get_renders()
  report = '\n'.join(renders)

  with app.app_context():
    mail.send(Message('Subject', ['me@me.com'], html=report))

(this is basically pseudocode, I was doing other things in the grouping section)

And when I was running, I'd get through the first _db_obj, but then I'd get the error on any run after.

The culprit? with app.app_context().

Basically it does a few things when you come out of that context, including kinda freshening up the db connections. One of the things that comes from that is getting rid of the last session that was around, which was the session that all the my_db_objs were associated with.

There's a few different options for solutions, but I went with a variant of,

def render_obj(db_obj):
  return render_template('template_for_my_db_obj.html', db_obj=db_obj

def get_renders():
  my_db_objs = MyDbObj.query.all()

  renders = []
  for day, _db_objs in itertools.groupby(my_db_objs, MyDbObj.get_date):
    renders.extend(list(map(render_obj, _db_obj)))

  return renders

def email_report():
  with app.app_context():
    renders = get_renders()
    report = '\n'.join(renders)

    mail.send(Message('Subject', ['me@me.com'], html=report))

Only 1 with app.app_context() which wraps them all. The main thing you need to do (if you've a setup like mine) is ensure any dB object you're using to be "inside" any app_context you're using. If you do what I did in the first iteration, all your dB objects will lose their session, ending in DetachedInstanceError like me.

My solution was a simple oversight;

I created an object, added and ,committed it to the db and after that I tried to access on of the original object attributes without refreshing session session.refresh(object)

user = UserFactory()
session.add(user)
session.commit()

# missing session.refresh(user) and causing the problem

print(user.name)

My solution to this error was also a simple oversight, which I don't think any of the other answers cover.

My function is fetching object x, modifying it, then returning the original x, because I would like the older version.

Before committing and returning x, I was calling expunge_all, but it was "too late", as the object was already marked dirty.

The solution was simply to expunge the object as early as possible.

# pseudo code
x = session.fetch_x()
# adding the following line fixed it
session.expunge(x)
y = session.update(x)
return x

I have a similar problem in my current project and this fix works for me. Please check in your DB relationship for options lazy=True and change it to lazy='dynamic'.

As for me (newbie), I made a mistake on the indent and close the session inside my loop, in which I loop each row, do some operation and commit each time.

So for those newbie like me, check your code before setting things like expire_on_commit=False, it may lead your to another trap.