Integrating an SQLite database into a standalone Tkinter application using PyInstaller


Create Standalone Tkinter App with Integrated SQLite Database #Python #Tkinter #SQLite #PyInstaller

By Bundling a Python application that utilizes Tkinter for its graphical user interface and SQLite for data management into a single executable, simplifies distribution and enhances the user experience.
PyInstaller is a powerful tool that facilitates this process. Below is a step-by-step guide to achieve this:
Part I: Browse and connect to SQLite database ( without bundling )

1. Install PyInstaller:


Ensure PyInstaller is installed in your Python environment:
pip install pyinstaller

2. Organize all Project Files:

Place our main Python script (e.g., my_sqlite_app.py) and the SQLite database file (e.g., my_db.db) in the same directory.

3. Modify the Application to Locate the Database:

When bundled, the application's directory structure changes. Adjust our code to dynamically locate the database file:
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:

Use PyInstaller's --add-data option to include the SQLite database in the executable. The syntax differs between operating systems:

my_sqlite_app.py : Source code or our main file.
D:\\testing\\sqlite\\my_sqlite_app.py : Full Path to our source file.
D:\\testing\\sqlite\\my_db.db : Full path to our SQLite database file.
D:\\my_app : Destination path where our executable file will be stored.
PyInstaller --onefile --windowed --add-data "D:\\testing\\sqlite\\my_db.db;." --distpath D:\\my_app D:\\testing\\sqlite\\my_sqlite_app.py
We will bundle one application with SQLite database where we will display records from student table. We kept both our application file my_sqlite_app.py and SQLite database my_db.db in same directory.

This is a read only application where records are only displayed.

Bundling of SQLite with Tkinter read only Application

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()
Writable Databases: If the application needs to write to the database, consider copying the bundled database to a writable location (e.g., the user's home directory) upon the application's first run.

my_sqlite_app2.py : Source code or our main file.
D:\\testing\\sqlite\\my_sqlite_app2.py : Full Path to our source file.
D:\\testing\\sqlite\\my_db.db : Full path to our SQLite database file.
D:\\my_app : Destination path where our executable file will be stored.
PyInstaller --onefile --windowed --add-data "D:\\testing\\sqlite\\my_db.db;." --distpath D:\\my_app D:\\testing\\sqlite\\my_sqlite_app2.py

Bundling of SQLite with Tkinter read write Application

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()


Part I: Browse and connect to SQLite database ( without bundling )
Part III: Creating a Holiday Calendar Application with Python Tkinter and SQLite ( bundled )
Creating Applications by using PyInstaller
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    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