On Oct 5, 2010, at 11:34 PM, csingley wrote: > Hi, I'm just learning to use sqlalchemy now (although I've had some > exposure to SQLObject and Django ORM, so not completely new to the > game). > > I'd like to address a standard ORM issue - i.e. interfacing Python > Decimal objects with SQLite backend. It seems that sqlalchemy follows > the standard strategy of passing the buck to sqlite, which converts > fixed-point to floating point. > > If I may be pardoned a diversion... I understand why sqlite does this, > but it constantly boggles my mind that ORM layers allow fixed<- >> floating conversions. I have yet to find a single application where > this is desirable behavior, since fixed-point generally goes along > with guarantees of strict equality (I'm speaking here as a finance > geek, money-handling being perhaps the preeminent real-world use of > Decimals). > > Anyway, religious matters notwithstanding... although I'm comforted by > sqlite's assurance that it tests identity out to 15 decimal places, > which exceeds my own needs for precision... I would like to follow the > recommendation of sqlalchemy, and guarantee strict precision by use of > Decimal<->String<->Decimal conversions round-tripping to the > database. I am looking at the sqlalchemy.types source code, and I > like what I see... it looks not difficult to define what I want with > AbstractType, UserDefinedType, TypeDecorator, etc. > > However, as a new user, the docstrings aren't completely clear. Can > y'all give a hint to a newbie looking not to reinvent the wheel? Can > I use TypeDecorator with impl=types.Numeric? If so, what do I need to > override - bind_processor(), result_processor(), and/or what? > > I can't be the first person to want to do this. TIA for any pointers.
You're not, but unfortunately pysqlite offers no solution here. Here is the ticket on Pysqlite's tracker, closed as "wontfix" : http://code.google.com/p/pysqlite/issues/detail?id=14&can=1 "These people seem to want a lossless numeric type in the SQLite database. If somebody comes up with a suggestion how pysqlite can help here, I will think about it again. For now, I'm closing this." SQlite has no lossless DECIMAL type and SQLAlchemy can't work around that. Previous uproars over this limitation led to the warning that we raise, which I'm sure you've noticed, so that people don't inadvertently use SQLite in their financial applications without understanding the risks (I work in finance as well some years, including this one). The approaches to working around this are: 1. store the decimals as strings. Use String, and place a TypeDecorator around it which marshals Decimal objects. 2. store the decimals as integers, using a type with a fixed exponent. Use Integer, and place a TypeDecorator around it which multiplies Decimal objects upwards by the fixed exponent going in and back down going out. This is the approach I've favored on past projects since you can still do math operations within SQL queries. You can in fact create expression wrapping that makes the system mostly transparent (something I've been thinking about blogging since I do it a lot these days). 3. stick with the FP program. -- 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.
