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

Question

我目前有一个用测试条目创建的sqlite表，需要能够使用用户的ask字符串更新DB中条目的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）和新的rackloc位置（如a）

然后，应找到序列号，并仅使用该序列号更新行的rack和rackloc部分

非常感谢您的帮助

Answer 1

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

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

如果您的SQLite版本是3.15.0+，您还可以使用Row Values语法：

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