Forgot the actual question; is there a better less complicated solution? With hybrid_property expression I get into the problem that it doesn't load up front: session.query(Variable_Value)
Thanks! Mattias L On Friday, September 18, 2015 at 12:25:41 PM UTC+2, Mattias Lagergren wrote: > > Hi again Michael, > > I'm still working with this problem but have changed it so that I'm no > longer trying to CAST in the database, since you made me aware about the > limitations of mixed types in traditional RDBMS. > > What I want is a column, casted_value, on the model that gives me the > CASTed (decoded) value column and when writing to it it should encode and > update value column. Unfortunately I don't have the type on the same table > (variable_value) but in another table, variable_register. > > I've been looking at both @property and @hybrid_property + expression but > I've ran into two problems: > > 1. Is that I need the value to be loaded upfront with a > session.query(model) > 2. I want to avoid doing extra queries when casting in the casted_value. > My property would have to use a relation (register) to figure out the type: > > @property > def dynamic_value(self): > if self.register.type == 'date': > return date_from_string(self. > strvalue) > elif self.register.type == 'number': > return Decimal(self.strvalue) > # ... etc. > > I've resorted to a solution that feels overly complicated but works. The > solution is a column_property with a custom select that embeds the type in > the select. Due to how our API works all read, write and filter must be > pointed to the same column so to make this work I have a Comparator to > allow filtering on the value column, an AttributeExtension to encode and > update the value column when casted_value is set and a TypeDecorator to > decode (CAST) the query result to the appropriate type: > > > class Comparator(sqlalchemy.orm.properties.ColumnProperty.Comparator): > '''Comparator class to compare on value instead of casted_value.''' > > def __init__(self, column, mapper): > self.column = column > self.mapper = mapper > super(Comparator, self).__init__(column, mapper) > > def __lt__(self, other): > return self.mapper.c.value < other > > def __gt__(self, other): > return self.mapper.c.value > other > > def __eq__(self, other): > return self.mapper.c.value == other > > def __nq__(self, other): > return self.mapper.c.value != other > > > class UpdateCustomAttributeValue(sqlalchemy.orm.interfaces. > AttributeExtension): > > def set(self, state, value, oldvalue, initiator): > '''Set value on custom attribute instead of casted_value.''' > obj = state.obj() > obj.value = encode(value) > return value > > > class DynamicTypeDecorator(types.TypeDecorator): > '''Type decorator class for converting links.''' > > impl = types.Unicode > > def process_result_value(self, raw_value, dialect): > '''Process query result and cast to appropriate type.''' > try: > value, custom_attribute_type = raw_value.split(separator) > except: > return None > > return decode(value, custom_attribute_type) > > > > class Variable_Value(Base): > '''Represent a custom attribute value.''' > > __tablename__ = 'variable_value' > __table_args__ = table_args > > registerid = Column( > types.CHAR(36), ForeignKey('variable_register.registerid'), > default=None, primary_key=True > ) > register = relation('Variable_Register', backref=backref('values')) > > value = Column(types.UnicodeText) > > entityid = Column(types.CHAR(36), primary_key=True) > > @classmethod > def __declare_last__(cls): > from .custom_attribute import Variable_Register > variable_register = Variable_Register.__table__.alias() > variable_value = cls.__table__.alias() > > # Join to register to figure out the type. > from_obj = variable_value.join( > variable_register, > variable_register.c.registerid == variable_value.c.registerid > ) > > dynamic_cast = sqlalchemy.type_coerce( > variable_value.c.value + separator + variable_register.c.type, > DynamicTypeDecorator > ) > > cls.casted_value = column_property( > sqlalchemy.select( > [dynamic_cast], > from_obj=fromObj > ).where( > sqlalchemy.and_( > variable_value.c.entityid == cls.entityid, > variable_value.c.registerid == cls.registerid > ) > ).as_scalar(), > comparator_factory=Comparator, > extension=[UpdateCustomAttributeValue()] > ) > > > > With this I can read: > > for variable in session.query(model.Variable_Value): > # Correct type and no extra queries are issued even though the type > is stored on a relation. > print variable.casted_value > > > > I can also write: > > # Run this and do my own encoding > variable.casted_value = True # 100.1, datetime.now(), etc. > > > > I can query on it with MySQL implicit casting to number, boolean or other > values: > > print > session.query(model.Variable_Value).filter(model.Variable_Value.casted_value > > 5).all() > > > > > > > > > > > > > > > > On Wednesday, September 16, 2015 at 5:00:02 PM UTC+2, Michael Bayer wrote: > > > > On 9/16/15 6:46 AM, Mattias Lagergren wrote: > > > The important part is the casting inside of the case expression: > > dynamic_cast = sqlalchemy.sql.expression.case( > [ > ( > variable_register.c.type == 'number', > sqlalchemy.cast(variable_value.c.value, types.Numeric) > ), > ( > variable_register.c.type == 'date', > sqlalchemy.cast(variable_value > > ... -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.