Python Forum

Full Version: SQL varbinary data type retrieval in python
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I'm working on python scripts to store and retrieve some videos and images from an SQL database, I stored a jpg file on the server as a varbinary type. However when I SELECT the record it returns an address b'\xff\xd8\xff\xe1o\x9eExif\x00\x00II*\x00\x08\x00\x00\x00\x0c\x00\x0f\x01\x02\x00\n\x00\x00\x00\x9.... (as expected) I'm wondering how exactly can I go about converting this back to an image. I would like to to test both streaming the image and writing it to disk and storing it locally.

here is my insertion code that successfully stores the image, I'm using pyodbc

#Insertion query 
insertion = "INSERT INTO " + '"Shubert Robots & Electrical Panels"' + " (Path, Filename, ID, Image)" + '\n'
insertion += "SELECT " + Path + ", " + Name + ", " + str(ID) + ", BulkColumn" +'\n'
insertion += "FROM Openrowset( Bulk " +  Image + ", " + "Single_Blob) as Image;" 
print(insertion + '\n')
cur.execute(insertion)


Here is my select code which returns the address
#select query
select = 'SELECT * FROM "Shubert Robots & Electrical Panels" WHERE ID = 0;'
print(select + '\n')
cur.execute(select)
values = cur.fetchall()
Any help is appreciated thank you.
Are you SURE your table name is "Shubert Robots & Electrical Panels"? If you're still early in development, you should fix that as soon as possible lol.

Anyway, can you just open a file in binary mode, and write the image out? Something like:
value = b'' # however you get it from the db
with open("the_img.jpg", "wb") as img:
  img.write(value)