如何用Python中的变量更新sqlite条目

Question

我目前有一个用测试条目创建的sqlite表，需要能够使用来自用户的请求字符串更新条目在数据库中的rack和rackloc位置(如下所示(

# Move asset to another location in depot
def move_data_to_rack():
serial_to_move = askstring('Move Asset', 'Please enter the Serial Number you want to move')
serial_to_move = serial_to_move.upper()
to_rack = askstring('Move Asset', 'Please enter the Rack you want to move to')
to_rack = to_rack.upper()
to_rack_loc = askstring('Move Asset', 'Please enter the Rack LOC you want to move to')
to_rack_loc = to_rack_loc.upper()
cursor.execute('''UPDATE depot_racks SET rack, rackloc = ?, ? WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))
db.commit()
messagebox.showinfo('Serial Moved', serial_to_move + ' has now been moved to ' + to_rack + to_rack_loc)

该表的创建方式如下。。。

# Depot Table creation
cursor.execute('''
CREATE TABLE IF NOT EXISTS depot_racks(
id INTEGER PRIMARY KEY,
rack TEXT,
rackloc TEXT,
floorzone TEXT,
product TEXT,
serial TEXT,
status TEXT,
entdate TEXT,
servdate TEXT,
parts TEXT,
pat TEXT)''')

当上面的函数运行时，我得到以下错误。。。

Exception in Tkinter callback
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/tkinter/__init__.py", line 1883, in __call__
return self.func(*args)
File "/Users/grantpeach/Library/Mobile Documents/com~apple~CloudDocs/Programming/Python Programming/Personal Projects/Arjo RackSys/RackSysMain.py", line 104, in move_data_to_rack
cursor.execute('''UPDATE depot_racks SET rack, rackloc = ?, ? WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))
sqlite3.OperationalError: near ",": syntax error

因此，该功能背后的想法是，用户将添加他们想要更新的序列号，然后输入新的机架位置(例如RACK1(和新的机架定位(例如a(。

然后，它应该找到序列号，并且只使用该序列号更新行的机架和机架位置部分。

非常感谢您对此的任何帮助

Answer 1

每个?占位符都必须分配给相应的列名：

cursor.execute('''UPDATE depot_racks SET rack = ?, rackloc = ? WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))

如果你的SQLite版本是3.15.0+，你也可以使用行值语法：

cursor.execute('''UPDATE depot_racks SET (rack, rackloc) = (?, ?) WHERE serial = ?''', (to_rack, to_rack_loc, serial_to_move,))