Path: csiph.com!fu-berlin.de!uni-berlin.de!not-for-mail
From: Laura Creighton <lac@openend.se>
Newsgroups: comp.lang.python
Subject: Problem with sqlite3 and Decimal (fwd)
Date: Fri, 11 Dec 2015 14:45:42 +0100
Lines: 94
Message-ID: <mailman.137.1449841547.12405.python-list@python.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-ID: <24999.1449841542.1@fido>
Precedence: list
Xref: csiph.com comp.lang.python:100260

=46rom python-list.
Very weird.
Another reason not to use sqlite3

------- Forwarded Message

To: python-list@python.org
From: "Frank Millman" <frank@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-decim=
al-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 =3D sqlite3.connect(':memory:', detect_types=3Dsqlite3.PARSE_DECLTYPE=
S)
cur =3D conn.cursor()

cur.execute("CREATE TABLE fmtemp (acno INT, bal DEC)")
cur.execute("INSERT INTO fmtemp (acno, bal) VALUES (?, ?)", ('A001',
D('0')))

sql1 =3D "SELECT bal FROM fmtemp"
sql2 =3D "UPDATE fmtemp SET bal =3D bal + ?"

while True:
    print(cur.execute(sql1).fetchone()[0])
    cur.execute(sql2, (D('123.45'),))
    q =3D input()
    if q =3D=3D 'q':
        break
"""

It initialises a decimal value in the database, then loops adding a decima=
l
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 i=
s
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