What's the best way to update multiple entries in a database based on key/value pairs

[Don Baldwin]

Hi,

I have a dictionary where the key is the primary key from one of my database tables, and the values are the values that I want to update with.

For example, I may have the following dictionary:

new_values = {1:'a', 2:'b', 3:'c'}

And the following model:

class TestThing(models.Model):

    new_value = models.IntegerField(default=0) 

And I want to update the entry with primary key 1 with value 'a', etc.

Note that there are other fields in the table that need to be preserved, so I can't just create a new table.

Obviously, I could loop through each entry and update each entry individually, but this seems really inefficient, so I think there must be a better way to do this.

Also, my table may have entries that aren't represented in my dictionary, so I don't think I can do the following:

TestThing.objects.all().update(new_value=new_values[F(id)])

Thanks,

Don

[Simon Charette]

Hello Don,

If you're on Django 2.2+ you should use bulk_update for this purpose[0]

TestThing.objects.bulk_update((

    TestThing(id=id, value=new_value)

    for id, value in new_values.items()

), ['value'])

Under the hood it performs a SQL query of the form

UPDATE ... SET value = (CASE WHEN id=1 THEN foo WHEN id=2 THEN ...)

That you can emulate yourself by using Case and When expressions if you're

using Django < 2.2.

Cheers,

Simon

[0] https://docs.djangoproject.com/en/2.2/ref/models/querysets/#bulk-update

[Don Baldwin]

Thanks for the quick response Simon.  Worked like a charm.

-Don

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/J79WXBWaNuA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8fe0d69c-0158-4eaa-be5d-435a09a29535%40googlegroups.com.