Building a Lightweight Python GUI App with SQLite in Under 30 Lines of Code

by
PinLinkedInPrint

Today, we are going to build a Graphical User Interface (GUI) application that connects to a local database and fetches data. The best part? We’ll accomplish the entire setup—database creation, UI layout, and data fetching—with just a few lines of clean, straightforward Python code.

Let’s delve right into it!

Step 1: Prerequisites & Installation

Before we start, make sure Python is installed on your system. We will be using Tkinter, Python’s built-in GUI library, and SQLite, a lightweight, serverless database engine that comes bundled with Python.

If you are on a Linux-based system (like Ubuntu/Debian), Tkinter might not be pre-installed by default. You can install it by opening your terminal and entering:

Bash

sudo apt install python3-tk

Step 2: Setting Up the Database

Next, let’s create a local database file and seed it with some sample data using the SQLite shell.

Run the following command in your terminal to create a empty database file:

Bash

touch my.db

Now, open this file using the SQLite interactive shell:

Bash

sqlite3 my.db

(Hint: if sqlite3 is not already installed, then install it by using sudo apt install sqlite3)

Once you are inside the SQLite shell (you’ll see a sqlite> prompt), run the following command to create a basic table named cars:

SQL

CREATE TABLE cars (brand VARCHAR(255), model VARCHAR(255), year INT);

With the table ready, let’s insert a couple of records so we have something to fetch later:

SQL

INSERT INTO cars (brand, model, year) VALUES ('Ford', 'Mustang', 1964);
INSERT INTO cars (brand, model, year) VALUES ('Toyota', 'Corolla', 1995);

That’s it for the database configuration! Exit the SQLite shell by typing:

SQL

.quit

Step 3: Writing the Python GUI Application

Now, let’s write the application logic. Create a new Python file in the same directory as your my.db file:

Bash

touch gui.py

Open gui.py in your favorite code editor and insert the following code:

Python

import tkinter as tk
import sqlite3

# Initialize the main application window
app = tk.Tk()
app.title("Python App")
app.geometry("400x300")

# Create a text widget to display our data
text = tk.Text(app)

def get_data():
    # Establish a connection to the database
    conn = sqlite3.connect("my.db")
    cursor = conn.cursor()

    # Execute the query and fetch all results
    cursor.execute("SELECT * FROM cars")
    data = cursor.fetchall()

    # Clear the text area before inserting new data
    text.delete('1.0', tk.END)

    # Loop through the data and insert it into the text widget
    for row in data:
        text.insert('1.0', f"{row}\n")

    # Close the database connection
    conn.close()

# Add a button that triggers the get_data function
button = tk.Button(app, text="Fetch Data", command=get_data)

# Layout the widgets using the pack manager
button.pack(pady=10)
text.pack(padx=10, pady=10)

# Start the application loop
app.mainloop()

Pro Tip: In the code above, text.delete('1.0', tk.END) was added right inside the get_data function. This ensures that if you click the “Fetch Data” button multiple times, it clears the old view instead of continuously appending duplicate rows!

Step 4: Running the App

Save the file and run your newly minted application from your terminal:

Bash

python3 gui.py

A clean window titled “Python App” will pop up. Click the “Fetch Data” button, and watch your SQLite database records seamlessly populate the text field.

Wrapping Up

Congratulations! You’ve just built a fully functional database-driven desktop app using nothing but Python’s standard libraries. This minimalist setup serves as a rock-solid foundation. From here, you can easily extend the database schema, add input forms to write new data back to the database, or style the Tkinter UI to fit your needs.

PinLinkedInPrint

Leave a Comment