Can't execute queries until end of atomic block in my data migration on django 1.7

Question

I have a pretty long data migration that I'm doing to correct an earlier bad migration where some rows were created incorrectly. I'm trying to assign values to a new column based upon old ones, however, sometimes this leads to integrity errors. When this happens, I want to throw away the one that's causing the integrity error

Here is a code snippet:

def load_data(apps, schema_editor):
    MyClass = apps.get_model('my_app', 'MyClass')

    new_col_mapping = {old_val1: new_val1, ....}

    for inst in MyClass.objects.filter(old_col=c):

        try:
            inst.new_col = new_col_mapping[c]
            inst.save()

        except IntegrityError:
            inst.delete()

Then in the operations of my Migration class I do

operations = [
    migrations.RunPython(load_data)
]

I get the following error when running the migration

django.db.transaction.TransactionManagementError: An error occurred in the current transaction. You can't execute queries until the end of the 'atomic' block

I get the feeling that doing

with transaction.atomic():

somewhere is my solution but I'm not exactly sure where the right place is. More importantly I'd like to understand WHY this is necessary

Answer 1

This is similar to the example in the docs.

First, add the required import if you don't have it already.

from django.db import transaction

Then wrap the code that might raise an integrity error in an atomic block.

try:
    with transaction.atomic():
        inst.new_col = new_col_mapping[c]
        inst.save()
except IntegrityError:
    inst.delete()

The reason for the error is explained in the warning block 'Avoid catching exceptions inside atomic!' in the docs. Once Django encounters a database error, it will roll back the atomic block. Attempting any more database queries will cause a TransactionManagementError, which you are seeing. By wrapping the code in an atomic block, only that code will be rolled back, and you can execute queries outside of the block.

Answer 2

Each migration is wrapped around one transaction, so when something fails during migration, all operations will be cancelled. Because of that, each transaction in which something failed, can't take new queries (they will be cancelled anyway).

Wrapping some operations with with transaction.atomic(): is not good solution, because you won't be able to cancel that operation when something will fail. Instead of that, avoid integrity errors by doing some more checks before saving data.

Answer 3

It seems that the same exception can have a variety of causes. In my case it was caused by an invalid model field name: I used a greek letter delta 𐤃 in my field name.

It seemed to work fine, all app worked well (perhaps I just didn't try any more complex use case). The tests, however, raised TransactionManagementError.

I solved the problem by removing 𐤃 from the field name and from all the migration files.