Problem with sqlite3 and Decimal

[Frank Millman]

"Frank Millman"  wrote in message news:n4ei3l$b98$1 at 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