Problem with sqlite3 and Decimal

[Chris Angelico]

IOn Sat, Dec 12, 2015 at 6:31 PM, Frank Millman <frank at 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