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.c.value, types.Date)
> )
> ],
> else_=variable_value.c.value
> )
>
> The result when using this in a query only gives back strings, <type
> 'str'>:
>
>
> A creative idea, but unfortunately that's not how SQL works. A given
> column that comes back in a result tuple must be of a fixed datatype on a
> traditional RDBMS. The datatype for an individual result-column is not
> dynamic per-row (except on SQLite). SQLAlchemy Core makes this same
> assumption, so the datatype can't switch like this on a per-row basis.
>
> Also, the mixing of SQL-side CAST with a SQL column that is essentially
> just a string is not correct. On the SQL side, operations with this column
> all deal with the same type (string) so there's no need for CASTing within
> the database.
>
> In this case since all the vardata is just in a char column, there's no
> need for SQL-side manipulation and you only need to make sure that Python
> types are converted to String on the setter side and converted to
> appropriate datatype on the getter side. A simple Python @property would
> achieve this:
>
> class MyClass(Base):
> # ...
>
> @property
> def dynamic_value(self):
> if self.type == 'date':
> return date_from_string(self.strvalue)
> elif self.type == 'number':
> return Decimal(self.strvalue)
> # ... etc.
>
> @dynamic_value.setter
> def dynamic_value(self, value):
> if self.type == 'date':
> self.strvalue = date_as_string(value)
> elif self.type == 'number':
> self.strvalue = str(value)
> # ... etc.
>
>
>
>
>
>
> for item in session.query(Variable_Value):
> print item, type(item)
>
> When reading the SqlAlchemy docs I can see that sqlalchemy.cast 1. Creates
> a SQL CAST statement, 2. Associates it with an appropriate type. I can see
> that casting in a case will make this impossible for SqlAlchemy to solve.
>
> Do you have any thoughts on the approach or any suggestions on how I can
> do it differently. This is to improve an existing system and a solution
> that does not involve changes to the database schema would be ideal.
>
> Thanks!
> Mattias L
>
> --
> 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] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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.
