PyMySQL执行多语句SQL报1064错误，本地MySQL运行正常求解决

错误根源 #

你遇到的问题是因为MySQL Python驱动（如mysqlclient）默认不允许在单个cursor.execute()调用中执行多条用分号分隔的SQL语句，这是驱动的安全限制（防范SQL注入），哪怕这些语句在MySQL客户端中能正常执行也不行。

解决方案 #

方式1：拆分SQL语句，逐个执行（推荐） #

将原本的多语句拆分成独立的SQL，分别调用cursor.execute()执行，这是最安全且符合驱动规范的做法：

def delete_people(tname, del_name):
    try:
        with connection.cursor() as cursor:
            # 1. 删除指定ID的记录
            delete_sql = 'DELETE FROM project3.%s WHERE id = %s;'
            cursor.execute(delete_sql % (tname, del_name))
            
            # 2. 删除原id列
            drop_id_sql = 'ALTER TABLE project3.%s DROP id;'
            cursor.execute(drop_id_sql % (tname,))
            
            # 3. 重新添加自增id列
            add_id_sql = 'ALTER TABLE project3.%s ADD id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;'
            cursor.execute(add_id_sql % (tname,))
            
            # 4. 重置自增起始值
            reset_auto_sql = 'ALTER TABLE project3.%s AUTO_INCREMENT = 1;'
            cursor.execute(reset_auto_sql % (tname,))
            
            connection.commit()
            print('deleted successfully')

    except Exception as ex:
        print("Connection refused...")
        print(ex)

额外优化建议 #

• 原代码中用%d格式化del_name存在风险，如果del_name是字符串类型会触发类型错误，换成%s更通用。
• 为降低SQL注入风险，对于SQL中的值（比如del_name），建议使用参数化查询（虽然表名无法用参数化，只能格式化）：

def delete_people(tname, del_name):
    try:
        with connection.cursor() as cursor:
            # 用参数化方式传递id值
            delete_sql = f'DELETE FROM project3.{tname} WHERE id = %s;'
            cursor.execute(delete_sql, (del_name,))
            
            drop_id_sql = f'ALTER TABLE project3.{tname} DROP id;'
            cursor.execute(drop_id_sql)
            
            add_id_sql = f'ALTER TABLE project3.{tname} ADD id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;'
            cursor.execute(add_id_sql)
            
            reset_auto_sql = f'ALTER TABLE project3.{tname} AUTO_INCREMENT = 1;'
            cursor.execute(reset_auto_sql)
            
            connection.commit()
            print('deleted successfully')

    except Exception as ex:
        print("Connection refused...")
        print(ex)

方式2：启用多语句执行（不推荐） #

如果一定要在单个调用中执行多语句，可以创建支持多语句的cursor，但这会大幅提升SQL注入风险，仅在完全信任输入源的场景下使用：

from MySQLdb.constants import CLIENT

def delete_people(tname, del_name):
    try:
        # 创建支持多语句的连接与cursor
        with connection.cursor(connection.cursorclass) as cursor:
            del_ppl = 'DELETE FROM project3.%s WHERE id = %s; ALTER TABLE project3.%s DROP id; ' \
                      'ALTER TABLE project3.%s ADD id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; ' \
                      'ALTER TABLE project3.%s AUTO_INCREMENT = 1;'
            cursor.execute(del_ppl % (tname, del_name, tname, tname, tname))
            connection.commit()
            print('deleted successfully')

    except Exception as ex:
        print("Connection refused...")
        print(ex)

注意：需要先导入CLIENT常量，且创建连接时需指定client_flag=CLIENT.MULTI_STATEMENTS才能启用多语句功能。

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