MySQL 5.7报Not all parameters were used错误咨询

问题复现 #

运行如下Python代码操作MySQL 5.7数据库：

if file == '' :
    authorization_id = 0
    try :
        cnx = mysql.connector.connect(user='******', password='******',
                                      host='**************************',
                                      port='*****',
                                      database='Test')
        
    except mysql.connector.errors.DatabaseError:
        print("Impossible Server Connection :/")
        sys.exit()

    cur = cnx.cursor()
        
    numbers = '0123456789'
    player_id = ''


    for x in range(25) :
        player_id+=(numbers[random.randint(0,9)])     
    
    with open("Player_id.txt","w") as file :
        file.write(player_id)
        list_player_id = []
        list_player_id.append(player_id)
        player_id = list_player_id

    cur.execute(('''INSERT INTO best_score(user_id) VALUES (?)'''),player_id)
    cnx.commit()
    cur.close()

# 以下为重复粘贴的冗余代码
if file == '' :
    authorization_id = 0
    try :
        cnx = mysql.connector.connect(user='username', password='password',
                                      host='hostname',
                                      port='portname',
                                      database='Test')
        
    except mysql.connector.errors.DatabaseError:
        print("Impossible Server Connection :/")
        sys.exit()

    cur = cnx.cursor()
        
    numbers = '0123456789'
    player_id = ''


    for x in range(25) :
        player_id+=(numbers[random.randint(0,9)])     
    
    with open("Player_id.txt","w") as file :
        file.write(player_id)
        list_player_id = []
        list_player_id.append(player_id)
        player_id = list_player_id

    cur.execute(('''INSERT INTO best_score(user_id) VALUES (?)'''),player_id)
    cnx.commit()
    cur.close()

运行后抛出异常：

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

错误原因 #

• 核心触发原因：占位符使用错误。mysql.connector驱动不支持?作为SQL参数占位符，该驱动统一使用%s作为位置参数的占位符（和字段类型无关）。驱动解析SQL时无法识别?为待替换参数位，检测到代码传入了参数但SQL中无匹配的占位符，直接抛出该错误。
• 附加逻辑问题：整段数据库操作逻辑被重复粘贴了两遍，第一部分的cur.close()直接拼接在第二部分逻辑前，实际运行会触发语法问题；将生成参数列表的逻辑放在文件写入的with块缩进内无实际意义，且代码执行完仅关闭游标未关闭数据库连接，会造成连接泄漏。

修复方案 #

1. 将SQL插入语句中的?占位符替换为mysql.connector要求的%s。
2. 调整参数传递格式，传入和占位符数量一一对应的参数元组，删除无意义的列表转换逻辑。
3. 删掉重复粘贴的冗余代码块，调整代码缩进，执行完操作后主动关闭数据库连接。

修复后的可运行代码如下：

import mysql.connector
import random
import sys

file = ''
if file == '' :
    authorization_id = 0
    try :
        cnx = mysql.connector.connect(
            user='******', 
            password='******',
            host='**************************',
            port='*****',
            database='Test'
        )
    except mysql.connector.errors.DatabaseError:
        print("Impossible Server Connection :/")
        sys.exit()

    cur = cnx.cursor()
    numbers = '0123456789'
    player_id = ''

    # 生成25位随机数字用户ID
    for x in range(25):
        player_id += numbers[random.randint(0, 9)]
    
    # 写入ID到本地文件
    with open("Player_id.txt", "w") as f:
        f.write(player_id)

    # 执行插入，使用%s作为占位符，参数传单元素元组
    insert_sql = "INSERT INTO best_score(user_id) VALUES (%s)"
    cur.execute(insert_sql, (player_id,))
    cnx.commit()

    # 释放资源
    cur.close()
    cnx.close()

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