'Error inserting values in SQL: Unknown column 'nan' in 'field list'

for row in dfp.itertuples():
 cursor.execute('''
             INSERT INTO players (PID, NAME)
             VALUES (%s,%s)
             ''',
                (row.PID,
                 row.NAME)
              )

After running this code, I am getting this error:

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'nan' in 'field list'

What should I do to solve this error?

pythonmysqlsqlmysql-error-1054


Solution 1:[1]

The problem is that the dataframe contains NaN values; when passed to the query NaN is not quoted because it's a number, but it gets stringified like this

INSERT INTO players (PID, NAME) VALUES (1, NaN)

and the database interprets the unquoted "string" as a column name and can't find the column.

The solution is to replace or remove such values before writing to the database, for example by using fillna to replace them with some other, suitable value:

for row in dfp.fillna(-1).itertuples():
 cursor.execute('''
             INSERT INTO players (PID, NAME)
             VALUES (%s,%s)
             ''',
                (row.PID,
                 row.NAME)
              )

Solution 2:[2]

Insert

dfp['NAME'] = dfp['NAME'].fillna("---")

before for loop, for fill up Nan value with '---'

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
Solution 2 namjoo

Related Questions