[sqlalchemy] Simple one-to-one translation with hybrid_attribute
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.
Thanks,
Ross
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
Session = orm.scoped_session(orm.sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
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]
Base.metadata.create_all(engine)
d1 = Device(captured)
d2 = Device(registered)
d3 = Device(prepared)
Session.add_all([d1, d2, d3])
Session.commit()
q = Session.query(Device)
q.filter_by(state=captured)
q.filter(Device.state 0)
signature.asc
Description: This is a digitally signed message part
Re: [sqlalchemy] Simple one-to-one translation with hybrid_attribute
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.
Re: [sqlalchemy] Simple one-to-one translation with hybrid_attribute
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
