Database Basics with SQLite & Python

import sqlite3

# Connect (creates file if not exists)
conn = sqlite3.connect(":memory:")  # in-memory for demo
cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        age INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# Insert data
users = [
    ("Alice", "alice@email.com", 25),
    ("Bob", "bob@email.com", 30),
    ("Charlie", "charlie@email.com", 28),
    ("Diana", "diana@email.com", 22),
]

cursor.executemany(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    users
)
conn.commit()

# Query data
print("=== All Users ===")
cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(f"  {row}")

# Filtered query
print("\n=== Users over 25 ===")
cursor.execute("SELECT name, age FROM users WHERE age > ?", (25,))
for name, age in cursor.fetchall():
    print(f"  {name}: {age}")

# Update
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, "Alice"))
conn.commit()

# Aggregate queries
cursor.execute("SELECT AVG(age), MAX(age), MIN(age), COUNT(*) FROM users")
avg, max_age, min_age, count = cursor.fetchone()
print(f"\n=== Stats ===")
print(f"  Count: {count}")
print(f"  Avg age: {avg:.1f}")
print(f"  Range: {min_age}-{max_age}")

# Using Row factory for dict-like access
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
user = cursor.fetchone()
print(f"\n=== Dict Access ===")
print(f"  {dict(user)}")

conn.close()