Lesson Learned #264: Python and PyODBC error messages.

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...