We have implemented a decimal arithmetic module to handle money in
Sqlite. It uses the regular SQL definitions and maintains precison and
scale. The data are stored as TEXT and in "display format", right
aligned with leading spaces so that they display without editing or
radix transformation when inserting into HTML pages or reports. The
arithmetic maintains the fixed point precision and applies correct
rounding using the classic algorithm which minimizes skew.
Sqlite tries to torpedo these types by wanting to transform decimal
numbers into integers or floating point. Some care is needed at the
interface point to stop that happening.
The alternative way to store money in Sqlite is to use integers with an
implied decimal point and perform appropriate rounding and point
position maintenance when performing multiplication and division. This
is just like the traditional COMP3 BCD integer usage.
Using floating point for money in exacting applications is a trap for
young players. Once bitten, twice shy.
wcmadness wrote:
Hey, Folks:
I'm writing a financial application and MUST have exact math decimals (no
floats). So, I'm using Python's decimal module.
My database is Sqlite (and my language is Python with Pysqlite); Sqlite
doesn't offer a non-floating point decimal type. But, it does have adapters
and converters to store data as a native Sqlite type (string / text) in the
database and bring it out of the database and into memory as something else
(Python Decimal, in my case). That works great, but it does NOT seem to
apply to aggregation operations. I need it to.
So, I tried using another Sqlite feature, custom aggregation functions, but
to no avail. Does anyone know how to fix this? What am I doing wrong?
I am submitting all of my test code, below. Following that, I am submitting
my results.
Thanks for your time in helping with this!
Here's the code:
import sqlite3
import decimal
######### This way will store the value as float, potentially losing
precision.
print '-' * 25
print 'Testing native data types, no adapters / converters.'
con = sqlite3.connect('test1.db')
cur = con.cursor()
cur.execute("create table test (pkey integer primary key,somenumber
Decimal);")
cur.execute("insert into test values (null,.1);")
cur.execute("insert into test values (null,.2);")
cur.execute("select * from test;")
rows = cur.fetchall()
for row in rows:
print row[0], type(row[0])
print row[1], type(row[1])
cur.close()
con.close()
print '-' * 25
######### This way will store the value as decimal, keeping exact precision.
def AdaptDecimal(pdecValue):
return str(pdecValue)
def ConvertDecimal(pstrValue):
return decimal.Decimal(pstrValue)
decimal.getcontext().precision = 50
sqlite3.register_adapter(decimal.Decimal, AdaptDecimal)
sqlite3.register_converter("Decimal", ConvertDecimal)
print 'Testing data type with adapters / converters. Decimal numbers should
be Python Decimal types.'
con = sqlite3.connect('test2.db',detect_types = sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test (pkey integer primary key,somenumber
Decimal);")
cur.execute("insert into test values (null,.1);")
cur.execute("insert into test values (null,.2);")
cur.execute("select * from test;")
rows = cur.fetchall()
for row in rows:
print row[0], type(row[0])
print row[1], type(row[1])
cur.close()
con.close()
print '-' * 25
######### OK. That works. Now for the real test. Let's try an equality test.
######### Classic float equality failure .1 + .1... 10 times should NOT
equal 1.
######### As predicted, this will FAIL the equality test
print 'Testing Sum aggregation on native data types. Should be float and
should fail equality test.'
con = sqlite3.connect('test3.db')
cur = con.cursor()
cur.execute("create table test (pkey integer primary key,somenumber
Decimal);")
for x in range(10):
cur.execute("insert into test values (null,.1);")
cur.execute("select sum(somenumber) as total from test;")
rows = cur.fetchall()
print rows[0][0], type(rows[0][0])
if rows[0][0] == 1:
print 'equal'
else:
print 'NOT equal'
cur.close()
con.close()
print '-' * 25
######### Now, we try the exact same equality test, using adapters and
converters, substituting
######### the Python exact precision decimal type for float.
# Probably don't need to re-register. We did that above. We probably just
need to parse declared types when
# we open the connection.
# Hmmmm... This fails whether I re-register or not.
# sqlite3.register_adapter(decimal.Decimal, AdaptDecimal)
# sqlite3.register_converter("Decimal", ConvertDecimal)
print "Testing Sum aggregation with adapters / converters registered. Result
SHOULD BE Python Decimal type, but is NOT. Should PASS equality test, but
doesn't."
con = sqlite3.connect('test4.db',detect_types = sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test (pkey integer primary key,somenumber
Decimal);")
for x in range(10):
cur.execute("insert into test values (null,.1);")
cur.execute("select sum(somenumber) as total from test;")
rows = cur.fetchall()
print rows[0][0], type(rows[0][0])
if rows[0][0] == 1:
print 'equal'
else:
print 'NOT equal'
cur.close()
con.close()
print '-' * 25
########## OK. Let's try the exact same equality test, using manual
summation. First for floats.
print 'Testing manual summation against native data types, no adapters
converters. Should FAIL equality test, because the sum is a float.'
con = sqlite3.connect('test5.db')
cur = con.cursor()
cur.execute("create table test (pkey integer primary key,somenumber
Decimal);")
for x in range(10):
cur.execute("insert into test values (null,.1);")
cur.execute("select * from test;")
rows = cur.fetchall()
total = 0.0
for row in rows:
total += row[1]
print total,type(total)
if total == 1:
print 'equal'
else:
print 'NOT equal'
cur.close()
con.close()
print '-' * 25
######### Now, using adapters and converters with manual summation.
print 'Testing manual summation against adapted / converted decimals. Should
PASS equality test and DOES!'
con = sqlite3.connect('test6.db',detect_types = sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test (pkey integer primary key,somenumber
Decimal);")
for x in range(10):
cur.execute("insert into test values (null,.1);")
cur.execute("select * from test;")
rows = cur.fetchall()
total = decimal.Decimal('0.0')
for row in rows:
total += row[1]
print total,type(total)
if total == 1:
print 'equal'
else:
print 'NOT equal'
cur.close()
con.close()
print '-' * 25
########## Try custom aggregation.
########## Hmmm... This doesn't seem to work either. It returns None.
class XSum(object):
__slots__ = ("RunningSum",)
def __init__(self):
self.RunningSum = decimal.Decimal("0")
def step(self, pdecValue):
self.RunningSum += pdecValue
def finalize(self):
return self.RunningSum
print "So, native Sum aggregation doesn't work. Try custom aggregation."
con = sqlite3.connect('test6.db',detect_types = sqlite3.PARSE_DECLTYPES)
con.create_aggregate("XSum",1,XSum)
cur = con.cursor()
cur.execute("select XSum(somenumber) as total from test;")
rows = cur.fetchall()
print rows[0][0], type(rows[0][0])
if rows[0][0] == 1:
print 'equal'
else:
print 'NOT equal'
cur.close()
con.close()
print '-' * 25
########## Same test, but this time we don't parse types, so our step value
should come in as a string or a float.
########## Still does not work. It's as if step never gets called.
class XSum(object):
__slots__ = ("RunningSum",)
def __init__(self):
self.RunningSum = 0.0
def step(self, pvarValue):
self.RunningSum += pvarValue
def finalize(self):
return self.RunningSum
print 'Hmmm... Returns None above. Try without custom data typing. Now,
using custom aggregation without custom data types.'
print 'Still returns None, as if step is never called or immediately fails.'
con = sqlite3.connect('test6.db')
con.create_aggregate("XSum",1,XSum)
cur = con.cursor()
cur.execute("select XSum(somenumber) as total from test;")
rows = cur.fetchall()
print rows[0][0], type(rows[0][0])
if rows[0][0] == 1:
print 'equal'
else:
print 'NOT equal'
cur.close()
con.close()
print '-' * 25
Here are my results:
-------------------------
Testing native data types, no adapters / converters.
1 <type 'int'>
0.1 <type 'float'>
2 <type 'int'>
0.2 <type 'float'>
-------------------------
Testing data type with adapters / converters. Decimal numbers should be
Python Decimal types.
1 <type 'int'>
0.1 <class 'decimal.Decimal'>
2 <type 'int'>
0.2 <class 'decimal.Decimal'>
-------------------------
Testing Sum aggregation on native data types. Should be float and should
fail equality test.
1.0 <type 'float'>
NOT equal
-------------------------
Testing Sum aggregation with adapters / converters registered. Result SHOULD
BE Python Decimal type, but is NOT. Should PASS equality test, but doesn't.
1.0 <type 'float'>
NOT equal
-------------------------
Testing manual summation against native data types, no adapters converters.
Should FAIL equality test, because the sum is a float.
1.0 <type 'float'>
NOT equal
-------------------------
Testing manual summation against adapted / converted decimals. Should PASS
equality test and DOES!
1.0 <class 'decimal.Decimal'>
equal
-------------------------
So, native Sum aggregation doesn't work. Try custom aggregation.
None <type 'NoneType'>
NOT equal
-------------------------
Hmmm... Returns None above. Try without custom data typing. Now, using
custom aggregation without custom data types.
Still returns None, as if step is never called or immediately fails.
None <type 'NoneType'>
NOT equal
-------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
