CODEWITHSUNDEEP
pythonsqldjango
Shane
Shane

Reputation: 4983

Proper way to bulk update with different IDs in Django

For example, a table with columns of acct, ip, status, (acct is unique), there's about 1000 rows need to be updated to a specific ip and status (e.g. 11.11.11.11 and great), I noticed update seems to be a good choice: Entry.objects.filter(acct=xxx).update(comments_on=False), but these 1000 rows all come with different acct, and iterating would cost too much overhead(actually there're at least 100k rows need to be updated at a time), what's a proper way to do this?

Upvotes: 0

Views: 1059

Answers (2)

catavaran
catavaran

Reputation: 45595

I am afraid that using filter().update() is the only way to do it.

To increase the speed of mass operation I suggest you to wrap this bulk update into transaction:

from django.db import transaction

with transaction.atomic():
    for acct, ip, status in accounts_to_update:
        Entry.objects.filter(acct=acct).update(ip=ip, status=status)

UPDATE: @jessamyn-smith's idea is brilliant! acct is unique but how about ip/status pair? If you regroup source list by this pair will it reduce number of queries?

accounts_to_update = [
    ('xxx', '1.2.3.4', 'great'),
    ('xxy', '1.2.3.4', 'fail'),
    ('xxz', '1.2.3.4', 'great'),
    ('xx0', '1.2.3.0', 'great'),
]

ip_status_dict = {}
for acct, ip, status in accounts_to_update:
    accounts = ip_status_dict.setdefault((ip, status), [])
    accounts.append(acct)

with transaction.atomic():
    for (ip, status), accounts in ip_status_dict.iteritems():
        Entry.objects.filter(acct__in=accounts).update(ip=ip, status=status)

Upvotes: 0

Jessamyn Smith
Jessamyn Smith

Reputation: 1649

You can filter on any attributes, so if it's possible to write a query that describes the set of objects to update, you can use update.

Entry.objects.filter(attr1=yyy, attr2=zzz).update('11.11.11.11', 'great')

If what you have is a list of ids, you could do the following, though I don't guarantee it'll give you the performance you want:

Entry.objects.filter(acct__in=<list_of_accounts>).update('11.11.11.11', 'great')

Upvotes: 3

Related Questions