Displaying all records of Blob images in Tkinter


displaying records with image
Tkinter GUI application to add, display update and delete Blob column data with image of MySQL table


We will divide the application in parts.
  1. Displaying all records of MySQL table with photo ( Blob data type )
  2. Uploading and inserting image data to Blob column of MySQL table
  3. Listing the record with all details based on user selection.
  4. Updating Blob data by uploading new image
  5. Deleting record with Blob data
Learn how to handle Blob data using Python here ( without GUI )
Learn how to upload and display image in Tkinter window

About MySQL table student_profile.

Our student_profile table has three columns,

id int(2)
student varchar(10)
profile_photo blob

SQL dump to create table in MySQL database.
CREATE TABLE `student_profile` (
  `id` int NOT NULL,
  `student` varchar(10) NOT NULL,
  `profile_photo` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
COMMIT;
Using SQLite database
CREATE TABLE IF NOT EXISTS student_profile(id integer,
	student text,profile_photo blob);
This can be linked with our student table and photos of the student can be displayed.

Displaying record details with Photo

We defined my_conn as connection object Collect the data from student_profile table by using SQLALchemy.
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
my_row=my_conn.execute("SELECT * FROM student_profile limit 0,4")
for student in my_row:
	pass
Here we used student to collect data.

student[0] -> id column ( int )
student[1] -> Name column ( varchar)
student[2] -> profile_photo ( Blob )

Here is the code to display student id and name.
    e = Label(my_w, text=student[0]) 
    e.grid(row=i,column=1,ipadx=20) 
    e = Label(my_w, text=student[1]) 
    e.grid(row=i,column=2,ipadx=60) 
In above code we will add one line images.append(img) to keep refrence to all images in a directory images[]. Without this line only the last image will be displayed. Here the image being used in the Button, or in a Label does not count as a reference for the garbage collector.

Managing image display.
import io
from PIL import Image, ImageTk
    stream = io.BytesIO(student[2])
    img=Image.open(stream)
    img = ImageTk.PhotoImage(img)    
    e = Label(my_w, image=img) 
    e.grid(row=i, column=3,ipady=7)
    images.append(img) # garbage collection 

Full code is here
import tkinter  as tk 
from tkinter import * 
from sqlalchemy import create_engine
import io
from PIL import Image, ImageTk
my_w = tk.Tk() # parent window 
my_w.geometry("400x500") # size as width height
my_w.title("www.plus2net.com")  # Adding a title
# database connection 
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
my_row=my_conn.execute("SELECT * FROM student_profile limit 0,4")

# Column headers  row 0
l1=Label(my_w, text='ID') 
l1.grid(row=0,column=1) 

l2=Label(my_w, text='Name') 
l2.grid(row=0,column=2) 

l3=Label(my_w, text='Photo') 
l3.grid(row=0,column=3) 

i=1 # data starts from row 1 
images = [] # to manage garbage collection. 

for student in my_row: 
    stream = io.BytesIO(student[2])
    img=Image.open(stream)
    img = ImageTk.PhotoImage(img)    
    e = Label(my_w, text=student[0]) 
    e.grid(row=i,column=1,ipadx=20) 
    e = Label(my_w, text=student[1]) 
    e.grid(row=i,column=2,ipadx=60) 
    e = Label(my_w, image=img) 
    e.grid(row=i, column=3,ipady=7)  
    images.append(img) # garbage collection 
    i=i+1    
my_w.mainloop()

Deleting record

We will add one button to each row or record to trigger the delete operation.
The button once clicked will pass the record id to a function which will delete the particular record.
Show one confirmation message to the user asking to confirm the operation.
Based on confirmation or rejection the delete operation should be executed.
After the delete operation we will refresh the view so a fresh data is taken from backend MySQL database reflecting the change by displaying the balance records.

Tkinter window to display MySQL Blob Data type image( Single record)
Tkinter window to upload and insert image to MySQL Blob Data column
Changes required for using SQLite database in place of MySQL

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    08-08-2023

    id=mycur.execute("INSERT INTO pic(CARNO,NAME,IMG)"/ "VALUES ( %d,%s, %s)",data)
    TypeError: unsupported operand type(s) for /: 'str' and 'str'




    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer