How to 'bulk update' with Django?

Question

I'd like to update a table with Django - something like this in raw SQL:

update tbl_name set name = 'foo' where name = 'bar'

My first result is something like this - but that's nasty, isn't it?

list = ModelClass.objects.filter(name = 'bar')
for obj in list:
    obj.name = 'foo'
    obj.save()

Is there a more elegant way?

Answer 1

Update:

Django 2.2 version now has a bulk_update.

Old answer:

Refer to the following django documentation section

Updating multiple objects at once

In short you should be able to use:

ModelClass.objects.filter(name='bar').update(name="foo")

You can also use F objects to do things like incrementing rows:

from django.db.models import F
Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1)

See the documentation.

However, note that:

• This won't use ModelClass.save method (so if you have some logic inside it won't be triggered).
• No django signals will be emitted.
• You can't perform an .update() on a sliced QuerySet, it must be on an original QuerySet so you'll need to lean on the .filter() and .exclude() methods.

Answer 2

Consider using django-bulk-update found here on GitHub.

Install: pip install django-bulk-update

Implement: (code taken directly from projects ReadMe file)

from bulk_update.helper import bulk_update

random_names = ['Walter', 'The Dude', 'Donny', 'Jesus']
people = Person.objects.all()

for person in people:
    r = random.randrange(4)
    person.name = random_names[r]

bulk_update(people)  # updates all columns using the default db

Update: As Marc points out in the comments this is not suitable for updating thousands of rows at once. Though it is suitable for smaller batches 10's to 100's. The size of the batch that is right for you depends on your CPU and query complexity. This tool is more like a wheel barrow than a dump truck.

Answer 3

Django 2.2 version now has a bulk_update method (release notes).

https://docs.djangoproject.com/en/stable/ref/models/querysets/#bulk-update

Example:

# get a pk: record dictionary of existing records
updates = YourModel.objects.filter(...).in_bulk()
....
# do something with the updates dict
....
if hasattr(YourModel.objects, 'bulk_update') and updates:
    # Use the new method
    YourModel.objects.bulk_update(updates.values(), [list the fields to update], batch_size=100)
else:
    # The old & slow way
    with transaction.atomic():
        for obj in updates.values():
            obj.save(update_fields=[list the fields to update])

Answer 4

Here is a useful content which i found in internet regarding the above question

https://www.sankalpjonna.com/learn-django/running-a-bulk-update-with-django

The inefficient way

model_qs= ModelClass.objects.filter(name = 'bar')
for obj in model_qs:
    obj.name = 'foo'
    obj.save()

The efficient way

ModelClass.objects.filter(name = 'bar').update(name="foo") # for single value 'foo' or add loop

Using bulk_update

update_list = []
model_qs= ModelClass.objects.filter(name = 'bar')
for model_obj in model_qs:
    model_obj.name = "foo" # Or what ever the value is for simplicty im providing foo only
    update_list.append(model_obj)
    
ModelClass.objects.bulk_update(update_list,['name'])

Using an atomic transaction

from django.db import transaction

with transaction.atomic():
    model_qs = ModelClass.objects.filter(name = 'bar')
    for obj in model_qs:
       ModelClass.objects.filter(name = 'bar').update(name="foo")

Any Up Votes ? Thanks in advance : Thank you for keep an attention ;)

Answer 5

If you want to set the same value on a collection of rows, you can use the update() method combined with any query term to update all rows in one query:

some_list = ModelClass.objects.filter(some condition).values('id')
ModelClass.objects.filter(pk__in=some_list).update(foo=bar)

If you want to update a collection of rows with different values depending on some condition, you can in best case batch the updates according to values. Let's say you have 1000 rows where you want to set a column to one of X values, then you could prepare the batches beforehand and then only run X update-queries (each essentially having the form of the first example above) + the initial SELECT-query.

If every row requires a unique value there is no way to avoid one query per update. Perhaps look into other architectures like CQRS/Event sourcing if you need performance in this latter case.

Answer 6

To update with same value we can simply use this

ModelClass.objects.filter(name = 'bar').update(name='foo')

To update with different values

ob_list = ModelClass.objects.filter(name = 'bar')
obj_to_be_update = []
for obj in obj_list:
    obj.name = "Dear "+obj.name
    obj_to_be_update.append(obj)
ModelClass.objects.bulk_update(obj_to_be_update, ['name'], batch_size=1000)

It won't trigger save signal every time instead we keep all the objects to be updated on the list and trigger update signal at once.

Answer 7

IT returns number of objects are updated in table.

update_counts = ModelClass.objects.filter(name='bar').update(name="foo")

You can refer this link to get more information on bulk update and create. Bulk update and Create

Answer 8

In Django 4.1 QuerySet.bulk_create() to update fields can be used. The update is executed when a row insertion fails uniqueness constraints.

QuerySet.bulk_create() now supports updating fields when a row insertion fails uniqueness constraints. This is supported on MariaDB, MySQL, PostgreSQL, and SQLite 3.24+.