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

[Ross Vandegrift]

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