Thanks for your answer Michael. I experimented with column_property and generally it works, however now I have 2 separate attributes - one for setting a value and another, read-only one, for reading the modified value.
class User(Base): ... email = sa.Column(String) @validates('email') def validate_email(self, key, value): return sa.func.lower(value) email_upper = column_property(sa.func.upper(email)) Is there a way to have just a single field which looks like a normal attribute, but performs some processing on the database side on the way in and out? What I'm looking for is: user.email = "john.len...@beatles.com" session.flush() session.select(text("SELECT email FROM users WHERE name='Lennon')) # returns "john.len...@beatles.com" - just to illustrate that it's stored lower-case in the database user = session.query(User).filter(...).one() print user.email # prints "john.len...@beatles.com" I was able to achieve something similar using a property: class User(Base): ... _email = Column("email", String) @property def email(self): return object_session(self)\ .scalar( select([func.upper(self._email)]) ) @email.setter def email(self, value): self._email = object_session(self)\ .scalar( select([func.lower(value)]) ) - but obviously the "email" attribute is not loaded "inline" - instead, a separate query is issued each time the attribute is accessed. Also, the _email attribute IS loaded and then sent back to the server, which sort of undermines the idea of database-side processing... On Jul 28, 2:06 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > On Jul 27, 2011, at 8:56 PM, Sergey V. wrote: > > > Good day, > > > I'm trying to figure out how to do something similar to the Symmetric > > Encryption recipe (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ > > SymmetricEncryption), only on the database side, not in Python. > > > I have a suspicion that @compiles decorator may provide a solution, > > but having trouble understaning how to apply it to my case. > > > For simplicity, let's imagine a field which stores data in upper case > > but always returns it in lower case... so it needs to generate SQL > > similar to > > > "INSERT INTO tablename VALUES (..., upper(...), ...)" > > this can be assigned, (1) i.e. > > myobject.fieldname = func.upper(somename) > > which you can apply with a @validates decorator (2) > > > > > on insert and > > > "SELECT ..., lower(fieldname) as fieldname, ... FROM tablename" > > for this you'd use column_property(). (3) > > for the SQL functions themselves we're using func (4) > > 1:http://www.sqlalchemy.org/docs/orm/session.html#embedding-sql-insert-... > 2:http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators > 3:http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions... > 4:http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sq... > > For a slightly old example of some of this kind of thing (probably more > complicated than you'd need here), see the PostGIS example under > examples/postgis/. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.