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

Question

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?

Answer 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)
              )

Answer 2

Insert

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

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