Problem with sqlite3 and Decimal

[Frank Millman]

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

[Laura Creighton]

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

------- Forwarded Message

To: pytho...@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

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

------- End of Forwarded Message

[Igor Korot]

Hi,

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

[Frank Millman]

"Igor Korot" wrote in message
news:CA+FnnTyaLLEsYGU7v2BreySDOQ1rVsMzJ=5f4iQTLW3=tn=E...@mail.gmail.com...

> Hi,

>
> > To: pytho...@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 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?
>

Well, maybe you know something that I don't, but my understanding is that
sqlite3 has a very limited range of supported data types, and decimal is not
one of them.

The stackoverflow article explains how to emulate a decimal type, and it
works well most of the time.

I found that I could reproduce the problem using sqlite3 directly, without
using Python, so I have sent a post to the sqlite3 mailing list. I will
report back with any info received.

Frank

[Igor Korot]

Hi, Frank,

Yes, I saw your post to sqlite3 ML.
And I do know that by default sqlite3 does not have many types supported.

However, all you need to do is save it as DECIMAL(10,2).
It is supported is sqlite3 and it will have NUMERIC affinity (ref 1)
or REAL (ref 2), which means the data will
be stored as decimals and not a string.

Ref.: 1) http://www.tutorialspoint.com/sqlite/sqlite_data_types.htm
2) https://www.sqlite.org/datatype3.html

Thank you.

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

[Chris Angelico]

On Fri, Dec 11, 2015 at 8:21 PM, Frank Millman <fr...@chagford.com> wrote:
> 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.

It's an SQLite3 issue, not a Python one. I used the sqlite3
stand-alone tool to do the same thing:

sqlite> update fmtemp set bal = bal + cast('123.45' as numeric);
sqlite> select bal from fmtemp;
...
5678.7
5802.15
5925.59999999999

And this might be why:

https://www.sqlite.org/datatype3.html

SQLite doesn't *have* all the SQL data types, and NUMERIC is one of
the ones that isn't available. If you recreate your example using
PostgreSQL, it should work fine.

ChrisA

[Frank Millman]

"Chris Angelico" wrote in message
news:CAPTjJmor6NewucCo7XTSSwyy...@mail.gmail.com...

On Fri, Dec 11, 2015 at 8:21 PM, Frank Millman <fr...@chagford.com> wrote:
> > 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.

> It's an SQLite3 issue, not a Python one. I used the sqlite3
> stand-alone tool to do the same thing:

> sqlite> update fmtemp set bal = bal + cast('123.45' as numeric);
> sqlite> select bal from fmtemp;
> ...
> 5678.7
> 5802.15
> 5925.59999999999
>

You are right. I am still investigating alternatives, and will report back,
but here is a quick question.

I can reproduce your example above. However, if I set the initial value to
5678.7, then the sequence goes

5678.7
5802.15
5925.6
6049.05
6172.5

I would have thought that adding 123.45 to 5802.15 would always produce the
same result, but here it seems to depend on prior events.

Any idea why? Academic interest only, but I am curious.

Frank

[Frank Millman]

"Igor Korot" wrote in message

news:CA+FnnTyZY_1=62Rbk_kKZ39tkeoA6JV...@mail.gmail.com...

>
> Yes, I saw your post to sqlite3 ML.
> And I do know that by default sqlite3 does not have many types supported.
>
> However, all you need to do is save it as DECIMAL(10,2).
> It is supported is sqlite3 and it will have NUMERIC affinity (ref 1)
> or REAL (ref 2), which means the data will
> be stored as decimals and not a string.
>

Do you mean CREATE TABLE fmtemp (acno INT, balance DECIMAL(10,2)); ?

I tried that, but I got exactly the same result.

The answer, as explained by several people on the sqlite3 ML, is that
sqlite3 does not have a true decimal type and therefore uses floating point
internally. As we all know from many questions asked on this forum, floating
point and exact decimal representation are incompatible.

Frank

[Chris Angelico]

On Sat, Dec 12, 2015 at 4:10 PM, Frank Millman <fr...@chagford.com> wrote:
> I can reproduce your example above. However, if I set the initial value to
> 5678.7, then the sequence goes
>
> 5678.7
> 5802.15
> 5925.6
> 6049.05
> 6172.5
>
> I would have thought that adding 123.45 to 5802.15 would always produce the
> same result, but here it seems to depend on prior events.
>
> Any idea why? Academic interest only, but I am curious.

You weren't adding 123.45 to 5802.15. Here's why.

The number 123.45 is actually represented as:

>>> 123.45.as_integer_ratio()
(8687021468732621, 70368744177664)

that is, 8687021468732621/2**46. The exact value you want is actually
a repeating binary fraction:

0b1111011.0111001100110011001100...

with the 1100 part repeating. Python rounds this up to
0b11110110111001100110011001100110011001100110011001101, with a
notation that this has an exponent of -46. (Presumably SQLite3 is
doing the same, but I'm using Python's introspection here. This is all
IEEE 754 floating point.)

So when you set the initial value to 0 and then add 123.45 fifty
times, you're adding that tiny bit of rounding error fifty times, too.
When you set your initial value to 5678.7, you're skipping most of the
accumulated error, and so the result still looks the same. When you
printed out something that looked fine, it's because it actually
rounded to the value you started with; that is to say, you weren't
working with 5802.15, but with something really REALLY close to it.
When you added one more of that rounding error, you tipped the sum
across a boundary mark, and suddenly it didn't look like the decimal
number you were expecting; but in reality, it never was.

There's a lot of academic interest to be found in this, and a lot of
fun to be had playing around. If you want to learn more, separate this
from SQLite3 and just play around in Python - you'll find it easier.

ChrisA

[Frank Millman]

"Chris Angelico" wrote in message

news:CAPTjJmoPXFSnXe1QA8MjjncBZBpqNkztha8YHJv=MBm--...@mail.gmail.com...

> On Sat, Dec 12, 2015 at 4:10 PM, Frank Millman <fr...@chagford.com> wrote:
> > I can reproduce your example above. However, if I set the initial value
> > to
> > 5678.7, then the sequence goes
> >
> > 5678.7
> > 5802.15
> > 5925.6
> > 6049.05
> > 6172.5
> >
> > I would have thought that adding 123.45 to 5802.15 would always produce
> > the
> > same result, but here it seems to depend on prior events.
> >
> > Any idea why? Academic interest only, but I am curious.
>
> You weren't adding 123.45 to 5802.15. Here's why.

[snip really interesting explanation]

Wow, thanks for that, Chris. Consider my academic curiosity well and truly
satisfied :-)

I have found a workaround for my problem, but I will post that in a separate
message - still testing to make sure it is 100%.

Frank

[Frank Millman]

"Frank Millman" wrote in message news:n4ei3l$b98$1...@ger.gmane.org...

> 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.
>

[...]

I have found a workaround for my problem, but first I needed to understand
what was going on more clearly. This is what I have figured out.

1. The solution in the SO article is a bit of sleight of hand, though very
effective. It does not create a Decimal type in sqlite3. It simply provides
a way of converting Decimal objects to strings when you pass them into the
database, and converting them back to Decimal types when you read them back.

2. This works if you only use sqlite3 as a storage mechanism, and use Python
to perform any arithmetic required. It fails when you try to use sqlite3 to
perform arithmetic, as it uses floating point internally and suffers from
the same problem that Python does when trying to mix floating point and
precise decimal representation.

3. Normally I do use Python to perform the arithmetic, but in this situation
I wanted to do the following -

UPDATE table SET balance = balance + ? WHERE date > ?

It would be very inefficient to read every row into Python, perform the
addition, and write it back again.

4. The Python sqlite3 module allows you to create a user-defined function
that you can use from within SQL statements. I realised I could use this to
get the best of both worlds. I wrote the following function -

def aggregate(curr_value, aggr_value):
return '#{}'.format(D(curr_value[1:]) + D(aggr_value[1:]))

and added this to the connection -

conn.create_function('aggregate', 2, aggregate)

I could then rewrite my statement as -

UPDATE table SET balance = aggregate(balance, ?) WHERE date > ?

5. The reason for the '#' in the above function is that sqlite3 passes the
current value of 'balance' into my function, and it has a bad habit of
trying to second-guess the data-type to use. Even though I store it as a
string, it passes in an integer or float. Prefixing it with a '#' forces it
to remain as a string.

My adapters therefore now look like this -

# 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')[1:]))

6. Putting it all together, I can now run my test program -

while True:
print(cur.execute("SELECT bal FROM fmtemp").fetchone()[0])
cur.execute("UPDATE fmtemp SET bal = aggregate(bal, ?)",

(D('123.45'),))
q = input()
if q == 'q':
break

and it runs up to 123450.00 without misbehaving.

Hope this is of interest.

Frank

[Chris Angelico]

IOn Sat, Dec 12, 2015 at 6:31 PM, Frank Millman <fr...@chagford.com> wrote:
> I have found a workaround for my problem, but first I needed to understand
> what was going on more clearly. This is what I have figured out.
>
> 1. The solution in the SO article is a bit of sleight of hand, though very
> effective. It does not create a Decimal type in sqlite3. It simply provides
> a way of converting Decimal objects to strings when you pass them into the
> database, and converting them back to Decimal types when you read them back.
>
> 2. This works if you only use sqlite3 as a storage mechanism, and use Python
> to perform any arithmetic required. It fails when you try to use sqlite3 to
> perform arithmetic, as it uses floating point internally and suffers from
> the same problem that Python does when trying to mix floating point and
> precise decimal representation.

There's another possibility, and that's fixed-point arithmetic. You
store the numbers as integers - probably cents, if you're talking
about dollar amounts - and as long as the scaled values fit inside the
available integer type (probably 64-bit), you'll be fine.

Or, of course, you could switch to a database back end that actually
supports NUMERIC data.

ChrisA

[Frank Millman]

"Frank Millman" wrote in message news:n4gigr$f51$1...@ger.gmane.org...

> I have found a workaround for my problem, but first I needed to understand
> what was going on more clearly. This is what I have figured out.
>

[...]

>
> The reason for the '#' in the above function is that sqlite3 passes the
> current value of 'balance' into my function, and it has a bad habit of
> trying to second-guess the data-type to use. Even though I store it as a
> string, it passes in an integer or float. Prefixing it with a '#' forces
> it to remain as a string.

Well that theory did not last very long!

As soon as I applied this to my live app, I found that I am using the
database to perform arithmetic all over the place - calculating tax,
exchange rates, etc. I always round the result once it arrives from the
database, so there was no rounding problem.

With the prefix of '#' the calculations all just crash, and return null
values.

My new solution is to pass a 'scale' factor into my aggregate function. The
function uses the Decimal quantize method to round the result before
returning. So far it seems to be working.

Frank

[Chris Angelico]

On Sun, Dec 13, 2015 at 4:00 PM, Frank Millman <fr...@chagford.com> wrote:
> My new solution is to pass a 'scale' factor into my aggregate function. The
> function uses the Decimal quantize method to round the result before
> returning. So far it seems to be working.

So, effectively, you're using fixed point arithmetic. As long as
you're restricting yourself to adding values together, that's easy; be
careful of multiplying by tax percentages, as you might flick to
float.

Really, you'd do a lot better to move to PostgreSQL.

ChrisA

[Frank Millman]

"Chris Angelico" wrote in message

news:CAPTjJmrfw-qNx-a=3Q2qJ244FGVxz3MPe...@mail.gmail.com...

Thanks for the warning, but I think I am safe.

There is only one exceptional case where I use my 'aggregate' function. It
is a substitute for the following SQL statement -

UPDATE table SET balance = balance + ? WHERE date > ?

Normally it works fine. However, I wanted to populate my database with some
real-world values, so I wrote a program to generate a few thousand
transactions, and that triggered the rounding errors that caused me to start
this thread.

I have replaced the statement with -

UPDATE table SET balance = aggregate(balance, ?, ?) WHERE date > ?

This prevents rounding errors from creeping in.

In all other cases, I use unadorned SQL to calculate a scalar value, which I
round to the appropriate scaling factor before storing the result.

Regarding PostgreSQL, I have mentioned before that I offer my users a choice
of 3 databases - PostgreSQL, Sql Server, and sqlite3 - so I have to make
sure that my app works with all of them. I agree that for serious database
work one should use PostgreSQL or Sql Server. But I think that sqlite3 is
perfect for demos and for one-man businesses. It is fast, lightweight, and
very 'standards compliant'. It does have some quirks, but these are clearly
documented and the mailing list is very responsive.

Frank