Incorrect object state

[Former user]

Hello,

I have a table:

albums_table = Table('albums', metadata,
    sa.Column('id', sa.types.Integer, primary_key = True),
    sa.Column('st_id', sa.types.Integer),
    sa.Column('description', sa.types.UnicodeText, nullable = True),
    sa.Column('order_number', sa.types.Integer, nullable = False ),
    sa.Column('create_date', sa.types.DateTime, nullable = False)
)

class Album(object): pass
mapper(Album, albums_table)

So, when I do first query request I see results as expected:

alb = session.query(Album).filter(Album.st_id == st_id).filter(Album.create_date <= datetime.now()).one()
print alb.description


DEBUG:sqlalchemy.engine.base.Engine.0x...2e2c:Row (198, 262, u'TEST_0', 1667, datetime.datetime(2010, 7, 12, 14, 14, 50, 143986))
TEST_0

Then I modify data directly in database (some external tool) and after that script continue working and do the same query, but results are not as I expect:

alb = session.query(Album).filter(Album.st_id == st_id).filter(Album.create_date <= datetime.now()).one()
print alb.description


DEBUG:sqlalchemy.engine.base.Engine.0x...2e2c:Row (198, 262, u'!!! TEST !!!', 1667, datetime.datetime(2010, 7, 12, 14, 14, 50, 143986))
TEST_0

So, I see that SA fetched correct row, but Album object has incorrect attribute. How I can fix it?

Best regards, Anton

[Mike Bayer]

there's no bug here, and you want to Session.rollback() or Session.commit() your transaction and start a new one under normal usage, since SQLAlchemy assumes an isolated transaction. Here I assume you are using MySQL with MyISAM tables so there is no isolation in effect. To expire state without committing/rolling back, use Session.expire_all(), or alternatively Query.populate_existing() on a per-query basis. Please post further questions in this regard to the mailing list, thanks.

[Former user]

Thank you for reply,

I use PostgreSQL and see, that even after Session.commit() new query return correct results, but object have incorrect attributes. Example:

photo = Session.query(Photo).filter(Photo.id == 1).one()
photo.visible = True
Session.add(photo)
Session.commit()

after I do the query again:

photo = Session.query(Photo).filter(Photo.id == 1).one()
log.debug([photo.__getattribute__(p)) for p in dir(photo) if not p.startswith('_')]((p,))

and see in logs

17:15:45,913 INFO  [sqlalchemy.engine.base.Engine.0x...78ec](sqlalchemy.engine.base.Engine.0x...78ec) SELECT ent_photos.id AS ent_photos_id, ent_photos.alb_id AS ent_photos_alb_id, ent_photos.visible AS ent_photos_visible
FROM ent_photos 
WHERE ent_photos.id = %(id_1)s
17:15:45,913 INFO  [sqlalchemy.engine.base.Engine.0x...78ec](sqlalchemy.engine.base.Engine.0x...78ec) {'id_1': 1}
17:15:45,916 DEBUG [sqlalchemy.engine.base.Engine.0x...78ec](sqlalchemy.engine.base.Engine.0x...78ec) Col ('ent_photos_id', 'ent_photos_alb_id', 'ent_photos_visible')
17:15:45,916 DEBUG [sqlalchemy.engine.base.Engine.0x...78ec](sqlalchemy.engine.base.Engine.0x...78ec) Row (1, 201, True)

17:15:45,917 DEBUG [poject.controllers.adm](poject.controllers.adm) [201), ('album', <project.model.tables.Album object at 0xa61ffcc>), ('id', 1), ('visible', False)](('alb_id',)

so, the visible attribute in row object is correct (True), but incorrect in photo object (False).

I use SA in pylons projects and see this behavior both with mysql and postgres. additional config options is:

sqlalchemy.pool_size = 20
sqlalchemy.pool_timeout = 28800

version of SA 0.6.3, but in previous versions have the same problems.

[Mike Bayer]

No test case has been attached here which illustrates any issue -all you've given is a fragment. The fragment you've shown also looks nothing like the previous table illustration. If I put your fragment into a test, the behavior is as expected:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Photo(Base):
    __tablename__ = 'photo'

    id = Column(Integer, primary_key=True)
    visible = Column(Boolean)

e = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

Session = sessionmaker(e)()

photo = Photo(visible=False)
Session.add(photo)
Session.commit()

assert \
    sorted([photo.__getattribute__(p))) for p in dir(photo) if not p.startswith('_')](str((p,)) ==  \
    [1)", "('metadata', MetaData(None))", "('visible', False)"]("('id',)

photo = Session.query(Photo).filter(Photo.id == 1).one()
photo.visible = True

# this is not necessary
Session.add(photo)


Session.commit()

photo = Session.query(Photo).filter(Photo.id == 1).one()
assert \
    sorted([photo.__getattribute__(p))) for p in dir(photo) if not p.startswith('_')](str((p,)) ==  \
    [1)", "('metadata', MetaData(None))", "('visible', True)"]("('id',)

It seems most likely that the "visible" attribute on your class is not actually mapped, seeing that the name of the column in your SQL logs is "ent_photos_visible", and no UPDATE statement is present in the logs (but your SQL log, too, is only a fragment, so also not very useful). Perhaps you've mapped this column correctly, but we have no idea, since you did not take the time to give us a full test case like the "new ticket" instructions request, and we continue to waste time trying to guess. Please do not reopen this ticket unless you can fully illustrate a reproducible bug - for usage questions, as I mentioned earlier, please post on the mailing list.