Problem with sqlite3 and Decimal (fwd)

[Igor Korot]

Hi,

On Fri, Dec 11, 2015 at 8:45 AM, Laura Creighton <lac at openend.se> wrote:
> 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.

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