Student Management System - A GUI-based project using Tkinter for frontend and MySQL (via PyMySQL) for backend
Project Idea: Student Management System (SMS)
A
GUI-based project using Tkinter for frontend and MySQL (via PyMySQL) for backend.
It will
allow students/admins to:
- Add, update, delete student
records
- Search and view records
- Manage login/signup system
- Display statistics (count of
students, charts if needed)
- Export data (CSV/Excel)
This
covers all Python + Tkinter widgets + MySQL CRUD operations.
Step-by-Step Approach
1. Foundation Setup
- Install required modules (pymysql, tkinter is built-in).
- Create MySQL database (student_db) and table (students).
- Basic connection with pymysql.connect().
Learning
outcome: Students
understand SQL integration.
2. Tkinter Basics
- Teach Tk(), Frame, Label, Entry, Button.
- Create a login window
(username/password stored in MySQL).
Learning
outcome: Students
understand GUI basics & event handling.
3. CRUD Operations with MySQL
- Insert: Add student details (name,
age, course, email, contact).
- Read: Display student records in
a Tkinter Treeview.
- Update: Select a record → update
details.
- Delete: Delete selected record.
Learning
outcome: Complete
CRUD with Tkinter + SQL.
4. Tkinter Widgets (Advanced)
- Treeview (display data table-like
format).
- Scrollbar (vertical/horizontal).
- Combobox (for course selection).
- Radiobuttons & Checkbuttons (for gender/hobbies).
- Messagebox for confirmation alerts.
- Menu (File → Exit, Help →
About).
Learning outcome: Students master all major Tkinter widgets.
📚 Lesson-by-Lesson Project Plan
Project: Student Management System (Tkinter + MySQL using
PyMySQL)
Lesson 1 – Introduction &
Setup
Topics:
·
Install required tools (Python, MySQL, PyMySQL).
·
Create MySQL database & table.
·
Write first DB connection in Python.
Code:
import pymysql # Connect to MySQLtry: con = pymysql.connect(host="localhost", user="root", password="", database="student_db") print("Database Connected Successfully!") con.close()except Exception as e: print("Error:", e)Task for Students:
·
Install MySQL & create a database student_db.
·
Create table:
CREATE TABLE students( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT, course VARCHAR(100), email VARCHAR(100), phone VARCHAR(15));Lesson 2 – Tkinter Basics (Login
Window)
Topics:
·
Tkinter window setup
·
Labels, Entry, Button
·
Event handling
Code:
from tkinter import * def login(): user = username_entry.get() pwd = password_entry.get() print("Username:", user, "Password:", pwd) # Later connect to DB root = Tk()root.title("Login Page") Label(root, text="Username").grid(row=0, column=0, padx=10, pady=10)username_entry = Entry(root)username_entry.grid(row=0, column=1) Label(root, text="Password").grid(row=1, column=0, padx=10, pady=10)password_entry = Entry(root, show="*")password_entry.grid(row=1, column=1) Button(root, text="Login", command=login).grid(row=2, column=0, columnspan=2, pady=10) root.mainloop()Task:
·
Create a Signup page with
Username, Password, Email.
·
Print entered values on console.
Lesson 3 – Connecting Login with MySQL
Topics:
·
Insert & fetch data from MySQL
·
Simple signup/login system
Code (Signup & Login):
import pymysqlfrom tkinter import *from tkinter import messagebox def signup(): user = user_entry.get() pwd = pass_entry.get() con = pymysql.connect(host="localhost", user="root", password="", database="student_db") cur = con.cursor() cur.execute("INSERT INTO users(username, password) VALUES(%s, %s)", (user, pwd)) con.commit() con.close() messagebox.showinfo("Success", "User Registered!") root = Tk()root.title("Signup") Label(root, text="Username").grid(row=0, column=0)user_entry = Entry(root)user_entry.grid(row=0, column=1) Label(root, text="Password").grid(row=1, column=0)pass_entry = Entry(root, show="*")pass_entry.grid(row=1, column=1) Button(root, text="Signup", command=signup).grid(row=2, column=0, columnspan=2) root.mainloop()👉 (Students first create users table:
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), password VARCHAR(50));)
Lesson 4 – Student Form (Insert Data)
Topics:
·
Tkinter form with multiple entries
·
Insert student details into DB
Code:
def add_student(): name = name_entry.get() age = age_entry.get() course = course_entry.get() email = email_entry.get() phone = phone_entry.get() con = pymysql.connect(host="localhost", user="root", password="", database="student_db") cur = con.cursor() cur.execute("INSERT INTO students(name, age, course, email, phone) VALUES(%s, %s, %s, %s, %s)", (name, age, course, email, phone)) con.commit() con.close() messagebox.showinfo("Success", "Student Added!") root = Tk()root.title("Add Student") Label(root, text="Name").grid(row=0, column=0)name_entry = Entry(root)name_entry.grid(row=0, column=1) Label(root, text="Age").grid(row=1, column=0)age_entry = Entry(root)age_entry.grid(row=1, column=1) Label(root, text="Course").grid(row=2, column=0)course_entry = Entry(root)course_entry.grid(row=2, column=1) Label(root, text="Email").grid(row=3, column=0)email_entry = Entry(root)email_entry.grid(row=3, column=1) Label(root, text="Phone").grid(row=4, column=0)phone_entry = Entry(root)phone_entry.grid(row=4, column=1) Button(root, text="Add Student", command=add_student).grid(row=5, column=0, columnspan=2) root.mainloop()
Lesson 5 – Displaying Data with Treeview
Topics:
·
Tkinter Treeview
widget
·
Fetch & display MySQL data
Code:
from tkinter import ttk def show_students(): con = pymysql.connect(host="localhost", user="root", password="", database="student_db") cur = con.cursor() cur.execute("SELECT * FROM students") rows = cur.fetchall() con.close() for row in rows: tree.insert("", END, values=row) root = Tk()root.title("Student Records") tree = ttk.Treeview(root, columns=("ID","Name","Age","Course","Email","Phone"), show='headings')for col in ("ID","Name","Age","Course","Email","Phone"): tree.heading(col, text=col) tree.column(col, width=100)tree.pack(fill=BOTH, expand=True) Button(root, text="Show Students", command=show_students).pack() root.mainloop()
✅ Lesson 2 – Basic Tkinter Form
(UI only)
- Build a Login Window
(Username + Password fields, Login button).
- No DB yet — just show "Login Successful" or "Invalid" with dummy values.
- Concepts: Tkinter Label, Entry, Button, pack/grid, messagebox.
✅ Lesson 3 – Connect Login with
MySQL
- Store credentials in a table
users.
- On login: verify user with SELECT.
- If correct → show "Welcome User".
- Concepts: SQL INSERT, SELECT, safe queries with
placeholders (%s).
✅ Lesson 4 – Student Form (Insert
Records)
- Create a Student Entry
Form: Name, Age, Course, Email, Phone.
- Add Save button →
Insert into students table.
- Concepts: Multi-field form,
validation, SQL INSERT.
✅ Lesson 5 – Display Records
(Treeview)
- Use ttk.Treeview to display all students
from the table.
- Show ID, Name, Age, Course,
Email, Phone.
- Concepts: Reading from DB,
populating Tkinter tables.
✅ Lesson 6 – Update & Delete
(CRUD complete)
- Select row in Treeview →
load into Entry fields.
- Update button → modify student
details (UPDATE).
- Delete button → remove student (DELETE).
- Concepts: Tkinter event
binding, SQL UPDATE/DELETE.
✅ Lesson 7 – Search & Filter
- Add Search box (by Name or
Course).
- Show filtered results in
Treeview.
- Concepts: SQL WHERE clause, dynamic queries.
✅ Lesson 8 – User Management
(Advanced)
- Add Signup page for new
users (with password hashing).
- Login authentication with
secure credentials.
- Concepts: hashlib, authentication logic,
multiple windows in Tkinter.
✅ Lesson 9 – Project Polishing
- Add Menu bar (tk.Menu) → Navigate between forms.
- Add Logout option.
- Add export to CSV/Excel (pandas).
📝 Lesson 2 – Login UI (Tkinter only)
🎯 Learning Goals
·
Create a login form using Tkinter.
·
Use Label,
Entry, Button, and messagebox.
·
Dummy authentication: compare entered
username/password with hardcoded values.
🔹 Full Code: lesson2/login_ui.py
import tkinter as tkfrom tkinter import messagebox # ---------- Dummy login check ----------def login(): username = entry_username.get() password = entry_password.get() if username == "admin" and password == "1234": # hardcoded for now messagebox.showinfo("Login Success", f"Welcome {username}!") else: messagebox.showerror("Login Failed", "Invalid Username or Password") # ---------- GUI ----------root = tk.Tk()root.title("Login System - Lesson 2")root.geometry("400x250")root.resizable(False, False) # Title labeltitle = tk.Label(root, text="Login Window", font=("Arial", 16, "bold"))title.pack(pady=10) # Usernameframe = tk.Frame(root)frame.pack(pady=5) tk.Label(frame, text="Username:", font=("Arial", 12)).grid(row=0, column=0, padx=5, pady=5, sticky="e")entry_username = tk.Entry(frame, font=("Arial", 12))entry_username.grid(row=0, column=1, padx=5, pady=5) # Passwordtk.Label(frame, text="Password:", font=("Arial", 12)).grid(row=1, column=0, padx=5, pady=5, sticky="e")entry_password = tk.Entry(frame, show="*", font=("Arial", 12))entry_password.grid(row=1, column=1, padx=5, pady=5) # Login Buttonbtn_login = tk.Button(root, text="Login", font=("Arial", 12), command=login, width=12, bg="blue", fg="white")btn_login.pack(pady=20) # Exit Buttonbtn_exit = tk.Button(root, text="Exit", font=("Arial", 12), command=root.quit, width=12, bg="red", fg="white")btn_exit.pack() root.mainloop()
📝 Lesson 3 – Login with MySQL
🎯 Learning Goals
·
Connect Tkinter UI with MySQL using PyMySQL.
·
Validate login by checking DB records.
·
Use safe parameterized
queries (%s)
to prevent SQL injection.
🔹 SQL Setup (Run Once in MySQL)
Before running the Python code, create a users
table:
CREATE DATABASE IF NOT EXISTS student_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE student_db; CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(100) NOT NULL); -- Insert one test userINSERT INTO users (username, password) VALUES ('admin', '1234');
🔹 Full Code: lesson3/login_mysql.py
import tkinter as tkfrom tkinter import messageboximport pymysql # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- Login Function ----------def login(): username = entry_username.get() password = entry_password.get() if not username or not password: messagebox.showwarning("Input Error", "All fields are required") return try: con = get_connection() cur = con.cursor() cur.execute("SELECT * FROM users WHERE username=%s AND password=%s", (username, password)) row = cur.fetchone() con.close() if row: messagebox.showinfo("Login Success", f"Welcome {username}!") else: messagebox.showerror("Login Failed", "Invalid Username or Password") except Exception as e: messagebox.showerror("Error", f"Database Error: {str(e)}") # ---------- GUI ----------root = tk.Tk()root.title("Login System - Lesson 3 (MySQL)")root.geometry("400x250")root.resizable(False, False) # Title labeltitle = tk.Label(root, text="Login Window", font=("Arial", 16, "bold"))title.pack(pady=10) # Usernameframe = tk.Frame(root)frame.pack(pady=5) tk.Label(frame, text="Username:", font=("Arial", 12)).grid(row=0, column=0, padx=5, pady=5, sticky="e")entry_username = tk.Entry(frame, font=("Arial", 12))entry_username.grid(row=0, column=1, padx=5, pady=5) # Passwordtk.Label(frame, text="Password:", font=("Arial", 12)).grid(row=1, column=0, padx=5, pady=5, sticky="e")entry_password = tk.Entry(frame, show="*", font=("Arial", 12))entry_password.grid(row=1, column=1, padx=5, pady=5) # Buttonsbtn_login = tk.Button(root, text="Login", font=("Arial", 12), command=login, width=12, bg="blue", fg="white")btn_login.pack(pady=20) btn_exit = tk.Button(root, text="Exit", font=("Arial", 12), command=root.quit, width=12, bg="red", fg="white")btn_exit.pack() root.mainloop()
📚 Concepts Covered
·
Integrating Tkinter with MySQL using PyMySQL
·
Running SELECT queries
with parameterized inputs
·
Handling exceptions
(try/except)
·
Simple DB-driven authentication
📝 Lesson 4 – Student Form (Insert Records)
🎯 Learning Goals
·
Design a form with multiple Entry fields (Name, Age, Course, Email,
Phone).
·
Add data into the students table using INSERT query.
·
Validate input before saving.
·
Show success/failure messages.
🔹 SQL Setup (Run Once)
If not already created, run in MySQL:
CREATE DATABASE IF NOT EXISTS student_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE student_db; CREATE TABLE IF NOT EXISTS students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT CHECK (age >= 0), course VARCHAR(100), email VARCHAR(100), phone VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
🔹 Full Code: lesson4/student_form.py
import tkinter as tkfrom tkinter import messageboximport pymysql # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- Insert Student ----------def add_student(): name = entry_name.get() age = entry_age.get() course = entry_course.get() email = entry_email.get() phone = entry_phone.get() if not name or not age: messagebox.showwarning("Input Error", "Name and Age are required") return try: con = get_connection() cur = con.cursor() cur.execute( "INSERT INTO students (name, age, course, email, phone) VALUES (%s, %s, %s, %s, %s)", (name, age, course, email, phone) ) con.commit() con.close() messagebox.showinfo("Success", "Student added successfully!") clear_fields() except Exception as e: messagebox.showerror("Error", f"Database Error: {str(e)}") # ---------- Clear Form ----------def clear_fields(): entry_name.delete(0, tk.END) entry_age.delete(0, tk.END) entry_course.delete(0, tk.END) entry_email.delete(0, tk.END) entry_phone.delete(0, tk.END) # ---------- GUI ----------root = tk.Tk()root.title("Student Form - Lesson 4")root.geometry("500x350")root.resizable(False, False) # Titletitle = tk.Label(root, text="Add Student", font=("Arial", 16, "bold"))title.pack(pady=10) form_frame = tk.Frame(root)form_frame.pack(pady=10) # Nametk.Label(form_frame, text="Name:", font=("Arial", 12)).grid(row=0, column=0, padx=5, pady=5, sticky="e")entry_name = tk.Entry(form_frame, font=("Arial", 12))entry_name.grid(row=0, column=1, padx=5, pady=5) # Agetk.Label(form_frame, text="Age:", font=("Arial", 12)).grid(row=1, column=0, padx=5, pady=5, sticky="e")entry_age = tk.Entry(form_frame, font=("Arial", 12))entry_age.grid(row=1, column=1, padx=5, pady=5) # Coursetk.Label(form_frame, text="Course:", font=("Arial", 12)).grid(row=2, column=0, padx=5, pady=5, sticky="e")entry_course = tk.Entry(form_frame, font=("Arial", 12))entry_course.grid(row=2, column=1, padx=5, pady=5) # Emailtk.Label(form_frame, text="Email:", font=("Arial", 12)).grid(row=3, column=0, padx=5, pady=5, sticky="e")entry_email = tk.Entry(form_frame, font=("Arial", 12))entry_email.grid(row=3, column=1, padx=5, pady=5) # Phonetk.Label(form_frame, text="Phone:", font=("Arial", 12)).grid(row=4, column=0, padx=5, pady=5, sticky="e")entry_phone = tk.Entry(form_frame, font=("Arial", 12))entry_phone.grid(row=4, column=1, padx=5, pady=5) # Buttonsbtn_add = tk.Button(root, text="Add Student", font=("Arial", 12), bg="green", fg="white", command=add_student, width=15)btn_add.pack(pady=10) btn_clear = tk.Button(root, text="Clear", font=("Arial", 12), bg="gray", fg="white", command=clear_fields, width=15)btn_clear.pack() root.mainloop()
📚 Concepts Covered
·
Tkinter form with multiple inputs.
·
SQL INSERT
with parameterized query.
·
Input validation (if not name or not age).
·
Clearing fields after submission.
📝 Lesson 5 – Display Records with Treeview
🎯 Learning Goals
·
Use ttk.Treeview
to create a table-like widget in Tkinter.
·
Fetch all records from the students table.
·
Populate the Treeview with MySQL results.
·
Add a Refresh button
to reload data.
🔹 Full Code: lesson5/student_display.py
import tkinter as tkfrom tkinter import ttk, messageboximport pymysql # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- Fetch and Display ----------def fetch_students(): try: con = get_connection() cur = con.cursor() cur.execute("SELECT * FROM students") rows = cur.fetchall() con.close() # Clear previous data for row in tree.get_children(): tree.delete(row) # Insert new data for row in rows: tree.insert("", tk.END, values=row) except Exception as e: messagebox.showerror("Error", f"Database Error: {str(e)}") # ---------- GUI ----------root = tk.Tk()root.title("Student Records - Lesson 5")root.geometry("800x400") title = tk.Label(root, text="Student Records", font=("Arial", 16, "bold"))title.pack(pady=10) # Table (Treeview)columns = ("ID", "Name", "Age", "Course", "Email", "Phone", "Created At")tree = ttk.Treeview(root, columns=columns, show="headings") for col in columns: tree.heading(col, text=col) tree.column(col, width=100, anchor="center") tree.pack(fill=tk.BOTH, expand=True, padx=10, pady=10) # Buttonsbtn_refresh = tk.Button(root, text="Refresh Records", font=("Arial", 12), bg="blue", fg="white", command=fetch_students)btn_refresh.pack(pady=10) # Load data initiallyfetch_students() root.mainloop()
📚 Concepts Covered
·
Using ttk.Treeview
as a table widget.
·
Looping through DB results and inserting into
Treeview.
·
Refreshing UI data dynamically.
📝 Lesson 6 – Update & Delete
🎯 Learning Goals
·
Select a student record from the Treeview.
·
Load it into the Entry fields.
·
Update student details in MySQL (UPDATE).
·
Delete student from MySQL (DELETE).
·
Refresh Treeview after changes.
🔹 Full Code: lesson6/student_crud.py
import tkinter as tkfrom tkinter import ttk, messageboximport pymysql # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- Functions ----------def fetch_students(): for row in tree.get_children(): tree.delete(row) con = get_connection() cur = con.cursor() cur.execute("SELECT * FROM students") rows = cur.fetchall() for row in rows: tree.insert("", tk.END, values=row) con.close() def add_student(): name = entry_name.get() age = entry_age.get() course = entry_course.get() email = entry_email.get() phone = entry_phone.get() if not name or not age: messagebox.showwarning("Input Error", "Name and Age are required") return con = get_connection() cur = con.cursor() cur.execute( "INSERT INTO students (name, age, course, email, phone) VALUES (%s, %s, %s, %s, %s)", (name, age, course, email, phone) ) con.commit() con.close() fetch_students() clear_fields() messagebox.showinfo("Success", "Student Added!") def clear_fields(): entry_id.delete(0, tk.END) entry_name.delete(0, tk.END) entry_age.delete(0, tk.END) entry_course.delete(0, tk.END) entry_email.delete(0, tk.END) entry_phone.delete(0, tk.END) def select_student(event): selected = tree.focus() if not selected: return data = tree.item(selected)["values"] clear_fields() entry_id.insert(0, data[0]) entry_name.insert(0, data[1]) entry_age.insert(0, data[2]) entry_course.insert(0, data[3]) entry_email.insert(0, data[4]) entry_phone.insert(0, data[5]) def update_student(): sid = entry_id.get() if not sid: messagebox.showwarning("Select", "Please select a student to update") return con = get_connection() cur = con.cursor() cur.execute( """UPDATE students SET name=%s, age=%s, course=%s, email=%s, phone=%s WHERE id=%s""", ( entry_name.get(), entry_age.get(), entry_course.get(), entry_email.get(), entry_phone.get(), sid, ), ) con.commit() con.close() fetch_students() clear_fields() messagebox.showinfo("Updated", "Student record updated successfully!") def delete_student(): sid = entry_id.get() if not sid: messagebox.showwarning("Select", "Please select a student to delete") return confirm = messagebox.askyesno("Confirm Delete", "Are you sure?") if confirm: con = get_connection() cur = con.cursor() cur.execute("DELETE FROM students WHERE id=%s", (sid,)) con.commit() con.close() fetch_students() clear_fields() messagebox.showinfo("Deleted", "Student record deleted!") # ---------- GUI ----------root = tk.Tk()root.title("Student Management - Lesson 6 (CRUD)")root.geometry("850x500") # Formform_frame = tk.Frame(root)form_frame.pack(side=tk.TOP, fill=tk.X, padx=10, pady=5) tk.Label(form_frame, text="ID").grid(row=0, column=0, padx=5, pady=5)entry_id = tk.Entry(form_frame)entry_id.grid(row=0, column=1, padx=5, pady=5) tk.Label(form_frame, text="Name").grid(row=0, column=2, padx=5, pady=5)entry_name = tk.Entry(form_frame)entry_name.grid(row=0, column=3, padx=5, pady=5) tk.Label(form_frame, text="Age").grid(row=0, column=4, padx=5, pady=5)entry_age = tk.Entry(form_frame)entry_age.grid(row=0, column=5, padx=5, pady=5) tk.Label(form_frame, text="Course").grid(row=1, column=0, padx=5, pady=5)entry_course = tk.Entry(form_frame)entry_course.grid(row=1, column=1, padx=5, pady=5) tk.Label(form_frame, text="Email").grid(row=1, column=2, padx=5, pady=5)entry_email = tk.Entry(form_frame)entry_email.grid(row=1, column=3, padx=5, pady=5) tk.Label(form_frame, text="Phone").grid(row=1, column=4, padx=5, pady=5)entry_phone = tk.Entry(form_frame)entry_phone.grid(row=1, column=5, padx=5, pady=5) btn_add = tk.Button(form_frame, text="Add", command=add_student)btn_add.grid(row=2, column=0, padx=5, pady=10) btn_update = tk.Button(form_frame, text="Update", command=update_student)btn_update.grid(row=2, column=1, padx=5, pady=10) btn_delete = tk.Button(form_frame, text="Delete", command=delete_student)btn_delete.grid(row=2, column=2, padx=5, pady=10) btn_clear = tk.Button(form_frame, text="Clear", command=clear_fields)btn_clear.grid(row=2, column=3, padx=5, pady=10) # Tabletree = ttk.Treeview(root, columns=("ID", "Name", "Age", "Course", "Email", "Phone"), show="headings")tree.heading("ID", text="ID")tree.heading("Name", text="Name")tree.heading("Age", text="Age")tree.heading("Course", text="Course")tree.heading("Email", text="Email")tree.heading("Phone", text="Phone")tree.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) tree.bind("<ButtonRelease-1>", select_student) # Load recordsfetch_students() root.mainloop()
📚 Concepts Covered
·
Tkinter Treeview
row selection with .bind("<ButtonRelease-1>",
...).
·
Filling Entry
fields with selected row.
·
SQL UPDATE
and DELETE queries.
·
Confirmation dialog with messagebox.askyesno().
📝 Lesson 7 – Search & Validation
🎯 Learning Goals
·
Add a search bar
and button in Tkinter.
·
Use LIKE
in SQL for flexible search.
·
Apply basic validation rules
before inserting/updating:
o
Name: not empty
o
Age: must be a number
o
Email: must contain @ and .
o
Phone: must be 10 digits
🔹 Full Code: lesson7/student_search_validate.py
import tkinter as tkfrom tkinter import ttk, messageboximport pymysqlimport re # for validation # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- Functions ----------def fetch_students(query=None, value=None): """Fetch and display students in the Treeview.""" for row in tree.get_children(): tree.delete(row) con = get_connection() cur = con.cursor() if query == "name": cur.execute("SELECT * FROM students WHERE name LIKE %s", (f"%{value}%",)) elif query == "course": cur.execute("SELECT * FROM students WHERE course LIKE %s", (f"%{value}%",)) else: cur.execute("SELECT * FROM students") rows = cur.fetchall() for row in rows: tree.insert("", tk.END, values=row) con.close() def validate_inputs(name, age, email, phone): """Validate form fields before DB insert/update.""" if not name.strip(): messagebox.showwarning("Validation Error", "Name is required") return False if not age.isdigit() or int(age) <= 0: messagebox.showwarning("Validation Error", "Age must be a positive number") return False if email and not re.match(r"[^@]+@[^@]+\.[^@]+", email): messagebox.showwarning("Validation Error", "Invalid email address") return False if phone and not re.match(r"^\d{10}$", phone): messagebox.showwarning("Validation Error", "Phone must be 10 digits") return False return True def add_student(): name = entry_name.get() age = entry_age.get() course = entry_course.get() email = entry_email.get() phone = entry_phone.get() if not validate_inputs(name, age, email, phone): return con = get_connection() cur = con.cursor() cur.execute( "INSERT INTO students (name, age, course, email, phone) VALUES (%s, %s, %s, %s, %s)", (name, age, course, email, phone) ) con.commit() con.close() fetch_students() clear_fields() messagebox.showinfo("Success", "Student Added!") def clear_fields(): entry_id.delete(0, tk.END) entry_name.delete(0, tk.END) entry_age.delete(0, tk.END) entry_course.delete(0, tk.END) entry_email.delete(0, tk.END) entry_phone.delete(0, tk.END) def select_student(event): selected = tree.focus() if not selected: return data = tree.item(selected)["values"] clear_fields() entry_id.insert(0, data[0]) entry_name.insert(0, data[1]) entry_age.insert(0, data[2]) entry_course.insert(0, data[3]) entry_email.insert(0, data[4]) entry_phone.insert(0, data[5]) def update_student(): sid = entry_id.get() if not sid: messagebox.showwarning("Select", "Please select a student to update") return if not validate_inputs(entry_name.get(), entry_age.get(), entry_email.get(), entry_phone.get()): return con = get_connection() cur = con.cursor() cur.execute( """UPDATE students SET name=%s, age=%s, course=%s, email=%s, phone=%s WHERE id=%s""", ( entry_name.get(), entry_age.get(), entry_course.get(), entry_email.get(), entry_phone.get(), sid, ), ) con.commit() con.close() fetch_students() clear_fields() messagebox.showinfo("Updated", "Student record updated successfully!") def delete_student(): sid = entry_id.get() if not sid: messagebox.showwarning("Select", "Please select a student to delete") return confirm = messagebox.askyesno("Confirm Delete", "Are you sure?") if confirm: con = get_connection() cur = con.cursor() cur.execute("DELETE FROM students WHERE id=%s", (sid,)) con.commit() con.close() fetch_students() clear_fields() messagebox.showinfo("Deleted", "Student record deleted!") def search_student(): search_type = search_option.get() search_value = entry_search.get() if not search_value.strip(): fetch_students() else: fetch_students(query=search_type, value=search_value) # ---------- GUI ----------root = tk.Tk()root.title("Student Management - Lesson 7 (Search & Validation)")root.geometry("950x550") # Formform_frame = tk.LabelFrame(root, text="Student Form", padx=10, pady=5)form_frame.pack(side=tk.TOP, fill=tk.X, padx=10, pady=5) tk.Label(form_frame, text="ID").grid(row=0, column=0, padx=5, pady=5)entry_id = tk.Entry(form_frame)entry_id.grid(row=0, column=1, padx=5, pady=5) tk.Label(form_frame, text="Name").grid(row=0, column=2, padx=5, pady=5)entry_name = tk.Entry(form_frame)entry_name.grid(row=0, column=3, padx=5, pady=5) tk.Label(form_frame, text="Age").grid(row=0, column=4, padx=5, pady=5)entry_age = tk.Entry(form_frame)entry_age.grid(row=0, column=5, padx=5, pady=5) tk.Label(form_frame, text="Course").grid(row=1, column=0, padx=5, pady=5)entry_course = tk.Entry(form_frame)entry_course.grid(row=1, column=1, padx=5, pady=5) tk.Label(form_frame, text="Email").grid(row=1, column=2, padx=5, pady=5)entry_email = tk.Entry(form_frame)entry_email.grid(row=1, column=3, padx=5, pady=5) tk.Label(form_frame, text="Phone").grid(row=1, column=4, padx=5, pady=5)entry_phone = tk.Entry(form_frame)entry_phone.grid(row=1, column=5, padx=5, pady=5) btn_add = tk.Button(form_frame, text="Add", command=add_student)btn_add.grid(row=2, column=0, padx=5, pady=10) btn_update = tk.Button(form_frame, text="Update", command=update_student)btn_update.grid(row=2, column=1, padx=5, pady=10) btn_delete = tk.Button(form_frame, text="Delete", command=delete_student)btn_delete.grid(row=2, column=2, padx=5, pady=10) btn_clear = tk.Button(form_frame, text="Clear", command=clear_fields)btn_clear.grid(row=2, column=3, padx=5, pady=10) # Search Framesearch_frame = tk.LabelFrame(root, text="Search", padx=10, pady=5)search_frame.pack(fill=tk.X, padx=10, pady=5) search_option = tk.StringVar(value="name")tk.Radiobutton(search_frame, text="By Name", variable=search_option, value="name").pack(side=tk.LEFT, padx=5)tk.Radiobutton(search_frame, text="By Course", variable=search_option, value="course").pack(side=tk.LEFT, padx=5) entry_search = tk.Entry(search_frame)entry_search.pack(side=tk.LEFT, padx=5) btn_search = tk.Button(search_frame, text="Search", command=search_student)btn_search.pack(side=tk.LEFT, padx=5) btn_show_all = tk.Button(search_frame, text="Show All", command=lambda: fetch_students())btn_show_all.pack(side=tk.LEFT, padx=5) # Tabletree = ttk.Treeview(root, columns=("ID", "Name", "Age", "Course", "Email", "Phone"), show="headings")tree.heading("ID", text="ID")tree.heading("Name", text="Name")tree.heading("Age", text="Age")tree.heading("Course", text="Course")tree.heading("Email", text="Email")tree.heading("Phone", text="Phone")tree.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) tree.bind("<ButtonRelease-1>", select_student) # Load recordsfetch_students() root.mainloop()
📚 Concepts Covered
·
SQL LIKE
query for search.
·
Tkinter Radiobuttons
for search type.
·
Input validation with re (regex).
·
Separation of logic (validate_inputs) to encourage clean
code.
📝 Lesson 8 – Export & Import Data (CSV)
🎯 Learning Goals
·
Export student records from MySQL into a CSV
file.
·
Import student records into MySQL from a CSV
file.
·
Use filedialog
to select file paths in Tkinter.
·
Apply error handling to avoid duplicate imports.
🔹 Full Code: lesson8/student_export_import.py
import tkinter as tkfrom tkinter import ttk, messagebox, filedialogimport pymysqlimport csvimport re # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- Functions ----------def fetch_students(): for row in tree.get_children(): tree.delete(row) con = get_connection() cur = con.cursor() cur.execute("SELECT * FROM students") rows = cur.fetchall() for row in rows: tree.insert("", tk.END, values=row) con.close() def validate_inputs(name, age, email, phone): if not name.strip(): messagebox.showwarning("Validation Error", "Name is required") return False if not age.isdigit() or int(age) <= 0: messagebox.showwarning("Validation Error", "Age must be a positive number") return False if email and not re.match(r"[^@]+@[^@]+\.[^@]+", email): messagebox.showwarning("Validation Error", "Invalid email address") return False if phone and not re.match(r"^\d{10}$", phone): messagebox.showwarning("Validation Error", "Phone must be 10 digits") return False return True def add_student(): name = entry_name.get() age = entry_age.get() course = entry_course.get() email = entry_email.get() phone = entry_phone.get() if not validate_inputs(name, age, email, phone): return con = get_connection() cur = con.cursor() cur.execute( "INSERT INTO students (name, age, course, email, phone) VALUES (%s, %s, %s, %s, %s)", (name, age, course, email, phone) ) con.commit() con.close() fetch_students() clear_fields() messagebox.showinfo("Success", "Student Added!") def clear_fields(): entry_id.delete(0, tk.END) entry_name.delete(0, tk.END) entry_age.delete(0, tk.END) entry_course.delete(0, tk.END) entry_email.delete(0, tk.END) entry_phone.delete(0, tk.END) def select_student(event): selected = tree.focus() if not selected: return data = tree.item(selected)["values"] clear_fields() entry_id.insert(0, data[0]) entry_name.insert(0, data[1]) entry_age.insert(0, data[2]) entry_course.insert(0, data[3]) entry_email.insert(0, data[4]) entry_phone.insert(0, data[5]) # ---------- Export / Import ----------def export_csv(): file_path = filedialog.asksaveasfilename(defaultextension=".csv", filetypes=[("CSV Files", "*.csv")]) if not file_path: return con = get_connection() cur = con.cursor() cur.execute("SELECT * FROM students") rows = cur.fetchall() con.close() try: with open(file_path, "w", newline="", encoding="utf-8") as f: writer = csv.writer(f) writer.writerow(["ID", "Name", "Age", "Course", "Email", "Phone", "Created_At"]) writer.writerows(rows) messagebox.showinfo("Export Successful", f"Data exported to {file_path}") except Exception as e: messagebox.showerror("Error", f"Failed to export: {str(e)}") def import_csv(): file_path = filedialog.askopenfilename(filetypes=[("CSV Files", "*.csv")]) if not file_path: return try: with open(file_path, "r", encoding="utf-8") as f: reader = csv.DictReader(f) con = get_connection() cur = con.cursor() for row in reader: # Avoid duplicate email import cur.execute("SELECT * FROM students WHERE email=%s", (row["Email"],)) if cur.fetchone(): continue cur.execute( "INSERT INTO students (name, age, course, email, phone) VALUES (%s, %s, %s, %s, %s)", (row["Name"], row["Age"], row["Course"], row["Email"], row["Phone"]) ) con.commit() con.close() fetch_students() messagebox.showinfo("Import Successful", f"Data imported from {file_path}") except Exception as e: messagebox.showerror("Error", f"Failed to import: {str(e)}") # ---------- GUI ----------root = tk.Tk()root.title("Student Management - Lesson 8 (Export & Import CSV)")root.geometry("1000x600") # Formform_frame = tk.LabelFrame(root, text="Student Form", padx=10, pady=5)form_frame.pack(side=tk.TOP, fill=tk.X, padx=10, pady=5) tk.Label(form_frame, text="ID").grid(row=0, column=0, padx=5, pady=5)entry_id = tk.Entry(form_frame)entry_id.grid(row=0, column=1, padx=5, pady=5) tk.Label(form_frame, text="Name").grid(row=0, column=2, padx=5, pady=5)entry_name = tk.Entry(form_frame)entry_name.grid(row=0, column=3, padx=5, pady=5) tk.Label(form_frame, text="Age").grid(row=0, column=4, padx=5, pady=5)entry_age = tk.Entry(form_frame)entry_age.grid(row=0, column=5, padx=5, pady=5) tk.Label(form_frame, text="Course").grid(row=1, column=0, padx=5, pady=5)entry_course = tk.Entry(form_frame)entry_course.grid(row=1, column=1, padx=5, pady=5) tk.Label(form_frame, text="Email").grid(row=1, column=2, padx=5, pady=5)entry_email = tk.Entry(form_frame)entry_email.grid(row=1, column=3, padx=5, pady=5) tk.Label(form_frame, text="Phone").grid(row=1, column=4, padx=5, pady=5)entry_phone = tk.Entry(form_frame)entry_phone.grid(row=1, column=5, padx=5, pady=5) btn_add = tk.Button(form_frame, text="Add", command=add_student)btn_add.grid(row=2, column=0, padx=5, pady=10) btn_clear = tk.Button(form_frame, text="Clear", command=clear_fields)btn_clear.grid(row=2, column=1, padx=5, pady=10) btn_export = tk.Button(form_frame, text="Export CSV", command=export_csv, bg="lightgreen")btn_export.grid(row=2, column=2, padx=5, pady=10) btn_import = tk.Button(form_frame, text="Import CSV", command=import_csv, bg="lightblue")btn_import.grid(row=2, column=3, padx=5, pady=10) # Tabletree = ttk.Treeview(root, columns=("ID", "Name", "Age", "Course", "Email", "Phone", "Created_At"), show="headings")for col in ("ID", "Name", "Age", "Course", "Email", "Phone", "Created_At"): tree.heading(col, text=col)tree.pack(fill=tk.BOTH, expand=True, padx=10, pady=5) tree.bind("<ButtonRelease-1>", select_student) # Load recordsfetch_students() root.mainloop()
📚 Concepts Covered
·
File handling (csv
module).
·
Tkinter filedialog
for open/save dialogs.
·
Export with column headers.
·
Import with duplicate check
(based on email).
📝 Lesson 9 – Dashboard & Charts
🎯 Learning Goals
·
Create a dashboard window
to show key stats:
o
Total students
o
Students by course (bar chart)
o
Students by age group (pie chart)
·
Use matplotlib
inside Tkinter with FigureCanvasTkAgg.
·
Learn how to fetch summary data from SQL using GROUP BY.
🔹 Full Code: lesson9/student_dashboard.py
import tkinter as tkfrom tkinter import ttkimport pymysqlfrom matplotlib.figure import Figurefrom matplotlib.backends.backend_tkagg import FigureCanvasTkAgg # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- Functions ----------def get_total_students(): con = get_connection() cur = con.cursor() cur.execute("SELECT COUNT(*) FROM students") total = cur.fetchone()[0] con.close() return total def get_students_by_course(): con = get_connection() cur = con.cursor() cur.execute("SELECT course, COUNT(*) FROM students GROUP BY course") data = cur.fetchall() con.close() return data def get_students_by_age_group(): """Group students by age ranges: <18, 18-25, >25""" con = get_connection() cur = con.cursor() cur.execute(""" SELECT CASE WHEN age < 18 THEN '<18' WHEN age BETWEEN 18 AND 25 THEN '18-25' ELSE '>25' END AS age_group, COUNT(*) FROM students GROUP BY age_group """) data = cur.fetchall() con.close() return data # ---------- GUI ----------root = tk.Tk()root.title("Student Management - Lesson 9 (Dashboard & Reports)")root.geometry("1000x700") # Dashboard Frameframe = tk.Frame(root, padx=20, pady=20)frame.pack(fill=tk.BOTH, expand=True) # Total Students Labeltotal_students = get_total_students()lbl_total = tk.Label(frame, text=f"📊 Total Students: {total_students}", font=("Arial", 16, "bold"))
lbl_total.pack(pady=10) # ---- Chart 1: Students by Course (Bar Chart) ----course_data = get_students_by_course()courses = [row[0] for row in course_data]counts = [row[1] for row in course_data] fig1 = Figure(figsize=(4.5, 3), dpi=100)ax1 = fig1.add_subplot(111)ax1.bar(courses, counts, color="skyblue")ax1.set_title("Students by Course")ax1.set_ylabel("Count") canvas1 = FigureCanvasTkAgg(fig1, master=frame)canvas1.draw()canvas1.get_tk_widget().pack(side=tk.LEFT, padx=10, pady=10) # ---- Chart 2: Students by Age Group (Pie Chart) ----age_data = get_students_by_age_group()age_groups = [row[0] for row in age_data]age_counts = [row[1] for row in age_data] fig2 = Figure(figsize=(4.5, 3), dpi=100)ax2 = fig2.add_subplot(111)ax2.pie(age_counts, labels=age_groups, autopct="%1.1f%%", startangle=90)ax2.set_title("Students by Age Group") canvas2 = FigureCanvasTkAgg(fig2, master=frame)canvas2.draw()canvas2.get_tk_widget().pack(side=tk.RIGHT, padx=10, pady=10) root.mainloop()
📚 Concepts Covered
·
GROUP BY
queries in MySQL.
·
Using matplotlib
to plot bar and pie charts.
·
Embedding matplotlib figures in Tkinter with FigureCanvasTkAgg.
·
Building a dashboard view
for reports.
📝 Lesson 10 – Final Project
🎯 Learning Goals
·
Add Login System
(Admin / User).
·
Implement multi-screen navigation:
o
Login screen
o
Student Management (CRUD) screen
o
Dashboard (Reports) screen
·
Use tkinter.ttk.Notebook
(tabs) OR side menu for navigation.
·
Secure passwords with hashing
(SHA256).
🔹 Full Code: lesson10/final_project.py
import tkinter as tkfrom tkinter import ttk, messageboximport pymysqlimport hashlibfrom matplotlib.figure import Figurefrom matplotlib.backends.backend_tkagg import FigureCanvasTkAgg # ---------- Database Connection ----------def get_connection(): return pymysql.connect( host="localhost", user="root", # change if needed password="", # your MySQL password database="student_db" ) # ---------- User Authentication ----------def create_users_table(): con = get_connection() cur = con.cursor() cur.execute(""" CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) UNIQUE, password_hash VARCHAR(256), role VARCHAR(20) DEFAULT 'user' ) """) # Insert default admin if not exists cur.execute("SELECT * FROM users WHERE username='admin'") if not cur.fetchone(): password_hash = hashlib.sha256("admin123".encode()).hexdigest() cur.execute("INSERT INTO users (username, password_hash, role) VALUES (%s, %s, %s)", ("admin", password_hash, "admin")) con.commit() con.close() def check_login(username, password): con = get_connection() cur = con.cursor() cur.execute("SELECT password_hash, role FROM users WHERE username=%s", (username,)) row = cur.fetchone() con.close() if row: stored_hash, role = row if hashlib.sha256(password.encode()).hexdigest() == stored_hash: return True, role return False, None # ---------- CRUD for Students ----------def create_student_table(): con = get_connection() cur = con.cursor() cur.execute(""" CREATE TABLE IF NOT EXISTS students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT CHECK (age >= 0), course VARCHAR(100), email VARCHAR(100), phone VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) """) con.commit() con.close() def add_student(name, age, course, email, phone): con = get_connection() cur = con.cursor() cur.execute("INSERT INTO students (name, age, course, email, phone) VALUES (%s, %s, %s, %s, %s)", (name, age, course, email, phone)) con.commit() con.close() def get_all_students(): con = get_connection() cur = con.cursor() cur.execute("SELECT * FROM students") rows = cur.fetchall() con.close() return rows # ---------- Dashboard Functions ----------def get_total_students(): con = get_connection() cur = con.cursor() cur.execute("SELECT COUNT(*) FROM students") total = cur.fetchone()[0] con.close() return total def get_students_by_course(): con = get_connection() cur = con.cursor() cur.execute("SELECT course, COUNT(*) FROM students GROUP BY course") data = cur.fetchall() con.close() return data # ---------- GUI ----------class StudentApp: def __init__(self, root): self.root = root self.root.title("Student Management System") self.root.geometry("1100x700") # First screen: Login self.login_screen() def login_screen(self): self.clear_root() frame = tk.Frame(self.root, padx=30, pady=30) frame.pack(expand=True) tk.Label(frame, text="🔑Login", font=("Arial", 20, "bold")).pack(pady=10)
tk.Label(frame, text="Username:").pack() self.username_entry = tk.Entry(frame) self.username_entry.pack(pady=5) tk.Label(frame, text="Password:").pack() self.password_entry = tk.Entry(frame, show="*") self.password_entry.pack(pady=5) tk.Button(frame, text="Login", command=self.do_login).pack(pady=10) def do_login(self): username = self.username_entry.get() password = self.password_entry.get() success, role = check_login(username, password) if success: messagebox.showinfo("Success", f"Welcome {username} ({role})!") self.main_screen(role) else: messagebox.showerror("Error", "Invalid username or password") def main_screen(self, role): self.clear_root() # Tab navigation notebook = ttk.Notebook(self.root) notebook.pack(fill=tk.BOTH, expand=True) # Tab 1: Student Management student_frame = tk.Frame(notebook, padx=10, pady=10) notebook.add(student_frame, text="Students") self.build_student_tab(student_frame) # Tab 2: Dashboard dashboard_frame = tk.Frame(notebook, padx=10, pady=10) notebook.add(dashboard_frame, text="Dashboard") self.build_dashboard_tab(dashboard_frame) def build_student_tab(self, frame):tk.Label(frame, text="👩🎓Student Management", font=("Arial", 16, "bold")).pack(pady=10)
# Form form_frame = tk.Frame(frame) form_frame.pack(pady=10) tk.Label(form_frame, text="Name:").grid(row=0, column=0, padx=5, pady=5) self.name_entry = tk.Entry(form_frame) self.name_entry.grid(row=0, column=1) tk.Label(form_frame, text="Age:").grid(row=1, column=0, padx=5, pady=5) self.age_entry = tk.Entry(form_frame) self.age_entry.grid(row=1, column=1) tk.Label(form_frame, text="Course:").grid(row=2, column=0, padx=5, pady=5) self.course_entry = tk.Entry(form_frame) self.course_entry.grid(row=2, column=1) tk.Label(form_frame, text="Email:").grid(row=3, column=0, padx=5, pady=5) self.email_entry = tk.Entry(form_frame) self.email_entry.grid(row=3, column=1) tk.Label(form_frame, text="Phone:").grid(row=4, column=0, padx=5, pady=5) self.phone_entry = tk.Entry(form_frame) self.phone_entry.grid(row=4, column=1) tk.Button(form_frame, text="Add Student", command=self.add_student_action).grid(row=5, columnspan=2, pady=10) # Student Table self.tree = ttk.Treeview(frame, columns=("ID", "Name", "Age", "Course", "Email", "Phone"), show="headings") for col in self.tree["columns"]: self.tree.heading(col, text=col) self.tree.pack(fill=tk.BOTH, expand=True, pady=10) self.load_students() def add_student_action(self): name = self.name_entry.get() age = self.age_entry.get() course = self.course_entry.get() email = self.email_entry.get() phone = self.phone_entry.get() if not name or not age: messagebox.showerror("Error", "Name and Age are required") return add_student(name, int(age), course, email, phone) messagebox.showinfo("Success", "Student added successfully") self.load_students() def load_students(self): for row in self.tree.get_children(): self.tree.delete(row) rows = get_all_students() for r in rows: self.tree.insert("", tk.END, values=r) def build_dashboard_tab(self, frame):tk.Label(frame, text="📊Dashboard", font=("Arial", 16, "bold")).pack(pady=10)
total = get_total_students() tk.Label(frame, text=f"Total Students: {total}", font=("Arial", 14)).pack(pady=10) # Students by Course Chart data = get_students_by_course() courses = [row[0] for row in data] counts = [row[1] for row in data] fig = Figure(figsize=(5, 4), dpi=100) ax = fig.add_subplot(111) ax.bar(courses, counts, color="lightblue") ax.set_title("Students by Course") canvas = FigureCanvasTkAgg(fig, master=frame) canvas.draw() canvas.get_tk_widget().pack() def clear_root(self): for widget in self.root.winfo_children(): widget.destroy() # ---------- Run ----------if __name__ == "__main__": create_users_table() create_student_table() root = tk.Tk() app = StudentApp(root) root.mainloop()
📚 Concepts Covered
·
Login authentication
with hashed passwords.
·
Role-based access
(Admin/User).
·
Multi-screen app
with Notebook tabs.
·
Integrated CRUD + Dashboard +
Login into one full project.
International finance requires a solid understanding of exchange rates, global markets, and financial management concepts. Many learners benefit from international finance assignment answer support to understand difficult topics through structured explanations. This guidance encourages better learning, improves assignment quality, and helps students apply theoretical concepts to practical business situations. It also reduces academic pressure during busy semesters.
ReplyDelete