On Jun 15, 2010, at 7:14 AM, Harry Percival wrote: > Not sure what the etiquette is re cross-posting to this list from > stackoverflow? here's my question: > > http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem
AFAIK this would be a function of Pysqlite. You can make your own SQLAlchemy types sure but thats SQLalchemy doing it, not SQLite's feature. I've read that sqlite documentation page many times and I've never seen what it says actually occur, particularly regarding numerics. Its either inaccurate or Pysqlite squashes the behavior, or I just don't really understand it. There's no way to get numerics of arbitrary precision in and out of SQLite, for example - if you google around for "decimal" support you'll see that its not possible - floating point conversions always kick in. > > I like the idea of sqlite's manifest typing / type affinity: > > http://www.sqlite.org/datatype3.html > > Essentially, if I set a column's affinity as 'numeric', it will duck type > integers or floats to store them as such, but still allow me to store strings > if I want to. Seems to me this is the best 'default' type for a column when > i'm not sure ahead of time of what data i want to store in it. > > so off i go: > > metadata = MetaData() > new_table = Table(table_name, metadata ) > > > for col_name in column_headings: > new_table.append_column(Column(col_name, > > > sqlite.NUMERIC, #this should duck-type > numbers but can handle strings as well > > > primary_key=col_name in > primary_key_columns)) > > > new_table.create(self.engine, checkfirst=False) > > > but when i try and store some string values, eg "abc" in the table, > sqlalchemy falls over: > > File "[...]\sqlalchemy\processors.py", line 79, in to_float > > > return float(value) > ValueError: invalid literal for float(): abc > > > Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do > the typing? perhaps i can use a type from sqlalchemy.types instead of > sqlachemy.dialects.sqlite? > > > since the so post, i've done a little more digging. Am I going to have to > write my own custom type as per > http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types ? > > > hp > > > -- > ------------------------------ > Harry J.W. Percival > ------------------------------ > Italy Mobile: +39 389 095 8959 > UK Mobile: +44 (0) 78877 02511 (may be turned off) > Skype: harry.percival > Email: [email protected] > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
