Why is it that pyodbc converts a bit data type to boolean?

[km123456]

Why is it that pyodbc converts a bit data type to boolean?
As best I can tell this appears to be the standard behavior based on the documentation on this site.
Why not keep that value literally what it is - a 1 or 0 number data type?

Is there an easy way - setting, function, etc. to control this behavior with pyodbc?

[gordthompson]

From what I've seen, the most common use of a BIT column is for storing Boolean values, at least on SQL Server. pyodbc probably maps BIT to Boolean because that's what most people want.

If you want something else then you can use an output converter function like this

def handle_bit_type(bit_value):
    return bit_value

for which code like this

cnxn = pyodbc.connect(conn_str)
cnxn.add_output_converter(pyodbc.SQL_BIT, handle_bit_type)
crsr = cnxn.cursor()
print(crsr.execute("SELECT CAST(1 AS BIT)").fetchval())

will print b'\x01' instead of True. You can modify the function to return some other value if that's your preference.

[mkleehammer]

It might have been a good idea, but it has been this way so long I couldn't change it if we wanted to.

In the ODBC specification there are data types like SQL_BIT but there isn't one specifically for Booleans. Other interfaces like ADO.NET convert to Boolean so I assumed that's what SQL_BIT was really mean to be. (And it might be.). IIRC, some databases that have a bool type tend to map the to SQL_BIT, so there would be complaints the other way if a Boolean type was not True and False.

The best case would have been if ODBC had both SQL_BIT and SQL_BOOL types.