Inventory System Example

A product inventory system with categories, stock tracking, and search.

Full Source

  1"""
  2Inventory System Example - sqbooster
  3
  4A product inventory with categories, stock tracking, and search.
  5Uses auto-created tables for simple data, explicit schemas for constraints.
  6"""
  7
  8import sqbooster
  9from sqbooster.schema import Column
 10from sqbooster.types import Integer, Text, Float, Boolean
 11
 12
 13def create_schema(db):
 14    db.create_table("categories", [
 15        Column("id", Integer(), primary_key=True, autoincrement=True),
 16        Column("name", Text(), unique=True, nullable=False),
 17    ])
 18
 19    db.create_table("products", [
 20        Column("id", Integer(), primary_key=True, autoincrement=True),
 21        Column("name", Text(), nullable=False),
 22        Column("sku", Text(), unique=True, nullable=False),
 23        Column("price", Float(), nullable=False),
 24        Column("stock", Integer(), default=0),
 25        Column("category_id", Integer()),
 26        Column("active", Boolean(), default=True),
 27    ])
 28
 29
 30def seed_data(db):
 31    db.insert_many("categories", [
 32        {"name": "Electronics"},
 33        {"name": "Books"},
 34        {"name": "Clothing"},
 35    ])
 36
 37    db.insert_many("products", [
 38        {"name": "Laptop", "sku": "ELEC-001", "price": 999.99, "stock": 15, "category_id": 1},
 39        {"name": "Mouse", "sku": "ELEC-002", "price": 29.99, "stock": 200, "category_id": 1},
 40        {"name": "Keyboard", "sku": "ELEC-003", "price": 79.99, "stock": 50, "category_id": 1},
 41        {"name": "Python Cookbook", "sku": "BOOK-001", "price": 45.00, "stock": 30, "category_id": 2},
 42        {"name": "Clean Code", "sku": "BOOK-002", "price": 35.00, "stock": 25, "category_id": 2},
 43        {"name": "T-Shirt", "sku": "CLO-001", "price": 19.99, "stock": 100, "category_id": 3},
 44    ])
 45
 46
 47def main():
 48    db = sqbooster.sqlite()
 49
 50    print("=== Inventory System Example ===\n")
 51
 52    create_schema(db)
 53    seed_data(db)
 54
 55    print("--- All Products ---")
 56    for p in db.query("products").order_by("name").all():
 57        print(f"  {p['sku']:12s}  {p['name']:15s}  ${p['price']:>8.2f}  stock={p['stock']}")
 58
 59    print("\n--- Electronics Under $100 ---")
 60    cheap = db.query("products").filter(
 61        category_id=1,
 62        price__lt=100.0,
 63    ).order_by("price").all()
 64    for p in cheap:
 65        print(f"  {p['name']:15s}  ${p['price']:.2f}")
 66
 67    print("\n--- Low Stock (< 30 units) ---")
 68    low = db.query("products").filter(stock__lt=30).all()
 69    for p in low:
 70        print(f"  {p['name']:15s}  stock={p['stock']}")
 71
 72    print("\n--- Search: 'book' in name ---")
 73    books = db.query("products").filter(name__contains="ook").all()
 74    for p in books:
 75        print(f"  {p['name']}")
 76
 77    print("\n--- Products $20 - $50 ---")
 78    mid = db.query("products").filter(
 79        price__gte=20, price__lte=50
 80    ).all()
 81    for p in mid:
 82        print(f"  {p['name']:15s}  ${p['price']:.2f}")
 83
 84    all_products = db.query("products").all()
 85    total_value = sum(p["price"] * p["stock"] for p in all_products)
 86    print(f"\n--- Total Inventory Value: ${total_value:,.2f} ---")
 87
 88    db.update("products", {"stock": 14}, sku="ELEC-001")
 89    laptop = db.query("products").filter(sku="ELEC-001").one()
 90    print(f"\nLaptop stock after sale: {laptop['stock']}")
 91
 92    db.update("products", {"active": False}, stock=0)
 93    active_count = db.query("products").filter(active=True).count()
 94    print(f"Active products: {active_count}")
 95
 96    db.close()
 97    print("\nDone!")
 98
 99
100if __name__ == "__main__":
101    main()

What It Demonstrates

  • Typed columns for different data types

  • Query chaining with multiple filters

  • Aggregation via count queries

  • Bulk inserts with insert_many