4

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()
General Grievance
  • 4,555
  • 31
  • 31
  • 45
Daniele Bernardini
  • 1,516
  • 1
  • 12
  • 29

1 Answers1

5

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.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • 1
    As far as I understand this would happen also if I open and close a cursor for each delete. I was more worried that the cursor was really thought as a one use object, even if it worked multiple times and failed to clean up resources if used like the above. – Daniele Bernardini Aug 31 '14 at 14:39
  • @DanieleBernardini got it, the way you use the cursor is perfectly fine. – alecxe Aug 31 '14 at 14:40
  • After a lot of searching, breaking my head, I am glad I finally found the solution. This not only helped the proper synchronization, but also reduced my process execution time. +1. Thanks. – Reema Parakh Nov 15 '18 at 06:36