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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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.
