Hi Michael, Thank you for your quick reply and clarifying both SqlAlchemy and the SQL-side.
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 > As I mentioned I had two reasons to do this; the order of the result and the result type. When CASTing to a single type (e.g. DECIMAL) within an order by it does affect the result and works to get numerical values sorted correctly. However, as you said it does not work when mixing data types. Again, thank you for helping! /Mattias L 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.
