1

I use django-pyodbc-azure 2.1.0.0 for the connection with an Azure SQL database which works fine.

When I understand the documentation of django-pyodbc-azure correctly, transactions should be supported.

However, this code immediately updates the row. I would expect, that the row is updated after 20 seconds.

from django.db import transaction
from myapp.models import MyModel
import time

with transaction.atomic():
    MyModel.objects.filter(id=1).update(my_field='Test')
    time.sleep(20)

Am I doing something wrong? Do I need to specifiy certain settings on the Azure SQL database?

When I set AUTOCOMMIT = False in my database settings, then the following code will not update the row at all.

MyModel.objects.filter(id=1).update(my_field='Test')
time.sleep(20)
transaction.commit()

My current settings.py

'azure_reporting': {
    'ENGINE': 'sql_server.pyodbc',
    'NAME': 'reporting_db',
    'HOST': 'xxxxxx.database.windows.net',
    'PORT': '',
    'USER': 'xxxx@xxxxxx.database.windows.net',
    'PASSWORD': 'xxxxxx',

    'OPTIONS': {
        'driver': 'ODBC Driver 17 for SQL Server'
    }
}
RaideR
  • 869
  • 1
  • 12
  • 33
  • no error message? – Leon Yue Jul 24 '19 at 08:40
  • Unfortunately not - no output at all in the Python shell – RaideR Jul 24 '19 at 08:44
  • did you set the AUTOCOMMIT true? – Leon Yue Jul 24 '19 at 09:07
  • @LeoYue - I really appreciate your help! In my settings I didn't set AUTOCOMMIT at all - the default should be `True` though. However, I just tried to set it to `False` in my settings. I edited the question so you can see the actions I further took. – RaideR Jul 24 '19 at 09:17
  • If you set `AUTOCOMMIT = False` and the code will not update the row, I think the transactions works well. – Leon Yue Jul 24 '19 at 09:35
  • No error happend, it's hard to judge what is going wrong. We just can recheck the [Configuration](https://github.com/michiya/django-pyodbc-azure#configuration) of `django-pyodbc-azure`. – Leon Yue Jul 24 '19 at 09:44

1 Answers1

0

Please make sure you have set the AUTOCOMMIT=true on database settings:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': 'yourserver.com',
        'PORT': '1433',
        'NAME': 'your_db',
        'USER': 'your_user',
        'PASSWORD': 'your_pw',
        'AUTOCOMMIT': True,

        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',

        },
    },
}

No error happened, the only things what we can do is recheck the configuration of django-pyodbc-azure.

As you said: When I set AUTOCOMMIT = False in my database settings, then the following code will not update the row at all.

I think the transactions should works well.

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • I have exactly the same settings except that I'm using `'driver': 'ODBC Driver 17 for SQL Server'`. In regards to `AUTOCOMMIT = False` - shouldn't the row be updated when I do a `transaction.commit()` at the end? – RaideR Jul 24 '19 at 10:03
  • @RaideR just try it. I think you need to test a lot, maybe you can find the solution. I'm sorry that that's all the help which I can give for you for now. – Leon Yue Jul 24 '19 at 10:20