Why is my data not being represented properly in my SQLAchemy model?

Question

I have a peculiar SQLAlchemy ORM problem. This is occurring in a Pylons application, against a Postgresql 8.2 database using psycopg2 as my database driver under SQLAlchemy 0.6.0 (and tried with 0.6.4 as well)

I have defined a User model object that has (at minimum) the following properties:

class User(Base):
    __tablename__ = 'users'

    __table_args__ = (saschema.UniqueConstraint("login", "company_id"), {})

    __mapper_args__ = {
      'order_by' : 'lower(users.name)',
      }

    user_id = Column(Integer, primary_key=True)
    email = Column(String)
    login = Column(String)
    company_id = Column(String, ForeignKey('company.company_id'))

There are others, but those are all that are germane, I think.

I am editing an instance of this in a session using this code (it deals with some of those other properties):

def do_update(user_id):
    existing = Session().query(User).filter_by(user_id=user_id).one()

    for field in ('login', 'email', 'name', 'is_admin_user'): # only email changes; it's set to "" from "[email protected]"
        if field in params:
            setattr(existing, field, params[field])

    if 'advanis_portal_user_id' in params:
        if not existing.portal_link:
            existing.portal_link = UserPortalLink()
        existing.portal_link.advanis_portal_user_id = params['advanis_portal_user_id']

    if 'password' in params and existing.password:
        existing.password.password = Password.encrypt(existing.login, params['password'])

    UserValidator(existing) # raises an exception
    self._commit()
    return existing

Note the two comments; they describe the sequence of events. Especially, the exception that is raised before the commit.

What I'm doing is taking a User whose email field is set to '[email protected]' and setting it to '', which causes the validator to raise an exception. Subsequent to this, even after a restart of the Pylons server, users returned by the following query have '' for an email address! These two queries return a user with the '' email address:

Session().query(User).filter_by(login=login, company_id=company).one()
Session().query(User).all()

Here's the SQL/parameters/response for the first one of these two queries:

[sqlalchemy.engine.base.Engine] BEGIN
[sqlalchemy.engine.base.Engine] SELECT users.user_id AS users_user_id, users.login AS users_login, users.name AS users_name, users.email AS users_email, users.company_id AS users_company_id, users.is_admin_user AS users_is_admin_user 
FROM users 
WHERE %(param_1)s = users.company_id ORDER BY lower(users.name)
[sqlalchemy.engine.base.Engine] {'param_1': u'offby1'}
[sqlalchemy.engine.base.Engine] Col ('users_user_id', 'users_login', 'users_name', 'users_email', 'users_company_id', 'users_is_admin_user')
[sqlalchemy.engine.base.Engine] Row (8, u'newuser', u'A new user', u'[email protected]', u'offby1', False)
[sqlalchemy.engine.base.Engine] Row (3, u'crose', u'Chris Rose', u'[email protected]', u'offby1', True)

Note that the email address is returned. This object is encoded as JSON and sent over the wire, and by that time the email property is ''

This query returns the complete user with the email address (Note that this is all the same object!):

Session().query(User).filter_by(user_id=user_id).one()

Here's the detailed logging for that query:

[sqlalchemy.engine.base.Engine] BEGIN
[sqlalchemy.engine.base.Engine] SELECT users.user_id AS users_user_id, users.login AS users_login, users.name AS users_name, users.email AS users_email, users.company_id AS users_company_id, users.is_admin_user AS users_is_admin_user 
FROM users 
WHERE users.user_id = %(user_id_1)s ORDER BY lower(users.name)
[sqlalchemy.engine.base.Engine] {'user_id_1': u'3'}
[sqlalchemy.engine.base.Engine] Col ('users_user_id', 'users_login', 'users_name', 'users_email', 'users_company_id', 'users_is_admin_user')
[sqlalchemy.engine.base.Engine] Row (3, u'crose', u'Chris Rose', u'[email protected]', u'offby1', True)

For this query, I get the right user email address back.

What's happening here, and how do I fix it?

Answer 1

I'm not familiar with pylons, but your database returns seem OK (since the email column isn't empty). Is there some sort of caching with pylons?

Answer 2

This is a stab in the dark, but try using Session.commit() instead of self._commit().