Inventory Management System
# ============================================================
# INVENTORY MANAGEMENT SYSTEM
# CBSE Class 12 Computer Science Investigatory Project
# Retail / Shop Context
#
# Topics Covered:
# - Python–MySQL Connectivity
# - CRUD Operations (Create, Read, Update, Delete)
# - Aggregate Functions (SUM, AVG, MAX, MIN, COUNT)
# - Multi-table Queries & Joins
# - Functions, Dictionaries, Lists
# ============================================================
#
# REQUIREMENTS:
# pip install mysql-connector-python
#
# MYSQL SETUP:
# CREATE DATABASE inventory_db;
# (Tables are auto-created on first run)
#
# HOW TO RUN:
# python inventory_management.py
# ============================================================
import mysql.connector
from datetime import datetime, date
# ─────────────────────────────────────────────
# DATABASE CONNECTION
# ─────────────────────────────────────────────
def connect_db():
"""Establish connection to MySQL database."""
try:
conn = mysql.connector.connect(
host="localhost",
user="root",
password="admin" # Change your password
)
cursor = conn.cursor()
# Create database if it does not exist
cursor.execute("CREATE DATABASE IF NOT EXISTS inventory_db")
# Select database
cursor.execute("USE inventory_db")
cursor.close()
return conn
except mysql.connector.Error as e:
print(f"\n [ERROR] Cannot connect to MySQL: {e}")
print(" Make sure MySQL is running and credentials are correct.\n")
exit()
# ─────────────────────────────────────────────
# DATABASE INITIALISATION
# ─────────────────────────────────────────────
def initialize_database():
conn = connect_db()
cursor = conn.cursor()
# ── Categories ──
cursor.execute("""
CREATE TABLE IF NOT EXISTS categories (
cat_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE,
description TEXT
)
""")
# ── Suppliers ──
cursor.execute("""
CREATE TABLE IF NOT EXISTS suppliers (
sup_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
contact VARCHAR(15),
email VARCHAR(100),
address TEXT,
city VARCHAR(60),
status VARCHAR(20) DEFAULT 'Active'
)
""")
# ── Products ──
cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_code VARCHAR(30) NOT NULL UNIQUE,
name VARCHAR(120) NOT NULL,
cat_id INT,
sup_id INT,
unit VARCHAR(20) DEFAULT 'pcs',
cost_price DECIMAL(10,2) DEFAULT 0,
sell_price DECIMAL(10,2) DEFAULT 0,
quantity INT DEFAULT 0,
reorder_level INT DEFAULT 10,
location VARCHAR(50),
status VARCHAR(20) DEFAULT 'Active',
added_date DATE,
FOREIGN KEY (cat_id) REFERENCES categories(cat_id),
FOREIGN KEY (sup_id) REFERENCES suppliers(sup_id)
)
""")
# ── Stock-In (Purchase) ──
cursor.execute("""
CREATE TABLE IF NOT EXISTS stock_in (
txn_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
product_name VARCHAR(120),
quantity INT NOT NULL,
cost_price DECIMAL(10,2),
total_cost DECIMAL(12,2),
sup_id INT,
invoice_no VARCHAR(40),
txn_date DATE,
remarks TEXT,
FOREIGN KEY (product_id) REFERENCES products(product_id)
)
""")
# ── Stock-Out (Sales / Issue) ──
cursor.execute("""
CREATE TABLE IF NOT EXISTS stock_out (
txn_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
product_name VARCHAR(120),
quantity INT NOT NULL,
sell_price DECIMAL(10,2),
total_amount DECIMAL(12,2),
customer VARCHAR(100),
bill_no VARCHAR(40),
txn_date DATE,
remarks TEXT,
FOREIGN KEY (product_id) REFERENCES products(product_id)
)
""")
# ── Seed default categories ──
seed_cats = [
("Electronics", "Mobile phones, laptops, accessories"),
("Clothing", "Shirts, pants, footwear"),
("Groceries", "Food, beverages, daily needs"),
("Stationery", "Notebooks, pens, office supplies"),
("Furniture", "Tables, chairs, shelves"),
("Health & Beauty", "Medicines, cosmetics"),
]
for cat, desc in seed_cats:
try:
cursor.execute(
"INSERT INTO categories (name, description) VALUES (%s, %s)",
(cat, desc)
)
except mysql.connector.IntegrityError:
pass # already exists
conn.commit()
cursor.close()
conn.close()
print(" [✓] Database ready.")
# ─────────────────────────────────────────────
# HELPERS
# ─────────────────────────────────────────────
LINE = "=" * 72
LINE2 = "-" * 72
def print_header(title):
print("\n" + LINE)
print(f" {title.upper()}")
print(LINE)
def pause():
input("\n Press Enter to continue...")
def pick_from_query(cursor, query, params=(), prompt="Select"):
"""Run a query, show numbered rows, return chosen row as dict."""
cursor.execute(query, params)
rows = cursor.fetchall()
cols = [d[0] for d in cursor.description]
if not rows:
print(" [!] No records found.")
return None
print(f"\n {prompt}:")
for i, row in enumerate(rows, 1):
print(f" {i:>3}. " + " | ".join(str(v) for v in row))
while True:
try:
n = int(input(" Enter number (0 to cancel): ").strip())
if n == 0:
return None
if 1 <= n <= len(rows):
return dict(zip(cols, rows[n - 1]))
except ValueError:
pass
print(f" [!] Enter 1–{len(rows)} or 0.")
def auto_bill():
return f"BILL{datetime.now().strftime('%Y%m%d%H%M%S')}"
# ─────────────────────────────────────────────
# MODULE 1 — CATEGORY MANAGEMENT
# ─────────────────────────────────────────────
def category_menu():
while True:
print_header("Category Management")
print(" 1. Add Category")
print(" 2. View All Categories")
print(" 3. Update Category")
print(" 4. Delete Category")
print(" 0. Back")
print(LINE)
ch = input(" Choice: ").strip()
if ch == "1": add_category()
elif ch == "2": view_categories()
elif ch == "3": update_category()
elif ch == "4": delete_category()
elif ch == "0": break
else: print(" [!] Invalid choice.")
def add_category():
print_header("Add Category")
name = input(" Category Name : ").strip()
desc = input(" Description : ").strip()
conn = connect_db(); cursor = conn.cursor()
try:
cursor.execute(
"INSERT INTO categories (name, description) VALUES (%s, %s)",
(name, desc)
)
conn.commit()
print(f" ✓ Category '{name}' added.")
except mysql.connector.IntegrityError:
print(f" [!] Category '{name}' already exists.")
cursor.close(); conn.close(); pause()
def view_categories():
print_header("All Categories")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT c.cat_id, c.name, c.description,
COUNT(p.product_id) AS products
FROM categories c
LEFT JOIN products p ON c.cat_id = p.cat_id
GROUP BY c.cat_id ORDER BY c.name
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" [!] No categories found.")
else:
print(f"\n {'ID':<5} {'Name':<22} {'Products':>9} Description")
print(" " + LINE2)
for r in rows:
desc = (str(r[2]) or "")[:35]
print(f" {r[0]:<5} {r[1]:<22} {r[3]:>9} {desc}")
pause()
def update_category():
print_header("Update Category")
conn = connect_db(); cursor = conn.cursor()
choice = pick_from_query(
cursor,
"SELECT cat_id, name FROM categories ORDER BY name",
prompt="Select category to update"
)
if not choice:
cursor.close(); conn.close(); return
new_name = input(f" New name [{choice['name']}]: ").strip() or choice['name']
new_desc = input(" New description : ").strip()
cursor.execute(
"UPDATE categories SET name=%s, description=%s WHERE cat_id=%s",
(new_name, new_desc, choice['cat_id'])
)
conn.commit()
print(" ✓ Category updated.")
cursor.close(); conn.close(); pause()
def delete_category():
print_header("Delete Category")
conn = connect_db(); cursor = conn.cursor()
choice = pick_from_query(
cursor,
"SELECT cat_id, name FROM categories ORDER BY name",
prompt="Select category to delete"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("SELECT COUNT(*) FROM products WHERE cat_id=%s", (choice['cat_id'],))
cnt = cursor.fetchone()[0]
if cnt > 0:
print(f" [!] Cannot delete — {cnt} product(s) linked to this category.")
cursor.close(); conn.close(); pause(); return
confirm = input(f" Delete '{choice['name']}'? (yes/no): ").lower()
if confirm == "yes":
cursor.execute("DELETE FROM categories WHERE cat_id=%s", (choice['cat_id'],))
conn.commit()
print(" ✓ Category deleted.")
cursor.close(); conn.close(); pause()
# ─────────────────────────────────────────────
# MODULE 2 — SUPPLIER MANAGEMENT
# ─────────────────────────────────────────────
def supplier_menu():
while True:
print_header("Supplier Management")
print(" 1. Add Supplier")
print(" 2. View All Suppliers")
print(" 3. View Supplier Details")
print(" 4. Update Supplier")
print(" 5. Delete Supplier")
print(" 0. Back")
print(LINE)
ch = input(" Choice: ").strip()
if ch == "1": add_supplier()
elif ch == "2": view_all_suppliers()
elif ch == "3": view_supplier_detail()
elif ch == "4": update_supplier()
elif ch == "5": delete_supplier()
elif ch == "0": break
else: print(" [!] Invalid choice.")
def add_supplier():
print_header("Add New Supplier")
name = input(" Supplier Name : ").strip().title()
contact = input(" Contact Number : ").strip()
email = input(" Email Address : ").strip()
city = input(" City : ").strip().title()
address = input(" Full Address : ").strip()
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
INSERT INTO suppliers (name, contact, email, address, city)
VALUES (%s, %s, %s, %s, %s)
""", (name, contact, email, address, city))
conn.commit()
print(f" ✓ Supplier '{name}' added (ID: {cursor.lastrowid}).")
cursor.close(); conn.close(); pause()
def view_all_suppliers():
print_header("All Suppliers")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT s.sup_id, s.name, s.contact, s.city,
COUNT(p.product_id) AS products, s.status
FROM suppliers s
LEFT JOIN products p ON s.sup_id = p.sup_id
GROUP BY s.sup_id ORDER BY s.name
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" [!] No suppliers found.")
else:
print(f"\n {'ID':<5} {'Name':<24} {'Contact':<14} {'City':<14} {'Products':>9} Status")
print(" " + LINE2)
for r in rows:
print(f" {r[0]:<5} {r[1]:<24} {r[2]:<14} {r[3]:<14} {r[4]:>9} {r[5]}")
pause()
def view_supplier_detail():
print_header("Supplier Details")
conn = connect_db(); cursor = conn.cursor(dictionary=True)
choice = pick_from_query(
cursor,
"SELECT sup_id, name FROM suppliers ORDER BY name",
prompt="Select supplier"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("SELECT * FROM suppliers WHERE sup_id=%s", (choice['sup_id'],))
s = cursor.fetchone()
print("\n " + "-" * 45)
for k, v in s.items():
print(f" {k.replace('_',' ').title():<16}: {v}")
cursor.close(); conn.close(); pause()
def update_supplier():
print_header("Update Supplier")
conn = connect_db(); cursor = conn.cursor(dictionary=True)
choice = pick_from_query(
cursor,
"SELECT sup_id, name FROM suppliers ORDER BY name",
prompt="Select supplier to update"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("SELECT * FROM suppliers WHERE sup_id=%s", (choice['sup_id'],))
s = cursor.fetchone()
print(" (Press Enter to keep current value)\n")
contact = input(f" Contact [{s['contact']}]: ").strip() or s['contact']
email = input(f" Email [{s['email']}] : ").strip() or s['email']
city = input(f" City [{s['city']}] : ").strip() or s['city']
status = input(f" Status [{s['status']}] : ").strip() or s['status']
cursor.execute("""
UPDATE suppliers SET contact=%s, email=%s, city=%s, status=%s
WHERE sup_id=%s
""", (contact, email, city, status, s['sup_id']))
conn.commit()
print(" ✓ Supplier updated.")
cursor.close(); conn.close(); pause()
def delete_supplier():
print_header("Delete Supplier")
conn = connect_db(); cursor = conn.cursor()
choice = pick_from_query(
cursor,
"SELECT sup_id, name FROM suppliers ORDER BY name",
prompt="Select supplier to delete"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("SELECT COUNT(*) FROM products WHERE sup_id=%s", (choice['sup_id'],))
if cursor.fetchone()[0] > 0:
print(" [!] Cannot delete — products are linked to this supplier.")
cursor.close(); conn.close(); pause(); return
confirm = input(f" Delete '{choice['name']}'? (yes/no): ").lower()
if confirm == "yes":
cursor.execute("DELETE FROM suppliers WHERE sup_id=%s", (choice['sup_id'],))
conn.commit()
print(" ✓ Supplier deleted.")
cursor.close(); conn.close(); pause()
# ─────────────────────────────────────────────
# MODULE 3 — PRODUCT MANAGEMENT
# ─────────────────────────────────────────────
def product_menu():
while True:
print_header("Product Management")
print(" 1. Add Product")
print(" 2. View All Products")
print(" 3. View Product Details")
print(" 4. Update Product")
print(" 5. Delete Product")
print(" 6. Search Product")
print(" 7. Low Stock Alert")
print(" 0. Back")
print(LINE)
ch = input(" Choice: ").strip()
if ch == "1": add_product()
elif ch == "2": view_all_products()
elif ch == "3": view_product_detail()
elif ch == "4": update_product()
elif ch == "5": delete_product()
elif ch == "6": search_product()
elif ch == "7": low_stock_alert()
elif ch == "0": break
else: print(" [!] Invalid choice.")
def add_product():
print_header("Add New Product")
conn = connect_db(); cursor = conn.cursor()
code = input(" Product Code (unique) : ").strip().upper()
cursor.execute("SELECT product_id FROM products WHERE product_code=%s", (code,))
if cursor.fetchone():
print(f" [!] Code '{code}' already exists.")
cursor.close(); conn.close(); pause(); return
name = input(" Product Name : ").strip().title()
# Category
cat = pick_from_query(cursor,
"SELECT cat_id, name FROM categories ORDER BY name",
prompt="Select Category")
cat_id = cat['cat_id'] if cat else None
# Supplier
sup = pick_from_query(cursor,
"SELECT sup_id, name FROM suppliers ORDER BY name",
prompt="Select Supplier (0 to skip)")
sup_id = sup['sup_id'] if sup else None
unit = input(" Unit (pcs/kg/ltr/box) : ").strip() or "pcs"
try:
cost = float(input(" Cost Price (₹) : ").strip())
sell = float(input(" Selling Price (₹) : ").strip())
qty = int(input(" Opening Stock (qty) : ").strip())
reord = int(input(" Reorder Level : ").strip() or "10")
except ValueError:
print(" [!] Invalid numeric input."); cursor.close(); conn.close(); pause(); return
loc = input(" Shelf / Location : ").strip()
cursor.execute("""
INSERT INTO products
(product_code, name, cat_id, sup_id, unit, cost_price,
sell_price, quantity, reorder_level, location)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
""", (code, name, cat_id, sup_id, unit, cost, sell, qty, reord, loc))
conn.commit()
print(f" ✓ Product '{name}' added (ID: {cursor.lastrowid}).")
cursor.close(); conn.close(); pause()
def view_all_products():
print_header("All Products")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT p.product_code, p.name,
IFNULL(c.name,'—') AS category,
p.unit, p.cost_price, p.sell_price,
p.quantity, p.reorder_level, p.status
FROM products p
LEFT JOIN categories c ON p.cat_id = c.cat_id
ORDER BY p.name
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" [!] No products found."); pause(); return
print(f"\n Total: {len(rows)} product(s)\n")
print(f" {'Code':<12} {'Name':<24} {'Category':<16} {'Unit':<5} "
f"{'Cost':>8} {'Sell':>8} {'Qty':>5} {'Reord':>6} Status")
print(" " + LINE2)
for r in rows:
alert = " ⚠" if int(r[6]) <= int(r[7]) else ""
print(f" {r[0]:<12} {r[1]:<24} {r[2]:<16} {r[3]:<5} "
f"₹{r[4]:>7,.2f} ₹{r[5]:>7,.2f} {r[6]:>5} {r[7]:>6} {r[8]}{alert}")
pause()
def view_product_detail():
print_header("Product Details")
conn = connect_db(); cursor = conn.cursor(dictionary=True)
choice = pick_from_query(
cursor,
"SELECT product_id, product_code, name FROM products ORDER BY name",
prompt="Select product"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("""
SELECT p.*, c.name AS category, s.name AS supplier
FROM products p
LEFT JOIN categories c ON p.cat_id = c.cat_id
LEFT JOIN suppliers s ON p.sup_id = s.sup_id
WHERE p.product_id = %s
""", (choice['product_id'],))
p = cursor.fetchone()
margin = ((p['sell_price'] - p['cost_price']) / p['sell_price'] * 100
if p['sell_price'] > 0 else 0)
stock_val = p['cost_price'] * p['quantity']
print("\n " + "+" + "-" * 52 + "+")
fields = [
("Product ID", p['product_id']),
("Product Code", p['product_code']),
("Name", p['name']),
("Category", p['category']),
("Supplier", p['supplier']),
("Unit", p['unit']),
("Cost Price", f"₹{p['cost_price']:,.2f}"),
("Selling Price", f"₹{p['sell_price']:,.2f}"),
("Profit Margin", f"{margin:.1f}%"),
("Quantity", f"{p['quantity']} {p['unit']}"),
("Reorder Level", p['reorder_level']),
("Stock Value", f"₹{stock_val:,.2f}"),
("Location", p['location']),
("Status", p['status']),
("Added Date", p['added_date']),
]
for label, val in fields:
print(f" {label:<18}: {val}")
if p['quantity'] <= p['reorder_level']:
print("\n ⚠ WARNING: Stock at or below reorder level!")
print(" " + "+" + "-" * 52 + "+")
cursor.close(); conn.close(); pause()
def update_product():
print_header("Update Product")
conn = connect_db(); cursor = conn.cursor(dictionary=True)
choice = pick_from_query(
cursor,
"SELECT product_id, product_code, name FROM products ORDER BY name",
prompt="Select product to update"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("SELECT * FROM products WHERE product_id=%s", (choice['product_id'],))
p = cursor.fetchone()
print(" (Press Enter to keep current value)\n")
sell = input(f" Sell Price [₹{p['sell_price']}] : ").strip()
cost = input(f" Cost Price [₹{p['cost_price']}] : ").strip()
reord = input(f" Reorder Lvl [{p['reorder_level']}] : ").strip()
loc = input(f" Location [{p['location']}] : ").strip()
stat = input(f" Status [{p['status']}] : ").strip()
cursor.execute("""
UPDATE products
SET sell_price=%s, cost_price=%s, reorder_level=%s, location=%s, status=%s
WHERE product_id=%s
""", (
float(sell) if sell else p['sell_price'],
float(cost) if cost else p['cost_price'],
int(reord) if reord else p['reorder_level'],
loc if loc else p['location'],
stat if stat else p['status'],
p['product_id']
))
conn.commit()
print(" ✓ Product updated.")
cursor.close(); conn.close(); pause()
def delete_product():
print_header("Delete Product")
conn = connect_db(); cursor = conn.cursor()
choice = pick_from_query(
cursor,
"SELECT product_id, product_code, name FROM products ORDER BY name",
prompt="Select product to delete"
)
if not choice:
cursor.close(); conn.close(); return
confirm = input(f" Delete '{choice['name']}'? (yes/no): ").lower()
if confirm == "yes":
cursor.execute("DELETE FROM products WHERE product_id=%s", (choice['product_id'],))
conn.commit()
print(" ✓ Product deleted.")
cursor.close(); conn.close(); pause()
def search_product():
print_header("Search Product")
kw = input(" Enter name or product code: ").strip()
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT p.product_code, p.name,
IFNULL(c.name,'—') AS category,
p.sell_price, p.quantity, p.status
FROM products p
LEFT JOIN categories c ON p.cat_id = c.cat_id
WHERE p.name LIKE %s OR p.product_code LIKE %s
ORDER BY p.name
""", (f"%{kw}%", f"%{kw}%"))
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(f" [!] No results for '{kw}'.")
else:
print(f"\n Found {len(rows)} result(s):\n")
print(f" {'Code':<12} {'Name':<26} {'Category':<18} {'Price':>9} {'Qty':>5} Status")
print(" " + LINE2)
for r in rows:
print(f" {r[0]:<12} {r[1]:<26} {r[2]:<18} ₹{r[3]:>8,.2f} {r[4]:>5} {r[5]}")
pause()
def low_stock_alert():
print_header("⚠ Low Stock Alert")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT p.product_code, p.name,
IFNULL(c.name,'—') AS category,
p.quantity, p.reorder_level,
IFNULL(s.name,'—') AS supplier, s.contact
FROM products p
LEFT JOIN categories c ON p.cat_id = c.cat_id
LEFT JOIN suppliers s ON p.sup_id = s.sup_id
WHERE p.quantity <= p.reorder_level AND p.status='Active'
ORDER BY p.quantity ASC
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" ✓ All products are above reorder level.")
else:
print(f"\n {len(rows)} product(s) need reorder:\n")
print(f" {'Code':<12} {'Name':<24} {'Category':<16} {'Qty':>5} {'Reord':>6} {'Supplier':<20} Contact")
print(" " + LINE2)
for r in rows:
print(f" {r[0]:<12} {r[1]:<24} {r[2]:<16} {r[3]:>5} {r[4]:>6} {r[5]:<20} {r[6]}")
pause()
# ─────────────────────────────────────────────
# MODULE 4 — STOCK IN (PURCHASE)
# ─────────────────────────────────────────────
def stock_in_menu():
while True:
print_header("Stock In (Purchase)")
print(" 1. Record Stock In / Purchase")
print(" 2. View Stock-In History")
print(" 3. View by Product")
print(" 0. Back")
print(LINE)
ch = input(" Choice: ").strip()
if ch == "1": record_stock_in()
elif ch == "2": view_stock_in_history()
elif ch == "3": view_stock_in_by_product()
elif ch == "0": break
else: print(" [!] Invalid choice.")
def record_stock_in():
print_header("Record Stock In")
conn = connect_db(); cursor = conn.cursor(dictionary=True)
choice = pick_from_query(
cursor,
"SELECT product_id, product_code, name, quantity FROM products ORDER BY name",
prompt="Select Product"
)
if not choice:
cursor.close(); conn.close(); return
try:
qty = int(input(" Quantity Received : ").strip())
cost = float(input(f" Cost Price/unit (₹)[current: {choice.get('cost_price',0)}]: ").strip() or choice.get('cost_price', 0))
except ValueError:
print(" [!] Invalid input."); cursor.close(); conn.close(); pause(); return
sup = pick_from_query(cursor,
"SELECT sup_id, name FROM suppliers ORDER BY name",
prompt="Select Supplier (0 to skip)")
sup_id = sup['sup_id'] if sup else None
invoice = input(" Invoice No (or Enter to auto): ").strip() or auto_bill()
remarks = input(" Remarks : ").strip()
total = round(qty * cost, 2)
# Insert transaction
cursor.execute("""
INSERT INTO stock_in
(product_id, product_name, quantity, cost_price, total_cost,
sup_id, invoice_no, remarks)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
""", (choice['product_id'], choice['name'], qty, cost, total,
sup_id, invoice, remarks))
# Update product stock & cost price
cursor.execute("""
UPDATE products
SET quantity = quantity + %s,
cost_price = %s
WHERE product_id = %s
""", (qty, cost, choice['product_id']))
conn.commit()
new_qty = choice.get('quantity', 0) + qty
print(f"\n ✓ Stock In recorded.")
print(f" Product : {choice['name']}")
print(f" Added Qty : {qty} | New Stock : {new_qty}")
print(f" Total Cost: ₹{total:,.2f} | Invoice: {invoice}")
cursor.close(); conn.close(); pause()
def view_stock_in_history():
print_header("Stock-In History")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT si.txn_id, si.txn_date, si.product_name,
si.quantity, si.cost_price, si.total_cost,
IFNULL(s.name,'—') AS supplier, si.invoice_no
FROM stock_in si
LEFT JOIN suppliers s ON si.sup_id = s.sup_id
ORDER BY si.txn_date DESC, si.txn_id DESC
LIMIT 50
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" [!] No stock-in records."); pause(); return
print(f"\n {'ID':<6} {'Date':<12} {'Product':<24} {'Qty':>5} "
f"{'Cost/u':>9} {'Total':>10} {'Supplier':<18} Invoice")
print(" " + LINE2)
for r in rows:
print(f" {r[0]:<6} {str(r[1]):<12} {r[2]:<24} {r[3]:>5} "
f"₹{r[4]:>8,.2f} ₹{r[5]:>9,.2f} {r[6]:<18} {r[7]}")
pause()
def view_stock_in_by_product():
print_header("Stock-In by Product")
conn = connect_db(); cursor = conn.cursor()
choice = pick_from_query(
cursor,
"SELECT product_id, name FROM products ORDER BY name",
prompt="Select Product"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("""
SELECT txn_id, txn_date, quantity, cost_price, total_cost, invoice_no
FROM stock_in WHERE product_id=%s ORDER BY txn_date DESC
""", (choice['product_id'],))
rows = cursor.fetchall()
cursor.execute("SELECT SUM(quantity), SUM(total_cost) FROM stock_in WHERE product_id=%s",
(choice['product_id'],))
totals = cursor.fetchone()
cursor.close(); conn.close()
print(f"\n Product: {choice['name']}\n")
print(f" {'ID':<6} {'Date':<12} {'Qty':>5} {'Cost/u':>9} {'Total':>10} Invoice")
print(" " + "-" * 55)
for r in rows:
print(f" {r[0]:<6} {str(r[1]):<12} {r[2]:>5} ₹{r[3]:>8,.2f} ₹{r[4]:>9,.2f} {r[5]}")
print(" " + "-" * 55)
print(f" Total Purchased: {totals[0]} units | Total Cost: ₹{totals[1]:,.2f}")
pause()
# ─────────────────────────────────────────────
# MODULE 5 — STOCK OUT (SALES)
# ─────────────────────────────────────────────
def stock_out_menu():
while True:
print_header("Stock Out (Sales)")
print(" 1. Record Sale / Stock Issue")
print(" 2. View Sales History")
print(" 3. Sales by Product")
print(" 4. Generate Bill")
print(" 0. Back")
print(LINE)
ch = input(" Choice: ").strip()
if ch == "1": record_stock_out()
elif ch == "2": view_sales_history()
elif ch == "3": sales_by_product()
elif ch == "4": generate_bill()
elif ch == "0": break
else: print(" [!] Invalid choice.")
def record_stock_out():
print_header("Record Sale / Stock Issue")
conn = connect_db(); cursor = conn.cursor(dictionary=True)
choice = pick_from_query(
cursor,
"SELECT product_id, product_code, name, quantity, sell_price FROM products WHERE quantity > 0 ORDER BY name",
prompt="Select Product"
)
if not choice:
print(" [!] No products with stock available.")
cursor.close(); conn.close(); pause(); return
print(f" Available Stock : {choice['quantity']}")
try:
qty = int(input(" Quantity to Sell : ").strip())
if qty <= 0 or qty > choice['quantity']:
print(f" [!] Invalid quantity. Available: {choice['quantity']}")
cursor.close(); conn.close(); pause(); return
sell = float(input(f" Sell Price/unit (₹)[{choice['sell_price']}]: ").strip()
or choice['sell_price'])
except ValueError:
print(" [!] Invalid input."); cursor.close(); conn.close(); pause(); return
customer = input(" Customer Name (optional) : ").strip() or "Walk-in"
bill_no = input(" Bill No (or Enter to auto): ").strip() or auto_bill()
remarks = input(" Remarks : ").strip()
total = round(qty * sell, 2)
cursor.execute("""
INSERT INTO stock_out
(product_id, product_name, quantity, sell_price, total_amount,
customer, bill_no, remarks)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
""", (choice['product_id'], choice['name'], qty, sell, total,
customer, bill_no, remarks))
cursor.execute("""
UPDATE products SET quantity = quantity - %s WHERE product_id = %s
""", (qty, choice['product_id']))
conn.commit()
# Check if now low stock
cursor.execute(
"SELECT quantity, reorder_level FROM products WHERE product_id=%s",
(choice['product_id'],)
)
updated = cursor.fetchone()
print(f"\n ✓ Sale recorded.")
print(f" Product : {choice['name']}")
print(f" Sold : {qty} units @ ₹{sell:,.2f}")
print(f" Bill Amount : ₹{total:,.2f} | Bill No: {bill_no}")
print(f" Remaining : {updated['quantity']} units")
if updated['quantity'] <= updated['reorder_level']:
print(" ⚠ Stock has dropped to or below reorder level!")
cursor.close(); conn.close(); pause()
def view_sales_history():
print_header("Sales History")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT txn_id, txn_date, product_name, quantity,
sell_price, total_amount, customer, bill_no
FROM stock_out
ORDER BY txn_date DESC, txn_id DESC
LIMIT 50
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" [!] No sales records."); pause(); return
print(f"\n {'ID':<6} {'Date':<12} {'Product':<24} {'Qty':>5} "
f"{'Price':>9} {'Total':>10} {'Customer':<18} Bill No")
print(" " + LINE2)
for r in rows:
print(f" {r[0]:<6} {str(r[1]):<12} {r[2]:<24} {r[3]:>5} "
f"₹{r[4]:>8,.2f} ₹{r[5]:>9,.2f} {r[6]:<18} {r[7]}")
pause()
def sales_by_product():
print_header("Sales by Product")
conn = connect_db(); cursor = conn.cursor()
choice = pick_from_query(
cursor,
"SELECT product_id, name FROM products ORDER BY name",
prompt="Select Product"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("""
SELECT txn_id, txn_date, quantity, sell_price, total_amount, customer, bill_no
FROM stock_out WHERE product_id=%s ORDER BY txn_date DESC
""", (choice['product_id'],))
rows = cursor.fetchall()
cursor.execute(
"SELECT SUM(quantity), SUM(total_amount) FROM stock_out WHERE product_id=%s",
(choice['product_id'],)
)
totals = cursor.fetchone()
cursor.close(); conn.close()
print(f"\n Product: {choice['name']}\n")
print(f" {'ID':<6} {'Date':<12} {'Qty':>5} {'Price':>9} {'Total':>10} {'Customer':<20} Bill")
print(" " + "-" * 68)
for r in rows:
print(f" {r[0]:<6} {str(r[1]):<12} {r[2]:>5} ₹{r[3]:>8,.2f} ₹{r[4]:>9,.2f} {r[5]:<20} {r[6]}")
print(" " + "-" * 68)
print(f" Total Sold: {totals[0]} units | Total Revenue: ₹{totals[1]:,.2f}")
pause()
def generate_bill():
print_header("Generate Bill (by Bill No)")
bill_no = input(" Enter Bill No: ").strip()
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT product_name, quantity, sell_price, total_amount,
customer, txn_date
FROM stock_out WHERE bill_no=%s
""", (bill_no,))
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(f" [!] No bill found: {bill_no}"); pause(); return
customer = rows[0][4]
txn_date = rows[0][5]
grand = sum(r[3] for r in rows)
print("\n" + "+" + "=" * 54 + "+")
print("|{:^54}|".format("RETAIL STORE — SALES BILL"))
print("+" + "=" * 54 + "+")
print(f" Bill No : {bill_no}")
print(f" Customer : {customer}")
print(f" Date : {txn_date}")
print(" " + "-" * 52)
print(f" {'Product':<26} {'Qty':>5} {'Price':>10} {'Total':>10}")
print(" " + "-" * 52)
for r in rows:
print(f" {r[0]:<26} {r[1]:>5} ₹{r[2]:>9,.2f} ₹{r[3]:>9,.2f}")
print(" " + "-" * 52)
print(f" {'GRAND TOTAL':>42} ₹{grand:>9,.2f}")
print("+" + "=" * 54 + "+")
print("|{:^54}|".format("Thank you for shopping with us!"))
print("+" + "=" * 54 + "+")
pause()
# ─────────────────────────────────────────────
# MODULE 6 — REPORTS & ANALYTICS
# ─────────────────────────────────────────────
def reports_menu():
while True:
print_header("Reports & Analytics")
print(" 1. Inventory Value Report")
print(" 2. Sales Summary (Date Range)")
print(" 3. Category-Wise Stock Report")
print(" 4. Profit / Margin Report")
print(" 5. Top Selling Products")
print(" 6. Stock Movement Report")
print(" 0. Back")
print(LINE)
ch = input(" Choice: ").strip()
if ch == "1": inventory_value_report()
elif ch == "2": sales_summary()
elif ch == "3": category_stock_report()
elif ch == "4": profit_margin_report()
elif ch == "5": top_selling_products()
elif ch == "6": stock_movement_report()
elif ch == "0": break
else: print(" [!] Invalid choice.")
def inventory_value_report():
print_header("Inventory Value Report")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT p.product_code, p.name,
IFNULL(c.name,'—') AS category,
p.quantity, p.cost_price,
ROUND(p.quantity * p.cost_price, 2) AS stock_value,
p.sell_price,
ROUND(p.quantity * p.sell_price, 2) AS mrp_value
FROM products p
LEFT JOIN categories c ON p.cat_id = c.cat_id
WHERE p.status = 'Active'
ORDER BY stock_value DESC
""")
rows = cursor.fetchall()
cursor.execute("""
SELECT SUM(quantity * cost_price), SUM(quantity * sell_price)
FROM products WHERE status='Active'
""")
totals = cursor.fetchone()
cursor.close(); conn.close()
print(f"\n {'Code':<12} {'Name':<22} {'Category':<16} {'Qty':>5} "
f"{'Cost':>9} {'Stock Val':>11} {'MRP Val':>11}")
print(" " + LINE2)
for r in rows:
print(f" {r[0]:<12} {r[1]:<22} {r[2]:<16} {r[3]:>5} "
f"₹{r[4]:>8,.2f} ₹{r[5]:>10,.2f} ₹{r[6]:>9,.2f}")
print(" " + LINE2)
print(f" {'TOTAL INVENTORY VALUE (Cost)':>60} : ₹{totals[0]:,.2f}")
print(f" {'TOTAL INVENTORY VALUE (MRP)':>60} : ₹{totals[1]:,.2f}")
pause()
def sales_summary():
print_header("Sales Summary")
s = input(" Start date (YYYY-MM-DD) or Enter for all: ").strip()
e = input(" End date (YYYY-MM-DD) or Enter for all: ").strip()
conn = connect_db(); cursor = conn.cursor()
if s and e:
cursor.execute("""
SELECT txn_date,
COUNT(*) AS transactions,
SUM(quantity) AS units_sold,
SUM(total_amount) AS revenue
FROM stock_out
WHERE txn_date BETWEEN %s AND %s
GROUP BY txn_date ORDER BY txn_date
""", (s, e))
else:
cursor.execute("""
SELECT txn_date,
COUNT(*) AS transactions,
SUM(quantity) AS units_sold,
SUM(total_amount) AS revenue
FROM stock_out
GROUP BY txn_date ORDER BY txn_date
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" [!] No sales data found."); pause(); return
total_rev = sum(r[3] for r in rows)
print(f"\n {'Date':<12} {'Transactions':>14} {'Units Sold':>11} {'Revenue':>13}")
print(" " + "-" * 54)
for r in rows:
print(f" {str(r[0]):<12} {r[1]:>14} {r[2]:>11} ₹{r[3]:>12,.2f}")
print(" " + "-" * 54)
print(f" {'TOTAL REVENUE':>39} ₹{total_rev:>12,.2f}")
pause()
def category_stock_report():
print_header("Category-Wise Stock Report")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT c.name AS category,
COUNT(p.product_id) AS products,
SUM(p.quantity) AS total_qty,
SUM(p.quantity * p.cost_price) AS stock_value,
SUM(CASE WHEN p.quantity <= p.reorder_level THEN 1 ELSE 0 END) AS low_stock
FROM categories c
LEFT JOIN products p ON c.cat_id = p.cat_id AND p.status='Active'
GROUP BY c.cat_id ORDER BY stock_value DESC
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
print(f"\n {'Category':<22} {'Products':>9} {'Total Qty':>10} {'Stock Value':>13} {'Low Stock':>10}")
print(" " + "-" * 68)
for r in rows:
sv = r[3] or 0
qty = r[2] or 0
ls = r[4] or 0
print(f" {r[0]:<22} {r[1]:>9} {qty:>10} ₹{sv:>12,.2f} {ls:>10}")
pause()
def profit_margin_report():
print_header("Profit / Margin Report")
conn = connect_db(); cursor = conn.cursor()
cursor.execute("""
SELECT p.product_code, p.name,
p.cost_price, p.sell_price,
ROUND(p.sell_price - p.cost_price, 2) AS profit_per_unit,
ROUND((p.sell_price - p.cost_price)
/ NULLIF(p.sell_price,0) * 100, 2) AS margin_pct,
IFNULL(SUM(so.quantity),0) AS units_sold,
IFNULL(SUM(so.total_amount),0) AS revenue,
IFNULL(SUM(so.quantity * p.cost_price),0) AS cogs,
IFNULL(SUM(so.total_amount)
- SUM(so.quantity * p.cost_price), 0) AS gross_profit
FROM products p
LEFT JOIN stock_out so ON p.product_id = so.product_id
GROUP BY p.product_id
ORDER BY gross_profit DESC
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
print(f"\n {'Code':<12} {'Name':<22} {'Cost':>8} {'Sell':>8} "
f"{'Margin%':>8} {'Sold':>6} {'Revenue':>11} {'Profit':>11}")
print(" " + LINE2)
for r in rows:
print(f" {r[0]:<12} {r[1]:<22} ₹{r[2]:>7,.2f} ₹{r[3]:>7,.2f} "
f"{r[5]:>7}% {r[6]:>6} ₹{r[7]:>10,.2f} ₹{r[9]:>10,.2f}")
pause()
def top_selling_products():
print_header("Top Selling Products")
limit = input(" Show top (default 10): ").strip()
try:
limit = int(limit) if limit else 10
except ValueError:
limit = 10
conn = connect_db(); cursor = conn.cursor()
cursor.execute(f"""
SELECT so.product_name,
SUM(so.quantity) AS units_sold,
SUM(so.total_amount) AS revenue,
COUNT(so.txn_id) AS transactions
FROM stock_out so
GROUP BY so.product_name
ORDER BY units_sold DESC
LIMIT {limit}
""")
rows = cursor.fetchall()
cursor.close(); conn.close()
if not rows:
print(" [!] No sales data."); pause(); return
print(f"\n {'Rank':<5} {'Product':<28} {'Units Sold':>11} {'Revenue':>12} {'Txns':>6}")
print(" " + "-" * 65)
for i, r in enumerate(rows, 1):
bar = "█" * min(int(r[1] / max(rows[0][1], 1) * 20), 20)
print(f" {i:<5} {r[0]:<28} {r[1]:>11} ₹{r[2]:>11,.2f} {r[3]:>6} {bar}")
pause()
def stock_movement_report():
print_header("Stock Movement Report")
conn = connect_db(); cursor = conn.cursor()
choice = pick_from_query(
cursor,
"SELECT product_id, name, quantity FROM products ORDER BY name",
prompt="Select Product"
)
if not choice:
cursor.close(); conn.close(); return
cursor.execute("""
SELECT 'IN' AS type, txn_date, quantity, cost_price AS price, invoice_no AS ref
FROM stock_in WHERE product_id=%s
UNION ALL
SELECT 'OUT', txn_date, quantity, sell_price, bill_no
FROM stock_out WHERE product_id=%s
ORDER BY txn_date, type DESC
""", (choice['product_id'], choice['product_id']))
rows = cursor.fetchall()
cursor.close(); conn.close()
print(f"\n Product: {choice['name']} | Current Stock: {choice['quantity']}\n")
print(f" {'Type':<5} {'Date':<12} {'Qty':>6} {'Price':>10} Reference")
print(" " + "-" * 50)
running = 0
for r in rows:
if r[0] == "IN":
running += r[2]
else:
running -= r[2]
print(f" {r[0]:<5} {str(r[1]):<12} {r[2]:>6} ₹{r[3]:>9,.2f} {r[4]}")
pause()
# ─────────────────────────────────────────────
# MAIN MENU
# ─────────────────────────────────────────────
def main_menu():
print("\n Initializing database...")
initialize_database()
while True:
print("\n" + LINE)
print(" 🏪 INVENTORY MANAGEMENT SYSTEM")
print(" CBSE Class 12 — Python + MySQL Project")
print(LINE)
print(" 1. Category Management")
print(" 2. Supplier Management")
print(" 3. Product Management")
print(" 4. Stock In (Purchase / Receive)")
print(" 5. Stock Out (Sale / Issue)")
print(" 6. Reports & Analytics")
print(" 0. Exit")
print(LINE)
ch = input(" Enter choice: ").strip()
if ch == "1": category_menu()
elif ch == "2": supplier_menu()
elif ch == "3": product_menu()
elif ch == "4": stock_in_menu()
elif ch == "5": stock_out_menu()
elif ch == "6": reports_menu()
elif ch == "0":
print("\n Thank you for using Inventory Management System. Goodbye!\n")
break
else:
print(" [!] Invalid choice. Enter 0–6.")
# ─────────────────────────────────────────────
# ENTRY POINT
# ─────────────────────────────────────────────
if __name__ == "__main__":
main_menu()