how to display blob as image in a table in tkinter python

Question

I am trying to insert and retrieve image from MySQL database.I have inserted the image as BLOB (longblob). When I am able to display it using image.show() but I want the image to be displayed in a table.

I used treeview for this purpose. But my table shows only BLOB objects. It doesn't show any image.

This is my code.

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

root = Tk()

id = Entry(root, width=10, font=("Helvetica", 20), bd=3)
id.pack()

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()

display_table_button = Button(root,text ='display Table',command =lambda:display_Table())
display_table_button.pack()

def display_Table():

    query = "SELECT * FROM image_db"
    person = mysql.connector.connect(host="localhost", user="root", password="", database="image")
    cursor_variable = person.cursor()
    cursor_variable.execute(query)

    vertical_scrollbar = ttk.Scrollbar(root)
    vertical_scrollbar.pack(side=RIGHT, fill=Y)

    my_tree = ttk.Treeview(root, yscrollcommand= vertical_scrollbar.set)
    my_tree.pack()

    vertical_scrollbar.config(command= my_tree.yview)

    style = ttk.Style(root)
    style.theme_use("winnative")
    style.configure(".", font=("Helvetica", 11))
    style.configure("Treeview.Heading", font=("Helvetica", 11, "bold"))

    my_tree['columns'] = ("id", "data")
    my_tree.column("#0", width=0, stretch='NO')
    my_tree.column("id", width=50, anchor='w')
    my_tree.column("data", width=130, anchor='w')


    my_tree.heading("#0", anchor='w', text='Label')
    my_tree.heading("id", anchor='w', text="Id")
    my_tree.heading("data", anchor='w', text="Image")

    count = 0

    for record in cursor_variable:
        # print(record)
        my_tree.insert(parent='', index='end', iid=count, text='Parent',
                            values=(record[0], record[1]))
        count += 1

    person.close()


def display_file():

    id2 = id.get()
    person = mysql.connector.connect(host="localhost", user="root", password="", database="image")
    cursor_variable = person.cursor()
    sql = "SELECT data FROM image_db WHERE id = '" + id2 + "'"
    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
    id1 = id.get()


    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 ('" + id1 + "',%s)"
    cursor_variable.execute(sql, (thedata,))
    person.commit()
    person.close()


root.mainloop()

Any help is appreciated.

Answer 1

I have modified your display_Table():

• set the rowheight of treeview to 50
• set width of column "#0" to 100
• resize the retrieved image to fit the treeview cell

def display_Table():
    ...
    style = ttk.Style(root)
    style.theme_use("winnative")
    style.configure(".", font=("Helvetica", 11))
    style.configure("Treeview.Heading", font=("Helvetica", 11, "bold"))
    style.configure("Treeview", rowheight=50) # set row height

    my_tree['columns'] = ("id",)
    my_tree.column("#0", width=100, stretch='NO') # set width
    my_tree.column("id", width=100, anchor='w')

    my_tree.heading("#0", anchor='w', text='Image')
    my_tree.heading("id", anchor='w', text="Id")

    count = 0

    my_tree.imglist = []
    for record in cursor_variable:
        img = Image.open(io.BytesIO(record[1]))
        img.thumbnail((50,50)) # resize the image to desired size
        img = ImageTk.PhotoImage(img)
        my_tree.insert(parent="", index="end", iid=count,
                       image=img, values=(record[0],)) # use "image" option for the image
        my_tree.imglist.append(img) # save the image reference
        count += 1

Note that I have used a list to hold the image references to avoid garbage collection.

The output: