'Inserting NaN value into MySQL Database

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:^[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:^[2]

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

Solution 3:^[3]

You may use:

if pd.isna(row[column]):
      values_data.append(None)
    else:
      values_data.append(row[column])

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	nacho
Solution 2	user88484
Solution 3	Re?at ARIKAN

'Inserting NaN value into MySQL Database

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]