如何从SQL Server数据库获取字节并在Python中转换为图像 [英] How to fetch bytes from SQL Server Database and Convert to Image in Python

问题描述

-我正在使用python将测试数据加载到SQL Server数据库中，并且能够成功拍摄图像并将其分解为字节，然后将其存储在数据库中，但是当尝试取回字节并将其解码为将其保存为新文件类型，我得到的只是一个空白图像文件.不知道我在这里做错了什么...

-- 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...

-我已经尝试使用其他教程中的base64进行了多次迭代以及类似的问题，但是似乎找不到能够解决我的问题的迭代.

-- 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.

推荐答案

我能够使我的代码正常工作，并且可以成功地将图像转换为字节，将其存储在sql server日期库中，并通过提取字节和再现图像.

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.

只有一个问题-仅当我将nvarchar(max)数据类型用于存储图像字节的列时，这才起作用.当我使用varbinary(max)或解决错误时遇到错误，它实际上并没有实际获取并正确转换-关于我可能做错了什么的任何指导，因为我感觉到它有点小.下面的更新代码是我使用正在运行的nvarchar(max)所做的事情.

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 = '[e-mail]'
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()

这篇关于如何从SQL Server数据库获取字节并在Python中转换为图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！