[SOLVED] Executing custom SQL directly does not work but shows no error

Executing custom SQL directly does not work but shows no error

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.

Solution

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!