Python 24-Day Course - Day 19: Database with SQLite

Day 19: Database with SQLite

Connecting to SQLite and Creating Tables

import sqlite3

conn = sqlite3.connect("myapp.db")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        age INTEGER
    )
""")
conn.commit()

Basic CRUD Operations

# Create: Insert data
cursor.execute(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
    ("Alice", "alice@example.com", 25)
)
conn.commit()

# Read: Query data
cursor.execute("SELECT * FROM users WHERE age >= ?", (20,))
rows = cursor.fetchall()
for row in rows:
    print(row)

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

# Delete: Remove data
cursor.execute("DELETE FROM users WHERE name = ?", ("Alice",))
conn.commit()

Inserting Multiple Rows

users_data = [
    ("Bob", "bob@example.com", 28),
    ("Charlie", "charlie@example.com", 22),
    ("Diana", "diana@example.com", 30),
]

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

Dictionary Results with Row Factory

conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(f"{row['name']}: {row['email']}")

Safe Usage with Context Manager

def get_users(min_age=0):
    with sqlite3.connect("myapp.db") as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute(
            "SELECT * FROM users WHERE age >= ? ORDER BY name",
            (min_age,)
        )
        return [dict(row) for row in cursor.fetchall()]

users = get_users(25)
for user in users:
    print(user)

Today’s Exercises

1. Design a todo app database and implement CRUD functions.
2. Create an expense tracker table and query income/expense records with monthly totals.
3. Write a query that JOINs two tables (users and orders).