Advanced SQL Query Viewer for Multi-Table Joins with Tkinter and SQLite

Generating repors by joining multiple tables using SQLite data


Part I : Application | Part II : Creating SQLite table | Part III : Sample Query to try

Database Table Relationships

Customers
  • customer_id (Primary Key)
  • customer_name
  • email

Sales
  • sales_id (Primary Key)
  • product_id (Foreign Key)
  • customer_id (Foreign Key)
  • quantity
  • sale_date

Products
  • product_id (Primary Key)
  • product_name
  • price
Legend

→ Foreign key linking tables


Master Relational Data Insights: Linking Tables in SQLite using Python Tkinter #sqlite #tkinter

Discover how to leverage Tkinter to execute SQL queries on an SQLite database and showcase results in an interactive Treeview widget. This tutorial combines intuitive design, error handling, and vibrant styling to help you build powerful data analysis and reporting tools with ease.

Code Sections 🔝

1. Import Libraries

from tkinter import ttk, tk
from sqlalchemy import create_engine, text  # For database connection
from sqlalchemy.exc import SQLAlchemyError  # Handle SQL errors
  • Imports Tkinter modules for GUI.
  • Uses SQLAlchemy for database interaction.

2. Database Connection

database_path = "F:\\testing\\projects2\\product_sales\\product_sales.db"
my_conn = create_engine(f"sqlite:///{database_path}").connect()  
  • Connects to the SQLite database located at the specified path.
  • Ensures the connection is ready for executing queries.

3. Main Application Window

my_w = tk.Tk()
my_w.geometry("530x600")  # Set window dimensions
my_w.title("www.plus2net.com")  # Window title
  • Sets up the main Tkinter window with a specific size and title.

4. User Input

t1 = tk.Text(my_w, height=10, width=70, bg='yellow')
t1.grid(row=0, column=0, padx=8, pady=10)
  • Adds a text widget for users to enter SQL queries.
  • Configured with a light yellow background for better visibility.

5. Query Execution and Results Display

def my_query(query):
    try:
        r_set = my_conn.execute(text(query))
        l1 = [r for r in r_set.keys()]
        r_set = list(r_set)
        ...
    except SQLAlchemyError as e:
        error = str(e.__dict__.get('orig', e))
        tk.Label(my_w, text=error, fg='red')
  • Executes SQL queries entered by the user.
  • Handles errors gracefully, displaying them in red.
  • Uses Treeview to display the query results in a tabular format.

6. Treeview Styling

style = ttk.Style(my_w)
style.theme_use("clam")  # Use clam theme
style.configure("Treeview", background='black', ...)
  • Applies a dark theme for the Treeview.
  • Uses custom colors for table headers and rows.

7. Running the Application

my_w.mainloop()  # Keeps the application running
  • Ensures the Tkinter application runs continuously until the user closes it.

Code Highlights

This application dynamically executes SQL queries and displays results with a polished UI. It’s perfect for database testing or quick query visualizations.



from tkinter import ttk
import tkinter as tk

from sqlalchemy import create_engine,text  # For database connection
from sqlalchemy.exc import SQLAlchemyError

# SQLite connection string
database_path = "F:\\testing\\projects2\\product_sales\\product_sales.db"
my_conn = create_engine(f"sqlite:///{database_path}").connect()  # Connect to SQLite database

# Creating tkinter window
my_w = tk.Tk()
my_w.geometry("530x600")  # Width and height of the window
my_w.title("www.plus2net.com")
font1 = ['Times', 10, 'normal']  # Font style for Text entry box

# Textbox for user input
t1 = tk.Text(my_w, height=10, width=70, bg='yellow', font=font1)
t1.grid(row=0, column=0, padx=8, pady=10)

# Button to execute query
b1 = tk.Button(my_w, text='GO', font=18, command=lambda: my_query(t1.get("1.0", 'end')))
b1.grid(row=0, column=1,padx=0)

# Adding style to Treeview
font2 = ['Times', 14, 'normal']
style = ttk.Style(my_w)
style.theme_use("clam")  # Set theme to clam
style.configure("Treeview", background="black", fieldbackground="black", foreground="white", font=font2)
style.configure('Treeview.Heading', background="PowderBlue")  # Header style

# Function to execute the query and display results
def my_query(query):
    for w in my_w.grid_slaves(1): 
        w.grid_forget()  # Remove all widgets

    try:
        # Execute query
        r_set = my_conn.execute(text(query))  # Execute query and get record set
        l1 = [r for r in r_set.keys()]  # List of columns from the database table
        r_set = list(r_set)  # List of rows of records

    except SQLAlchemyError as e:  # Handle database errors
        error = str(e.__dict__.get('orig', e))  # Get the error message
        l1 = tk.Label(my_w, text=error, fg='red', font=16)  # Display error message
        l1.grid(row=1, column=0, columnspan=1, padx=20, pady=20)
        print(error)  # Print error to console

    else:  # No error, so display the Treeview
        trv = ttk.Treeview(my_w, selectmode='browse', columns=l1, show='headings', height=15)
        trv.grid(row=1, column=0, columnspan=2, padx=20, pady=20)

        for i in l1:  # List of columns collected from Database
            trv.column(i, anchor='w', width=100)
            trv.heading(i, text=i)
        for row in r_set:  # Add rows or records
            trv.insert('', 'end', iid=row[0], text=row[0], values=list(row))
        
        # Adding vertical Scrollbar to the right of Treeview
        vs = ttk.Scrollbar(my_w, orient="vertical", command=trv.yview)
        trv.configure(yscrollcommand=vs.set)
        vs.grid(row=1, column=1, sticky='ns')  # Place scrollbar to the right of Treeview

# Run the Tkinter application
my_w.mainloop()

Creating and Populating an SQLite Database with SQLAlchemy 🔝

We will create and populate an SQLite database using SQLAlchemy. It defines three tables — products, customers, and sales — with appropriate relationships, such as foreign keys.
Sample data is inserted into each table to simulate a real-world scenario. The database file is stored at a specified path, making it portable and easy to integrate with applications.

# Connect to SQLite database (or create it if it doesn't exist)
from sqlalchemy import create_engine,text  # For database connection
from sqlalchemy.exc import SQLAlchemyError

# SQLite connection string
database_path = "F:\\testing\\projects2\\product_sales\\product_sales.db"
# Connect to SQLite database
my_conn = create_engine(f"sqlite:///{database_path}").connect()  

# Create `products` table
my_conn.execute(text("""
CREATE TABLE IF NOT EXISTS products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL NOT NULL
)
"""))

# Create `customers` table
my_conn.execute(text("""
CREATE TABLE IF NOT EXISTS customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL,
    email TEXT NOT NULL
)
"""))

# Create `sales` table
my_conn.execute(text("""
CREATE TABLE IF NOT EXISTS sales (
    sales_id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    sale_date TEXT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
)
"""))

# Insert data into `products` table
products = [
    {"product_id": 1, "product_name": "Laptop", "price": 800.00},
    {"product_id": 2, "product_name": "Mobile Phone", "price": 500.00},
    {"product_id": 3, "product_name": "Tablet", "price": 300.00},
    {"product_id": 4, "product_name": "Smart Watch", "price": 200.00},
    {"product_id": 5, "product_name": "Headphones", "price": 50.00},
    {"product_id": 6, "product_name": "CPU", "price": 30.00},
    {"product_id": 7, "product_name": "GPU", "price": 70.00}
]
my_conn.execute(text("""
        INSERT INTO products (product_id, product_name, price) 
        VALUES (:product_id, :product_name, :price)
        """), products)

# Insert data into `customers` table
customers = [
    {"customer_id": 1, "customer_name": "Alice Johnson", "email": "alice.johnson@example.com"},
    {"customer_id": 2, "customer_name": "Bob Smith", "email": "bob.smith@example.com"},
    {"customer_id": 3, "customer_name": "Charlie Brown", "email": "charlie.brown@example.com"},
    {"customer_id": 4, "customer_name": "Diana Prince", "email": "diana.prince@example.com"},
    {"customer_id": 5, "customer_name": "Ethan Hunt", "email": "ethan.hunt@example.com"},
    {"customer_id": 6, "customer_name": "Alex", "email": "alex@example.com"},
    {"customer_id": 7, "customer_name": "Ron", "email": "ron@example.com"}
]

my_conn.execute(text("""
        INSERT INTO customers (customer_id, customer_name, email) 
        VALUES (:customer_id, :customer_name, :email)
        """), customers)

# Insert data into `sales` table
sales = [
    {"sales_id": 1, "product_id": 1, "customer_id": 1, "quantity": 2, "sale_date": "2025-01-01"},  # Alice buys 2 Laptops
    {"sales_id": 2, "product_id": 2, "customer_id": 2, "quantity": 1, "sale_date": "2025-01-02"},  # Bob buys 1 Mobile Phone
    {"sales_id": 3, "product_id": 3, "customer_id": 3, "quantity": 3, "sale_date": "2025-01-03"},  # Charlie buys 3 Tablets
    {"sales_id": 4, "product_id": 4, "customer_id": 4, "quantity": 1, "sale_date": "2025-01-04"},  # Diana buys 1 Smart Watch
    {"sales_id": 5, "product_id": 5, "customer_id": 5, "quantity": 5, "sale_date": "2025-01-05"}   # Ethan buys 5 Headphones
]

my_conn.execute(text("""
        INSERT INTO sales (sales_id, product_id, customer_id, quantity, sale_date) 
        VALUES (:sales_id, :product_id, :customer_id, :quantity, :sale_date)
        """), sales)

# Commit changes and close the connection
my_conn.commit()
print("Database and tables created with sample data!")
my_conn.close()

Explore Comprehensive SQL Queries for Your Database 🔝

These SQL queries are designed to provide insightful reports and data analysis using our products, customers, and sales data. Whether you're looking to identify top customers, track revenue trends, or analyze sales by product, these queries have you covered. Copy, modify, and run them on your database to see the results in action!

Total revenue generated by each product.

SELECT 
    p.product_name,
    SUM(s.quantity * p.price) AS total_revenue
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.product_id
GROUP BY 
    p.product_name
ORDER BY 
    total_revenue DESC;

Total quantity sold for each product.

SELECT 
    p.product_name,
    SUM(s.quantity) AS total_quantity_sold
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.product_id
GROUP BY 
    p.product_name
ORDER BY 
    total_quantity_sold DESC;

Top 5 customers based on their total spending.

SELECT 
    c.customer_name,
    SUM(s.quantity * p.price) AS total_spent
FROM 
    sales s
JOIN 
    customers c ON s.customer_id = c.customer_id
JOIN 
    products p ON s.product_id = p.product_id
GROUP BY 
    c.customer_name
ORDER BY 
    total_spent DESC
LIMIT 5;

Daily sales summary including revenue and quantity sold.

SELECT 
    s.sale_date,
    SUM(s.quantity) AS total_quantity_sold,
    SUM(s.quantity * p.price) AS total_revenue
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.product_id
GROUP BY 
    s.sale_date
ORDER BY 
    s.sale_date ASC;

List of products purchased by each customer along with quantity.

SELECT 
    c.customer_name,
    p.product_name,
    s.quantity
FROM 
    sales s
JOIN 
    customers c ON s.customer_id = c.customer_id
JOIN 
    products p ON s.product_id = p.product_id
ORDER BY 
    c.customer_name, p.product_name;

Total revenue generated by the company.

SELECT 
    SUM(s.quantity * p.price) AS total_revenue
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.product_id;

Customers who purchased the product "Laptop".

SELECT 
    c.customer_name,
    s.quantity,
    p.product_name
FROM 
    sales s
JOIN 
    customers c ON s.customer_id = c.customer_id
JOIN 
    products p ON s.product_id = p.product_id
WHERE 
    p.product_name = 'Laptop';

Find the most popular product by total quantity sold.

SELECT 
    p.product_name,
    SUM(s.quantity) AS total_quantity_sold
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.product_id
GROUP BY 
    p.product_name
ORDER BY 
    total_quantity_sold DESC
LIMIT 1;

Average revenue per sale transaction.

SELECT 
    AVG(s.quantity * p.price) AS average_revenue_per_sale
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.product_id;

Total sales summary (revenue and quantity) by month.

SELECT 
    strftime('%Y-%m', s.sale_date) AS month,
    SUM(s.quantity) AS total_quantity_sold,
    SUM(s.quantity * p.price) AS total_revenue
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.product_id
GROUP BY 
    month
ORDER BY 
    month ASC;

Identify customers who have not made any purchases.

SELECT 
    c.customer_name
FROM 
    customers c
LEFT JOIN 
    sales s ON c.customer_id = s.customer_id
WHERE 
    s.customer_id IS NULL;

List products that were never purchased.

SELECT 
    p.product_name
FROM 
    products p
LEFT JOIN 
    sales s ON p.product_id = s.product_id
WHERE 
    s.product_id IS NULL;

From our SQL section you can rad more on these topics
LEFT JOIN query WHERE query LIMIT query ORDER BY query
Part II : Copy selected rows or all rows from the Treeview
Part I : Display database Query results in Treeview
Projects in Tkinter

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