→ Foreign key linking tables
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.
from tkinter import ttk, tk
from sqlalchemy import create_engine, text # For database connection
from sqlalchemy.exc import SQLAlchemyError # Handle SQL errors
database_path = "F:\\testing\\projects2\\product_sales\\product_sales.db"
my_conn = create_engine(f"sqlite:///{database_path}").connect()
my_w = tk.Tk()
my_w.geometry("530x600") # Set window dimensions
my_w.title("www.plus2net.com") # Window title
t1 = tk.Text(my_w, height=10, width=70, bg='yellow')
t1.grid(row=0, column=0, padx=8, pady=10)
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')
style = ttk.Style(my_w)
style.theme_use("clam") # Use clam theme
style.configure("Treeview", background='black', ...)
my_w.mainloop() # Keeps the application running
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()
# 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()
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;