[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