BP101T • Unit 5Topic 3 of 3⏱ 5 Min Read

3. SQLite Relational Database Fundamentals for Pharmacy Inventory

💡 Core Concept & Clinical Context:

Enterprise pharmacy stores and quality assurance laboratories require permanent, structured relational database storage. SQLite is a serverless, self-contained, zero-configuration SQL database engine built into Python's standard library via the `sqlite3` module. It allows creating tables, inserting batch monographs, executing queries, updating stock counts, and tracking expiry dates with ACID compliance.

Pharmaceutical Code & Calculation Examples:

1Complete Pharmacy Inventory & Expiry Tracker Database Script

Creating a SQLite table, inserting records, and querying for expiring drug stocks.

example_1.py

import sqlite3

# Connect to database file (or memory)
conn = sqlite3.connect(":memory:")  # In-memory for testing
cursor = conn.cursor()

# 1. Create Pharmacy Inventory Table
cursor.execute("""
CREATE TABLE IF NOT EXISTS inventory (
    item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    drug_name TEXT NOT NULL,
    batch_no TEXT UNIQUE,
    quantity_boxes INTEGER,
    mfg_date TEXT,
    exp_date TEXT,
    unit_price REAL
)
""")

# 2. Insert Batch Records
sample_batches = [
    ("Amoxicillin 500mg", "AMX2601", 150, "2026-01-10", "2028-01-10", 120.50),
    ("Paracetamol 650mg", "PCM2604", 500, "2026-02-15", "2029-02-15", 35.00),
    ("Azithromycin 500mg", "AZI2602", 80, "2025-06-01", "2026-12-01", 240.00)
]
cursor.executemany("INSERT INTO inventory (drug_name, batch_no, quantity_boxes, mfg_date, exp_date, unit_price) VALUES (?, ?, ?, ?, ?, ?)", sample_batches)
conn.commit()

# 3. Query records expiring before 2027
cursor.execute("SELECT drug_name, batch_no, exp_date, quantity_boxes FROM inventory WHERE exp_date < '2027-01-01'")
expiring_soon = cursor.fetchall()

print("--- URGENT: Batches Expiring in 2026 ---")
for row in expiring_soon:
    print(f"Drug: {row[0]} | Batch: {row[1]} | Expiry: {row[2]} | Stock: {row[3]} boxes")

conn.close()

📋Key Takeaways & Exam Highlights

SQL Commands: `CREATE TABLE`, `INSERT INTO`, `SELECT ... FROM ... WHERE`, `UPDATE`, `DELETE`.
Parameterized queries (`?` placeholders) prevent SQL injection attacks.
`conn.commit()` persists modifications to disk; `conn.close()` frees database file locks.

🇮🇳 हिन्दी व्याख्या (3. SQLite Relational Database Fundamentals for Pharmacy Inventory)

SQLite पायथन में पहले से मौजूद एक डेटाबेस इंजन है। इसके जरिए दवाओं की एक्सपायरी डेट, बैच नंबर और स्टॉक को सुरक्षित टेबल में स्टोर किया जाता है।