CODEWITHSUNDEEP
pythonsql-serverbinaryhexpyodbc
Led
Led

Reputation: 517

pyodbc Binary Column is returning odd characters python

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()

Upvotes: 2

Views: 1411

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

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

Upvotes: 3

Related Questions