"Chris Angelico" wrote in message
news:CAPTjJmrfw-qNx-a=3q2qj244fgvxz3mpe4wa-wdusmchxuf...@mail.gmail.com...
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.
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
--
https://mail.python.org/mailman/listinfo/python-list
