Search code examples
pythonsqlfloating-pointdecimalprecision

how to store python float in SQL database

In python3, I mainly use float or np.float32/64 and when it comes to store it into a database, even if SQL type is Numeric/Decimal we end up with 0.400000000000021 or something like that instead of 0.4
It may be a problem if such data is accessed from another application.

Working only with decimal.Decimal in python isn't an answer for us, since we heavely make use of pandas, and Decimal is not supported.
A solution would be to cast float to Decimal just before inserting into SQL (SQL Server in our case but it's a detail). And then back from Decimal to float after SELECT.
Do you have another (and nicer) way to handle such issue ?

Solution

  • The problem is that the value of your float isn't 0.4, because there is no value in either float32 or float64 (or Python native float, which is usually the same as float64) that's 0.4. The closest float64 to 0.4 that is 0.400000000000021, which is exactly what you've got.

    Since that's the closest float value there is to 0.4, if you ask Python to convert it to a string (e.g., print(f)), it'll be friendly and give you the string 0.4.

    But when you pass it to a database… Well, it actually depends on which database interface library you're using. With some, it will call repr, which would give you '0.4' (at least in Python 3.x), so you're asking the database to store the float value of the string '0.4'. But with others, it will pass the float value directly as a C double, so you're asking the database to store the float value 0.400000000000021.

    So, what should you do?

    • Do you want to use this database with other code that will be reading the values as strings and then converting them to something like Decimal or float80 or decimal64 or some other type? Then you almost certainly want to set a SQL data type like DECIMAL(12, 6) that matches your actual precision, and let the database take care of it. (After all, there is no difference between 0.4 rounded to 6 decimal places and 0.400000000000021 rounded to 6 decimal places.)
    • Do you want to do math inside the database itself? Same as above.
    • Otherwise? Do nothing.

    Seriously, if the other code that's going to use this database is just going to read the values as float64, or read them as strings and convert them to float64 (or float32), they are going to end up with 0.400000000000021 no matter what you do, so don't do anything.

    Also, consider this: if the difference between 0.4 and 0.400000000000021 is going to make any difference for any of your code, then your code is already broken by using float64, before you even get to the database.