Hi, On Fri, Dec 11, 2015 at 8:45 AM, Laura Creighton <l...@openend.se> wrote: > From python-list. > Very weird. > Another reason not to use sqlite3 > > ------- Forwarded Message > > To: python-list@python.org > From: "Frank Millman" <fr...@chagford.com> > Subject: Problem with sqlite3 and Decimal > Date: Fri, 11 Dec 2015 11:21:53 +0200 > Lines: 71 > > 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.
Is there a reason you are saving it as the string? What happens when you save it as decimal? Thank you. > > Any suggestions will be much appreciated. > > Frank Millman > > > - -- > https://mail.python.org/mailman/listinfo/python-list > > ------- End of Forwarded Message > -- > https://mail.python.org/mailman/listinfo/python-list -- https://mail.python.org/mailman/listinfo/python-list