Re: [sqlite] Custom Aggregation Not Working
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.
# H... 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
Re: [sqlite] Custom Aggregation Not Working
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.
Hi,
Firstly, you might get better answers on the pysqlite mailing list at
http://lists.initd.org/mailman/listinfo/pysqlite than here.
I've not used custom types/converters etc and couldn't see anything
wrong with your code at first glance so here are a couple of "off the
top of my head" comments.
In your tests you insert values as hard coded strings rather than bound
variables. From looking at the code it wasn't obvious (to me) that the
converters/adapters for the decimal classes will be called. I think
you've assumed pysqlite will examine the column declaration and call the
appropriate converter/adapter regardless, but the test4.db case doesn't
bear this out.
I put prints into the converter and it was NOT called with an insert like:
for x in range(10):
cur.execute("insert into test(somenumber) values (0.1)")
but it WAS called when I changed the insert to:
d=decimal.Decimal("0.1")
for x in range(10):
cur.execute("insert into test(somenumber) values (?)", (d,))
Going in the other direction, section 4.3 of the pysqlite docs at
http://initd.org/pub/software/pysqlite/doc/usage-guide.html warns:
"!!! Note that converter functions always get called with a string, no
matter under which data type you sent the value to SQLite !!!"
You've defined string <-> decimal conversions, but when you execute:
cur.execute("select sum(somenumber) as total from test;")
how does your adapter get called?
sum() won't return your custom type and there's no column info that I
can see that would cause your adapter to be called. The column names
example implies you could force the result of sum() be adapted but sum()
won't perform addition according to the semantics of your custom type.
HTH
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Custom Aggregation Not Working
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.
# H... 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 summ
