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]
-----------------------------------------------------------------------------
