如何使用Python和Django高效实现数据库批量更新操作？

Hey there! I see you're trying to populate the avg_deal_size field for 210k Account records with random values, and your current loop-based approach is taking around 20 minutes—totally understandable to want to speed this up. The main issue with your code right now is that every account.save() triggers a separate database query, adding up to 210k individual requests. Let's dive into two way more efficient solutions:

1. Use bulk_update to Cut Down Database Requests #

Django's bulk_update lets you update multiple objects in a single database query, which drastically reduces overhead. Here's how to adapt your code:

import random
from django.db import transaction
from django.http import HttpResponse

@transaction.atomic  # Ensures atomicity—rolls back if any batch fails (optional but recommended)
def populate(request):
    batch_size = 1000  # Adjust based on your database performance (500-2000 is a safe range)
    all_accounts = Account.objects.all()
    updated_accounts = []
    total_count = 0

    for account in all_accounts:
        account.avg_deal_size = round(random.randint(10, 200000), 2)
        updated_accounts.append(account)
        total_count += 1

        # Update in batches to avoid memory bloat and reduce DB calls
        if len(updated_accounts) >= batch_size:
            Account.objects.bulk_update(updated_accounts, ['avg_deal_size'])
            print(f"Updated {total_count} accounts so far")
            updated_accounts = []

    # Handle remaining records that don't fill a full batch
    if updated_accounts:
        Account.objects.bulk_update(updated_accounts, ['avg_deal_size'])
        print(f"Finished updating all {total_count} accounts")
    
    return HttpResponse("Bulk update completed successfully!")

Why this works: #

• Instead of 210k separate database requests, you'll only make ~210 requests (210000 / 1000), which cuts network and database overhead dramatically.
• The @transaction.atomic decorator ensures your update is all-or-nothing—no partial updates if something goes wrong.

2. Update Directly in the Database (The Fastest Option) #

If you don't need custom Python logic for each record (just generating a random number), let the database handle the entire operation. This avoids loading 210k objects into Python memory entirely, making it the quickest solution.

For PostgreSQL: #

from django.db.models.functions import Round
from django.db.models import Func, Value, FloatField
from django.http import HttpResponse

class RandomRange(Func):
    function = "RANDOM"
    template = "%(function)s() * %(range)s + %(min_val)s"

    def __init__(self, min_val, max_val, **extra):
        super().__init__(
            Value(max_val - min_val, output_field=FloatField()),
            Value(min_val, output_field=FloatField()),
            output_field=FloatField(),
            range=Value(max_val - min_val),
            min_val=Value(min_val),
            **extra
        )

def populate(request):
    # Let the database generate and apply random values directly
    Account.objects.update(
        avg_deal_size=Round(RandomRange(10, 200000), 2)
    )
    return HttpResponse("All accounts updated via database-level operation!")

For MySQL: #

Just tweak the RandomRange class to use MySQL's random function:

class RandomRange(Func):
    function = "RAND"
    template = "%(function)s() * %(range)s + %(min_val)s"
    # Rest of the code stays the same

Why this works: #

• All computation happens in the database, which is optimized for bulk operations. This can shrink your runtime from 20 minutes to just a few seconds or minutes.
• No memory overhead from loading 210k objects into Python.

Quick Tips #

• Batch Size Adjustment: For bulk_update, test different batch sizes (500, 1000, 2000) to find the sweet spot between memory usage and speed.
• Database Compatibility: If using SQLite, RANDOM() returns integers—adjust the template to scale it to your desired range (e.g., (RANDOM() % 199991) + 10 to get values between 10 and 200000).
• Atomicity: If data consistency is critical, stick with @transaction.atomic for bulk_update to prevent partial updates.

内容的提问来源于stack exchange，提问作者elece