0

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.

4
  • You cannot show images using values option. But you can show an image using image option in treeview.insert(). Commented May 24, 2021 at 11:33
  • I tried to give the option image = record[1] , but it shows error "_tkinter.TclError: list element in quotes followed by "­N" instead of space " and I don't understand it. Commented May 24, 2021 at 11:42
  • print(record[1]) what does it show. Commented May 24, 2021 at 11:50
  • It shows b'\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x02\xed\x00\x00\x01\xfe\x08\x06\x00\x00\x00(\x140\xcf\x00\x00\x00\x01sRGB\x00\xae\xce\x1c\xe9\x00\x00\x00\x04gAMA\x00\x00\xb1\x8f\x0b\xfca\x05\x00\x00\x00\tpHYs\x00\x00\x0e\xc3\x00\x00\x0e\xc3\x01\xc7o\xa8d\x00\x00\xff Commented May 24, 2021 at 11:55

1 Answer 1

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:

enter image description here

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.