Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.6k views
in Technique[技术] by (71.8m points)

python - Inserting pyodbc.Binary data (BLOB) into SQL Server image column

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.

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)

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

However, after inserting

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

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

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

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

Edit:

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

Working code:

ablob = pyodbc.Binary(f.read())
cur.execute(insertSQL, 'test200.ics', 'text/calendar', ablob, 1717)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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)

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

SQL Server Management Studio results

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...