How do you reduce the cost of inserting many rows into SQLite?

Short answer

Batch the inserts into a single transaction and use executemany, instead of running a commit for every row.

Explanation

Every commit forces a safe write to disk (fsync), so inserting row by row with a commit for each can be hundreds of times slower. Using with conn: opens a transaction, commits on success, and rolls back on error. For very large volumes, split the insertion into bounded batches (such as 10 thousand rows) so the transaction does not grow too long. Enabling WAL mode improves reading concurrently with writing.

Code example

import sqlite3

conn = sqlite3.connect("app.db")
rows = [(i, f"user{i}") for i in range(100_000)]

with conn:  # One transaction: commit on success and rollback on error
    conn.executemany("INSERT INTO users (id, name) VALUES (?, ?)", rows)

conn.close()

Common mistakes

• Calling conn.commit() inside the loop after every row.
• Building a huge SQL statement by string concatenation instead of executemany with parameters.

Follow-up questions

• When do generators improve memory use and when do they not improve time?

Sources

• Python documentation — sqlite3: Transaction control