Problem with sqlite3 and Decimal (fwd)

[Laura Creighton]

From python-list.
Very weird.
Another reason not to use sqlite3

------- Forwarded Message

To: python-list at python.org
From: "Frank Millman" <frank at 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.

Any suggestions will be much appreciated.

Frank Millman 


- -- 
https://mail.python.org/mailman/listinfo/python-list

------- End of Forwarded Message