使用 `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 executes 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屋！