相同代码Windows正常运行，Linux下遇pymysql NaN编程错误求助

相同代码在Windows系统可正常执行，但在Linux系统运行时抛出以下错误：

pymysql.err.ProgrammingError: nan can not be used with MySQL

项目中将计算结果存入pandas DataFrame，转换为列表后通过pymysql插入MySQL，核心代码如下：

df_score = DataFrame()
temp = df_score.values.tolist()
insert(REPORT, report_cursor, insert_score.format(suffix), temp)

已确认DataFrame中仅包含None值，不存在NaN，甚至尝试用以下代码替换NaN为None，但错误仍未解决：

df_score = df_score.where(pd.notnull(df_score), None)

环境详情 #

• Python版本：3.8.10
• 操作系统版本：Ubuntu 18.04
• pandas版本：2.0.3
• pymysql版本：1.1.1

完整错误栈 #

Traceback (most recent call last):
  File "/root/analysis/./dataAnalysisApp/service.py", line 3872, in score
    insert(REPORT, report_cursor, insert_score.format(suffix), temp)
  File "/root/analysis/./utils/mysql_db_pools.py", line 207, in insert
    db_pools.execute_sql(db_name, cursor, sql, params, fetch, executemany)
  File "/root/analysis/./utils/mysql_db_pools.py", line 91, in execute_sql
    raise e
  File "/root/analysis/./utils/mysql_db_pools.py", line 82, in execute_sql
    cursor.executemany(sql, params or ())
  File "/root/anaconda3/envs/analysis-py38/lib/python3.8/site-packages/dbutils/steady_db.py", line 605, in tough_method
    result = method(*args, **kwargs)  # try to execute
  File "/root/anaconda3/envs/analysis-py38/lib/python3.8/site-packages/pymysql/cursors.py", line 182, in executemany
    return self._do_execute_many(
  File "/root/anaconda3/envs/analysis-py38/lib/python3.8/site-packages/pymysql/cursors.py", line 211, in _do_execute_many
    v = values % escape(arg, conn)
  File "/root/anaconda3/envs/analysis-py38/lib/python3.8/site-packages/pymysql/cursors.py", line 102, in _escape_args
    return tuple(conn.literal(arg) for arg in args)
  File "/root/anaconda3/envs/analysis-py38/lib/python3.8/site-packages/pymysql/cursors.py", line 102, in <genexpr>
    return tuple(conn.literal(arg) for arg in args)
  File "/root/anaconda3/envs/analysis-py38/lib/python3.8/site-packages/pymysql/connections.py", line 530, in literal
    return self.escape(obj, self.encoders)
  File "/root/anaconda3/envs/analysis-py38/lib/python3.8/site-packages/pymysql/connections.py", line 523, in escape
    return converters.escape_item(obj, self.charset, mapping=mapping)
  File "/root/anaconda3/envs/xjy-data-analysis-py38/lib/python3.8/site-packages/pymysql/converters.py", line 25, in escape_item
    val = encoder(val, mapping)
  File "/root/anaconda3/envs/xjy-data-analysis-py38/lib/python3.8/site-packages/pymysql/converters.py", line 56, in escape_float
    raise ProgrammingError("%s can not be used with MySQL" % s)
pymysql.err.ProgrammingError: nan can not be used with MySQL

解决方案与原因分析 #

核心原因 #

Windows和Linux环境下pandas对None与数值类型的处理逻辑存在差异：

• 当使用df.where(pd.notnull(df), None)替换值时，pandas的数值列（如float64）无法直接存储None，会自动将其转为np.nan。Windows环境可能因隐式转换未触发该问题，但Linux环境严格遵循类型规则，导致最终转成列表后仍存在nan。
• 空DataFrame初始化后默认列类型为float，即使未赋值，后续操作也可能引入nan。

解决方法 #

1. 强制转换列类型为object
   先将DataFrame的列类型改为object，确保能真正存储None而不被转为nan：

   df_score = df_score.astype(object)
   df_score = df_score.where(pd.notnull(df_score), None)
   

2. 转换列表时手动替换nan
   若上述方法无效，在转成列表后直接遍历替换所有nan：

   temp = df_score.values.tolist()
   # 递归替换嵌套列表中的nan为None
   def replace_nan(item):
       if isinstance(item, list):
           return [replace_nan(i) for i in item]
       return None if pd.isna(item) else item
   temp = replace_nan(temp)
   

3. 使用pandas自带的to_sql方法插入
   放弃手动转列表的方式，用to_sql自动处理nan和None为SQL的NULL：

   from sqlalchemy import create_engine
   engine = create_engine('mysql+pymysql://user:password@host/dbname')
   df_score.to_sql('table_name', engine, if_exists='append', index=False)
   

4. 初始化时指定列类型为object
   空DataFrame默认列类型为float，初始化时明确指定类型：

   df_score = pd.DataFrame(columns=['col1', 'col2'], dtype=object)
   

为什么Windows下没问题？ #

大概率是Windows环境中pandas版本或系统层面的隐式转换规则更宽松，掩盖了None转nan的问题；而Linux环境严格遵循数值类型存储规则，直接暴露了该问题。

内容的提问来源于stack exchange，提问作者Rain