Using an Output Converter function

Output Converter functions offer a flexible way to work with returned results that pyodbc does not natively support. For example, Microsoft SQL Server returns values from a DATETIMEOFFSET column as SQL type -155, which does not have native support in pyodbc (and many other ODBC libraries). Simply retrieving the value …

import pyodbc
cnxn = pyodbc.connect("DSN=mssqlLocal")

crsr = cnxn.cursor()
# create test data
crsr.execute("CREATE TABLE #dto_test (id INT PRIMARY KEY, dto_col DATETIMEOFFSET)")
crsr.execute("INSERT INTO #dto_test (id, dto_col) VALUES (1, '2017-03-16 10:35:18.5 -06:00')")

value = crsr.execute("SELECT dto_col FROM #dto_test WHERE id=1").fetchval()
print(repr(value))

crsr.close()
cnxn.close()

… results in the error

pyodbc.ProgrammingError: ('ODBC SQL type -155 is not yet supported. column-index=0 type=-155', 'HY106')

However, we can define an Output Converter function to decode the bytes returned, add that function to the Connection object, and then use the resulting value, e.g.,

from datetime import datetime, timedelta, timezone
import struct

import pyodbc

cnxn = pyodbc.connect("DSN=mssqlLocal")


def handle_datetimeoffset(dto_value):
    # ref: https://github.com/mkleehammer/pyodbc/issues/134#issuecomment-281739794
    tup = struct.unpack("<6hI2h", dto_value)  # e.g., (2017, 3, 16, 10, 35, 18, 500000000, -6, 0)
    return datetime(tup[0], tup[1], tup[2], tup[3], tup[4], tup[5], tup[6] // 1000,
                    timezone(timedelta(hours=tup[7], minutes=tup[8])))


crsr = cnxn.cursor()
# create test data
crsr.execute("CREATE TABLE #dto_test (id INT PRIMARY KEY, dto_col DATETIMEOFFSET)")
crsr.execute("INSERT INTO #dto_test (id, dto_col) VALUES (1, '2017-03-16 10:35:18.5 -06:00')")

cnxn.add_output_converter(-155, handle_datetimeoffset)
value = crsr.execute("SELECT dto_col FROM #dto_test WHERE id=1").fetchval()
print(repr(value))

crsr.close()
cnxn.close()

and value is returned as a timezone-aware datetime object

datetime.datetime(2017, 3, 16, 10, 35, 18, 500000, tzinfo=datetime.timezone(datetime.timedelta(days=-1, seconds=64800)))

Removing an Output Converter function

To remove all Output Converter functions, simply do:

cnxn.clear_output_converters()

To remove a single Output Converter function (new in version 4.0.25), use remove_output_converter like so:

cnxn.add_output_converter(pyodbc.SQL_WVARCHAR, decode_sketchy_utf16)
rows = crsr.columns("Clients").fetchall()
cnxn.remove_output_converter(pyodbc.SQL_WVARCHAR)  # restore default behaviour

Temporarily replacing an Output Converter function

Starting with version 4.0.26 we can also use get_output_converter to retrieve the currently active Output Converter function so we can temporarily replace it and then restore it afterwards.

prev_converter = cnxn.get_output_converter(pyodbc.SQL_WVARCHAR)
cnxn.add_output_converter(pyodbc.SQL_WVARCHAR, decode_sketchy_utf16)  # temporary replacement
rows = crsr.columns("Clients").fetchall()
cnxn.add_output_converter(pyodbc.SQL_WVARCHAR, prev_converter)  # restore previous behaviour