Inaccessible ORM attributes data after commit-close

[Former user]

I'm seeing odd behavior with sessions, commits, and attribute accesses. Perhaps this is expected, but I don't understand why and it does not seem obvious from the documentation.

The following is a test case:

import sqlalchemy as sa
import sqlalchemy.orm

dbUri = 'mysql://...'

engine = sa.create_engine( dbUri )
meta = sa.MetaData()
meta.bind = engine
meta.reflect()

class lcd_PlanManager( object ): pass

sa.orm.mapper( lcd_PlanManager, meta.tables[ 'lcd_PlanManager' ] )
session = sa.orm.sessionmaker( bind = engine )()

pm = session.query( lcd_PlanManager ).first()
#session.commit()
#session.rollback()
session.close()
print 'pm.id', pm.id

Assume that there is a table with the following definition (though the details of the table don't seem particularly relevant):

CREATE TABLE `lcd_PlanManager` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `host` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `createdAt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Running the above code on the table results in:

$ python test_session.py
pm.id 1

However, if either the commit or rollback is uncommented, the behavior becomes:

$ python test_session.py
Traceback (most recent call last):
  File "test_session.py", line 20, in <module>
    print 'pm.id', pm.id
  File "/home/y/lib/python2.6/site-packages/sqlalchemy/orm/attributes.py", line 158, in __get__
    return self.impl.get(instance_state(instance), instance_dict(instance))
  File "/home/y/lib/python2.6/site-packages/sqlalchemy/orm/attributes.py", line 377, in get
    value = callable_()
  File "/home/y/lib/python2.6/site-packages/sqlalchemy/orm/state.py", line 185, in __call__
    attr.impl.key in unmodified
  File "/home/y/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py", line 1845, in _load_scalar_attributes
    "attribute refresh operation cannot proceed" % (state_str(state)))
sqlalchemy.exc.UnboundExecutionError: Instance <lcd_PlanManager at 0xb791230c> is not bound to a Session; attribute refresh operation cannot proceed

It seems that the pm.dict dictionary becomes unloaded after the commit and then can't be reloaded after the close. Merging the object back into the session seems to work but also seems like it should not be necessary.

This now makes it impossible to grab some data from the DB, disconnect, perform some long operation using the ORM object(s) in a safe and offline way, and then reconnect to the DB to do the next unit. If it's not legitimate to do this (may have missed that in the docs?), why does the commit/rollback matter?

I'm not sure why this happens in SqlAlchemy 0.5.6. We've been using 0.4.6 and this has not been an issue.

[Former user]

oops, pm.dict should have been {{{pm.dict}}}

[Mike Bayer]

Replying to guest:

I'm seeing odd behavior with sessions, commits, and attribute accesses. Perhaps this is expected, but I don't understand why and it does not seem obvious from the documentation.

#session.commit() #session.rollback() session.close() print 'pm.id', pm.id

However, if either the commit or rollback is uncommented, the behavior becomes:

commit() and rollback() fully expire the contents of the session. Documentation is here:

http://www.sqlalchemy.org/docs/05/session.html

When you close() the session, "pm" has no internal state and is no longer associated with database resources, so cannot return information about its in-database state until reattached.

This now makes it impossible to grab some data from the DB, disconnect, perform some long operation using the ORM object(s) in a safe and offline way, and then reconnect to the DB to do the next unit. If it's not legitimate to do this (may have missed that in the docs?), why does the commit/rollback matter?

you should merge() state back to a session once you're ready to use it again, assuming you'd like it to remain synchronized with the state of an ongoing transaction. Otherwise, if you'd like to work with the state as detached from whatever is going on with the database, you can detach the state from the session before committing or rolling back, use the expire_on_commit=False flag to disable the expiration behavior on commit, or just don't call commit/rollback - only call close().

I'm not sure why this happens in SqlAlchemy 0.5.6. We've been using 0.4.6 and this has not been an issue.

The entire behavioral change from 0.4 to 0.5 is significant, and is mentioned in the 05Migration document. It is the optimal behavior in that it sets boundaries that most closely correspond to the transaction, and when serializable isolation is used makes it nearly impossible to load stale data from a previous transaction that may have been overwritten - when a new transaction starts, state is transparently re-loaded into the model as its requested, without the need to clear out all the objects and start over again.

Since this is a usage question more appropriate for the mailing list, closing this ticket.