Python报错：DataFrame真值判断歧义，MySQL数据插入失败求助

从Excel文件导入数据到MySQL新建表时，执行查询触发报错：The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

数据示例：

使用代码：

import os
import pandas as pd
import mysql.connector

survey = pd.read_excel("surveyraw.xlsx")
pd.DataFrame(survey)

connection = mysql.connector.connect(
host="localhost",
user="root",
password="***",
database="***"
)

cursor = connection.cursor()
cursor.execute(
"CREATE TABLE survey (resp_id VARCHAR(64), var VARCHAR(128), val VARCHAR(64))"
)

insert_query = "INSERT INTO survey (resp_id, var, val) VALUES (%s, %s, %s)"

cursor.executemany(insert_query, survey.to_records(index=False))
connection.commit()

报错信息：

ValueError: ValueError                                
Traceback (most recent call last)
<ipython-input-20-ab80c19b7fda> in <module>
----> 1 cursor.executemany(insert_query, survey.to_records(index=False))
  2 connection.commit()

~\anaconda3\lib\site-packages\mysql\connector\cursor_cext.py in executemany(self, operation, seq_params)
373         data using the execute() method.
374         """
375         if not operation or not seq_params:
376             return None
377 

ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

报错原因 #

survey.to_records(index=False)返回的是numpy结构化数组，而mysql.connector的executemany仅支持可迭代的元组/列表序列，无法直接解析numpy数组，导致判断数组真值时出现歧义。

修复方案 #

方案1：转换numpy数组为元组列表

将to_records的结果转为元组列表后传入：

cursor.executemany(insert_query, list(survey.to_records(index=False)))

方案2：直接从DataFrame生成元组序列

如果Excel列名与表字段完全匹配，用itertuples直接生成符合要求的元组：

# index=False排除DataFrame自身索引，name=None返回纯元组而非命名元组
cursor.executemany(insert_query, survey.itertuples(index=False, name=None))

额外优化 #

• 删除多余代码：原代码中pd.DataFrame(survey)无意义，pd.read_excel已返回DataFrame对象。
• 添加连接关闭逻辑：避免数据库资源泄漏：

try:
    cursor.execute("CREATE TABLE IF NOT EXISTS survey (resp_id VARCHAR(64), var VARCHAR(128), val VARCHAR(64))")
    cursor.executemany(insert_query, survey.itertuples(index=False, name=None))
    connection.commit()
finally:
    cursor.close()
    connection.close()

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