What is an efficient way of inserting thousands of records into an SQLite table using Django?

python

bulk-create

transaction-management

performance-optimization

byNikita Barsukov·Jan 19, 2025

Leverage Django's bulk_create() function for effective batch insertion of records into your SQLite database in a single, efficient query.

from myapp.models import MyModel

# Prep a bulk of records faster than you can say 'bulk_create!'
bulk_records = [MyModel(field1='value1', etc) for _ in range(thousands)]

# Bulk insert - the magic moment!
MyModel.objects.bulk_create(bulk_records)

This approach reduces the number of database interactions, significantly improving execution time.

Transaction management in Django

Alongside bulk_create(), Django's django.db.transaction.atomic makes a beautiful duet for managing transactions, enhancing overall performance.

Splitting records into chunks

SQLite wrists get tired at a limit of 999 variables per query. So, help out by breaking down large operations into bite-sized chunks.

# Set your chunk size
batch_size = 100  
# Fast food style, split your order into manageable meals
batches = [bulk_records[i:i + batch_size] for i in range(0, len(bulk_records), batch_size)]

with transaction.atomic():  # Because we care about successful operations
    for batch in batches:
        MyModel.objects.bulk_create(batch)

When dealing with heavy data-munching operations, transaction.atomic() can be your best friend, ensuring all operations in the block are completed before sending them all to the database.

Bulk tasks and performance factors

Understanding the knocks and twists of your script's performance is vital:

Non-database operations: These could be potential highway roadblocks for your fast-running bulk tasks.
Scheduled Tasks: If you have reoccurring data insertion needs, set up a cronjob to run bulk insert tasks at a scheduled interval.
Profiler's Eye: Use the Django Debug Toolbar to investigate your SQL queries and isolate any niggling performance issues.

Bypassing SQLite limits

Working around SQLite's variable limit requires some finesse, consider a wrapper function that blends queries to sit under the SQLite limit nicely like a well-mixed drink.

def batch_bulk_create(objects, batch_size):
    for i in range(0, len(objects), batch_size):
        MyModel.objects.bulk_create(objects[i:i+batch_size])

# Portion sizes fit for SQLite
batch_bulk_create(bulk_records, 999)

Things to note when using bulk_create

Save yourselves!: The save method isn't called by bulk_create(), so your UUIDs, signals, and custom save logic will need their own stage.
Playing safe: Data sensitive to duplicates or certain validations might get the stage fright. In such cases, reconsider or finetune the use of bulk_create().
File-borne data: For humongous datasets, check out specialized bulk load tools that play directly with the SQLite files.

Good practices

Optimize your bulk_create() operations with these tips:

Test thoroughly: Ensure your solution scales with various data volumes and complexities
Monitor closely: Keep tabs on your execution times, especially when cronjobs are involved
Eyes on Updates: Regularly check the Django release notes for updates or improvements to bulk_create()
Optimized chaining: Chains of commands like select_related() or prefetch_related() combined with bulk_create(), can further boost performance