试图通过更新我的数据库中的a值来更新我的库存量，我早些时候用ID得到了这个值

Question

需要用新的数字更新我的SQL数据库，但我似乎无法做到。我在SQLite中使用python，我对SQL并不太擅长，所以如果有任何帮助，我将不胜感激。我找不到直接更新它的方法，所以我想一个解决办法是从数据库中获取数字，然后将其转换回一个字符串以重新输入。我相信我会让这件事变得更加困难，因为它需要被卡住很长时间，真的不知道现在该怎么办

def delete():
#create a database or connect ot one
conn = sqlite3.connect('Food_item.db')
#create cursor
c = conn.cursor()


# Delete a record
if (len(f_name_editor.get()) != None and len(l_name_editor.get()) == 0):
print ("hello")
c.execute("DELETE from FOOD WHERE oid= " + f_name_editor.get())

elif (len(f_name_editor.get()) != None and len(l_name_editor.get()) != None):

c.execute("SELECT QUANTITY FROM FOOD WHERE ADMIN_ID= "+ f_name_editor.get())
record_id = f_name_editor.get()
records = c.fetchone()[0]
sub_num= l_name_editor.get()
new_items= (records-int(sub_num))
Removed_items =str(new_items)
print(Removed_items)

c.execute("UPDATE QUANTITY FROM FOOD  =" +Removed_items+" WHERE ADMIN_ID= "+f_name_editor.get())

Answer 1

代码中的UPDATESQL无效，应该是：

c.execute('UPDATE FOOD SET QUANTITY = ? WHERE ADMIN_ID = ?',
(Removed_items, f_name_editor.get()))
conn.commit()

最好使用占位符来避免SQL注入，并且需要调用conn.commit()才能使更新生效。

---

请注意，如果l_name_editor.get()是要从QUANTITY中扣除的金额，则不需要获得当前的QUANTITY。只需使用一个UPDATESQL，如下所示：

c.execute('UPDATE FOOD SET QUANTITY = QUANTITY - ? WHERE ADMIN_ID = ?',
(l_name_editor.get(), f_name_editor.get()))
conn.commit()