Python中mysql.connector.ProgrammingError 1064错误排查与解决

错误原因 #

1. SQL语句格式错误：INSERT语句的VALUES子句缺少外层括号，MySQL要求VALUES后必须以(值1, 值2, ...)的形式传入参数，原代码直接将值罗列，导致MySQL无法识别正确的语法结构。
2. 字符串/日期类型未加引号：Description、Country这类字符串类型，以及InvoiceDate日期类型的值，直接拼接到SQL中时没有用单引号包裹，MySQL会将空格分隔的内容识别为多个无效语法元素，引发报错。
3. 直接拼接SQL存在安全风险：使用f-string直接拼接外部数据到SQL语句中，不仅容易引发语法错误，还会导致SQL注入漏洞。

解决方法 #

方法1：使用参数化查询（推荐） #

利用mysql.connector的参数化查询功能，用%s作为占位符，无需手动处理引号和格式，同时避免SQL注入：

cursor = cnx.cursor()
xlsx = pd.read_excel('online_retail.xlsx')
# 定义带占位符的INSERT语句
insert_sql = "INSERT INTO online_retail.registers VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
for index, row in xlsx.iterrows():
    values = (
        row['Invoice'],
        row['StockCode'],
        row['Description'],
        row['Quantity'],
        row['InvoiceDate'],
        row['Price'],
        row['Customer ID'],
        row['Country']
    )
    cursor.execute(insert_sql, values)
# 提交事务完成插入
cnx.commit()

方法2：使用pandas批量插入（高效） #

对于大量数据，推荐使用pandas的to_sql方法，批量插入效率更高：

from sqlalchemy import create_engine

# 创建数据库连接引擎，替换为你的数据库账号信息
engine = create_engine('mysql+mysqlconnector://用户名:密码@主机地址/数据库名')
xlsx = pd.read_excel('online_retail.xlsx')
# 重命名DataFrame列名，匹配数据库表字段名
xlsx.rename(columns={
    'Invoice': 'invoice',
    'StockCode': 'stock_code',
    'Description': 'description',
    'Quantity': 'quantity',
    'InvoiceDate': 'invoice_date',
    'Price': 'price',
    'Customer ID': 'customer_ID',
    'Country': 'country'
}, inplace=True)
# 批量插入数据，if_exists='append'表示追加数据
xlsx.to_sql(name='registers', schema='online_retail', con=engine, if_exists='append', index=False)

方法3：修正SQL拼接格式（不推荐，仅作参考） #

如果一定要用字符串拼接，需要给VALUES加外层括号，同时给字符串/日期类型的值包裹单引号（需手动处理转义，易出错）：

cursor = cnx.cursor()
xlsx = pd.read_excel('online_retail.xlsx')
for index, row in xlsx.iterrows():
    # 对字符串/日期类型值加单引号，处理内容中的单引号转义
    values = [
        str(row['Invoice']),
        f"'{row['StockCode']}'",
        f"'{row['Description'].replace('''', '''''')}'",
        str(row['Quantity']),
        f"'{row['InvoiceDate']}'",
        str(row['Price']),
        str(row['Customer ID']),
        f"'{row['Country']}'"
    ]
    INSERT_INTO = f"INSERT INTO online_retail.registers VALUES ({', '.join(values)})"
    cursor.execute(INSERT_INTO)
cnx.commit()

原代码、数据库结构及错误信息 #

原Python代码 #

cursor = cnx.cursor()
xlsx = pd.read_excel('online_retail.xlsx')
for index, row in xlsx.iterrows():
    values = [row['Invoice'],
              row['StockCode'],
              row['Description'],
              row['Quantity'],
              row['InvoiceDate'],
              row['Price'],
              row['Customer ID'],
              row['Country']]
    INSERT_INTO = (f"INSERT INTO online_retail.registers VALUES {values[0]}, "
                   f"{values[1]}, "
                   f"{values[2]}, "
                   f"{values[3]}, "
                   f"{values[4]}, "
                   f"{values[5]}, "
                   f"{values[6]}, "
                   f"{values[7]}")
    cursor.execute(INSERT_INTO)

数据库表结构 #

CREATE TABLE online_retail.registers (
    invoice INT NOT NULL,
    stock_code VARCHAR(6) NOT NULL,
    description TEXT,
    quantity INT ,
    invoice_date DATETIME(0),
    price FLOAT,
    customer_ID INT,
    country VARCHAR(32),
    PRIMARY KEY(invoice)
);

错误信息 #

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '489434, 85048, 15CM CHRISTMAS GLASS BALL 20 LIGHTS, 12, 2009-12-01 07:45:00, 6.9' at line 1

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