Truncated image displayed when tried to retrieve from mysql database using python

Question

I am trying to insert and retrieve image as BLOB data from my sql database.

I am able to insert it as BLOB. However, when I try to retrieve it,the retrieved image is truncated.

First it showed OSError: image file is truncated. By referring some posts I included the code ImageFile.LOAD_TRUNCATED_IMAGES = True. Now there is no error.

But the image is still truncated.

This is my code.

from tkinter import *
from PIL import ImageTk,Image
from tkinter import filedialog
import mysql.connector
import io
from PIL import ImageFile

root = Tk()

browse_button = Button(root,text ='Browse',command = lambda:open_file())
browse_button.pack()

display_button = Button(root,text ='display',command =lambda:display_file())
display_button.pack()

def display_file():
    ImageFile.LOAD_TRUNCATED_IMAGES = True
    person = mysql.connector.connect(host="localhost", user="root", password="", database="image")
    cursor_variable = person.cursor()
    sql = "SELECT data FROM image_db WHERE id = 5"
    cursor_variable.execute(sql)
    all_data = cursor_variable.fetchall()
    image = all_data[0][0]
    image = Image.open(io.BytesIO(image))
    image.show()
    person.commit()
    person.close()


def open_file():
    root.filename = filedialog.askopenfilename(initialdir="/Users/write/PycharmProjects/slider/img", title='Select a File',
                                               filetypes=(('png files', '*.png'), ('jpeg files', '*.jpeg'),
                                                          ('jpg files', '*.jpg')))
    my_label = Label(root, text=root.filename).pack()
    my_image = ImageTk.PhotoImage(Image.open(root.filename))
    path = root.filename
    id = '5'


    person = mysql.connector.connect(host="localhost", user="root", password="", database="image")
    cursor_variable = person.cursor()
    thedata = open(root.filename, 'rb').read()
    sql = "INSERT INTO image_db (id,data) VALUES ('" + id + "',%s)"
    cursor_variable.execute(sql, (thedata,))
    person.commit()
    person.close()


root.mainloop()

Any help is appreciated.

score 1 · Accepted Answer · answered May 23 '21 at 19:09

I am going to make a guess at your problem, based on a few assumptions which aren't specified (or at least aren't specified clearly) in your question:

the data column of your image_db table has type BLOB, and not MEDIUMBLOB or LONGBLOB.
The images you are attempting to upload are larger than 64KB.
You are not running your database in strict SQL mode.

The MySQL docs on the BLOB and TEXT types state that:

If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated.

According to this answer, the maximum length of a BLOB column is 64KB. Combining all the assumptions together therefore gives us one possible explanation for the behaviour you are seeing.

I ran your code with some smallish images and it worked fine: the images displayed without truncation.

I would suggest changing the data column of your image_db table to MEDIUMBLOB or LONGBLOB, depending on what size of images you need to store in your database.

Truncated image displayed when tried to retrieve from mysql database using python

1 Answers1