CODEWITHSUNDEEP
pythonmysqlpython-2.7mysql-python
Clíodhna
Clíodhna

Reputation: 818

Changing invalid value to None for INSERT SQL statement but getting "Data Truncated" error

I have a FLOAT column in a MySQL table which I am trying to populate using a Python script. Most of the time for the data I'm using the values are valid floats but sometimes they are "NaN". So when inserting into the table I check if the value is "NaN" and if so I set it to None which, to my understanding, be insert as NULL in the table. This is not happening and I'm unsure as to why.

Here is the Python code:

for row in zip(plasma_potential_set, floating_potential_set):
        row = [scan_result_id] + list(row)
        for i in range(len(row)):
            if str(row[i]).lower() == "nan":
                row[i] = None
        sql_insert = ('INSERT INTO langmuir_result(scan_data_id, 
                                                   plasma_potential, floating_potential)'
                        'VALUES(%s, "%s", "%s")')
        cursor.execute(sql_insert, row)
    mydb.commit()
    cursor.close()

Here is the CREATE statement for the table:

CREATE TABLE result (result_id INT auto_increment, scan_data_id INT, 
plasma_potential FLOAT, floating_potential FLOAT, PRIMARY KEY (result_id));

Here is the error I get:

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

Here is what the row looks like after I set any invalid values to None:

[1, None, 17.4651]

Why is this happening and how can I fix it? Thank you in advance.

Have looked at this question but mine is slightly different and this one doesn't have an answer: Unable to use None (NULL) values in python mysql.connector in prepared INSERT statement

Upvotes: 1

Views: 755

Answers (1)

jps
jps

Reputation: 22545

You just have to remove the " in the VALUES argument list. Change your insert statement to:

sql_insert = ('INSERT INTO langmuir_result(scan_data_id, plasma_potential, floating_potential)'
                    'VALUES(%s, %s, %s)')

and it will work as expected.

Upvotes: 1

Related Questions