因含inf值无法将Pandas DataFrame上传至MySQL的问题

问题描述 #

上传包含无穷值(inf)的Excel文件到MySQL数据库时，触发ProgrammingError，核心报错为：inf can not be used with MySQL。

报错完整Traceback #

ProgrammingError                          Traceback (most recent call last)
~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1750                 if not evt_handled:
-> 1751                     self.dialect.do_executemany(
   1752                         cursor, statement, parameters, context

~/.local/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py in do_executemany(self, cursor, statement, parameters, context)
    196     def do_executemany(self, cursor, statement, parameters, context=None):
-> 197         rowcount = cursor.executemany(statement, parameters)
    198         if context is not None:

~/.local/lib/python3.8/site-packages/pymysql/cursors.py in executemany(self, query, args)
    172             assert q_values[0] == "(" and q_values[-1] == ")"
-> 173             return self._do_execute_many(
    174                 q_prefix,

~/.local/lib/python3.8/site-packages/pymysql/cursors.py in _do_execute_many(self, prefix, values, postfix, args, max_stmt_length, encoding)
    201         for arg in args:
-> 202             v = values % escape(arg, conn)
    203             if isinstance(v, str):

~/.local/lib/python3.8/site-packages/pymysql/cursors.py in _escape_args(self, args, conn)
    108         elif isinstance(args, dict):
-> 109             return {key: conn.literal(val) for (key, val) in args.items()}
    110         else:

~/.local/lib/python3.8/site-packages/pymysql/cursors.py in <dictcomp>(.0)
    108         elif isinstance(args, dict):
-> 109             return {key: conn.literal(val) for (key, val) in args.items()}
    110         else:

~/.local/lib/python3.8/site-packages/pymysql/connections.py in literal(self, obj)
    516         """
-> 517         return self.escape(obj, self.encoders)
    518 

~/.local/lib/python3.8/site-packages/pymysql/connections.py in escape(self, obj, mapping)
    509             return ret
-> 510         return converters.escape_item(obj, self.charset, mapping=mapping)
    511 

~/.local/lib/python3.8/site-packages/pymysql/converters.py in escape_item(val, charset, mapping)
     24     else:
-> 25         val = encoder(val, mapping)
     26     return val

~/.local/lib/python3.8/site-packages/pymysql/converters.py in escape_float(value, mapping)
     59     if s in ("inf", "nan"):
-> 60         raise ProgrammingError("%s can not be used with MySQL" % s)
     61     if "e" not in s:

ProgrammingError: inf can not be used with MySQL

已尝试方案 #

• 将inf替换为0，但修改原始数据，不符合业务需求
• 保留三位小数处理：

df = df.replace(np.nan, 0)
df = np.round(df, decimals = 3)

• 转换含inf的列为float类型：

df["col1"]=df["col1"].astype("float")
df["col2"]=df["col2"].astype("float")

• 检查报错后的DataFrame确认无inf值，但报错仍触发

可行解决方案 #

1. 彻底清理DataFrame中的inf/-inf #

MySQL不支持inf类型，必须将其转换为数据库兼容的格式，推荐替换为对应NULL的pandas NaN（需确保数据库字段允许NULL）：

import numpy as np
# 替换所有正/负无穷值为NaN
df = df.replace([np.inf, -np.inf], np.nan)
# 若业务需要占位值，可替换为合理的极大/极小值，例如：
# df = df.replace([np.inf, -np.inf], [1e18, -1e18])

2. 确认清理时机 #

确保数据清理操作在所有数据计算、转换之后执行——比如分组、聚合等操作可能再次生成inf，必须保证最终上传的DataFrame是已清理完成的版本。

3. 自定义SQLAlchemy类型转换器 #

如果需要保留inf的语义，可通过自定义类型转换器将inf映射为MySQL支持的特殊值：

from sqlalchemy import types
class FloatWithInf(types.TypeDecorator):
    impl = types.Float
    def process_bind_param(self, value, dialect):
        if value == np.inf:
            return 1e18  # 替换为业务允许的极大值
        elif value == -np.inf:
            return -1e18  # 替换为业务允许的极小值
        return value

# 上传时指定列使用自定义类型
df.to_sql('your_table_name', engine, dtype={'col1': FloatWithInf(), 'col2': FloatWithInf()})

4. 全面验证清理结果 #

用以下代码确认DataFrame中无残留的inf/-inf：

# 检查每列是否存在inf/-inf
print(df.isin([np.inf, -np.inf]).any())
# 定位具体含inf的行
inf_rows = df[df.isin([np.inf, -np.inf]).any(axis=1)]
print(inf_rows)

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