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?
3 Answers
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)
Try converting your NaN to None since MySQL understands None and in the UI you'll see NULL
You may use:
if pd.isna(row[column]):
values_data.append(None)
else:
values_data.append(row[column])
