Clíodhna Clíodhna - 1 month ago 10
MySQL Question

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?

Answer Source

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)