Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #100259

Problem with sqlite3 and Decimal

Show all headers | View raw

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 

Back to comp.lang.python | Previous | Next | Find similar | Unroll thread

Thread

Problem with sqlite3 and Decimal "Frank Millman" <frank@chagford.com> - 2015-12-11 11:21 +0200

csiph-web