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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
