插入带有executemany的二进制(16)

Question

我将md5散列作为二进制(16)存储在MSSQL数据库中。我需要用pymssql插入几千个值，如果用cursor.execute()而不是cursor.executemany()逐个插入值，这是非常慢的。但问题是，我不能插入二进制数据，因为executemany获取了我的md5散列列表，并将其视为字符串.

我的插入行如下所示：

# generated from hashlib.md5('somestring').hexdigest()
md5list = ['9e107d9d372bb6826bd81d3542a419d6', 'e4d909c290d0fb1ca068ffaddf22cbd0']
# query should look like this: insert into hashes (md5) values (0x9e107d9d372bb6826bd81d3542a419d6)
cursor.executemany("insert into hashes (md5) values (0x%s)", md5list) 
# now we have a query like: insert into hashes (md5) values (0x'9e107d9d372bb6826bd81d3542a419d6')

有没有一种方法可以插入散列和执行？

Answer 1

Cursor.executemany的查询参数应该是序列序列。

使用‘int (.，16)将十六进制字符串转换为int。

>>> int('9e107d9d372bb6826bd81d3542a419d6', 16)
210103647840849757586127012022035159510L
>>> hex(int('9e107d9d372bb6826bd81d3542a419d6', 16))
'0x9e107d9d372bb6826bd81d3542a419d6L'

如何使用列表理解？

md5list = ['9e107d9d372bb6826bd81d3542a419d6',
           'e4d909c290d0fb1ca068ffaddf22cbd0']
params = [[int(h, 16)] for h in md5list]
cursor.executemany("insert into hashes (md5) values (%s)", params)