从 SQL 服务器查询 Python 中的二进制值 [英] Querying binary values in Python from SQL server
问题描述
我正在执行这个查询
SELECT CMDB_ID FROM DB1.[dbo].[CDMID]
当我在 SSMS 18 上这样做时,我得到了这个:
when I do this on SSMS 18 I get this:
我知道这些是十六进制值,尽管我不是该主题的专家.
I'm aware these are HEX values, although I'm not an expert on the topic.
我需要在 python 上执行这个精确的查询,以便我可以通过脚本处理该信息,这个脚本需要输入 HEX 值而不进行任何操作(如您在 SSMS 输出中看到的).
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).
因此,通过具有常规连接的 pyodbc
库:
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;")
我明白了:
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"
我有三个问题:
- 如何解释这些数据,为什么我会得到这个?
- 有没有办法以原始 HEX 值操作这些数据?如果没有...
- 如何接收原始 HEX 值?
我一直在寻找解决方案,但还没有找到任何东西,如您所见,我不是此类主题的专家,因此如果您无法提供解决方案,我也会非常感谢我需要获取具有一些背景知识的文档,以便我可以自己提供解决方案.
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.
推荐答案
我认为您的问题仅仅是由于 SSMS 和 Python 产生不同的二进制的十六进制表示> 数据.您的列显然是 binary
或 varbinary
列,当您在 SSMS 中查询它时,您会看到二进制值的相当标准的十六进制表示,例如 0x01476F726400代码>.
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
.
当您使用 pyodbc 检索值时,您会得到一个 <class 'bytes'>
对象,该对象表示为 b'hex_representation'
并稍加改动:而不是只需显示 b'\x01\x47\x6F\x72\x64\x00'
,Python 会将与可打印 ASCII 字符对应的任何字节渲染为该字符,因此我们得到 b'\x01Gord\x00'
代替.
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.
撇开那个小烦恼 (IMO),好消息是您已经在 <class 'bytes'>
对象中拥有正确的字节,准备传递给任何 Python 函数期望接收二进制数据.
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.
这篇关于从 SQL 服务器查询 Python 中的二进制值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!