pandas + pyodbc ODBC SQL type -150 is not yet supported

I know there is many topics on this but i think this is much specific. I get the current code for audit purpose:

import pandas as pd
import pyodbc

query = """
--Top 50 high total CPU Queries
SELECT TOP 50
'High CPU Queries' as Type,
serverproperty('machinename') as 'Server Name',
isnull(serverproperty('instancename'),serverproperty('machinename')) as 'Instance Name',
        COALESCE(DB_NAME(qt.dbid),
        DB_NAME(CAST(pa.value as int)), 
        'Resource') AS DBNAME,
    qs.execution_count as [Execution Count],
    qs.total_worker_time/1000 as [Total CPU Time],
    (qs.total_worker_time/1000)/qs.execution_count as [Avg CPU Time],
    qs.total_elapsed_time/1000 as [Total Duration],
    (qs.total_elapsed_time/1000)/qs.execution_count as [Avg Duration],
    qs.total_physical_reads as [Total Physical Reads],
    qs.total_physical_reads/qs.execution_count as [Avg Physical Reads],
    qs.total_logical_reads as [Total Logical Reads],
    qs.total_logical_reads/qs.execution_count as [Avg Logical Reads],
SUBSTRING(qt.text,qs.statement_start_offset/2, 
        (case when qs.statement_end_offset = -1 
        then len(convert(nvarchar(max), qt.text)) * 2 
        else qs.statement_end_offset end -qs.statement_start_offset)/2)
        as query_text     
    FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
    outer apply sys.dm_exec_query_plan (qs.plan_handle) qp
    outer APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
    where attribute = 'dbid'   
    ORDER BY 
        [Total CPU Time] DESC
"""
cnxn = pyodbc.connect('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
cnxn.execute(query).fetchall()
cnxn.close()

I get the following error:

cnxn.execute(sql_status_20).fetchall() Traceback (most recent call last): File "", line 1, in pyodbc.ProgrammingError: ('ODBC SQL type -150 is not yet supported. column-index=1 type=-150', 'HY106')

Anyone can help me to handle this ? I have the same problem with many SQL Server audit scripts using dates and i can't change the driver as i have all kind of SQL versions in my production env.

Solution

If it would be difficult to change the existing queries to explicitly CAST or CONVERT the troublesome values then you might consider trying to use a pyodbc Output Converter Function. It enables you to define a Python function that will be applied to the raw bytes returned for a given ODBC SQL type.

For example, this test code fails with the error you describe:

import pyodbc

cnxn = pyodbc.connect('DSN=SQLmyDb', autocommit=True)
crsr = cnxn.cursor()

server_name = crsr.execute("SELECT SERVERPROPERTY('machinename')").fetchval()
print(server_name)

crsr.close()
cnxn.close()

but this works correctly for me under Python3

import pyodbc


def handle_sql_variant_as_string(value):
    return value.decode('utf-16le')


cnxn = pyodbc.connect('DSN=SQLmyDb', autocommit=True)
crsr = cnxn.cursor()

cnxn.add_output_converter(-150, handle_sql_variant_as_string)
server_name = crsr.execute("SELECT SERVERPROPERTY('machinename')").fetchval()
print(server_name)

crsr.close()
cnxn.close()