"Frank Millman" wrote in message news:n4ei3l$b98$1...@ger.gmane.org...
I need to store Decimal objects in a sqlite3 database, using Python 3.4 on
Windows 7.
I followed the instructions here -
http://stackoverflow.com/questions/6319409/how-to-convert-python-decimal-to-sqlite-numeric
It seemed to work well, but then I hit a problem.
[...]
I have found a workaround for my problem, but first I needed to understand
what was going on more clearly. This is what I have figured out.
1. The solution in the SO article is a bit of sleight of hand, though very
effective. It does not create a Decimal type in sqlite3. It simply provides
a way of converting Decimal objects to strings when you pass them into the
database, and converting them back to Decimal types when you read them back.
2. This works if you only use sqlite3 as a storage mechanism, and use Python
to perform any arithmetic required. It fails when you try to use sqlite3 to
perform arithmetic, as it uses floating point internally and suffers from
the same problem that Python does when trying to mix floating point and
precise decimal representation.
3. Normally I do use Python to perform the arithmetic, but in this situation
I wanted to do the following -
UPDATE table SET balance = balance + ? WHERE date > ?
It would be very inefficient to read every row into Python, perform the
addition, and write it back again.
4. The Python sqlite3 module allows you to create a user-defined function
that you can use from within SQL statements. I realised I could use this to
get the best of both worlds. I wrote the following function -
def aggregate(curr_value, aggr_value):
return '#{}'.format(D(curr_value[1:]) + D(aggr_value[1:]))
and added this to the connection -
conn.create_function('aggregate', 2, aggregate)
I could then rewrite my statement as -
UPDATE table SET balance = aggregate(balance, ?) WHERE date > ?
5. The reason for the '#' in the above function is that sqlite3 passes the
current value of 'balance' into my function, and it has a bad habit of
trying to second-guess the data-type to use. Even though I store it as a
string, it passes in an integer or float. Prefixing it with a '#' forces it
to remain as a string.
My adapters therefore now look like this -
# Decimal adapter (store Decimal in database as str)
sqlite3.register_adapter(D, lambda d:'#'+str(d))
# Decimal converter (convert back to Decimal on return)
sqlite3.register_converter('DEC', lambda s: D(s.decode('utf-8')[1:]))
6. Putting it all together, I can now run my test program -
while True:
print(cur.execute("SELECT bal FROM fmtemp").fetchone()[0])
cur.execute("UPDATE fmtemp SET bal = aggregate(bal, ?)",
(D('123.45'),))
q = input()
if q == 'q':
break
and it runs up to 123450.00 without misbehaving.
Hope this is of interest.
Frank
--
https://mail.python.org/mailman/listinfo/python-list
