pyodbc claims UPDATE statement "is not a query"

[lbilodeau]

Environment

HOST: Ubuntu 20.04.4 LTS (GNU/Linux 5.13.0-1031-azure x86_64)

• Python: 3.8.2
• pyodbc: 4.0.32
• DB: Azure MS SQL (managed service)
• driver: Microsoft ODBC Driver 18 for SQL Server

Issue

When attempting to execute a simple update command no changes are made to my data in the database and it seems that the pyodbc is ignoring the query.

Here's what I have set up:

class sqlDal:
    _connection = ""
    _cursor = ""
    _currentBVMeta = {}

    def __init__(self, server, database, user, password):
        try:
            self._connection = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+user+';PWD='+ password)
            self._cursor = self._connection.cursor()
        except Exception as e:
            logMessageObj = {"status": "CRITICAL", "message": str(e)}
            send_log_message(self._currentBVMeta, logMessageObj, "ERROR")

    def ExecteQuery(self, query):
        eprint ("\n======= query:", query)
        self._cursor.execute(query)
        results = []
        for row in self._cursor.fetchall():
          results.append(row)
        eprint ("\n======= results:",str(results))
        return

and this is the query and the exception I get:

======= query: UPDATE dbo.CourseInstance SET CourseBlueBucks = 5.5 WHERE CourseInstanceId = 592;

+++Logging message: {'EXCEPTION': 'No results.  Previous SQL was not a query.', 'TRACEBACK': 'Traceback (most recent call last):\n  File "/home/incentives/incentives_backsync/incentiveupdateProcessor.py", line 60, in ProcessUpdateMessage\n    dal.ExecteQuery(sqlQuery)\n  File "/home/incentives/incentives_backsync/bvdb_dal.py", line 22, in ExecteQuery\n    for row in self._cursor.fetchall():\npyodbc.ProgrammingError: No results.  Previous SQL was not a query.\n'}
+++logstash socketed
log message sent: {'service': 'University_Incentives_Back_sync', 'level': 'ERROR', 'data': {'EXCEPTION': 'No results.  Previous SQL was not a query.', 'TRACEBACK': 'Traceback (most recent call last):\n  File "/home/incentives/incentives_backsync/incentiveupdateProcessor.py", line 60, in ProcessUpdateMessage\n    dal.ExecteQuery(sqlQuery)\n  File "/home/incentives/incentives_backsync/bvdb_dal.py", line 22, in ExecteQuery\n    for row in self._cursor.fetchall():\npyodbc.ProgrammingError: No results.  Previous SQL was not a query.\n'}, 'BVMeta': {'transactionId': '', 'universityId': 0, 'userId': 0, 'serviceName': 'University_Incentives_Back_sync'}, 'serviceName': 'University_Incentives_Back_sync'}

the query works fine from SSMS.

I don't understand why pyodbc is not processing my UPDATE query. Does anyone else have an idea why this query isn't processed properly.

[lbilodeau]

OK, I figured out why the update wasn't working. Now I understand that one MUST commit the query either specifically or by setting the autocommit property on the connection.

[gordthompson]

one MUST commit the query either specifically or by setting the autocommit property on the connection.

True, but that's not why you were getting the error. You were getting the error because you were calling .fetchall() when the cursor had no result set. You can check the cursor's .description to determine that:

def show_cursor_status(crs):
    descr = crs.description
    if descr:
        print("Previous SQL was a query.")
        print(f"Results: {crs.fetchall()}")
    else:
        print("Previous SQL was not a query.")
        print(f"Row count: {crs.rowcount}")

crsr.execute("create table #tmp (id int primary key)")
show_cursor_status(crsr)
"""
Previous SQL was not a query.
Row count: -1
"""

crsr.execute("insert into #tmp (id) values (1), (2)")
show_cursor_status(crsr)
"""
Previous SQL was not a query.
Row count: 2
"""

crsr.execute("select * from #tmp")
show_cursor_status(crsr)
"""
Previous SQL was a query.
Results: [(1, ), (2, )]
"""

crsr.execute("delete from #tmp where id = 2")
show_cursor_status(crsr)
"""
Previous SQL was not a query.
Row count: 1
"""