Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Updating selection of objects, each with a different value in bulk (Django)

Imagine I have a python dictionary where keys are existing user ids, and values are scores to be added to those users' existing scores.

For example: {1: 1580, 4: 540, 2: 678} (this could stretch to n k,v pairs)

I need to update the scores of all these user objects (updated_score = original_score + new_score). One way to do it is iteratively, like so:

from django.db.models import F
scores = {1: 1580, 4: 540, 2: 678}
for user_id,score_to_add in scores.iteritems():
    UserProfile.objects.filter(user_id=user_id).update(score=F('score')+score_to_add)

But that's multiple DB calls. Can I do it in a single call? An illustrative example would be great. As you would have guessed, this is for a Django project.

like image 749
Hassan Baig Avatar asked Mar 11 '17 08:03

Hassan Baig


1 Answers

Something like that:

from django.db.models import F
from django.db import transaction

with transaction.atomic():
    scores = {1: 1580, 4: 540, 2: 678}
    for user_id,score_to_add in scores:
        UserProfile.objects.filter(user_id=user_id).update(score=F('score')+score_to_add)

More on this here

You can take a look at this answer too.

[UPDATE]:

TL;DR: It'll not make one db query but it will be faster cause each query lacks the database overhead.

As the docs and @ahmed in his answer say:

Django’s default behavior is to run in autocommit mode. Each query is immediately committed to the database, unless a transaction is active.

By using with transaction.atomic() all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

like image 198
nik_m Avatar answered Nov 20 '22 12:11

nik_m