Inserting pyodbc.Binary data (BLOB) into SQL Serve

Question

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)

Answer 1

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:

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.