1

I have a funtion in my views.py that execute a sql query directly in the database, like this

cursor.execute("INSERT INTO table1(field1,field2,field3) VALUES(%s,%s,%s)",[value1,value2,value3])
cursor.execute("UPDATE table1 SET field1=%s,field2=%s,field3=%s WHERE field4=%s",[value1,value2,value3,value4])

The problem is when the function run not show me errors and in the debug mode returns "POST /something/20/ HTTP/1.1" 200 but in the database the data it is not saved. I use django 1.8.19, django-pyodbc-azure 1.8.17 and the database is on sql server 2016. Any help is really appreciated.

M. Gar
  • 889
  • 4
  • 16
  • 33
  • Can I ask why didn't you use Django ORM after all this is simple create and update operation..? – JPG Jul 31 '20 at 19:16
  • Are you connecting with `pyodbc` directly, or through Django to get your cursor? – FlipperPA Aug 01 '20 at 14:29
  • through Django I get the cursor – M. Gar Aug 01 '20 at 16:49
  • 1
    If you're doing `from django.db import connection`, `cursor = connection.cursor()`, auto-commit should be on. Let's test it, however. After your two `cursor.execute()` statements, can you put a `cursor.close()` and `print("CURSOR CLOSED!")`? Then see if the data has been inserted, and if you get a `print()` statement in your `runserver`. – FlipperPA Aug 01 '20 at 21:07
  • 1
    Also: you're using a very old, very insecure version of Django. I would highly recommend upgrading to version 2.2 for LTS, or 3.x. Just so you know, when you do this, you'll need to drop `django-pyodbc-azure`, which is no longer maintained. The new maintained SQL Server backend is here, which includes support for current Django versions: https://pypi.org/project/django-mssql-backend/ – FlipperPA Aug 01 '20 at 21:08
  • 1
    @FlipperPA it was a problem with the auto-commit I do not know why ignoring the auto-commit so I have to specify in the function, thank you. You can post it as an answer. – M. Gar Aug 06 '20 at 15:44

1 Answers1

1

If you're using Django for you connection like this:

from django.db import connection
cursor = connection.cursor()

Then normally, auto-commit should be on. But it is worth testing: after your two cursor.execute() statements, can you put a cursor.close() and print("CURSOR CLOSED!")?

Then see if the data has been inserted, and if you get a print() statement in your runserver.

If autocommit isn't turned on, you can activate it in your DATABASES options. It is documented in django-pyodbc-azure as an option here:

https://pypi.org/project/django-pyodbc-azure/

Good luck!

FlipperPA
  • 13,607
  • 4
  • 39
  • 71