将 pyodbc.Binary 数据 (BLOB) 插入 SQL Server 图像列 [英] Inserting pyodbc.Binary data (BLOB) into SQL Server image column

问题描述

我正在尝试将二进制数据插入到 SQL Server 数据库中 image 数据类型的列中.我知道 varbinary(max) 是首选数据类型，但我无权更改架构.

I am trying to insert binary data into a column of image datatype in a SQL Server database. I know varbinary(max) is the preferred data type, but I don't have rights to alter the schema.

无论如何，我正在读取文件的内容并将其包装在 pyodbc.Binary() 中，如下所示:

Anyhow, I am reading the contents of a file and wrapping it in pyodbc.Binary() as below:

f = open('Test.ics', 'rb')
ablob = f.read().encode('hex')
ablob = pyodbc.Binary(ablob)

当我 print repr(ablob) 时，我看到了正确的值 bytearray(b'424547494e3a5 . . . . (省略号添加).

When I print repr(ablob) I see the correct value bytearray(b'424547494e3a5 . . . (ellipsis added).

但是插入后

insertSQL = """insert into documents(name, documentType, document, customerNumber) values(?,?,?,?)"""
cur.execute(insertSQL, 'test200.ics', 'text/calendar', pyodbc.Binary(ablob), 1717)

文档列的值为 0x343234353 ... 看起来好像将十六进制数据转换为 ASCII 字符代码.

The value of the document column is 0x343234353 . . . which appears as if the hexadecimal data was converted to ASCII character codes.

我认为将值包装在 pyodbc.Binary() 中会解决这个问题吗?任何帮助将不胜感激.

I thought wrapping the value in pyodbc.Binary() would take care of this? Any help would be greatly appreciated.

我使用的是 Python 2.7 和 SQL Server 2008 R2 (10.50).

I am using Python 2.7 and SQL Server 2008 R2 (10.50).

编辑:

beargle 好心地指出，我不必要地调用了 encode('hex')，这导致了我的问题.我相信这一定是将数据强制转换为字符串(尽管更全面的解释会有所帮助).

beargle kindly pointed out that I was needlessly calling encode('hex'), which was leading to my issue. I believe this must have been coercing the data into a string (although a fuller explanation would be helpful).

工作代码:

ablob = pyodbc.Binary(f.read())
cur.execute(insertSQL, 'test200.ics', 'text/calendar', ablob, 1717)

推荐答案

首先确保使用 with open(..) 读取文件 (另一个例子).这会在文件对象耗尽时自动关闭或引发异常.

First make sure you use with open(..) to read the file (another example). This automatically closes file objects when they are exhausted or an exception is raised.

# common vars
connection = pyodbc.connect(...)
filename = 'Test.ics'
insert = 'insert into documents (name, documentType, document, customerNumber)'

# without hex encode
with open(filename, 'rb'):
    bindata = f.read()

# with hex encode
with open(filename, 'rb'):
    hexdata = f.read().encode('hex')

# build parameters
binparams = ('test200.ics', 'text/calendar', pyodbc.Binary(bindata), 1717)
hexparams = ('test200.ics', 'text/calendar', pyodbc.Binary(hexdata), 1717)

# insert binary
connection.cursor().execute(insert, binparams)
connection.commit()

# insert hex
connection.cursor().execute(insert, hexparams)
connection.commit()

# print documents
rows = connection.cursor().execute('select * from documents').fetchall()
for row in rows:
    try:
        # this will decode hex data we inserted
        print str(row.document).decode('hex')
    # attempting to hex decode binary data throws TypeError
    except TypeError:
        print str(row.document)

我猜您是通过查看 Management Studio 中的结果获得 0x343234353... 数据:

I'm guessing you are getting the 0x343234353... data by looking at results in Management Studio:

这并不意味着数据以这种方式存储，这只是 Management Studio 表示image、text、ntext、varbinary 等.结果窗格中的数据类型.

This doesn't mean the data is stored this way, it's just the way Management Studio represents image, text, ntext, varbinary, etc. datatypes in the result pane.

这篇关于将 pyodbc.Binary 数据 (BLOB) 插入 SQL Server 图像列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！