CODEWITHSUNDEEP
pythonsql-server-2008pyodbc
slachterman
slachterman

Reputation: 1575

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)

Upvotes: 7

Views: 16642

Answers (1)

Bryan
Bryan

Reputation: 17703

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.

Upvotes: 6

Related Questions