# ----------------------------------------------
# School Chemistry Lab Inventory System
# CBSE Grade 12 Project (Tkinter + MySQL)
# ----------------------------------------------
import tkinter as tk
from tkinter import messagebox
import mysql.connector
from datetime import date
from tkinter import font
# -------- DATABASE CONNECTION --------
def connect_db():
"""Connect to MySQL and create the lab database + tables"""
con = mysql.connector.connect(host="localhost", user="root", password="")
cur = con.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS lab_inventory")
cur.execute("USE lab_inventory")
# Create tables if not already present
cur.execute("""
CREATE TABLE IF NOT EXISTS chemicals(
chem_id INT PRIMARY KEY,
name VARCHAR(50),
expiry DATE,
quantity INT
)
""")
cur.execute("""
CREATE TABLE IF NOT EXISTS equipment(
equip_id INT PRIMARY KEY,
name VARCHAR(50),
expiry DATE,
quantity INT
)
""")
con.commit()
return con, cur
con, cur = connect_db()
# -------- HELPER FUNCTIONS --------
def clear_frame():
"""Remove all widgets from the input frame"""
for widget in frm_input.winfo_children():
widget.destroy()
def clear_text():
"""Clear the output text area"""
text_box.delete(1.0, tk.END)
def show_text(lines):
"""Show formatted text in the output box"""
clear_text()
for line in lines:
text_box.insert(tk.END, line + "\n")
text_box.see(tk.END)
# bring output box to focus again
frm_input.pack_forget()
text_box.pack(fill="both", expand=True, padx=15, pady=15)
def return_to_output():
"""Show output area again"""
frm_input.pack_forget()
text_box.pack(fill="both", expand=True, padx=15, pady=15)
# -------- MAIN FUNCTIONS --------
def menu_add_chemical():
"""Add new chemical"""
clear_frame()
text_box.pack_forget()
tk.Label(frm_input, text="Add New Chemical", fg="white", bg="#202020",
font=("Arial", 14, "bold")).grid(row=0, column=0, columnspan=2, pady=10)
tk.Label(frm_input, text="Chemical ID:", fg="white", bg="#202020").grid(row=1, column=0, pady=5)
tk.Label(frm_input, text="Name:", fg="white", bg="#202020").grid(row=2, column=0, pady=5)
tk.Label(frm_input, text="Expiry (YYYY-MM-DD):", fg="white", bg="#202020").grid(row=3, column=0, pady=5)
tk.Label(frm_input, text="Quantity:", fg="white", bg="#202020").grid(row=4, column=0, pady=5)
custom_font = font.Font(family="Arial", size=30)
id_entry = tk.Entry(frm_input, width=35,font=custom_font)
name_entry = tk.Entry(frm_input, width=35,font=custom_font)
exp_entry = tk.Entry(frm_input, width=35,font=custom_font)
qty_entry = tk.Entry(frm_input, width=35,font=custom_font)
id_entry.grid(row=1, column=1, pady=5)
name_entry.grid(row=2, column=1, pady=5)
exp_entry.grid(row=3, column=1, pady=5)
qty_entry.grid(row=4, column=1, pady=5)
def submit():
try:
cid = int(id_entry.get())
name = name_entry.get()
exp = exp_entry.get()
qty = int(qty_entry.get())
cur.execute("SELECT * FROM chemicals WHERE chem_id=%s", (cid,))
if cur.fetchone():
messagebox.showwarning("Duplicate", "Chemical ID already exists!")
return
cur.execute("INSERT INTO chemicals VALUES(%s,%s,%s,%s)", (cid, name, exp, qty))
con.commit()
messagebox.showinfo("Success", "Chemical added successfully!")
return_to_output()
except Exception as e:
messagebox.showerror("Error", str(e))
tk.Button(frm_input, text="Submit", command=submit,
bg="#555", fg="white", font=("Arial", 11, "bold"), width=20).grid(row=5, column=0, columnspan=2, pady=15)
frm_input.pack(pady=20)
def menu_delete_chemical():
"""Delete a chemical by ID"""
clear_frame()
text_box.pack_forget()
tk.Label(frm_input, text="Delete Chemical", fg="white", bg="#202020",
font=("Arial", 14, "bold")).grid(row=0, column=0, columnspan=2, pady=10)
tk.Label(frm_input, text="Enter Chemical ID to delete:", fg="white", bg="#202020").grid(row=1, column=0, pady=5)
id_entry = tk.Entry(frm_input, width=25)
id_entry.grid(row=1, column=1, pady=5)
def delete():
try:
cid = int(id_entry.get())
cur.execute("SELECT * FROM chemicals WHERE chem_id=%s", (cid,))
if not cur.fetchone():
messagebox.showwarning("Not Found", "Chemical not found!")
return
cur.execute("DELETE FROM chemicals WHERE chem_id=%s", (cid,))
con.commit()
messagebox.showinfo("Deleted", "Chemical deleted successfully!")
return_to_output()
except Exception as e:
messagebox.showerror("Error", str(e))
tk.Button(frm_input, text="Delete", command=delete, bg="#a33", fg="white",
font=("Arial", 11, "bold"), width=20).grid(row=2, column=0, columnspan=2, pady=10)
frm_input.pack(pady=20)
def menu_delete_equipment():
"""Delete equipment by ID"""
clear_frame()
text_box.pack_forget()
tk.Label(frm_input, text="Delete Equipment", fg="white", bg="#202020",
font=("Arial", 14, "bold")).grid(row=0, column=0, columnspan=2, pady=10)
tk.Label(frm_input, text="Enter Equipment ID to delete:", fg="white", bg="#202020").grid(row=1, column=0, pady=5)
id_entry = tk.Entry(frm_input, width=25)
id_entry.grid(row=1, column=1, pady=5)
def delete():
try:
eid = int(id_entry.get())
cur.execute("SELECT * FROM equipment WHERE equip_id=%s", (eid,))
if not cur.fetchone():
messagebox.showwarning("Not Found", "Equipment not found!")
return
cur.execute("DELETE FROM equipment WHERE equip_id=%s", (eid,))
con.commit()
messagebox.showinfo("Deleted", "Equipment deleted successfully!")
return_to_output()
except Exception as e:
messagebox.showerror("Error", str(e))
tk.Button(frm_input, text="Delete", command=delete, bg="#a33", fg="white",
font=("Arial", 11, "bold"), width=20).grid(row=2, column=0, columnspan=2, pady=10)
frm_input.pack(pady=10)
def menu_add_equipment():
"""Add new equipment"""
clear_frame()
text_box.pack_forget()
tk.Label(frm_input, text="Add New Equipment", fg="white", bg="#202020",
font=("Arial", 14, "bold")).grid(row=0, column=0, columnspan=2, pady=10)
tk.Label(frm_input, text="Equipment ID:", fg="white", bg="#202020").grid(row=1, column=0, pady=5)
tk.Label(frm_input, text="Name:", fg="white", bg="#202020").grid(row=2, column=0, pady=5)
tk.Label(frm_input, text="Expiry (YYYY-MM-DD):", fg="white", bg="#202020").grid(row=3, column=0, pady=5)
tk.Label(frm_input, text="Quantity:", fg="white", bg="#202020").grid(row=4, column=0, pady=5)
custom_font = font.Font(family="Arial", size=30)
id_entry = tk.Entry(frm_input, width=25,font=custom_font)
name_entry = tk.Entry(frm_input, width=25,font=custom_font)
exp_entry = tk.Entry(frm_input, width=25,font=custom_font)
qty_entry = tk.Entry(frm_input, width=25,font=custom_font)
id_entry.grid(row=1, column=1, pady=5)
name_entry.grid(row=2, column=1, pady=5)
exp_entry.grid(row=3, column=1, pady=5)
qty_entry.grid(row=4, column=1, pady=5)
def submit():
try:
eid = int(id_entry.get())
name = name_entry.get()
exp = exp_entry.get()
qty = int(qty_entry.get())
cur.execute("SELECT * FROM equipment WHERE equip_id=%s", (eid,))
if cur.fetchone():
messagebox.showwarning("Duplicate", "Equipment ID already exists!")
return
cur.execute("INSERT INTO equipment VALUES(%s,%s,%s,%s)", (eid, name, exp, qty))
con.commit()
messagebox.showinfo("Success", "Equipment added successfully!")
return_to_output()
except Exception as e:
messagebox.showerror("Error", str(e))
tk.Button(frm_input, text="Submit", command=submit,
bg="#555", fg="white", font=("Arial", 11, "bold"), width=20).grid(row=5, column=0, columnspan=2, pady=15)
frm_input.pack(pady=20)
def menu_view_chemicals():
"""Display all chemicals"""
clear_frame()
cur.execute("SELECT * FROM chemicals")
rows = cur.fetchall()
data = ["CHEMICALS IN LAB:\n", "ID | NAME | EXPIRY | QTY", "-"*45]
for row in rows:
data.append(f"{row[0]} | {row[1]} | {row[2]} | {row[3]}")
show_text(data)
def menu_view_equipment():
"""Display all equipment"""
clear_frame()
cur.execute("SELECT * FROM equipment")
rows = cur.fetchall()
data = ["EQUIPMENT IN LAB:\n", "ID | NAME | EXPIRY | QTY", "-"*45]
for row in rows:
data.append(f"{row[0]} | {row[1]} | {row[2]} | {row[3]}")
show_text(data)
def menu_expiry_alerts():
"""Show expired items"""
clear_frame()
cur.execute("SELECT name, expiry FROM chemicals WHERE expiry < CURDATE()")
chem = cur.fetchall()
cur.execute("SELECT name, expiry FROM equipment WHERE expiry < CURDATE()")
equip = cur.fetchall()
data = ["EXPIRED ITEMS:\n"]
if not chem and not equip:
data.append("✅ No expired items found.")
else:
if chem:
data.append("Chemicals:")
for c in chem:
data.append(f"⚠ {c[0]} (Expired on {c[1]})")
if equip:
data.append("\nEquipment:")
for e in equip:
data.append(f"⚠ {e[0]} (Expired on {e[1]})")
show_text(data)
def menu_restock_alerts():
"""Show low stock items"""
clear_frame()
cur.execute("SELECT name, quantity FROM chemicals WHERE quantity < 2")
chem = cur.fetchall()
cur.execute("SELECT name, quantity FROM equipment WHERE quantity < 2")
equip = cur.fetchall()
data = ["LOW STOCK ITEMS:\n"]
if not chem and not equip:
data.append("✅ All items sufficiently stocked.")
else:
if chem:
data.append("Chemicals:")
for c in chem:
data.append(f"🔸 {c[0]} (Qty: {c[1]})")
if equip:
data.append("\nEquipment:")
for e in equip:
data.append(f"🔸 {e[0]} (Qty: {e[1]})")
show_text(data)
# -------- GUI DESIGN --------
root = tk.Tk()
root.title("School Chemistry Lab Inventory System")
root.geometry("1920x1080")
root.config(bg="#202020")
# Title
tk.Label(root, text="SCHOOL CHEMISTRY LAB INVENTORY SYSTEM",
fg="white", bg="#202020", font=("Arial", 20, "bold")).pack(pady=15)
# Menu Buttons
frm_menu = tk.Frame(root, bg="#202020")
frm_menu.pack(pady=10)
btn_font = ("Arial", 12, "bold")
btn_style = {"bg": "#333", "fg": "white", "font": btn_font, "width": 20, "height": 5}
tk.Button(frm_menu, text="Add Chemical", command=menu_add_chemical, **btn_style).grid(row=0, column=0, padx=15, pady=8)
tk.Button(frm_menu, text="View Chemicals", command=menu_view_chemicals, **btn_style).grid(row=0, column=1, padx=15, pady=8)
tk.Button(frm_menu, text="Add Equipment", command=menu_add_equipment, **btn_style).grid(row=1, column=0, padx=15, pady=8)
tk.Button(frm_menu, text="View Equipment", command=menu_view_equipment, **btn_style).grid(row=1, column=1, padx=15, pady=8)
tk.Button(frm_menu, text="Expiry Alerts", command=menu_expiry_alerts, **btn_style).grid(row=2, column=0, padx=15, pady=8)
tk.Button(frm_menu, text="Restock Alerts", command=menu_restock_alerts, **btn_style).grid(row=2, column=1, padx=15, pady=8)
tk.Button(frm_menu, text="Delete Chemical", command=menu_delete_chemical, **btn_style).grid(row=0, column=2, padx=15, pady=5)
tk.Button(frm_menu, text="Delete Equipment", command=menu_delete_equipment, **btn_style).grid(row=1, column=2, padx=15, pady=5)
tk.Button(frm_menu, text="Exit", command=root.destroy,**btn_style).grid(row=2,column=2,padx=15,pady=8)
# Frame for temporary input forms
frm_input = tk.Frame(root, bg="#202020")
# Frame for text box + scrollbar
frm_text = tk.Frame(root, bg="#202020")
frm_text.pack(fill="both", expand=True, padx=15, pady=15)
# Text box
# --- Output text area and scrollbar setup ---
text_frame = tk.Frame(root, bg="#202020")
text_frame.pack(fill="both", expand=True, padx=15, pady=15)
scrollbar = tk.Scrollbar(text_frame)
scrollbar.pack(side="right", fill="y")
text_box = tk.Text(
text_frame, width=110, height=20,
bg="#111", fg="white", font=("Consolas", 20),
yscrollcommand=scrollbar.set
)
text_box.pack(side="left", fill="both", expand=True)
scrollbar.config(command=text_box.yview)
# Exit Button
root.mainloop()