Build an Inventory Management System with SQLite
Every business that sells physical products needs to track inventory. How many units are in stock? When did they arrive? Are we running low on anything? An inventory management system answers all of these questions — and you're about to build one from scratch.
In this project, you'll use Python and SQLite to build a fully functional inventory system. SQLite is perfect for this because it stores the entire database in memory (no server required), supports full SQL, and comes built into Python. By the end, you'll have a reusable InventorySystem class that handles products, transactions, stock levels, and alerts.
Step 1: Design the Database Schema
Good database design starts with thinking about what you need to store. For inventory, we need two core tables: products (what we sell) and transactions (every time stock moves in or out).
The products table holds static information about each item — its name, category, price, and current stock quantity. The transactions table records every change: purchases that add stock and sales that remove it. Keeping transactions separate from products gives us a full audit trail.
Create an in-memory SQLite database with two tables:
1. products: columns id (INTEGER PRIMARY KEY AUTOINCREMENT), name (TEXT NOT NULL UNIQUE), category (TEXT NOT NULL), price (REAL NOT NULL), quantity (INTEGER DEFAULT 0), reorder_level (INTEGER DEFAULT 10)
2. transactions: columns id (INTEGER PRIMARY KEY AUTOINCREMENT), product_id (INTEGER NOT NULL), type (TEXT NOT NULL), quantity (INTEGER NOT NULL), timestamp (DATETIME DEFAULT CURRENT_TIMESTAMP), with a FOREIGN KEY on product_id
Print "Schema created" after committing, then print the table names.
Step 2: Add Products to Inventory
With the schema in place, we need a function that inserts new products. A good function handles errors gracefully — what if someone tries to add a product that already exists? What if the price is negative?
Write a function add_product(cursor, name, category, price, quantity, reorder_level) that:
1. Inserts a new product into the products table.
2. Returns the new product's ID (use cursor.lastrowid).
3. If the product name already exists (IntegrityError), print "Product already exists: {name}" and return None.
After defining the function, create the schema, add 3 products (e.g., "Laptop" in "Electronics" at 999.99 with qty 50, "Mouse" in "Electronics" at 29.99 with qty 200, "Desk Chair" in "Furniture" at 249.99 with qty 30), and print each product's ID. Use reorder_level=10 for all.
Step 3: Record Buy and Sell Transactions
Transactions are the heart of inventory management. Every time you buy stock from a supplier, the quantity goes up. Every time you sell to a customer, it goes down. We need a function that records the transaction and updates the product's quantity in one operation.
Write a function record_transaction(cursor, product_id, trans_type, quantity) that:
1. If trans_type is "buy": increase the product's quantity and insert a transaction record.
2. If trans_type is "sell": check current stock first. If stock >= quantity, decrease it and insert a record. Otherwise, print "Insufficient stock for product {product_id}" and return False.
3. Return True on success, False on failure.
Test it by adding a product, buying 100 units, selling 30 units, then attempting to sell 200 units (should fail). Print the product's quantity after each operation.
Step 4: Check Stock Levels
A warehouse manager needs to see stock levels at a glance. Let's build a function that queries the products table and returns a formatted stock report. We can also calculate the total inventory value (quantity times price for each product).
Write a function get_stock_report(cursor) that:
1. Queries all products and returns a list of dictionaries with keys: name, category, quantity, price, value (quantity * price).
2. Prints a formatted stock report with headers.
3. Prints the total inventory value at the end.
Format: "=== STOCK REPORT ===" header, then each product as "{name}: {quantity} units @ ${price:.2f} = ${value:.2f}", then "Total inventory value: ${total:.2f}".
Set up a database with at least 3 products (with quantities > 0) and call the function.
Step 5: Low Stock Alerts
Running out of stock means lost sales. Each product has a reorder_level — when quantity drops to or below this level, the system should raise an alert. This is one of the most valuable features of any inventory system.
Write a function check_low_stock(cursor) that:
1. Queries all products where quantity <= reorder_level.
2. Prints "=== LOW STOCK ALERTS ===" as a header.
3. For each low-stock product, prints "ALERT: {name} has {quantity} units (reorder at {reorder_level})".
4. If no products are low, prints "All stock levels OK".
5. Returns the list of low-stock product names.
Set up a database with products at various stock levels (some below their reorder level) and test the function.
Step 6: Transaction History Report
Auditing is critical. You need to know exactly what happened to your inventory and when. By joining the transactions table with the products table, we can generate a human-readable history that shows product names instead of just IDs.
The code below is supposed to generate a transaction history report, but it has bugs. Fix all the bugs so it correctly:
1. Joins transactions with products to show product names.
2. Prints "=== TRANSACTION HISTORY ===" as a header.
3. Prints each transaction as "{type}: {quantity} x {product_name}".
4. Prints the total number of transactions at the end.
Step 7: Build the Complete InventorySystem Class
Now let's wrap everything into a clean, reusable class. The InventorySystem class will encapsulate the database connection, schema creation, and all the operations we've built. This is how professional developers organize database-backed systems.
Build an InventorySystem class that wraps all previous functionality:
1. __init__(self) — creates the :memory: database and schema
2. add_product(self, name, category, price, quantity) — inserts product, returns ID
3. buy(self, product_id, quantity) — records a buy transaction
4. sell(self, product_id, quantity) — records a sell (with stock check), prints "Insufficient stock" on failure
5. get_stock(self, product_id) — returns current quantity as integer
Test the class: create an instance, add "Widget" (category "Parts", price 9.99, quantity 0), buy 100, sell 40, print the final stock. Expected output should include "Stock: 60".
Project Complete!
You've built a complete inventory management system. Here's what you accomplished: