Python sqlite3 OperationalError:接近“?":语法错误 [英] Python sqlite3 OperationalError: near "?": syntax error

问题描述

试图让用户更新名为Scenario"的特定表的现有记录的列值.正在更新的记录由名为Scenario_Key"的索引列标识，对于此类的每个实例都是唯一的.我已经拥有的代码生成了一个键值对字典，其中 key 是要更新的列的名称，value 是插入其中的值.要更新 sqlite 数据库，我正在尝试以下操作:

Trying to let users update column values on existing records for a specific table named "Scenario." The record being updated is identified by an index column called "Scenario_Key", unique to each instance of this class. The code I already have produces a dictionary of key, value pairs where key is the name of the column being updated and value is the value being inserted into it. To update the sqlite database I'm trying the following:

cursor.execute("""UPDATE Scenario SET ?=? WHERE Scenario_Key=?;""", (key, new_val, self.scenario_key))

但是当我尝试通过单击保存并关闭"按钮执行时，我得到以下信息:

But when I try to execute by clicking the "Save and Close" button, I get the following:

Traceback (most recent call last):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-tk/Tkinter.py", line 1536, in __call__
return self.func(*args)
File "/Users/xxx/Documents/Consulting/DCA/Damage Control Assistant/EditScenarioWindow.py", line 91, in <lambda>
SaveAndCloseButton = Button(ButtonFrame, text="Save and Close", command=lambda: self.SaveAndCloseWindow())
File "/Users/xxx/Documents/Consulting/DCA/Damage Control Assistant/EditScenarioWindow.py", line 119, in SaveAndCloseWindow
cursor.execute(cmd_string, (key, new_val, self.scenario_key))
OperationalError: near "?": syntax error

我已经阅读了sqlite3.OperationalError: near "?": syntax error，但我正在尝试执行单个 sqlite 查询，其中已经计算了所有变量，而不是从数据库中获取值并从那里构建查询.我将位置参数作为元组提供.那么为什么 sqlite3 不喜欢我提交的查询?

I've read over sqlite3.OperationalError: near "?": syntax error, but I'm trying to do a single sqlite query where all the variables have already been calculated, not get values from the database and build a query from there. I'm supplying the positional arguments as a tuple. So why doesn't sqlite3 like the query I'm submitting?

推荐答案

您不能参数化列名.虽然意识到 SQL 注入 攻击的可能性，但您可以改为:

You cannot parametrize column names. While being cognisant of the possibility of SQL Injection attacks, you could instead do:

cursor.execute("""UPDATE Scenario 
                     SET {}=? 
                   WHERE Scenario_Key=?;""".format(key), 
               (new_val, self.scenario_key))

这篇关于Python sqlite3 OperationalError:接近“?":语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！