NUMERIC in sqlite
csingley
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.
Michael Bayer
Christopher Singley
On Wed, Oct 6, 2010 at 9:06 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:
> SQlite has no lossless DECIMAL type and SQLAlchemy can't work around that.
Well SQLAlchemy itself suggests workarounds that it doesn't implement,
so here we are...
> 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.
Is it as simple as this?
"""
class DecimalString(sqlalchemy.types.TypeDecorator):
impl = sqlalchemy.types.String
def process_bind_param(self, value, dialect):
return str(value)
def process_result_value(self, value, dialect):
return decimal.Decimal(value)
"""
> 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.
Is it as simple as this?
"""
class DecimalInt(sqlalchemy.types.TypeDecorator):
impl = sqlalchemy.types.Integer
def process_bind_param(self, value, dialect):
return int(value * 10**4) # basis pt precision
def process_result_value(self, value, dialect):
return decimal.Decimal(value / 10**4) # basis pt precision
"""
> 3. stick with the FP program.
Who actually desires this behavior? I'm not trying to flame here, I
am genuinely curious why that is the preferred default for SQLite.
One more question, if I may. I suppose what would be ideal would be
to create a type that wrapped a String or Integer if the dialect was
sqlite, but wrapped a Numeric type if the dialect was something a
little more full-fledged. Possible/difficult/ill-advised to do within
the SQLAlchemy type system?
Well, despite raising the ominous specter of accounting identity
violations, so far I'm really liking the looks of SQLAlchemy. Thanks
for the help.
Michael Bayer
cursory glance, sure
>
>> 3. stick with the FP program.
>
> Who actually desires this behavior? I'm not trying to flame here, I
> am genuinely curious why that is the preferred default for SQLite.
if you're using the same table metadata with another database, and you'd like to do some non-critical tests using SQLite, or you just want Decimal objects back and didn't want to say Float(as_decimal=True)
>
> One more question, if I may. I suppose what would be ideal would be
> to create a type that wrapped a String or Integer if the dialect was
> sqlite, but wrapped a Numeric type if the dialect was something a
> little more full-fledged. Possible/difficult/ill-advised to do within
> the SQLAlchemy type system?
Well such a type wouldn't be backend agnostic. The String/Integer wrapped one would act very differently than a Numeric. So not much point in a transparent switch like that. We try to keep "switches" like that to a minimum (there are some, like Enum).
Conor
[...]
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.Is it as simple as this? """ class DecimalInt(sqlalchemy.types.TypeDecorator): impl = sqlalchemy.types.Integer def process_bind_param(self, value, dialect): return int(value * 10**4) # basis pt precision def process_result_value(self, value, dialect): return decimal.Decimal(value / 10**4) # basis pt precision """
"value / 10**4" will either truncate to an integer (Python 2) or return a float (Python 3 or with "from __future__ import division") if value is not divisible by 10**4. I think you want "decimal.Decimal(value) / 10**4" instead.
-Conor
Christopher Singley
> Well such a type wouldn't be backend agnostic. The String/Integer wrapped one would act very differently than a Numeric. So not much point in a transparent switch like that. We try to keep "switches" like that to a minimum (there are some, like Enum).
>
Makes sense.
A somewhat related follow-up question: is it possible to use
SQLAlchemy with "embedded" MySQL (libmysqld)? If so, how?
On Wed, Oct 6, 2010 at 10:36 AM, Conor <conor.edw...@gmail.com> wrote:
> "value / 10**4" will either truncate to an integer (Python 2) or return a
> float (Python 3 or with "from __future__ import division") if value is not
> divisible by 10**4. I think you want "decimal.Decimal(value) / 10**4"
> instead.
Good catch. Obviously the pseudocode still had skid marks on it from
being pulled straight out of my butt; just trying to catch the sense
of sqlalchemy data model.
Thanks guys, it's appreciated.