Import and update many rows efficiently

Approach

Build model instances in memory and write them in batches with bulk_create (optionally as an upsert with update_conflicts) and bulk_update. Wrap the import in a transaction so it's all-or-nothing.

Solution

import csv

from django.db import transaction

from shop.models import Product


@transaction.atomic
def import_prices(path: str) -> int:
    with open(path, newline="") as f:
        rows = [
            Product(sku=row["sku"], name=row["name"], price=row["price"])
            for row in csv.DictReader(f)
        ]
    Product.objects.bulk_create(
        rows,
        batch_size=1000,
        update_conflicts=True,                 # INSERT ... ON CONFLICT (sku) DO UPDATE
        unique_fields=["sku"],
        update_fields=["name", "price"],
    )
    return len(rows)


def apply_discount(product_ids: list[int], percent: int) -> None:
    products = list(Product.objects.filter(id__in=product_ids))
    for product in products:
        product.price = product.price * (100 - percent) / 100
    Product.objects.bulk_update(products, ["price"], batch_size=500)

Pitfalls

• Expecting save() overrides and pre_save/post_save signals to run; bulk operations skip them.
• Loading a huge file into memory at once; process it in chunks.
• Forgetting batch_size, producing one enormous SQL statement.

Variations

• For a single computed change, queryset.update(price=F("price") * 0.9) is one query.
• Use COPY (psycopg's copy) for very large PostgreSQL imports.

Further reading: bulk_create