Python更新SQLite数据触发OperationalError语法错误，如何修复？

嘿，我一眼就揪出问题所在了——你的SQL UPDATE语句语法写错啦！

问题根源 #

SQLite的UPDATE语句里，SET后面的字段赋值列表不需要加括号，你额外加的括号直接让数据库解析器懵圈了，这就是报错的核心原因。另外还有个必须重视的点：用f-string拼接SQL语句存在严重的SQL注入风险，要是你的字段值里包含引号这类特殊字符，还会触发更多语法问题。

你的原代码 #

def saveChanges(player_to_save, player_id):
    db = sqlite3.connect('db_player.db')
    sql = db.cursor()
    sql.execute(f"""UPDATE users SET (lvl = {player_to_save.lvl}, exp = {player_to_save.exp}, hp = {player_to_save.hp}, items = {converToJson_items(player_to_save)}, weapon = {convertToJson_weapon(player_to_save)}, armour = {convertToJson_armour(player_to_save)}, bounus = {convertToJson_bounus(player_to_save)}, bonuses_award = {convertToJson_bonuses_award(player_to_save)}, stats = {converToJson_stats(player_to_save)} ) WHERE login = '{player_id}'""")

报错信息 #

line 384, in saveChanges
sql.execute(f"""UPDATE users SET (lvl = {player_to_save.lvl}, exp = {player_to_save.exp}, hp = {player_to_save.hp}, items = {converToJson_items(player_to_save)}, weapon = {convertToJson_weapon(player_to_save)}, armour = {convertToJson_armour(player_to_save)}, bounus = {convertToJson_bounus(player_to_save)}, bonuses_award = {convertToJson_bonuses_award(player_to_save)}, stats = {converToJson_stats(player_to_save)} ) WHERE login = '{player_id}'""")
sqlite3.OperationalError: near "=": syntax error

修复后的代码 #

我帮你改成了参数化查询的写法，既修复了语法错误，又彻底解决了SQL注入的安全隐患：

def saveChanges(player_to_save, player_id):
    db = sqlite3.connect('db_player.db')
    sql = db.cursor()
    # 去掉SET后的括号，用?作为参数占位符，这是SQLite参数化查询的标准写法
    sql_query = """
        UPDATE users 
        SET lvl = ?, exp = ?, hp = ?, items = ?, weapon = ?, 
            armour = ?, bounus = ?, bonuses_award = ?, stats = ?
        WHERE login = ?
    """
    # 把所有参数整理成元组传入，sqlite会自动处理字符串转义和类型匹配
    params = (
        player_to_save.lvl,
        player_to_save.exp,
        player_to_save.hp,
        converToJson_items(player_to_save),
        convertToJson_weapon(player_to_save),
        convertToJson_armour(player_to_save),
        convertToJson_bounus(player_to_save),
        convertToJson_bonuses_award(player_to_save),
        converToJson_stats(player_to_save),
        player_id
    )
    sql.execute(sql_query, params)
    # 务必提交事务！不然修改只会停留在内存里，不会写入数据库文件
    db.commit()
    # 用完记得关闭连接，避免资源泄漏
    db.close()

额外提醒 #

• 注意你代码里的拼写错误：bounus应该是bonus吧？如果数据库字段名是bonus，这里一定要同步修改，不然会触发字段不存在的错误。
• 养成事务提交和关闭连接的好习惯，这能避免很多莫名其妙的数据丢失问题。

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