Is this a bug in mssql-python

[sravikrishna]

This is easily reproducible in mssql-python and pyodbc. However freetds based pymssql executs it with no issues.
Both pyodbc and mssql-python throw this error

cs = [column[0] for column in sql_cursor.description]
^^^^^^^^^^^^^^^^^^^^^^
TypeError: 'NoneType' object is not iterable

[sravikrishna]

bug.py

[sravikrishna]

Pls set the server name, database name and the login id / password and run this script with all the 3 drivers.
python bug.py pyodbc or mssql or pymssql

[sravikrishna]

I am using version 1.9.0 of mssql-python

[sravikrishna]

I am using cs to print the header of the result with column names.

[github-actions]

Hi @sravikrishna, thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[bewithgaurav]

Thanks for reporting, @sravikrishna

This is expected behavior per the DB-API 2.0 spec. Your batch has three statements - the first two (SELECT ... INTO #tmp1, SELECT ... INTO #tmp2) produce row counts, not result sets. After execute(), the cursor is positioned on that first row count, so cursor.description is None.

You need cursor.nextset() to advance past each row-count result before accessing the final SELECT:

sql_cursor.execute(sql_stmt)
 
# Skip past the two SELECT INTO row counts
sql_cursor.nextset()
sql_cursor.nextset()

# Now cursor.description has the column metadata
cs = [column[0] for column in sql_cursor.description]
while row := sql_cursor.fetchone():
    print(row)

This is the same reason pyodbc behaves identically, both follow the ODBC/DB-API spec.
pymssql (FreeTDS) auto-skips past statements that don't return rows (like SELECT INTO), which is convenient but non-standard.

This behaviour is by-design. Hope that helps!

[sravikrishna]

Well it explains, but I am not sure I am convinced. Same sql runs fine on JDBC, SSMS, sqlcmd.

Eventually it will become a tribal knowledge I need to keep in mind while working with pyodbc or mssql.

Thanks for the explanation,