Python, mysql, floating point values question

[Christopher Kang]

I had a question about python and mysql, but I want to first thank
those who taught me about the factory method, it was exactly what I
needed.

Anyway, I have a problem where I am pulling floating point values out
of mysql and into python using the MYSQLdb module.

However, the values seem to be altered a little when I store them in python..

Because the decimal places are off, I am unable to use the values that
I have in order to write a sql statement (again using the MYSQLdb)
that pulls column values from just that single value i'm looking for.

Let me explain more clearly:

Let's say I pull column1 values for all records, and they happen to be:

1.4, 1.5632, 1.32523 in the mysql database.

Now I realize that I am interested in all the column values for just
the 1.5632 record. Unfortunately, python seems to be taking the
floating point values, and doing something with it, such that my
variable has something like 1.5632000000001 stored in it or such.

I thus, can't pull the value out using a sql statement such as
"Select * from table_name where column1 = my_variable_value" becuase
my_variable_value has 1.563200000000001 stored in it, and not 1.5632.

The way that the variable gets adjusted seems to be random.

Is there a solution to this?



Thank you very much!

 

[mensanator]

I had a question about python and mysql, but I want to first thank
those who taught me about the factory method, it was exactly what I
needed.

Anyway, I have a problem where I am pulling floating point values out
of mysql and into python using the MYSQLdb module.

However, the values seem to be altered a little when I store them in python.

Because the decimal places are off, I am unable to use the values that
I have in order to write a sql statement (again using the MYSQLdb)
that pulls column values from just that single value i'm looking for.

Let me explain more clearly:

Let's say I pull column1 values for all records, and they happen to be:

1.4, 1.5632, 1.32523 in the mysql database.

Now I realize that I am interested in all the column values for just
the 1.5632 record. Unfortunately, python seems to be taking the
floating point values, and doing something with it, such that my
variable has something like 1.5632000000001 stored in it or such.

I thus, can't pull the value out using a sql statement such as
"Select * from table_name where column1 = my_variable_value" becuase
my_variable_value has 1.563200000000001 stored in it, and not 1.5632.

The way that the variable gets adjusted seems to be random.

Is there a solution to this?

The smart-ass answer is to learn how to use floating point properly.

But along those lines, once you get a useful representation of
1.5632 in Python, you may discover that not all the 1.5632's in
the database are identical. They, too, may differ insignificantly.

You may have to apply fixes on both ends. I don't know mysql but
in MS-Acces, I would probably make a calculated field in the db query
such as Format$([TheField],"0.0000"). As a string, the differences
in the many flavors of 1.5632 are discarded.

 

[qwweeeit]

Hi Christopher,
if you have to make calculations or comparing operations, the only
"safe" method is to save and use only integer values.
Of course there must be a preventive agreement on the precision you
want to have (2, 3 4 ... decimals).
The sw part is straigthforward:
- to save in the database you must multiply every float by
10**(precision)
- to use the "integers" and, for example, display them as float, you
must of course make the opposit operation (dividing).

There are drawbacks:
- the queries (from the point of view of the user) are much more
complicate
- the integer takes generally more place than the corresponding float
- the integer divide needs particular attention.

If the disk space occupied by the database is a concern, you can
evaluate the possibility to save the floats as string representation
as, for example, "1.5632"
(coming out from "%.4f" % 1.5631999999999999).

Sorry if the above considerations are not at the same level of those of
the experts, but I definitely am not an "expert". I encountered the
same problems many years ago (when the size of disks was at maximum 200
Mb).
Bye.

 

[Andy Dustman]

Use DECIMAL columns with MySQLdb-1.2.0 and Python-2.4 and you should
get values back using Python's new decimal type.

http://docs.python.org/whatsnew/node9.html

This avoids floating point inaccuracies.