MySQL语法错误排查求助：Python插入UPI数据时执行SQL报错

错误原因 #

你遇到的SQL语法错误，根源在于mysql.connector的execute方法参数格式不正确：
execute方法要求第二个参数必须是序列类型（元组、列表等），但你直接传入了单个字符串变量upi，导致数据库无法正确解析参数绑定，从而报错'%s)'附近语法错误。

修正方案 #

将单个变量包装成元组（推荐）或列表即可解决问题：
把val = upi修改为val = (upi,)（注意元组末尾的逗号不能省略，否则会被识别为单个变量），或者val = [upi]。

修正后的完整代码 #

upi = upi_entry.get()
mysqldb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="deol9646",
    database="train_login",
)
mycursor = mysqldb.cursor()

try:
    mycursor.execute(
        """create table if not exists upi_data(upi text)"""
    )
    sql = "INSERT INTO UPI_DATA (UPI) VALUES (%s)"
    val = (upi,)  # 改为元组格式
    mycursor.execute(sql, val)
    mysqldb.commit()
    lastid = mycursor.lastrowid
    messagebox.showinfo("information", "upi inserted successfully...")
    upi_entry.delete(0, END)
    upi_entry.focus_set()
except Exception as e:
    print(e)
    mysqldb.rollback()
finally:  # 确保连接关闭，避免资源泄漏
    mysqldb.close()

额外优化建议 #

推荐使用with语句管理数据库连接和游标，自动处理资源释放，代码更健壮：

upi = upi_entry.get()
sql = "INSERT INTO UPI_DATA (UPI) VALUES (%s)"
val = (upi,)

try:
    with mysql.connector.connect(
        host="localhost",
        user="root",
        password="deol9646",
        database="train_login",
    ) as mysqldb:
        with mysqldb.cursor() as mycursor:
            mycursor.execute("""create table if not exists upi_data(upi text)""")
            mycursor.execute(sql, val)
            mysqldb.commit()
            messagebox.showinfo("information", "upi inserted successfully...")
            upi_entry.delete(0, END)
            upi_entry.focus_set()
except Exception as e:
    print(e)

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