我的 python sqlite 更新语句没有提交

Question

我正在尝试编写一些Python SQLite3 CRUD语句。我认为我的更新声明写得很正确，但显然有一个问题我看不出来。语句执行，但数据库不更新。Insert和Read语句工作正常。我省略了createdatabase和db_insert((函数，因为它们正在工作。。。

我的代码：

import sqlite3 as sql

def db_read():
try:
conn = sql.connect(database_file)
c = conn.cursor()
with conn:
c.execute("SELECT * FROM netdevices")
result = c.fetchall()
conn.commit()
return result
except:
print("Database Read Error")
print(traceback.format_exc())

def db_update(record_id, ip_address, device_name, connection_type, username, password):
try:
conn = sql.connect(database_file)
c = conn.cursor()
with conn:
sql_cmd = "UPDATE netdevices SET ip_address = @ip_address, device_name = @device_name, connection_type = @connection_type, username = @username, password = @password WHERE id=@record_id"
sql_values = [record_id, ip_address, device_name, connection_type, username, password]
c.execute(sql_cmd, sql_values)
conn.commit()
print('Database Update Successful')
except:
print("Database Update Error")
print(traceback.format_exc())


db_insert(1, '10.10.10.1', 'Gateway', 'ssh', 'cisco', 'cisco')
db_insert(2, '10.10.10.2', 'WorkshopSwitch', 'telnet', 'cisco', 'cisco')
print('READ: ' + str(db_read()))
print('-------')
db_update(2, '10.10.10.250', 'WorkshopSwitch', 'ssh', 'admin', 'P@ssw0rd1')            # Update not working???
print('-------')
print('READ: ' + str(db_read()))

它给出以下输出：

Database Insert Successful
Database Insert Successful
READ: [(1, '10.10.10.1', 'Gateway', 'ssh', 'cisco', 'cisco'), (2, '10.10.10.2', 'WorkshopSwitch', 'telnet', 'cisco', 'cisco')]
-------
Database Update Successful
-------
READ: [(1, '10.10.10.1', 'Gateway', 'ssh', 'cisco', 'cisco'), (2, '10.10.10.2', 'WorkshopSwitch', 'telnet', 'cisco', 'cisco')]

正如您所看到的，没有进行更新。也许我的简单尝试遗漏了一些SQL错误：except:语句？

提前感谢你的建议。

Answer 1

我意外地找到了答案：(

sql_values数组的顺序必须与语句中的@variables相同。

我在UPDATE语句的末尾有id=@record_id，但在数组中列在第一位。

Python像旧式字符串格式一样按顺序匹配变量，而不是查找@variable并与数组中的同一变量匹配。

错误：

sql_cmd = "UPDATE netdevices SET ip_address = @ip_address, device_name = @device_name, connection_type = @connection_type, username = @username, password = @password WHERE id=@record_id"
sql_values = [record_id, ip_address, device_name, connection_type, username, password,]

正确：

sql_cmd = "UPDATE netdevices SET ip_address = @ip_address, device_name = @device_name, connection_type = @connection_type, username = @username, password = @password WHERE id=@record_id"
sql_values = [ip_address, device_name, connection_type, username, password, record_id]