pyodbc Binary Column is returning odd characters python

Question

I have a code that prints the query from db, I have no problems displaying values for Varchar, int and other format, But the value of Byteary is different. The value on the DB is something like 0xA09080BD1160AB16 but the result in print of python is like

b'\x03\x80\x03\x8c\x00\x03\x00S^k\xdb' //not actual value

Based on what I have read on the net, there is a conn.add_output_converter(pyodbc.SQL_BINARY, hexToString) but its not working, the pyodbc version is at 4.0.18

import pyodbc

def hexToString(binaryString):
    try:
      hashString = ["{0:0>2}".format(hex(b)[2:],upper()) for b in binaryString]
      return '0x' + "".join(hashString)
    except:
      return binaryString

query = """ select * from myDb.TestDb """

conn_str = (
      r'Driver={ODBC Driver 13 for SQL Server};'
      r'Server=yourserver\test;'
      r'Database=test;'
      r'Trusted_Connection=yes;'
      r'CHARSET=UTF8;'
    )


cnxn = pyodbc.connect(conn_str)
cnxn.add_output_converter(pyodbc.SQL_BINARY, hexToString)
cursor = cnxn.cursor()

try:
    cursor.execute(query)
    row = cursor.fetchone()
except MySQLdb.error as err:
    print(err)
else:
    while row is not None:
        print(row)
        row = cursor.fetchone()

What is the T-SQL column type that holds your binary data? BINARY ? VARBINARY ? something else ? — Gord Thompson
– Gord Thompson, Commented Nov 29, 2017 at 14:03
What happens when you run this code with no output converter function? I get b'\xa0\x90\x80\xbd\x11`\xab\x16' — Gord Thompson
– Gord Thompson, Commented Nov 29, 2017 at 14:45
Okay, so your pyodbc connection is working properly. Are you confused by the fact that Python is showing you an actual binary value (b'\xa0\x90...') instead of a string representation of that value ('0xA090...')? — Gord Thompson
– Gord Thompson, Commented Nov 29, 2017 at 14:54

Gord Thompson · Accepted Answer · 2017-11-29 15:58:17Z

3

Your output converter function is failing because you are trying to call the upper string function with ,upper() instead of .upper(). However, since you are doing the conversion for display purposes you really shouldn't be converting the returned value immediately via an output converter function, you should be formatting it just before you print it or pass it to a routine that will display it.

crsr.execute("CREATE TABLE #tmp (id INT PRIMARY KEY, foo BINARY(8) NULL)")
crsr.execute("INSERT INTO #tmp (id, foo) VALUES (1, 0xA09080BD1160AB16)")
crsr.execute("SELECT foo FROM #tmp WHERE id=1")
binary_value = crsr.fetchval()  # b'\xa0\x90\x80\xbd\x11`\xab\x16'

# format for printing
print('0x' + binascii.hexlify(binary_value).decode().upper())
# prints:
# 0xA09080BD1160AB16

edited Nov 29, 2017 at 15:58

answered Nov 29, 2017 at 15:50

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Led Over a year ago

thank you very much, I actually copied this somewhere looks like he also missed this part

Collectives™ on Stack Overflow

pyodbc Binary Column is returning odd characters python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related