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

by

You want to check out django.db.transaction.commit_manually.

http://docs.djangoproject.com/en/dev/topics/db/transactions/#django-db-transaction-commit-manually

So it would be something like:

from django.db import transaction

@transaction.commit_manually
def viewfunc(request):
    ...
    for item in items:
        entry = Entry(a1=item.a1, a2=item.a2)
        entry.save()
    transaction.commit()

Which will only commit once, instead at each save().

In django 1.3 context managers were introduced.
So now you can use transaction.commit_on_success() in a similar way:

from django.db import transaction

def viewfunc(request):
    ...
    with transaction.commit_on_success():
        for item in items:
            entry = Entry(a1=item.a1, a2=item.a2)
            entry.save()

In django 1.4, bulk_create was added, allowing you to create lists of your model objects and then commit them all at once.

NOTE the save method will not be called when using bulk create.

>>> Entry.objects.bulk_create([
...     Entry(headline="Django 1.0 Released"),
...     Entry(headline="Django 1.1 Announced"),
...     Entry(headline="Breaking: Django is awesome")
... ])

In django 1.6, transaction.atomic was introduced, intended to replace now legacy functions commit_on_success and commit_manually.

from the django documentation on atomic:

atomic is usable both as a decorator:

from django.db import transaction

@transaction.atomic
def viewfunc(request):
    # This code executes inside a transaction.
    do_stuff()

and as a context manager:

from django.db import transaction

def viewfunc(request):
    # This code executes in autocommit mode (Django's default).
    do_stuff()

    with transaction.atomic():
        # This code executes inside a transaction.
        do_more_stuff()

More Related Contents:

  1. Python and SQLite: insert into table
  2. Django: Deploying an application on Heroku with sqlite3 as the database
  3. Getting the SQL from a Django QuerySet [duplicate]
  4. OperationalError: database is locked
  5. How can I get dict from sqlite query?
  6. Using a WHERE ___ IN ___ statement
  7. Django: Adding “NULLS LAST” to query
  8. LEFT JOIN Django ORM
  9. How to see the raw SQL queries Django is running?
  10. How to log all sql queries in Django?
  11. How to get a single result from a SQL query in python?
  12. “no such table” exception
  13. Is this Python code vulnerable to SQL injection? (SQLite3)
  14. pysqlite: Placeholder substitution for column or table names?
  15. sqlite3.OperationalError: unable to open database file
  16. How to perform OR condition in django queryset?
  17. What is the best way to access stored procedures in Django’s ORM
  18. How to upgrade sqlite 3.8.2 to >= 3.8.3
  19. Python SQLite3 SQL Injection Vulnerable Code
  20. How to check the existence of a row in SQLite with Python?
  21. How to quote a string value explicitly (Python DB API/Psycopg2)
  22. Django: sudden DB reset after Heroku sleep
  23. How to delete a record in Django models?
  24. GeoDjango on Windows: “Could not find the GDAL library” / “OSError: [WinError 126] The specified module could not be found”
  25. Serving large files ( with high loads ) in Django
  26. What’s the purpose of Django setting ‘SECRET_KEY’?
  27. Why django urls end with a slash?
  28. Django Tastypie Advanced Filtering: How to do complex lookups with Q objects
  29. How can I specify a database for Django Tests to use instead of having it build it every time?
  30. How to create a django ViewFlow process programmatically

Leave a Comment