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 ------------------------- -- View this message in context: http://www.nabble.com/Custom-Aggregation-Not-Working-tf3944115.html#a11188082 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------