Index

[Link] Heading Page No.

1 Introduction 1

2 Objectives 4

3 System Analysis 4
4 Data Dictionary & Database 5
Schema (DDL)
5 Algorithm/Flowchart 6
6 Source Code (Python) 7
7 Test Cases Outputs 15
8 Limitations 19
9 Future Scope / Enhancements 19

10 Bibliography 19
 Introduction
A Stock Management System (Inventory Tracker) is a simple application
that helps small shops or businesses keep track of items, stock levels,
purchases (receipts) and sales. This project demonstrates how Python can
be used with a relational database (MySQL) to create a Stock
Management System.

Introduction to Python:
Python is a high-level, interpreted programming language known for its
simplicity, readability, and versatility.
It supports multiple programming paradigms, including procedural,
object-oriented, and functional programming.
Python is widely used in various fields, such as web development, data
analysis, artificial intelligence, scientific computing, and automation.

Key Features of Python:

1. Simple and Readable Syntax: Python's syntax is designed to be
readable and straightforward, making it an excellent choice for beginners
and experienced programmers alike.
2. Interpreted Language: Python is an interpreted language, meaning
code is executed line-by-line, which makes debugging and testing easier.
3. Dynamically Typed: Variable types are determined at runtime,
allowing for more flexibility in coding.
4. Extensive Standard Library: Python comes with a vast standard
library that provides modules and functions for a wide range of tasks.
5. Cross-Platform: Python is available on various operating systems,
including Windows, macOS, and Linux, making it a portable language.
1|P a ge
Introduction to MySQL:
MySQL is a popular open-source relational database management system
(RDBMS) known for its reliability, scalability, and ease of use.
It is widely used for web applications, data warehousing, and logging
applications.
MySQL uses Structured Query Language (SQL) for accessing and
managing data.

Key Features of MySQL:

1. Open Source: MySQL is open-source, which means it is free to use and
has a large community of developers contributing to its improvement.
2. Scalability: MySQL can handle large databases and can scale with the
growth of your application.
3. High Performance: MySQL is designed for speed and reliability in
processing high volumes of data.
4. Security: MySQL offers robust security features to protect data,
including data encryption, user authentication, and access control.
5. Cross-Platform: MySQL runs on various platforms, including
Windows, Linux, and macOS.

2|P a ge
How does connectivity between MySQL and Python
work?
The connectivity between Python and MySQL typically involves using a
MySQL database driver for Python such as MySQL Connector/Python or
PyMySQL. Here is a basic overview of how it works:

1. Install MySQL Connector/Python or PyMySQL:

You need to install one of these packages in your Python
environment. You can do this using pip, Python's package manager,
in the cmd(command prompt).
pip install mysql-connector-python

2. Import the MySQL Connector/Python or PyMySQL

module:
Once installed, import the module into your Python script or
program.
import [Link] # for MySQL Connector/Python
import pymysql # for PyMySQL

3. Establish a Connection:
Establish a connection to your MySQL database server using the
connect() function provided by the MySQL Connector/Python or
PyMySQL module. You need to provide details such as the host,
user, password, and database name.

# Using MySQL Connector/Python

Connection_Object(Identifier) = [Link] (host =
"localhost", user = "your_username", password = "your_password",
database = "your_database")
3|P a ge
 # Using PyMySQL
Connection_Object(Identifier) = [Link] (host =
"localhost", user = "your_username", password = "your_password",
database = "your_database")

4. Create a Cursor Object:

After establishing a connection, you create a cursor object. The
cursor object allows you to execute SQL queries and fetch data from
the database.
connection_object.cursor()
cursor = [Link]()

5. Execute SQL Queries:

Now, you can execute SQL queries using the execute() method of the
cursor object.
[Link] ("SELECT * FROM your_table")

6. Fetch Data (if needed):

If your query returns results, you can fetch the data using methods
like fetchall(), fetchone(), or fetchmany().
rows = [Link]()
for row in rows:
print(row)

7. Close the Cursor and Connection:

After executing your queries, it's good practice to close the cursor
and connection to release the database resources.
[Link]()
[Link]()
Connection_object.close()

4|P a ge
 That is the basic workflow for connecting Python to MySQL. Of
course, depending on your requirements, you may need to handle
errors, transactions, or other advanced features provided by the
MySQL database driver.

Example code after creating a “school” database with

“student” table

Output:

“mydb” is the Connection object

“cur” is the cursor object

5|P a ge
 Objectives
• Implement a console-based inventory system using Python and MySQL.
• Demonstrate Python–MySQL connectivity and secure query practices
(parameterized queries).
• Perform basic inventory operations: Add item, Sell item, Receive item,
Search items, Generate reports.
• Provide clear documentation: data dictionary, DDL, algorithms, test
cases and screenshots (sample outputs).

System Analysis
Functional Requirements
• Add new items to inventory with ID, name, price, quantity and reorder
level.
• Update stock when items are sold or received.
• Prevent selling more than available stock.
• Search items by name (partial match).
• Reports: List all items; Items below reorder level; Items above reorder
level.

Hardware/Software Requirements
• OS: Windows / macOS / Linux
• Python 3.8+
• MySQL Server (Community) installed and running
• Python package: mysql-connector-python (install via pip install mysql-connector-
python)
6|P a ge
 Data Dictionary & Database Schema (DDL)
Execute these SQL commands in MySQL (via MySQL shell) to
create the database and tables.
Creating database
CREATE DATABASE stock;
USE stock;
Creating Items table
CREATE TABLE items (id INT PRIMARY KEY,
name VARCHAR (150) NOT NULL,
price DECIMAL (10,2) NOT NULL DEFAULT 0.00,
qty INT NOT NULL DEFAULT 0,
reorder INT NOT NULL DEFAULT 0);

7|P a ge
 Algorithm / Flowchart
Simple menu-driven loop:

1. Start program → Connect to database (ask for credentials).

2. Show Main Menu: Add Item, Sell Item, Receive Item, Search Item,
Reports, Exit.
3. On each operation: Validate inputs (IDs numeric, quantities non-
negative, price non-negative). For sell: check available quantity before
update.
4. On exit: close database connection.

8|P a ge
 Source Code (Python)
import [Link]
from [Link] import Error

# Input Helpers
def read_int (prompt, min_value = None):
while True:
s = input(prompt).strip()
if not [Link]():
print("Please enter a valid non-negative integer.")
continue
val = int(s)
if min_value is not None and val < min_value:
print (f"Value must be at least {min_value}.")
continue
return val
def read_float (prompt, min_value = None):
while True:
s = input(prompt).strip()

9|P a ge
 try:
val = float(s)
if min_value is not None and val < min_value:
print (f"Value must be at least {min_value}.")
continue
return val
except ValueError:
print ("Please enter a valid number (e.g., 10.50).")

# Core Functionalities
def add_item(conn):
try:
cursor = [Link]()
item_id = read_int ("Enter Item ID (integer): ", min_value = 1)

# Check if exists
[Link]("SELECT id FROM items WHERE id = %s",
(item_id,))
if [Link]():
print ("Item with this ID already exists. Use update path or
choose another ID.")
10 | P a g e
 return
name = input ("Enter Item Name: ").strip()
if not name:
print ("Name cannot be empty.")
return
price = read_float ("Enter Price: ", min_value = 0.0)
qty = read_int ("Enter Quantity: ", min_value = 0)
reorder = read_int ("Enter Reorder Level: ", min_value = 0)
[Link]("INSERT INTO items (id, name, price, qty,
reorder) VALUES (%s, %s, %s, %s, %s)", (item_id, name, price,
qty, reorder))
[Link]()
except Error as e:
print ("Database error:", e)
[Link]()
def sell_item(conn):
try:
cursor = [Link]()
item_id = read_int ("Enter Item ID to sell: ", min_value = 1)
[Link]("SELECT qty, name FROM items WHERE

11 | P a g e
id = %s", (item_id,))
row = [Link]()
if not row:
print ("Item not found.")
return
current_qty, name = row
print ("Current stock for", name, "ID", item_id, ":", current_qty)
qty = read_int ("Enter quantity to sell: ", min_value = 1)
if qty > current_qty:
print ("Insufficient stock. Sale aborted.")
return

# Proceed with sale in transaction

try:
[Link]("UPDATE items SET qty = qty-%s WHERE id
= %s”, (qty,item_id))
[Link]()
print ("Stock updated.")
except Error as e:
[Link]()
print ("Error during sale:", e)
12 | P a g e
 except Error as e:
print ("Database error:", e)

def receive_item(conn):
try:
cursor = [Link]()
item_id = read_int("Enter Item ID to receive (restock): ",
min_value = 1)
[Link]("SELECT name FROM items WHERE id = %s",
(item_id,))
row = [Link]()
if not row:
print ("Item not found. Consider adding it first.")
return
name = row[0]
qty = read_int("Enter quantity received: ", min_value = 1)
try:
[Link]("UPDATE items SET qty = qty + %s WHERE
id = %s", (qty, item_id))
[Link]()
print ("Stock updated successfully.")
13 | P a g e
 except Error as e:
[Link]()
print ("Error updating stock:", e)
except Error as e:
print ("Database error:", e)
def search_item (conn):
cursor = [Link]()
term = input("Enter item name (partial match allowed): ").strip()
if not term:
print("Search term cannot be empty.")
return
like_term = f"%{term}%"
[Link] ("SELECT id, name, price, qty, reorder FROM items
WHERE name LIKE %s", (like_term,))
records = [Link] ()
if not records:
print ("No matching items found.")
return

14 | P a g e
 # Using Formatted String Literals to ensure readable
output

print(f"{'ID':<6}{'Name':<30}{'Price':>8}{'Qty':>8}{'Reorder':>10}")
print("-"*62)
for idr, name, price, qty, reorder in records:
print(f"{idr:<6}{name:<30}{price:>8.2f}{qty:>8}{reorder:>10}")

# Reports
def report_all_items(conn):
cursor = [Link]()
[Link] ("SELECT id, name, price, qty, reorder FROM items
ORDER BY id")
rows = [Link]()
print("\nAll Items:")
print(f"{'ID':<6}{'Name':<30}{'Price':>8}{'Qty':>8}
{'Reorder':>10}")
print("-"*62)
for idr, name, price, qty, reorder in rows:
print(f"{idr:<6}{name:<30}{price:>8.2f}{qty:>8}{reorder:>10}")

15 | P a g e
def report_reorder_status(conn, below = True):
cursor = [Link]()
if below:
[Link]("SELECT id, name, price, qty, reorder FROM items
WHERE qty < reorder ORDER BY qty")
title = "Items BELOW Reorder Level"
else:
[Link]("SELECT id, name, price, qty, reorder FROM items
WHERE qty > = reorder ORDER BY qty DESC")
title = "Items AT/ABOVE Reorder Level"
rows = [Link]()
print(f"\n{title}:")
if not rows:
print("No items in this category.")
return
print(f"{'ID':<6}{'Name':<30}{'Price':>8}{'Qty':>8}{'Reorder':>10}")
print("-"*62)
for idr, name, price, qty, reorder in rows:
print(f"{idr:<6}{name:<30}{price:>8.2f}{qty:>8}{reorder:>10}")

16 | P a g e
 # Main Menu
def main():
print(" = = = Stock Management System = = = ")
try:
conn = [Link] (host = 'localhost', user = 'root',
password = 'root123', database = 'stock')
except Error as e:
print ("Failed to connect to database:", e)
return
while True:
print("\nMain Menu")
print("1. Add Item")
print("2. Sell Item")
print("3. Receive Item")
print("4. Search Item")
print("5. Reports")
print("6. Exit")
choice = input("Enter choice (1-6): ").strip()
if choice = = '1':
17 | P a g e
 add_item(conn)
elif choice = = '2':
sell_item(conn)
elif choice = = '3':
receive_item(conn)
elif choice = = '4':
search_item(conn)
elif choice = = '5':
print("\nReport Menu")
print("1. All items")
print("2. Items below reorder level")
print("3. Items at/above reorder level")
sub = input("Choose (1-3): ").strip()
if sub = = '1':
report_all_items(conn)
elif sub = = '2':
report_reorder_status(conn, below = True)
elif sub = = '3':
report_reorder_status(conn, below = False)
else:
print("Invalid choice.")

18 | P a g e
 elif choice = = '6':
print("Closing application. Goodbye.")
[Link]()
break
else:
print("Invalid choice. Please select 1-6.")
main()

19 | P a g e
 Test Case Outputs
Test Case 1 — Add Item

Test Case 2 — Sell Item (valid)

20 | P a g e
Test Case 3 — Sell Item (invalid: insufficient)

Test Case 4 — Receive Item (Restocking)

Stock increased from 30 to 40

21 | P a g e
Test Case 5 — Search Items

Test Case 6 — Reports

i) All Items

22 | P a g e
ii) Items below reorder level

iii) Items at/above reorder level

23 | P a g e
 Limitations
• No GUI: Console interface only — acceptable for CBSE
practicals but not user-friendly for non-technical users.
• Single-user: No multi-user concurrency handling.
For concurrent multi-client safety, additional locking/transactions
and a server layer would be needed.
• No authentication module: The script prompts for DB
credentials but does not implement app-level user login/roles.
• Scalability: For very large datasets, display and performance
optimizations (pagination, indexing) should be considered.

Future Scope / Enhancements

• Add GUI or a simple web frontend (Flask) for easier use.
• Add User Authentication & Roles (admin, cashier) and audit
trail.
• Add Reports with aggregates (daily sales total, monthly revenue
— use SUM, GROUP BY) and graphs
(matplotlib).
• Add CSV/Excel import for bulk item upload.
• Add concurrency handling and API endpoints for multi-user
deployments.
24 | P a g e
 Bibliography
 NCERT Class 12 Computer Science (Python + SQL) — for
concepts and curriculum alignment.
 Sumita Arora Class 12 Computer Science textbook
 CBSE Sample Project Guidelines (Computer Science Code
083) — project format & marking scheme.
 Python official documents — Formatted String Literals
(f-strings)
 Python Tutorial – [Link]
 SQL Tutorial – [Link]

25 | P a g e