Add display delete Blob data from SQLite database using Tkinter GUI window

Tkitner to managing SQLite database Blob data using file browser to select upload photos and display

We will create the database and add sample data including one image.

Connection to SQLite Database or create database.

The connection object my_conn is created by using SQLite database, so in all script this line is to be changed.
For MySQL

my_conn = create_engine("mysql+mysqldb://userid:password@localhost/db_name")

For SQLite ( Change the path )

my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

Full code to create table in SQLite database is here. We used try except code block to handle error

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
try:
    #my_conn.execute('DROP TABLE student') 
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text,
                      photo blob 
                      );''')  
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("Student Table created successfully..")

Adding student data with Photo ( Blob column )

Note that in above code we created one photo column to store blob ( Binary Large Object ) data.

Using Tkinter filedialog() user can browse and select image to add to table along with other data.

file = filedialog.askopenfilename() # get file path
fob=open(file,'rb') # Open in binary mode 
blob_data=fob.read() # Binary data is ready

Here to keep the script simple, we are only asking user to select the image file and other input data we are directly using without any user inputs.

my_data=[(None, 'Tes Qry', 'Six', 78, 'male',blob_data)] # Data to store
q="INSERT INTO student values(?,?,?,?,?,?)" # query with place holders

We used try except code block to handle error while executing the query to insert data to table.

import tkinter as tk
from tkinter import filedialog
my_w = tk.Tk()
my_w.geometry("400x300")  # Size of the window 
my_w.title("www.plus2net.com")  #  title

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

l1 = tk.Label(my_w,text='Upload File & add Binary data ',width=30,font=20)  
l1.grid(row=0,column=1,padx=10,pady=10)
b1 = tk.Button(my_w, text='Upload File', 
   width=20,command = lambda:upload_file())
b1.grid(row=1,column=1) 

def upload_file():
    file = filedialog.askopenfilename()
    fob=open(file,'rb')
    blob_data=fob.read() # Binary data is ready 
    my_data=[(None, 'Tes Qry', 'Six', 78, 'male',blob_data)] # Data to store
    q="INSERT INTO student values(?,?,?,?,?,?)" # query with place holders 
    try:
        r_set=my_conn.execute(q,my_data)
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        print(error)
    else:
        l1.config(text="Data Added , ID: "+str(r_set.lastrowid))
my_w.mainloop()  # Keep the window open

In above code, once the data with the photo is added to the table, we will get the unique id of the row of data. This ID we will display using config() of the Label.
Before displaying the ID which is an integer, we will convert the same to string by using str().

l1.config(text="Data Added , ID: "+str(r_set.lastrowid))

This ID we will use to display the image ( photo or Blob data ) on the Tkinter window.

Displaying image ( Blob data ) from SQLite database table on Tkinter window

Using the ID ( taken from above code ) we will get the record details.

my_data=(54) # ID of the row to display 
q="SELECT * FROM  student WHERE id=?" # query with place holders

Inside the try catch code block we will execute the code.

try:
        my_cursor=my_conn.execute(q,my_data)
        r_set=my_cursor.fetchone()

After executing the query , we will collect the data of the student ( using ID ) and display the text data in one label ( l2 ) and image in another Label ( l3)

student=str(r_set[0])+','+r_set[1]+','+r_set[2]+','+str(r_set[3])
l2.config(text=student) # show student data other than image
img = ImageTk.PhotoImage(data=r_set[5]) # create image 
l3.config(image=img) # display image

Tkinter displaying Blob data from SQLite database table to show pictures with other text data

Full code is here .

import tkinter as tk
from tkinter import * 
from PIL import  ImageTk
my_w = tk.Tk()
my_w.geometry("700x550")  # Size of the window 
my_w.title("www.plus2net.com")  #  title
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
# connection to SQLite database
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

l1 = tk.Label(my_w,text='Display Image from SQLite Database',width=30,font=20)
l1.grid(row=0,column=1,padx=10,pady=10)
b1 = tk.Button(my_w, text='Show Data', 
   width=20,command = lambda:show_data())
b1.grid(row=1,column=1) 
img=[]

def show_data():
    global img # Image variable to display 
    my_data=(54) # ID of the row to display 
    q="SELECT * FROM  student WHERE id=?" # query with place holders
    try:
        my_cursor=my_conn.execute(q,my_data)
        r_set=my_cursor.fetchone()
    except SQLAlchemyError as e:
        error=str(e.__dict__['orig'])
        print(error)
    else:
        student=str(r_set[0])+','+r_set[1]+','+r_set[2]+','+str(r_set[3])
        l2.config(text=student) # show student data other than image
		
        img = ImageTk.PhotoImage(data=r_set[5]) # create image 
        l3.config(image=img) # display image 
l2 = tk.Label(my_w,text='Data here ',font=20 )
l2.grid(row=2,column=1)             
l3 = tk.Label(my_w,text='Image here ' )
l3.grid(row=3,column=1)     
my_w.mainloop()  # Keep the window open

Displaying Image from SQLite Blob column in Google Colab platform
Managing SQLite Blob data Displaying records from student table

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Managing Blob data of SQLite database table using Tkinter

Connection to SQLite Database or create database.

Adding student data with Photo ( Blob column )

Displaying image ( Blob data ) from SQLite database table on Tkinter window

Subscribe