使用 `executemany` 更新现有 SQLite3 数据库中的条目(使用 Python sqlite3) [英] Using `executemany` to update entries in an existing SQLite3 database (using Python sqlite3)
问题描述
我知道 executemany
可用于方便地向数据库添加新条目;与 for 循环中的单个 execute
相比,可用于减少 Python 方法调用开销.但是,我想知道这是否可以与 SQLite 的 UPDATE
一起使用.
I know that executemany
can be used to conveniently add new entries to a database; useful to reduce the Python method-call overheads compared to single execute
s in a for loop. However, I am wondering if this could work with SQLite's UPDATE
.
更具体地说,请考虑以下设置:
More concretely, consider the following setup:
cnx = sqlite3.connect(DATABASE)
c = cnx.cursor()
for path in paths:
for data in some_computation(path):
c.execute("UPDATE TABLENAME SET cont=? WHERE id=?", (data[1], data[0]))
cnx.commit()
cnx.close()
我什至不确定下面的方法是否会更快(必须对其进行基准测试),但问题是它不起作用,因为我认为我做错了.在下面的代码片段中使用 executemany
来完成我上面发布的任务有什么提示吗?
I am not even sure if the approach below would be any faster (would have to benchmark it), but the problem is that it doesn't work, because I am doing it incorrectly I assume. Any tips to use executemany
in the code snippet below to accomplish the task that I posted above?
cnx = sqlite3.connect(DATABASE)
c = cnx.cursor()
for path in paths:
data_ids, data_conts = [], []
for data in some_computation(path):
if len(data_ids) >= CHUNKSIZE:
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts, data_ids))
cnx.commit()
data_ids, data_conts = [], []
data_ids.append(data[0])
data_conts.append(data[1])
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts, data_ids))
cnx.commit()
cnx.commit()
cnx.close()
非常感谢您的提示和见解!
Many thanks for tips and insights!
编辑 1:
底部示例的问题:
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 50000 supplied.
(其中 CHUNKSIZE=50000)
(where CHUNKSIZE=50000)
编辑 2:
出现同样的错误
cnx = sqlite3.connect(DATABASE)
c = cnx.cursor()
for path in paths:
data_conts = []
for data in some_computation(path):
if len(data_ids) >= CHUNKSIZE:
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts,))
cnx.commit()
data_conts = []
data_conts.append([data[1], data[0]])
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", (data_conts,))
cnx.commit()
cnx.commit()
cnx.close()
但是多亏了@falsetru,我才注意到我的错误,应该是
but thanks to @falsetru I then noticed my error, It should be
... WHERE id=?", data_conts)
而不是
... WHERE id=?", (data_conts,))
推荐答案
你需要传递一个序列序列 ([[cont,id], [cont,id], [cont,id], ...]
,不是 [cont, cont, cont, ...], [id, id, id, ..]
):
You need to pass a sequence of sequences ([[cont,id], [cont,id], [cont,id], ...]
, not [cont, cont, cont, ...], [id, id, id, ..]
):
for path in paths:
params = []
for data in some_computation(path):
if len(data_ids) >= CHUNKSIZE:
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", params)
cnx.commit()
params = []
params.append([data[1], data[0]])
if params:
c.executemany("UPDATE TABLENAME SET cont=? WHERE id=?", params)
cnx.commit()
这篇关于使用 `executemany` 更新现有 SQLite3 数据库中的条目(使用 Python sqlite3)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!