executemany — Fast Bulk Operations

~10 min · python, bulk, executemany, performance

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Loop in C, not in Python

executemany takes a SQL statement and an iterable of parameter tuples. The driver loops over them in C, reusing the prepared statement. For bulk inserts, this is dramatically faster than a Python for loop calling execute per row — and it pairs naturally with a single transaction.

Three rules:

Use it whenever you have >= 100 rows to write.
Wrap it in a transaction (with conn:).
For very large batches, chunk into 1k–10k rows to keep memory and lock duration sane.

Tip: executemany + transaction is usually the difference between 'inserts feel slow' and 'inserts feel free'. The most common SQLite-is-slow report is missing transactions, not anything else.

Code

executemany with chunking·python

import sqlite3, itertools

def chunks(iterable, n):
    it = iter(iterable)
    while True:
        batch = list(itertools.islice(it, n))
        if not batch:
            return
        yield batch

conn = sqlite3.connect('demo.db')
conn.execute('PRAGMA journal_mode = WAL')

rows = ((f'u{i}@x.com', f'user{i}') for i in range(100_000))

for batch in chunks(rows, 5_000):
    with conn:
        conn.executemany('INSERT INTO users(email, username) VALUES (?, ?)', batch)

External links

sqlite3 — executemany

Exercise

Write a benchmark comparing four approaches to inserting 50,000 rows: (a) loop with execute and autocommit, (b) loop with execute inside one transaction, (c) executemany with one big call, (d) executemany chunked into batches of 5,000 with a transaction per chunk. Plot or print the timings. Explain the gap.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousParameterized Queries — Stop SQL Injection Next →Row Factories — Dict-Like Access

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.