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..")
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. 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.
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
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
AUTHOR
🎥 Join me live on YouTubePassionate about coding and teaching, I love sharing practical programming tutorials on PHP, Python, JavaScript, SQL, and web development. With years of experience, my goal is to make learning simple, engaging, and project-oriented. Whether you're a beginner or an experienced developer, I believe learning by doing is the best way to master coding. Let's explore the world of programming together!