Hi,
I have a table (variable_value) with a "value" column where I store a mix
of data (float, boolean, datetime) as strings. On a related table the type
is stored and now I'm trying to create a new calculated column
"casted_value" that casts the value column to the correct type in SQL (I'm
using MySQL). The idea is that I want to be able to have numerical values,
dates, etc sort correctly on "casted_value" - and also that SqlAlchelmy
would interpret the types correctly and give nice python types; <type
'float'>, etc.
Here is some simplified code to give you an idea:
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
)
# Dynamically cast value to the correct type depending on
variable_register
# type.
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
)
cls.casted_value = column_property(
sqlalchemy.select(
[dynamic_cast],
from_obj=from_obj
).where(
sqlalchemy.and_(
variable_value.c.entityid == cls.entityid,
variable_value.c.registerid == cls.registerid
)
).as_scalar()
)
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'>:
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].
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.
