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 = "[email protected]"
session.flush()
session.select(text("SELECT email FROM users WHERE name='Lennon'))
# returns "[email protected]" - just to illustrate that it's
stored lower-case in the database
user = session.query(User).filter(...).one()
print user.email
# prints "[email protected]"
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 <[email protected]> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
