Integrating an SQLite database into a standalone Tkinter application using PyInstaller

1. Install PyInstaller:

pip install pyinstaller

2. Organize all Project Files:

3. Modify the Application to Locate the Database:

from sqlalchemy import create_engine,text
base_path = os.path.dirname(__file__) # path to current directory 
db_path=os.path.join(base_path, 'my_db.db') # path to SQLite database
my_conn = create_engine("sqlite:///"+db_path).connect() # connection object

4. Bundle the Application with PyInstaller:

PyInstaller --onefile --windowed --add-data "D:\\testing\\sqlite\\my_db.db;." --distpath D:\\my_app D:\\testing\\sqlite\\my_sqlite_app.py

from tkinter import ttk
import tkinter as tk
import os

# Creating tkinter window
my_w = tk.Tk()
my_w.geometry("560x280") # width and height of the window.
my_w.title("www.plus2net.com")

# Using Treeview widget
trv = ttk.Treeview(my_w, selectmode='browse')
trv.grid(row=1, column=1, padx=30, pady=20)

style = ttk.Style(my_w)
style.theme_use("clam") # set theme to clam
style.configure("Treeview", background="black",
                fieldbackground="black", foreground="white")
style.configure('Treeview.Heading', background="PowderBlue")

from sqlalchemy import create_engine, text
base_path = os.path.dirname(__file__) # path to current directory
db_path = os.path.join(base_path, 'my_db.db') # path to SQLite database
my_conn = create_engine("sqlite:///" + db_path).connect() # connection object

r_set = my_conn.execute(text('SELECT * FROM student'))
l1 = [r for r in r_set.keys()] # List of column headers
r_set = [r for r in r_set]

trv['height'] = 5 # Number of rows to display, default is 10
trv['show'] = 'headings'
# column identifiers
trv["columns"] = l1
# Defining headings, other options in tree
# width of columns and alignment
for i in l1:
    trv.column(i, width=100, anchor='c')
    # Headings of respective columns
    trv.heading(i, text=i)

## Adding data to Treeview
for dt in r_set:
    v = [r for r in dt] # creating a list from each row
    trv.insert("", 'end', iid=v[0], values=v) # adding row

my_w.mainloop()

PyInstaller --onefile --windowed --add-data "D:\\testing\\sqlite\\my_db.db;." --distpath D:\\my_app D:\\testing\\sqlite\\my_sqlite_app2.py

import tkinter as tk
from tkinter import ttk, messagebox
from sqlalchemy import create_engine, text, exc
import os

# Function to add all records to the student table
def add_records():
    try:
        # SQL statement to insert multiple records
        insert_query = '''
        INSERT INTO student (id, name, class, mark, gender) VALUES
        (1, 'John Deo', 'Four', 75, 'female'),
        (2, 'Max Ruin', 'Three', 85, 'male'),
        (3, 'Arnold', 'Three', 55, 'male'),
        (4, 'Krish Star', 'Four', 60, 'female'),
        (5, 'John Mike', 'Four', 60, 'female'),
        (6, 'Alex John', 'Four', 55, 'male'),
        (7, 'My John Rob', 'Five', 78, 'male'),
        (8, 'Asruid', 'Five', 85, 'male'),
        (9, 'Tes Qry', 'Six', 78, 'male'),
        (10, 'Big John', 'Four', 55, 'female'),
        (11, 'Ronald', 'Six', 89, 'female'),
        (12, 'Recky', 'Six', 94, 'female'),
        (13, 'Kty', 'Seven', 88, 'female'),
        (14, 'Bigy', 'Seven', 88, 'female'),
        (15, 'Tade Row', 'Four', 88, 'male'),
        (16, 'Gimmy', 'Four', 88, 'male'),
        (17, 'Tumyu', 'Six', 54, 'male'),
        (18, 'Honny', 'Five', 75, 'male'),
        (19, 'Tinny', 'Nine', 18, 'male'),
        (20, 'Jackly', 'Nine', 65, 'female'),
        (21, 'Babby John', 'Four', 69, 'female'),
        (22, 'Reggid', 'Seven', 55, 'female'),
        (23, 'Herod', 'Eight', 79, 'male'),
        (24, 'Tiddy Now', 'Seven', 78, 'male'),
        (25, 'Giff Tow', 'Seven', 88, 'male'),
        (26, 'Crelea', 'Seven', 79, 'male'),
        (27, 'Big Nose', 'Three', 81, 'female'),
        (28, 'Rojj Base', 'Seven', 86, 'female'),
        (29, 'Tess Played', 'Seven', 55, 'male'),
        (30, 'Reppy Red', 'Six', 79, 'female'),
        (31, 'Marry Toeey', 'Four', 88, 'male'),
        (32, 'Binn Rott', 'Seven', 90, 'female'),
        (33, 'Kenn Rein', 'Six', 96, 'female'),
        (34, 'Gain Toe', 'Seven', 69, 'male'),
        (35, 'Rows Noump', 'Six', 88, 'female');
        '''
        my_conn.execute(text(insert_query))
        messagebox.showinfo("Success", "All records added successfully.")
        refresh_treeview()
    except exc.SQLAlchemyError as e:
        messagebox.showerror("Error", f"An error occurred: {e}")

# Function to delete all records from the student table
def delete_records():
    try:
        my_conn.execute(text('DELETE FROM student'))
        messagebox.showinfo("Success", "All records deleted successfully.")
        refresh_treeview()
    except exc.SQLAlchemyError as e:
        messagebox.showerror("Error", f"An error occurred: {e}")

# Function to refresh the Treeview with current data
def refresh_treeview():
    for item in trv.get_children():
        trv.delete(item)
    r_set = my_conn.execute(text('SELECT * FROM student'))
    for dt in r_set:
        v = [r for r in dt]
        trv.insert("", 'end', iid=v[0], values=v)

# Creating tkinter window
my_w = tk.Tk()
my_w.geometry("600x400")
my_w.title("Student Records Management")

# Using Treeview widget
trv = ttk.Treeview(my_w, selectmode='browse')
trv.grid(row=1, column=1, padx=20, pady=20, columnspan=2)

# Configuring Treeview style
style = ttk.Style(my_w)
style.theme_use("clam")
style.configure("Treeview", background="black",
                fieldbackground="black", foreground="white")
style.configure('Treeview.Heading', background="PowderBlue")

# Database connection
base_path = os.path.dirname(__file__)
db_path = os.path.join(base_path, 'my_db.db')
my_conn = create_engine("sqlite:///" + db_path).connect()

# Fetching column headers
r_set = my_conn.execute(text('SELECT * FROM student'))
l1 = [r for r in r_set.keys()]

# Configuring Treeview columns
trv['columns'] = l1
trv['show'] = 'headings'
for i in l1:
    trv.column(i, width=100, anchor='c')
    trv.heading(i, text=i)

# Adding data to Treeview
refresh_treeview()

# Adding buttons for adding and deleting records
add_button = tk.Button(my_w, text="Add All Records", command=add_records)
add_button.grid(row=2, column=1, padx=10, pady=10)

delete_button = tk.Button(my_w, text="Delete All Records", command=delete_records)
delete_button.grid(row=2, column=2, padx=10, pady=10)

my_w.mainloop()