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.
import sqlite3 # Create in-memory database and define schema
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.
import sqlite3
def add_product(cursor, name, category, price, quantity, reorder_level=10):
# Insert product and return its ID, handle duplicates
pass
# Create database and schema, then add products
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.
import sqlite3
def record_transaction(cursor, product_id, trans_type, quantity):
# Record buy/sell transaction and update stock
pass
# Set up database, add product, test transactions
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.
import sqlite3
def get_stock_report(cursor):
# Query products and print formatted stock report
pass
# Set up database, add products with stock, generate report
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.
import sqlite3
def check_low_stock(cursor):
# Find and report products below reorder level
pass
# Set up database with mixed stock levels and test alerts
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.
import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, category TEXT, price REAL, quantity INTEGER DEFAULT 0, reorder_level INTEGER DEFAULT 10)')
cursor.execute('CREATE TABLE transactions (id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER, type TEXT, quantity INTEGER, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)')
cursor.execute('INSERT INTO products (name, category, price, quantity) VALUES (?, ?, ?, ?)', ('Laptop', 'Electronics', 999.99, 0))
cursor.execute('INSERT INTO products (name, category, price, quantity) VALUES (?, ?, ?, ?)', ('Mouse', 'Electronics', 29.99, 0))
# Record some transactions
for pid, ttype, qty in [(1, 'buy', 50), (2, 'buy', 200), (1, 'sell', 10), (2, 'sell', 25)]:
cursor.execute('INSERT INTO transactions (product_id, type, quantity) VALUES (?, ?, ?)', (pid, ttype, qty))
conn.commit()
def transaction_history(cursor):
# BUG 1: Wrong JOIN syntax
cursor.execute('''
SELECT t.type, t.quantity, p.name
FROM transactions t
JOIN products ON t.product_id = products.id
ORDER BY t.id
''')
rows = cursor.fetchall()
# BUG 2: Wrong header
print('=== TRANSACTIONS ===')
for row in rows:
# BUG 3: Wrong index order for type, quantity, name
print(f' {row[2]}: {row[1]} x {row[0]}')
# BUG 4: Wrong count variable
print(f'\nTotal transactions: {len(row)}')
transaction_history(cursor)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".
import sqlite3
class InventorySystem:
def __init__(self):
# Create database and schema
pass
def add_product(self, name, category, price, quantity=0):
pass
def buy(self, product_id, quantity):
pass
def sell(self, product_id, quantity):
pass
def get_stock(self, product_id):
pass
# Test the complete system
Project Complete!
You've built a complete inventory management system. Here's what you accomplished: