Lesson Learned #264: Python and PyODBC error messages.

by | Dec 30, 2022 | Technology | 0 comments

This article is contributed. See the original author and article here.

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.


 


Enjoy!


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment