CODEWITHSUNDEEP
pythonsql-serverpyodbc
Cristian Pardo
Cristian Pardo

Reputation: 21

Querying binary values in Python from SQL server

I'm executing this query

SELECT CMDB_ID FROM DB1.[dbo].[CDMID]

when I do this on SSMS 18 I get this:

enter image description here

I'm aware these are HEX values, although I'm not an expert on the topic.

I need to excute this exact query on python so I can process that information through a script, this script need as input the HEX values without any manipulation (as you see in the SSMS output).

So, through pyodbc library with a regular connection:

SQLserver_Connection("Driver={SQL Server Native Client 11.0};"
                     "Server=INSTANCE;"
                     "Database=DB1;"
                     "UID=USER;"
                     "PWD=PASS;")

I get this:

0   b'@\x12\x90\xb2\xbb\x92\xbbe\xa3\xf9:\xe2\x97@...
1   b'@"\xaf\x13\x18\xc9}\xc6\xb0\xd4\x87\xbf\x9e\...
2   b'@G\xc5rLh5\x1c\xb8h\xe0\xf0\xe4t\x08\xbb'
3   b'@\x9f\xe65\xf8tR\xda\x85S\xdcu\xd3\xf6*\xa2'
4   b'@\xa4\xcb^T\x06\xb2\xd0\x91S\x9e\xc0\xa7\xe543'
... ...
122 b'O\xa6\xe1\xd8\tA\xe9E\xa0\xf7\x96\x7f!"\xa3\...
123 b'O\xa9j,\x02\x89pF\xb9\xb4:G]y\xc4\xb6'
124 b'O\xab\xb6gy\xa2\x17\x1b\xadd\xc3\r\xa6\xee50'
125 b'O\xd7ogpWj\xee\xb0\xd8!y\xec\x08\xc7\xfa'
126 b"O\xf0u\x14\xcd\x8cT\x06\x9bm\xea\xddY\x08'\xef"

I have three questions:

  1. How can this data be intepreted, why am I getting this?
  2. Is there a way to manipulate this data back at the original HEX value? and if not...
  3. What can I do to receive the original HEX value?

I've looking for a solution but haven't found anything yet, as you can see I'm not an expert on this kind of topics so if you are not able to provide a solution I will, also, really appreciate documents with some background knowledge that I need to get so I can provide a solution by myself.

Upvotes: 0

Views: 2295

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

I think your issue is simply due to the fact that SSMS and Python produce different hexadecimal representations of binary data. Your column is apparently a binary or varbinary column, and when you query it in SSMS you see its fairly standard hex representation of the binary values, e.g., 0x01476F726400.

When you retrieve the value using pyodbc you get a <class 'bytes'> object which is represented as b'hex_representation' with one twist: Instead of simply displaying b'\x01\x47\x6F\x72\x64\x00', Python will render any byte that corresponds to a printable ASCII character as that character, so we get b'\x01Gord\x00' instead.

That minor annoyance (IMO) aside, the good news it that you already have the correct bytes in a <class 'bytes'> object, ready to pass along to any Python function that expects to receive binary data.

Upvotes: 1

Related Questions