Lesson Learned #264: Python and PyODBC error messages.

[Guest Jose_Manuel_Jurado]

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.
  

 

 

 

Enjoy!

 

Continue reading...