Thanks Michael - i've seen others complain about the arbitrary-precision numbers issue... I'm not so bothered by that, but i do like the ability to natively store integers and strings in the same column.
On Tue, Jun 15, 2010 at 4:40 PM, Harry Percival <harry.perci...@gmail.com>wrote: > Answered my own question: > > Define a custom column type, as per > http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types > > a combination of the documentation and some trial & error have given me > this: > > class MyDuckType(sqlalchemy.types.TypeDecorator): > > """ > SQLALchemy custom column type, designed to let sqlite handle the typing > using 'numeric affinity' which intelligently handles both numbers and > strings > > """ > impl = sqlite.NUMERIC > > def bind_processor(self, dialect): > > > #function for type coercion during db write > return None #ie pass value as-is, let sqlite do the typing > > > def result_processor(self, dialect, coltype): > > #function for type coercion during db read > return None #ie pass value as sqlite has stored it, should be > ducktyped already > > > def process_bind_param(self, value, dialect): > > #any changes to an individual value before store in DN > return value > > def process_result_value(self, value, dialect): > > > #any changes to an individual value after retrieve from DB > return value > > def copy(self): > > > #not quite sure what this is for > return MyDuckType() > > The current sqlalchemy dialect type returns to_float in bind_processor, > which is why I was getting the errors before. i.m.v.v.h.o., this is a bug. > > for my bonus points: manually setting column type to MyDuckType in my > metadata.reflect() code: > > def get_database_tables(engine): > meta = MetaData() > > meta.reflect(bind=engine) > > tables = meta.raw_tables > for tbl in tables.values(): > > > for col in tbl.c: > > col.type = MyDuckType() > return tables > > seems to work for me. Any suggestions / improvements? I've tentatively > filled out a ticket on the sqlalchemy bug tracker, not sure if that was a > little arrogant? > > > On Tue, Jun 15, 2010 at 4:22 PM, Michael Bayer > <mike...@zzzcomputing.com>wrote: > >> >> 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: harry.perci...@gmail.com >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalch...@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> 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 sqlalch...@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com> >> . >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > > > -- > ------------------------------ > Harry J.W. Percival > ------------------------------ > Italy Mobile: +39 389 095 8959 > UK Mobile: +44 (0) 78877 02511 (may be turned off) > Skype: harry.percival > Email: harry.perci...@gmail.com > -- ------------------------------ Harry J.W. Percival ------------------------------ Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
