Hi all
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. Here is a stripped-down
example -
"""
from decimal import Decimal as D
import sqlite3
# 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')))
conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
cur = conn.cursor()
cur.execute("CREATE TABLE fmtemp (acno INT, bal DEC)")
cur.execute("INSERT INTO fmtemp (acno, bal) VALUES (?, ?)", ('A001',
D('0')))
sql1 = "SELECT bal FROM fmtemp"
sql2 = "UPDATE fmtemp SET bal = bal + ?"
while True:
print(cur.execute(sql1).fetchone()[0])
cur.execute(sql2, (D('123.45'),))
q = input()
if q == 'q':
break
"""
It initialises a decimal value in the database, then loops adding a decimal
value and displaying the result.
It runs fine for a while, and then the following happens -
5802.15
5925.6
6049.05
6172.4999999999
6295.9499999999
It consistently switches to floating point at the same position. If you
carry on for a while, it reverts back to two decimal places.
If I initialise the value as D('6049.05'), the next value is 6172.5, so it
is not the number itself that causes the problem.
I tried displaying the type - even when it switches to 6172.49999999, it is
still a Decimal type.
I noticed one oddity - I am asking sqlite3 to store the value as a string,
but then I am asking it to perform arithmetic on it.
Any suggestions will be much appreciated.
Frank Millman
--
https://mail.python.org/mailman/listinfo/python-list
