Python操作MySQL更新数据时触发1064语法错误求助

问题背景 #

刚学Python一周，很久没碰SQL，做了个带编辑窗口的功能——通过唯一标识C_ID调取待更新记录，输入ID执行更新/删除。但写的两种UPDATE语句都触发mysql.connector.errors.ProgrammingError: 1064 (42000)错误，代码和报错如下：

代码示例1 #

def update():
            cur, conn = connection.get_connection()

            record_id = delete_box.get()

            cur.execute("""UPDATE car_exp SET 
                        C_ID =:C_ID, 
                        C_Date =:C_Date, 
                        C_Payment =:C_Payment, 
                        C_CAA =:C_CAA, 
                        C_Maintenance =:C_Maintenance, 
                        C_Insurance =:C_Insurance, 
                        C_Gas =:C_Gas, 
                        C_Addt_1 =:C_Addt_1, 
                        C_Addt_2 =:C_Addt_2 
                        
                        WHERE C_ID = :C_ID""",
                        {
                        'C_ID': record_id,
                        'Date': C_Date_editor.get (),
                        'C_Payment': C_Payment_editor.get (), 
                        'C_CAA': C_CAA_editor.get (),
                        'C_Maintenance': C_Maintenance_editor.get (),
                        'C_Gas': C_Gas_editor.get (),
                        'C_Addt_1': C_Addt_1_editor.get (),
                        'C_Addt_2': C_Addt_2_editor.get ()})

            conn.commit()
            conn.close()

代码示例2 #

("UPDATE car_exp SET C_ID=%s,C_Date=%s,C_Payment=%s,C_CAA=%s,C_Maintenance=%s,C_Insurance=%s,C_Gas=%s,C_Addt_1=%s,C_Addt_2=%s, WHERE C_ID = :C_ID",{
                        'C_ID': C_ID_editor.get(),
                        'C_Date': C_Date_editor.get (),
                        'C_Payment': C_Payment_editor.get (), 
                        'C_CAA': C_CAA_editor.get (),
                        'C_Maintenance': C_Maintenance_editor.get (),
                        'C_Gas': C_Gas_editor.get (),
                        'C_Addt_1': C_Addt_1_editor.get (),
                        'C_Addt_2': C_Addt_2_editor.get ()})

报错信息 #

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 ':C_ID,
C_Date =:C_Date,
C_Paym' at line 2

错误原因分析 #

1. 占位符格式错误：MySQL Connector/Python不支持:参数名这种占位符（这是SQLAlchemy、psycopg2等工具的用法），它只认%s位置占位符或%(参数名)s命名占位符。
2. 参数键名不匹配：代码示例1里的参数字典写了'Date'，但SQL里的列名是C_Date，两者不对应；同时还漏了C_Insurance的参数获取。
3. SQL语法错误：代码示例2里的C_Addt_2=%s后面多了个逗号，导致SQL解析失败；而且WHERE子句混用了:C_ID占位符，和前面的%s格式不统一。
4. 不必要的主键更新：一般不建议修改主键C_ID（除非特殊需求），UPDATE语句里保留这列容易误改唯一标识。

修正后的代码 #

方案1：使用命名占位符（可读性更强） #

def update():
    cur, conn = connection.get_connection()
    record_id = delete_box.get()

    # 不需要修改C_ID就删掉SET里的C_ID行
    update_sql = """
        UPDATE car_exp 
        SET 
            C_Date = %(C_Date)s, 
            C_Payment = %(C_Payment)s, 
            C_CAA = %(C_CAA)s, 
            C_Maintenance = %(C_Maintenance)s, 
            C_Insurance = %(C_Insurance)s, 
            C_Gas = %(C_Gas)s, 
            C_Addt_1 = %(C_Addt_1)s, 
            C_Addt_2 = %(C_Addt_2)s 
        WHERE C_ID = %(record_id)s
    """
    
    params = {
        'record_id': record_id,
        'C_Date': C_Date_editor.get(),
        'C_Payment': C_Payment_editor.get(), 
        'C_CAA': C_CAA_editor.get(),
        'C_Maintenance': C_Maintenance_editor.get(),
        'C_Insurance': C_Insurance_editor.get(),  # 补上之前遗漏的参数
        'C_Gas': C_Gas_editor.get(),
        'C_Addt_1': C_Addt_1_editor.get(),
        'C_Addt_2': C_Addt_2_editor.get()
    }

    cur.execute(update_sql, params)
    conn.commit()
    conn.close()

方案2：使用位置占位符（更简洁） #

def update():
    cur, conn = connection.get_connection()
    record_id = delete_box.get()

    update_sql = """
        UPDATE car_exp 
        SET 
            C_Date = %s, 
            C_Payment = %s, 
            C_CAA = %s, 
            C_Maintenance = %s, 
            C_Insurance = %s, 
            C_Gas = %s, 
            C_Addt_1 = %s, 
            C_Addt_2 = %s 
        WHERE C_ID = %s
    """
    
    # 参数顺序必须和SQL里的%s顺序完全一致
    params = (
        C_Date_editor.get(),
        C_Payment_editor.get(), 
        C_CAA_editor.get(),
        C_Maintenance_editor.get(),
        C_Insurance_editor.get(),
        C_Gas_editor.get(),
        C_Addt_1_editor.get(),
        C_Addt_2_editor.get(),
        record_id
    )

    cur.execute(update_sql, params)
    conn.commit()
    conn.close()

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