Using `executemany` to update records in an existing SQLite3 database (using Python sqlite3)

Question

I know what executemanycan be used to conveniently add new records to the database; useful for reducing the overhead of a Python method method compared to a single one executein a for loop. However, I am wondering if this can work with SQLite UPDATE.

More specifically, consider the following setting:

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()

I'm not even sure if the approach below would be faster (I would have to compare it), but the problem is that it does not work because I am doing it wrong, I suppose. Any tips for using executemanyin the code snippet below to complete the task 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()

Thanks so much for the tips and ideas!

EDIT 1:

:

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 50000 supplied.

( 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 ,

... WHERE id=?", data_conts)

... WHERE id=?", (data_conts,))

Answer 1

([[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()

Answer 2

, , , , zip (conts, ids), conts id - . .