Lesson Learned #264: Python and PyODBC error messages.
Published Dec 30 2022 12:20 PM 12K Views

Today, I worked on a service request that our customer got several issues that I would like to share with you my findings here.

 

1) pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command (0) (SQLExecDirectW)')

 

  • This error ocurrs when the Python code is trying to open a new cursor when we have a previous one with results. 

 

 

import os

import pymssql
import pyodbc

conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");  

cursor = conn.cursor()

cursor.execute('select * from sys.databases')
row = cursor.fetchone()
print(f"row={row}")

cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")

conn.close()

 

 

 

  • As we mentioned in our previous article enabling Mars we could fix this issue.

 

2) pyodbc.ProgrammingError: Attempt to use a closed cursor.

 

 

 

import os

import pymssql
import pyodbc

conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");  

cursor = conn.cursor()

cursor.execute('select * from sys.databases')
cursor.close()
row = cursor.fetchone()
print(f"row={row}")

cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")

conn.close()

 

 

 

  • In this situation, the issue is regarding in the line 11 that the cursor is closed before executing it.

3) pyodbc.ProgrammingError: The cursor's connection has been closed.

 

 

 

import os

import pymssql
import pyodbc

conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");  

cursor = conn.cursor()

cursor.execute('select * from sys.databases')
conn.close()
row = cursor.fetchone()
print(f"row={row}")

cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")

conn.close()

 

 

 

  • This situation is happening when the connection is closed before obtaining the data or run the cursor. 

4) ERROR:asyncio:Unclosed connection - connection: <aioodbc.connection.Connection object at 0xXXX

 

This is a not ODBC driver code, this error message is coming from an exception captured by aioodbc/connection.py at master · aio-libs/aioodbc · GitHub because our customer is using this library aioodbc/aioodbc at master · aio-libs/aioodbc · GitHub for async ODBC calls. 

 

 

    def __del__(self):
        if not self.closed:
            # This will block the loop, please use close
            # coroutine to close connection
            self._conn.close()
            self._conn = None

            warnings.warn("Unclosed connection {!r}".format(self),
                          ResourceWarning)

            context = {'connection': self,
                       'message': 'Unclosed connection'}
            if self._source_traceback is not None:
                context['source_traceback'] = self._source_traceback
            self._loop.call_exception_handler(context)

 

 

So, all these errors above points to either the connection was closed before executing next query or the connection is busy processing the previous query which could cause bottleneck.

 

5) [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

  • This error ocurred when ODBC driver was not able to prepare a statement, for example, syntax error in the parametrized query that the application is trying to run. 

 

Enjoy!

 

Version history
Last update:
‎Jan 02 2023 03:17 AM
Updated by: