Python sqlite3: how to do efficient bulk update?

Question

I can make very efficient bulk inserts in Sqlite3 in Python (2.7) with this code:

cur.executemany("INSERT INTO " + tableName + " VALUES (?, ?, ?, ?);", data)

But I can’t receive updates to work effectively. I thought it might be a database structure / indexing problem, but even in a test database with only one table of 100 rows, the update still takes 2-3 seconds.

I tried different code options. The last code I have is from this answer to the previous question about updating and executing, but it is as slow for me as any other attempt I made:

data = []
for s in sources:
    source_id = s['source_id']
    val = get_value(s['source_attr'])
    x=[val, source_id]
    data.append(x)
cur.executemany("UPDATE sources SET source_attr = ? WHERE source_id = ?", data)
con.commit()

How can I improve this code to perform bulk updates efficiently?

Answer 1

( - UNIQUE).

. ( ), :

CREATE INDEX whatever ON sources(source_id);

source_id , ( ):

CREATE TABLE sources(
    source_id INTEGER PRIMARY KEY,
    source_attr TEXT,
    [...]
);