Pyodbc Transactions in Django View are Committing Changes Despite Rollback Attempts

I'm working on a Django application where I'm using pyodbc to connect to an AWS RDS SQL Server database. I need to run a series of raw SQL queries (including INSERT, UPDATE, DELETE, DISABLE or ENABLE a trigger etc.) as a transaction, ensuring that no changes are committed if any error occurs during execution. However, I’m running into an issue where changes are getting committed to the database even when an error occurs, and I call rollback().

Here’s what I’ve tried so far:

Set autocommit = False on the pyodbc connection to prevent automatic commits. Used multiple cursors for different SQL statements, all under the same connection with autocommit = False. Wrapped the Django view with @transaction.non_atomic_requests to disable Django’s default transaction handling. Also tried wrapping the code with transaction.atomic() to see if Django’s transaction management could help. Despite all of these efforts, changes are still being committed to the database after the error occurs in the Django view, but when I run the exact same code in a standalone Python script (outside of Django), the rollback works perfectly, and no changes are committed.

Here’s a simplified version of the code I’m using:

import pyodbc
from django.conf import settings
from django.db import transaction

@transaction.non_atomic_requests  # Tried this to disable Django transaction wrapping
def my_view(request):
    connection = pyodbc.connect(settings.DATABASE_CONNECTION_STRING, autocommit=False)
    try:
        # Start transaction
        cursor1 = connection.cursor()
        cursor1.execute("UPDATE my_table SET column1 = 'value1' WHERE id = 1")

        cursor2 = connection.cursor()
        cursor2.execute("INSERT INTO my_table (column1) VALUES ('value2')")

        # This line will cause an error
        cursor3 = connection.cursor()
        cursor3.execute("INVALID SQL STATEMENT")  # This should trigger a rollback

        # Commit if all queries succeed
        connection.commit()

    except Exception as e:
        print("Error occurred, rolling back transaction:", e)
        connection.rollback()  # This should prevent any changes from being committed

    finally:
        cursor1.close()
        cursor2.close()
        cursor3.close()
        connection.close()

Problem Summary:

When I run this code as a standalone script outside Django, it works as expected, with no changes committed if an error occurs. When I run the same code within a Django view, changes are committed even if an error occurs.

Additional Notes:

I’m aware that Django wraps views in transactions by default, which is why I tried @transaction.non_atomic_requests to prevent Django’s transaction handling. I’ve confirmed that autocommit is set to False on the pyodbc connection.

Questions:

1)Is there something in Django’s request lifecycle or middleware that could be interfering with my manual transaction handling?

2)Is there a way to ensure that rollback() works correctly in this setup?

3)Are there any specific settings or approaches in Django that can prevent these changes from being committed?

I’d appreciate any guidance on this issue. Thank you!

Вернуться на верх