Inserting pyodbc.Binary data (BLOB) into SQL Server image column
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.
slachterman
Updated on June 17, 2022Comments
-
slachterman about 2 years
I am trying to insert binary data into a column of
image
datatype in a SQL Server database. I knowvarbinary(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 valuebytearray(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)