Sqlalchemy session.refresh does not refresh object

Question

I have the following mapping (straight from SA examples):

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

I'm working with a MySql DB and the table has an innoDB engine.

I have a single record in my table: 1|'user1'|'user1 test'|'password'

I've opened a session with the following code:

from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.scoping import scoped_session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

db_engine = create_engine('mysql://...@localhost/test_db?charset=utf8',echo=False,pool_recycle=1800)
session_factory = sessionmaker(bind=db_engine,autocommit=False,autoflush=False)
session_maker = scoped_session(session_factory)
session = session_maker()

user_1 = session.query(User).filter(User.id==1).one()
user_1.name # This prints: u'user1'

Now, when I change the record's name in the DB to 'user1_change' and commit it and then refresh the object like this:

session.refresh(user_1)
user_1.name # This still prints: u'user1' and not u'user1_change'

It still prints: u'user1' and not u'user1_change'.

What am I missing (or setting up wrong) here?

Thanks!

Answer 1

From the docs:

Note that a highly isolated transaction will return the same values as were previously read in that same transaction, regardless of changes in database state outside of that transaction

SQLAlchemy uses a transactional unit of work model, wherein each transaction is assumed to be internally consistent. A session is an interface on top of a transaction. Since a transaction is assumed to be internally consistent, SQLAlchemy will only (well, not quite, but for ease of explanation...) retrieve a given piece of data from the database and update the state of the associated objects once per transaction. Since you already queried for the object in the same session transaction, SQLAlchemy will not update the data in that object from the database again within that transaction scope. If you want to poll the database, you'll need to do it with a fresh transaction each time.