multiple execute on a single connection.cursor in django. Is it safe?

I am opening a cursor with connection.cursor executing a bunch of deletes then closing the cursor. It works, but I am not sure if it has any side effect. Would appreciate any feedback.

from django.db import connection
c=connection.cursor()
try:
    c.execute('delete from table_a')
    c.execute('delete from table_b')
    ...
finally:
    c.close()

Answer

Since you are not executing these SQL statements in the transaction, you may encounter confusing states (for example, data was deleted from table_a, but not from table_b).

Django documentation says about this particular situation:

If you’re executing several custom SQL queries in a row, each one now runs in its own transaction, instead of sharing the same “automatic transaction”. If you need to enforce atomicity, you must wrap the sequence of queries in atomic().

So, results of each execute() call are committed right after it, but we want them either all to pass, or all to fail – as a single one set of changes.

Wrap the view with a transacton.atomic decorator:

from django.db import transaction

@transaction.atomic
def my_view(request):
    c = connection.cursor()
    try:
        c.execute('delete from table_a')
        c.execute('delete from table_b')
    finally:
        c.close()

Note that atomic() and the whole new transaction management system were introduced in Django 1.6.

If you are using Django < 1.6, apply transaction.commit_on_success decorator.