Inserting NaN value into MySQL Database

Question

I have some data that contains NULLs, floats and the occasional Nan. I'm trying to insert this data into a MySQL database using python and MySqldb.

Here's the insert statement:

for row in zip(currents, voltages):
        row = [id] + list(row)
        for item in row:
            sql_insert = ('INSERT INTO result(id, current, voltage)'
                            'VALUES(%s, "%s")')
            cursor.execute(sql_insert, row)

This is the table:

CREATE TABLE langmuir_result (result_id INT auto_increment, 
                              id INT, 
                              current FLOAT,
                              voltage FLOAT,
                              PRIMARY KEY (result_id));

When I try to insert NaN into the table I get this error:

_mysql_exceptions.DataError: (1265, "Data truncated for column 'current' at row 1")

I want to insert the NaN values into the database as a float or a number, not a string or NULL. I've tried having the type of the column be FLOAT and DECIMAL but get the same error. How can I do this without making it a string or NULL? Is that possible?

Solution 1

No, it's not possible to store a NaN value in a FLOAT type columns in Mysql. Values allowed are only NULL or a number. You may solve it using some value that you don't use for NaN (maybe negatives, a big/low value)

Solution 2

Try converting your NaN to None since MySQL understands None and in the UI you'll see NULL

Comments

[nacho]

No, it's not possible to store a NaN value in a FLOAT type columns in Mysql. Values allowed are only NULL or a number. You may solve it using some value that you don't use for NaN (maybe negatives, a big/low value)

[VoNWooDSoN]

If you know the size of the float you are trying to store, a NaN is specifically encoded in IEEE float numbers. "IEEE 754 NaNs are represented with the exponent field filled with ones (like infinity values), and some non-zero number in the significand (to make them distinct from infinity values)" en.wikipedia.org/wiki/NaN#Floating_point

[Clíodhna]

@nacho perfect, thanks! If you want to add that as an answer I'll accept it

[Clíodhna]

@VoNWooDSoN I unfortunately don't know the size, it's generated from a sensor

[snakecharmerb]

Related stackoverflow.com/questions/41936403/…

[Parfait]

And there is no such thing as NaN value in MySQL. That is strictly a Python numpy/pandas entity.

[GlGuru]

This isn't correct. NaN is part of the IEEE standard and NOT a Python/numpy/pandas entity. en.wikipedia.org/wiki/NaN

[Al Martins]

Perfect for Python