Re: [sqlalchemy] Simple one-to-one translation with hybrid_attribute

[Ross Vandegrift]

On Mon, 2011-08-01 at 17:22 -0400, Michael Bayer wrote:
> You're looking to convert from int->string using a mapping in a SQL
> expression, so I think you'd need to write @state.expression as a CASE
> statement.   
> 
> from sqlalchemy import case
> 
>@state.expression
>def state(self):
>return case(self.statenum, statei2a)

Exactly what I needed, works like a charm - thank you so much!

For the archives - the args are flipped above.  Should be
"case(statei2a, self.statenum)".

Thanks,
Ross


signature.asc
Description: This is a digitally signed message part

Re: [sqlalchemy] Simple one-to-one translation with hybrid_attribute

[Michael Bayer]

On Aug 1, 2011, at 5:08 PM, Ross Vandegrift wrote:

> Hello everyone,
> 
> Trying to use hybrid_attribute to provide friendly names for integers
> representing object states.  Storage and retrieval works fine, but I
> can't get filtering working.  I want the translation to happen on the
> Python side prior to filling in the query parameters, but
> hybrid_attribute is thinking the DB should do it.  Example at the
> bottom.
> 
> I don't really understand how to write the @state.expression the way I
> want things to happen.

> statei2a = {
>0 : "captured",
>1 : "registered",
>2 : "prepared",
> }
> statea2i = dict((v, k) for k, v in statei2a.iteritems())
> 
> class Device(Base):
>__tablename__ = "device"
> 
>id = sa.Column(sa.Integer, primary_key=True)
>statenum = sa.Column(sa.Integer, nullable=False, default=0)
> 
>def __init__(self, state):
>self.state = state
> 
>@hybrid_property
>def state(self):
>return statei2a[self.statenum]
> 
>@state.setter
>def state(self, state):
>self.statenum = statea2i[state]
> 
>@state.expression
>def state(self):
>return statea2i[self.statenum]

You're looking to convert from int->string using a mapping in a SQL expression, 
so I think you'd need to write @state.expression as a CASE statement.   

from sqlalchemy import case

   @state.expression
   def state(self):
   return case(self.statenum, statei2a)






-- 
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.