[Django]-Efficient way to bulk insert with get_or_create() in Django (SQL, Python, Django)

by

10👍

✅

You can’t do decent bulk insertions with get_or_create (or even create), and there’s no API for doing this easily.

If your table is simple enough that creating rows with raw SQL isn’t too much of a pain, it’s not too hard; something like:

INSERT INTO site_entry (field1, field2)
(
         SELECT i.field1, i.field2
         FROM (VALUES %s) AS i(field1, field2)
         LEFT JOIN site_entry as existing
                 ON (existing.field1 = i.field1 AND existing.field2 = i.field2)
         WHERE existing.id IS NULL
)

where %s is a string like ("field1, field2"), ("field3, field4"), ("field5, field6") that you’ll have to create and escape properly yourself.

👤Glenn Maynard

4👍

Depends on what you are aiming at. You can use manage.py‘s loaddata function to load data in a appropriate format (JSON, XML, YAML,…).

See also this discussion.

👤Felix Kling

1👍

Since 1.4 you can do bulk_create

See the docs

*Do pay attention to the caveats though (most important one being that the model’s save() method will not be called, and thus the pre_save and post_save signals will not be sent.) *

👤Jay

0👍

I’d say there isn’t.

But I wonder what type your items are, if they have field1 and field2 as attributes. Looks like there exists another class representing an entry but is not derived from models.Model. Maybe you can omit this class and create Entry instances immediately instead of creating those items.

👤Johannes Charra

0👍

If you’re not sure whether the things in your item_list already exist in your DB, and you need the model objects, then get_or_create is definitely the way to go.

If you know the items are NOT in your DB, you would be much better doing:

for item in item_list:
    new = Entry.objects.create(
        field1 = item.field1,
        field2 = item.field2,
    )

And if you don’t need the objects, then just ignore the return from the function call. It won’t speed the DB stuff, but it will help with memory management if that’s an issue.

If you’re not sure whether the data is already in the DB, but either field has a unique=True flag on it, then the DB will enforce the uniqueness, and you can just catch the exception and move on. This will prevent an extra DB hit by avoiding the attempt to select the existing object.

from django.db import IntegrityError

for item in item_list:
    try:
        new = Entry.objects.create(
            field1 = item.field1,
            field2 = item.field2,
        )
    except IntegrityError:
        continue

You could increase speed in either case by manually managing the transactions. Django will automatically create and commit a transaction for every save, but provides some decorators that will greatly increase efficiency if you know you’ll be doing a lot of DB saves in a particular function. The Django docs do a better job of explaining all of this than I can here, but you’ll probably want to pay particular attention to django.db.transaction.commit_on_success

👤Paul McLanahan

Leave a comment