Bulk and Batch Operations

Inserting, updating, or deleting one row at a time in a loop issues one statement per row - a round trip each, a transaction boundary each - and at scale that is the difference between a two-second load and a two-minute one. The bulk operations collapse the loop into a single (or a few) statements: insert_many for the batch insert, the query-level update and delete for the batch mutation, and the chunking that keeps a very large batch from exceeding the database's statement limits. This chapter covers insert_many and its options, the batch update/delete, the upsert, and the chunking rule for the large loads.

insert_many: The Batch Insert

from peewee import *

# the data as a list of dicts - one dict per row
rows = [
    {"name": "A1", "email": "[email protected]"},
    {"name": "A2", "email": "[email protected]"},
    {"name": "A3", "email": "[email protected]"},
]

# a single INSERT with multiple value sets:
result = Author.insert_many(rows).execute()
print(result.rowcount)          # 3 - one statement, not three

# the arguments that matter:
#   insert_many(rows, fields=[...]) - name the columns explicitly
#   insert_many(rows, bulk=True)    - the driver's bulk path (faster
#                                     on the supporting backends)
#   insert_many(rows) with 10k rows - chunk it (below)

# the rows are plain dicts, so the data comes from anywhere:
# a list, a generator materialized, the parsed rows of a CSV/JSON

insert_many(list_of_dicts) is the batch insert: one INSERT statement carrying many value sets instead of one statement per row, which removes the per-row round trip and the per-row overhead that make the loop slow. Each dict is a row, keyed by the field names, and the fields=[...] argument names the columns explicitly (the order and the subset), while bulk=True uses the driver's bulk-insert path where it is faster. The result's rowcount is the number of rows written, which is the verification that the batch landed. The data shape - a list of dicts - is what makes the source flexible: the rows come from a parsed file, an API response, a transformed dataset, and the same insert_many consumes them. The contrast with the loop is the entire value: the loop is N statements and N round trips, the batch is one, and at N in the thousands that is the difference between acceptable and not.

Chunking: The Large Load

from peewee import *

# a very large batch must be chunked - a single statement with
# 100k value sets exceeds the parameter limit of the backend
# (and the memory of the client), so the load is a loop of batches:
def bulk_load(rows, chunk=1000):
    # 1000 is the working default - large enough to amortize the
    # round trip, small enough to stay under the limits
    loaded = 0
    for i in range(0, len(rows), chunk):
        batch = rows[i:i + chunk]
        n = Author.insert_many(batch).execute()
        loaded += n.rowcount
    return loaded

# the chunk size is the knob:
#   too small - the round trips add back up
#   too large - the statement hits the parameter limit (the
#     backend-specific cap, often a few thousand parameters)
# 1000 rows x a handful of columns stays comfortably under it

# and the whole load can be ONE transaction, so a failure at row
# 50000 rolls back the load instead of leaving 49999 rows behind:
with atomic(db):
    bulk_load(huge_dataset)

The chunk is the rule for the large load: a single insert_many with a very large list hits the backend's parameter limit (a statement can only bind so many values) and the client's memory, so the load is a loop over fixed-size batches - 1000 rows is the working default, large enough that the round trips are amortized and small enough to stay under the limit. The chunk size is the knob with the two failure modes at the extremes (too small re-adds the round-trip cost, too large trips the parameter cap), and the per-chunk rowcount summed is the verification. The combination with the transactions chapter is the correctness layer: wrapping the whole load in atomic(db) makes the multi-chunk load one transaction, so a failure on the 50th chunk rolls back the 49 before it rather than leaving the table half-loaded - the bulk and the atomic together are the "load a large dataset or none" guarantee.

Batch Update and Delete

from peewee import *

# the query-level update IS the batch update - one statement
# for every matching row, no per-row load:
n = (Tag.update(active=False)
        .where(Tag.name.in_(["old1", "old2", "old3"])))\
    .execute()
print(n)   # the rows updated, in one UPDATE

# a conditional batch: the update expression with a condition
# on the data, applied to all matches at once
m = (Article.update(published=True)
        .where((Article.rating > 4) & (Article.views > 1000)))\
    .execute()

# the batch delete is the same shape:
d = (Comment.delete()
       .where(Comment.created_at < datetime(2025, 1, 1)))\
   .execute()

# the contrast with the loop:
#   for t in Tag.select().where(...): t.active=False; t.save()
# is one SELECT plus one UPDATE per row - the query-level form
# is one UPDATE, and it never loads the rows into Python

The batch mutation is the query-level update() and delete() from the CRUD chapter, seen from the scale side: a condition over the rows, applied in one statement to every match, returning the affected count. The loop version - select the rows, mutate each in Python, save() each - is a SELECT plus an UPDATE per row, and it also pulls the rows into memory, which the query-level form never does. The batch form is the right tool whenever the change is "all rows matching X get Y" - the deactivation of a tag set, the publish-by-threshold, the purge of old rows - because the logic is a condition, not a per-row decision, and a condition is exactly what a single statement expresses. The per-row loop is only right when each row's new value depends on its own data in a way the SQL cannot express (a transformation that needs Python), and even then the read and the write should be batched separately.

The Upsert: Insert or Update on Conflict

from peewee import *

# the sync case: the row may exist (match on the unique key),
# and the operation is "insert if absent, update if present":
(Article.insert(
        title="Synced Post",
        slug="synced-post",
        views=50,
    )
     .on_conflict(
         conflict_target=[Article.slug],        # the unique key
         preserve=[Article.id],                 # keep the existing id
         update={Article.views: 50,
                 Article.title: "Synced Post"},
     )
     .execute())

# a running counter that must not lose concurrent increments:
Article.update(views=Article.views + 1)\
      .where(Article.slug == "synced-post").execute()

The upsert - insert(...).on_conflict(...) - is the "insert or update on conflict" for the sync case, where the row's existence is decided by a unique key (the slug) and the operation must be one atomic statement. The conflict_target names the unique constraint, the update dict is what changes when the row exists, and preserve keeps the existing values (the id) rather than overwriting them. It is the race-free alternative to the read-then-write (get_or_create plus a save) - two statements with a window where a concurrent writer interleaves - because the database resolves the conflict in one statement. The companion for the counter is the column-arithmetic update (views = views + 1), which is the race-free increment the CRUD chapter introduced - both are the standing pattern for the "the row is shared, the write must not lose a concurrent one" case, and both are single statements the database executes atomically.

Summary

The bulk insert is insert_many(list_of_dicts) - one statement for many rows, with fields and bulk=True as the options, and the rowcount as the verification - and the large load is chunked (1000-row batches, the working default under the parameter limit) and wrapped in atomic() so a failure rolls back the whole load. The batch mutation is the query-level update()/delete() with a condition - one statement over all the matches, never loading the rows - which is right whenever the change is a condition rather than a per-row decision. The upsert (insert(...).on_conflict(...)) is the single-statement insert-or-update on a unique key, and the column-arithmetic update is the race-free counter. The unifying rule is the one from the CRUD chapter scaled up: express the change as a statement over a set of rows, not a loop over rows, and the round trips and the memory fall away with the loop.