How To Deal With Rounding Errors Of Floating Types For Financial Calculations In Python Sqlite?

I'm creating a financial app and it seems my floats in sqlite are floating around. Sometimes a 4.0 will be a 4.000009, and a 6.0 will be a 6.00006, things like that. How can I ma

Solution 1:

Please use Decimal

http://docs.python.org/library/decimal.html

Solution 2:

Seeing as this is a financial application, if you only have calculations up to 2 or 3 decimal places, you can store all the data internally as integers, and only convert them to float for presentation purposes.

E.g.

6.00->6004.35->435

Solution 3:

This is a common problem using SQLite as it does not have a Currency type. As S.Mark said you can use the Decimal representation library. However SQLite 3 only supports binary floating point numbers (sqlite type REAL) so you would have to store the Decimal encoded float as either TEXT or a BLOB or convert to REAL(but then you'd be back to a 64bit binary float) So consider the range of numbers that you need to represent and whether you need to be able to perform calculations from within the Database.

You may be better off using a different DB which supports NUMERIC types e.g. MYSql, PostgreSQL, Firebird

Solution 4:

Most people would probably use Decimal for this, however if this doesn't map onto a database type you may take a performance hit.

If performance is important you might want to consider using Integers to represent an appropriate currency unit - often cents or tenths of cents is ok.

There should be business rules about how amounts are to be rounded in various situations and you should have tests covering each scenario.

Solution 5:

Use Decimal to manipulate your figures, then use pickle to save it and load it from SQLite as text, since it doesn't handle numeric types.

Finaly, use unitest and doctest, for financial operations, you want to ensure all the code does what it is suppose to do in any circonstances. You can't fix bugs on the way like with, let's say, a social network...