Bulk updating a table

Question

I want to update a customer table with a spreadsheet from our accounting system. Unfortunately I can't just clear out the data and reload all of it, because there are a few records in the table that are not in the imported data (don't ask).

For 2000 records this is taking about 5 minutes, and I wondered if there was a better way of doing it.

 for row in data:

        try:

            try:
                customer = models.Retailer.objects.get(shared_id=row['Customer'])
            except models.Retailer.DoesNotExist:
                customer = models.Retailer()

            customer.shared_id = row['Customer']
            customer.name = row['Name 1']
            customer.address01 = row['Street']
            customer.address02 = row['Street 2']
            customer.postcode = row['Postl Code']
            customer.city = row['City']

            customer.save()

        except:
            print formatExceptionInfo("Error with Customer ID: " + str(row['Customer']))

Answer 1

I've had some success using this bulk update snippet: http://djangosnippets.org/snippets/446/

It's a bit outdated, but it worked on django 1.1, so I suppose you can still make it work. If you are looking for a quick way to do a one time bulk insert, this is the quickest (I'm not sure I'd trust it for regular use without seriously testing performance).

Answer 2

Look at my answer here: http://stackoverflow.com/questions/3778585/django-form-that-updates-x-amount-of-models/3779224#3779224

The QuerySet has update() method - rest is explained in above link.

Answer 3

I've made a terribly crude attempt on a solution for this problem, but it's not finished yet and it doesn`t support working with django orm objects directly - yet.

http://pypi.python.org/pypi/dse/0.1.0

It`s not been properly testet and let me know if you have any suggestions on how to improve it. Using the django orm to do stuff like this is terrible.

Thomas