Python SQLite & SQL Fundamentals

Before writing a single line of SQL, it is critical to understand why databases exist and why plain text files (like .txt or .csv) are inadequate for serious applications.

The Critical Flaws of File-Based Storage:

When you store application data in JSON or CSV files, every update requires reading the entire file into RAM, modifying the data structure, and writing the entire file back to disk. This causes four fatal problems:

1. No Concurrency (Race Conditions): If two users attempt to purchase the last item in a store simultaneously, two Python threads will read the file at the same time, see 1 item in stock, and both write back 0, selling 2 items when only 1 existed!
2. No Indexing ($O(N)$ Search Penalty): To find a user with ID 98421 in a 1,000,000-line CSV, Python must scan every single line from top to bottom ($O(N)$ time). Databases use B-Trees to find records in $O(log N)$ microsecond time.
3. No Data Integrity Constraints: Plain files cannot stop someone from writing a string into an "age" column or creating an order for a user ID that doesn't exist.
4. Lack of Crash Recovery (Atomicity): If power cuts out while rewriting a 50 MB CSV file, the file becomes corrupted and all data is permanently lost.

The Relational Model (Tables, Rows, Columns & Keys):

A Relational Database Management System (RDBMS) organizes information into two-dimensional Tables (also called Relations):

• Columns (Attributes/Fields): Define the schema and data type of each property (e.g. id: INTEGER, name: TEXT, price: REAL).
• Rows (Records/Tuples): Individual data entries representing a single real-world entity.
• Primary Key (PK): A unique column (usually an auto-incrementing integer or UUID) that guarantees every row in the table can be uniquely identified. No two rows can have the same Primary Key.
• Foreign Key (FK): A column in one table that points directly to the Primary Key of another table, establishing a verifiable relationship (e.g. orders.customer_id references customers.id).

# Conceptual demonstration: The building blocks of relational data
schema_explanation = {
    "Table": "A structured spreadsheet-like grid representing an entity (e.g. Products, Users)",
    "Column": "A typed field (e.g. price: REAL, email: TEXT UNIQUE)",
    "Row": "A single concrete instance/record stored in the table",
    "Primary Key": "Uniquely identifies each row (e.g. user_id = 101)",
    "Foreign Key": "Enforces relationship between tables (e.g. order.user_id -> user.id)"
}

for concept, definition in schema_explanation.items():
    print(f"📌 {concept:15}: {definition}")

Most relational databases (such as PostgreSQL, MySQL, Oracle, and Microsoft SQL Server) operate as Client-Server systems:

• They run as continuous background daemon processes on dedicated ports (e.g. port 5432 for Postgres, 3306 for MySQL).
• Your Python program must establish a network TCP/IP socket connection, transmit credentials, and send queries across the network wire.

SQLite is fundamentally different: It is Serverless and Embedded.

The entire SQLite database engine is written in ANSI C and compiled directly into the Python interpreter itself. When your Python code queries SQLite, there are zero network calls, zero socket overhead, and zero port configurations. The entire database is stored in a single standalone binary file on your hard disk (or in RAM with ":memory:").

When should you use SQLite?

• Desktop and Mobile Applications: SQLite is the internal database engine used inside Android, iOS, Windows 11, macOS, Google Chrome, and Firefox.
• Local Testing & Prototyping: Develop and run automated unit tests at lightning speed using in-memory databases (":memory:").
• Low-to-Medium Traffic Web Apps: Websites handling up to ~100,000 requests/day with mostly read operations perform exceptionally well on SQLite (using Write-Ahead Logging / WAL mode).

import sqlite3

# 1. Establish connection to local SQLite database file:
# If 'company.db' does not exist, SQLite automatically creates it on disk!
conn = sqlite3.connect("company.db")

# 2. Create a Cursor:
# The cursor acts as our execution pointer to send SQL commands to the engine:
cursor = conn.cursor()

# 3. Create 'departments' table using DDL (Data Definition Language):
cursor.execute("""
CREATE TABLE IF NOT EXISTS departments (
    dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dept_name TEXT NOT NULL UNIQUE,
    budget REAL NOT NULL
)
""")

# 4. Create 'employees' table with Foreign Key linking to departments:
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
    emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    salary REAL NOT NULL,
    dept_id INTEGER,
    joined_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
)
""")

conn.commit() # Flush and save schema changes permanently to disk
print("✅ Database 'company.db' initialized with relational departments and employees tables!")
conn.close()

CRUD stands for the four essential data operations in software engineering: Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE).

1. Inserting Records (Single vs Batch executemany):

Every time you execute an individual INSERT with a commit, SQLite must sync bytes to the physical storage disk (fsync). If you insert 1,000 records one by one in a loop, it will take several seconds.

By using cursor.executemany(), all 1,000 records are prepared and inserted in a single disk I/O operation in under 5 milliseconds!

2. Querying Data & Row Factories (sqlite3.Row):

By default, SQLite returns query results as plain tuples: (1, 'Balaji', 95000.0). Accessing fields by numerical indices like row[1] or row[2] makes code brittle and unreadable.

By configuring conn.row_factory = sqlite3.Row, SQLite returns rich mapping objects that allow accessing columns both by case-insensitive column name (row['full_name']) and by index!

import sqlite3

# Connect to database and configure row_factory:
conn = sqlite3.connect("company.db")
conn.row_factory = sqlite3.Row  # Enables column-name dictionary access!
cursor = conn.cursor()

# Enable SQLite foreign key enforcement:
cursor.execute("PRAGMA foreign_keys = ON")

# 1. CREATE: Batch Insert Departments:
departments_data = [
    ("Engineering", 5000000.0),
    ("Product Design", 2000000.0),
    ("Human Resources", 1200000.0)
]
cursor.executemany("INSERT OR IGNORE INTO departments (dept_name, budget) VALUES (?, ?)", departments_data)

# Batch Insert Employees:
employees_data = [
    ("Balaji Dev", "balaji.dev@company.com", 95000.0, 1),
    ("Alex Smith", "alex.smith@company.com", 82000.0, 1),
    ("Chloe Davis", "chloe.d@company.com", 78000.0, 2),
    ("David Miller", "david.m@company.com", 65000.0, 3)
]
cursor.executemany("INSERT OR IGNORE INTO employees (full_name, email, salary, dept_id) VALUES (?, ?, ?, ?)", employees_data)
conn.commit()

# 2. READ: Query Employees joined with their Department names:
print("--- 📋 Active Employees Report (SQL JOIN Query) ---")
cursor.execute("""
SELECT e.emp_id, e.full_name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary >= ?
ORDER BY e.salary DESC
""", (70000.0,))

high_earners = cursor.fetchall()
for emp in high_earners:
    # Notice clean dictionary-like column access:
    print(f"• ID #{emp['emp_id']}: {emp['full_name']:18} | Dept: {emp['dept_name']:15} | Salary: ₹{emp['salary']:,.2f}")

# 3. UPDATE: Give 10% raise to all Engineering employees:
cursor.execute("""
UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Engineering')
""")
print(f"\n📈 Promoted {cursor.rowcount} engineering employees with a 10% salary raise!")

# 4. DELETE: Remove an employee record safely:
cursor.execute("DELETE FROM employees WHERE email = ?", ("david.m@company.com",))
print(f"🗑️ Deleted {cursor.rowcount} employee record.")

conn.commit()
conn.close()