Dataframe中NaN值直接replace进入数据库表会报错，填充为None后插入表，表中显示为null

当pd.Dataframe中含有NaN值时，用python连接mysql数据库，直接replace进入表会报错
比如有如下dataFrame

replace会报错：pymysql.err.InternalError: (1054, “Unknown column ‘nan’ in ‘field list’”)

解决办法：
将DataFrame中的NaN值填充为None后再replace
df = df.where(df.notnull(),None)

完整代码如下：

import pymysql
import pandas as pd

import warnings
warnings.filterwarnings("ignore")


df = pd.DataFrame([['Tom',10],['Jerry',12],['Elsa',14],['Anna',16]],columns=['name','age'])
df=df.append([{'name':'Olaf'}]).reset_index(drop=True)
print(df)
df = df.where(df.notnull(),None)

#插入数据库
table_name = 'test_table'
data = df.values.tolist()
column_tuple = str(tuple(df.columns)).replace("'","")
sql_replace = """replace into {0} {1} values ({2} %s);""".format(table_name,column_tuple,'%s,'*(len(df.columns)-1))

conn = pymysql.connect(host="127.0.0.1",
                             port=3306,
                             user="user_name",
                             passwd="password",
                             db="dbname")
cursor = conn.cursor()
cursor.executemany(sql_replace,data)
conn.commit()
cursor.close()
conn.close()

数据库中显示：