1

-- I'm loading test data into my SQL Server Database using python and was able to successfully take images and break them down into bytes and store them in the database, but when trying to fetch back the bytes and decode it to save it as a new file type, all i get is a blank image file. Not sure what i am doing wrong here...

-- I've tried several iterations using base64 from other tutorials and similar questions, but cant seem to find one that will solve my problem.

SQLCommand = ("SELECT Photo FROM Validation")


cursor.execute(SQLCommand)
data = cursor.fetchone()[0]




image_64_decode = base64.decodebytes(data)
image_result = open('booking.png', 'wb')
image_result.write(image_64_decode)
image_result.close()


connection.close()

The expected result is that I should be able to fetch the bytes from the database which the database column is varbinary(max) the equivalent of bytes in python. once the bytes are fetched using the script in python it should save a file as booking.png which should replicate the image i stored in the database.

When i run the script i don't get an error, and in fact it saves a file, but the file is empty containing 1kb and does not reproduce the image. Not sure where i am going wrong, but it seems like it's not properly fetching the bytes.
benfsmith3
  • 21
  • 1
  • 4

2 Answers2

2

There really is no need for base64 encoding. If you use pyodbc instead of pypyodbc then it is as simple as

# test data
photo_path = r'C:\Users\Public\Pictures' + '\\'
email = 'bob@example.com'

# test environment
cursor.execute("""\
CREATE TABLE #validation (
    email nvarchar(255) PRIMARY KEY, 
    photo varbinary(max))
""")

# save binary file
with open(photo_path + 'generic_man.jpg', 'rb') as photo_file:
    photo_bytes = photo_file.read()
cursor.execute("INSERT INTO #validation (email, photo) VALUES (?, ?)", email, photo_bytes)
print(f'{len(photo_bytes)}-byte file written for {email}')
# 5632-byte file written for bob@example.com

# retrieve binary data and save as new file
retrieved_bytes = cursor.execute("SELECT photo FROM #validation WHERE email = ?", email).fetchval()
with open(photo_path + 'new.jpg', 'wb') as new_jpg:
    new_jpg.write(retrieved_bytes)
print(f'{len(retrieved_bytes)} bytes retrieved and written to new file')
# 5632 bytes retrieved and written to new file
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
1

I was able to get my code to work and can successfully convert an image to bytes, store it in the sql server datebase and retrieve it by fetching the bytes and reproducing the image.

There is only one problem- this only works if i use the nvarchar(max) data type for the column where I am storing the image bytes. I get errors when using varbinary(max) or when i solve the error, it just doesn't actually fetch the bites and properly convert it-- any guidance on what I might be doing wrong as I have a feeling it's something small. The updated code below is what I am doing using nvarchar(max) that is working.

import pypyodbc
import base64
from base64 import * 

connection = pypyodbc.connect('Driver=SQL Server;'
                            'Server=DESKTOP-MSSQLSERVER;'
                            'Database=Test;'
                            'Trusted_Connection=yes;'
                            )

cursor = connection.cursor()

a = 'bob@bob.com'
b = 'mack jones'
filename = 'bookingsuccessful.PNG'


image = open(filename, 'rb')
image_read = image.read()
image_64_encode = base64.encodebytes(image_read)


image.close()

SQLCommand = ("INSERT INTO Validation(email, myname, photo) VALUES(?,?,?)")
Values = [a,b,image_64_encode]
cursor.execute(SQLCommand, Values)
connection.commit()

SQLCommand = ("SELECT Photo FROM validation")
cursor.execute(SQLCommand)
data = cursor.fetchone()[0]
data = bytes(data.strip("\n"), 'utf-8')

image_64_decode = base64.decodebytes(data)
image_result = open('testfile.gif', 'wb')
image_result.write(image_64_decode)
image_result.close()

connection.close()
benfsmith3
  • 21
  • 1
  • 4
  • Base64 encoding converts a sequence of bytes into a sequence of ASCII characters. If you are storing the binary file in its base64-encoded form then you *should* be using a varchar(max) column, retrieving the encoded file as a string, and then decoding it. If you want to store the file data as raw bytes in a varbinary(max) column then don't base64-encode it. – Gord Thompson Jul 08 '19 at 13:04
  • Hi Gord- thanks for the comment. As a follow-up question, I can definitely try this and see if it fits my need as I had not thought about not decoding especially since this was my first time using base64, but from your experience is there a benefit to using base64 to encode vs storing raw bytes. Is there a storage or performance gain, or other? – benfsmith3 Jul 09 '19 at 02:29
  • "Is there a storage or performance gain [by using base64 encoding]" - Quite the opposite. [Base64](https://en.wikipedia.org/wiki/Base64) encoding converts 3 raw bytes to 4 ASCII characters (each of which will consume a byte itself) so encoding will increase the storage requirements by at least 33% (e.g., a 3MB file will result in a string of at least 4MB). There is also the effort required to encode the data before saving it and decode the data after retrieving it. – Gord Thompson Jul 09 '19 at 14:14