How to distinctly bulk update all objects of a django model without iterating over them in python?

Question

Basically can we achieve the same result without doing this:

from my_app import models
for prd,count in x.iteritems():
    models.AggregatedResult.objects.filter(product=prd).update(linked_epp_count=count)

? As is evident, x is a dictionary containing keys same as AggregatedResult's product field and the 'value' is the count that I wish to update. It is taking more than 2 - 3 minutes to run on a test table having < 15k rows and the size of the table is ~ 200k currently and is expected to grow upto a million. So, I need help.

J0HN · Answer 1 · 2013-09-04T14:06:52.483

Easiest (but not the safest) way is to use raw sql query.

Something like:

for prd,count in x.iteritems():
    from django.db import connection, transaction
    cursor = connection.cursor()

    query = """
        UPDATE {table} 
        set {column} = {value} 
        where {condition} = {condition_value}""".format(
        table=AggregatedResult._meta.db_table,
        condition='product',
        condition_value=prd,
        column='linked_epp_count',
        value=count
    )
    cursor.execute(query)
    transaction.commit_unless_managed()

Warning: Not tested and extremely vulnerable to sql-injections. Use at your own risk

Alternative (much safer) approach would be to first load contents of x into temporary table, than issue just one raw query to update. Assuming temp table for x is temp_prod:

update aggregated_result ar
set linked_epp_count=tp.count
from temp_prod tp
where ar.product = tp.product

How do you upload data from x to temp table is something that I'm not very proficient with, so it's left for you. :)

How to distinctly bulk update all objects of a django model without iterating over them in python?

1 Answers1