python - 编程错误 ("%s can not be used with MySQL"% s)

Question

我想使用下面的代码将数据框插入到 table1 中:

def insert(df):
    host='###'
    name='###'
    password='###'
    database='###'
    conn = pymysql.connect(host, user=name,passwd=password, db=database, connect_timeout=5,cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "INSERT INTO table1 (col1, col2, col3, col4, col5 ) VALUES (%s,%s,%s,%s,%s)"
    cur.execute( sql, (row[0], row[1], row[2], row[3], row[4]))
    conn.commit()
    cur.close()
    conn.close()

    return

for row in dataframe.itertuples():
    insert(row)

错误显示如下:

    cur.execute( sql, (row[0], row[1], row[2], row[3], row[4]))
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 161, in execute
    query = self.mogrify(query, args)
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 140, in mogrify
    query = query % self._escape_args(args, conn)
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 115, in _escape_args
    return tuple(conn.literal(arg) for arg in args)
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 115, in <genexpr>
    return tuple(conn.literal(arg) for arg in args)
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 469, in literal
    return self.escape(obj, self.encoders)
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 462, in escape
    return converters.escape_item(obj, self.charset, mapping=mapping)
  File "/usr/local/lib/python3.7/site-packages/pymysql/converters.py", line 26, in escape_item
    val = encoder(val, mapping)
  File "/usr/local/lib/python3.7/site-packages/pymysql/converters.py", line 58, in escape_float
    raise ProgrammingError("%s can not be used with MySQL" % s)
NameError: name 'ProgrammingError' is not defined

任何人都知道出了什么问题以及如何解决错误？

Answer 1

您必须检查所有值，如 inf 或 NaN。您可以在 df 处理的末尾放置类似的内容以避免此类问题:

report = report.replace([np.inf, -np.inf], np.nan)
report = report.fillna(0)

当然，如果您不介意，这些值将表示为零，这在许多情况下可能是错误的。然后你必须找到一些其他的代表。