Chapter 15: Database Fundamentals with SQLite

import requests

API_KEY = "YOUR_OPENWEATHERMAP_API_KEY"

def get_weather(location):
    response = requests.get(
        "https://api.openweathermap.org/data/2.5/weather",
        params={"q": location, "appid": API_KEY, "units": "metric"},
        timeout=10
    )
    return response.json()

weather = get_weather("Dublin")
print(f"Temperature: {weather['main']['temp']}°C")
print(f"Conditions: {weather['weather'][0]['description']}")

# Script ends. Data disappears.

import requests
import sqlite3
from datetime import datetime, timezone

API_KEY = "YOUR_OPENWEATHERMAP_API_KEY"

def get_weather(location):
    response = requests.get(
        "https://api.openweathermap.org/data/2.5/weather",
        params={"q": location, "appid": API_KEY, "units": "metric"},
        timeout=10
    )
    data = response.json()
    
    # Eight new lines - save to database
    with sqlite3.connect('weather.db') as conn:
        conn.execute("""
            INSERT INTO weather_history (location, temperature, conditions, timestamp)
            VALUES (?, ?, ?, ?)
        """, (
            location,
            data['main']['temp'],
            data['weather'][0]['description'],
            datetime.now(timezone.utc)
        ))
    
    return data

# Today's weather (from API)
weather = get_weather("Dublin")
print(f"Today: {weather['main']['temp']}°C - {weather['weather'][0]['description']}")

# Yesterday's weather (from database)
with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT temperature, conditions 
        FROM weather_history 
        WHERE location = ? AND date(timestamp) = date('now', '-1 day')
    """, ("Dublin",))
    yesterday = cursor.fetchone()
    if yesterday:
        print(f"Yesterday: {yesterday[0]}°C - {yesterday[1]}")

# Weekly temperature trend (from database)
with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT date(timestamp), AVG(temperature)
        FROM weather_history 
        WHERE location = ? AND timestamp >= date('now', '-7 days')
        GROUP BY date(timestamp)
        ORDER BY date(timestamp)
    """, ("Dublin",))
    
    print("\nLast 7 days:")
    for day, avg_temp in cursor.fetchall():
        print(f"  {day}: {avg_temp:.1f}°C")

weather_history table
┌────┬──────────┬─────────────┬────────────────┬─────────────────────┐
│ id │ location │ temperature │ conditions     │ timestamp           │
├────┼──────────┼─────────────┼────────────────┼─────────────────────┤
│ 1  │ Dublin   │ 12.5        │ Cloudy         │ 2024-03-15 14:30:22 │
│ 2  │ London   │ 15.2        │ Rain           │ 2024-03-15 14:35:10 │
│ 3  │ Dublin   │ 13.1        │ Partly Cloudy  │ 2024-03-16 09:15:44 │
│ 4  │ Paris    │ 18.0        │ Sunny          │ 2024-03-16 10:22:33 │
└────┴──────────┴─────────────┴────────────────┴─────────────────────┘

Each row = one weather reading from the API
Each column = one piece of information about that reading

import sqlite3

with sqlite3.connect('weather.db') as conn:
    conn.execute("""
        CREATE TABLE IF NOT EXISTS weather_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            location TEXT NOT NULL,
            temperature REAL,
            conditions TEXT,
            humidity INTEGER,
            wind_speed REAL,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)

import sqlite3
from datetime import datetime

# Fetch weather data from API (simplified)
weather_data = {
    "location": "Dublin",
    "temperature": 12.5,
    "conditions": "Cloudy",
    "humidity": 75,
    "wind_speed": 4.5
}

# Store in database
with sqlite3.connect('weather.db') as conn:
    conn.execute("""
        INSERT INTO weather_history (location, temperature, conditions, humidity, wind_speed, timestamp)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (
        weather_data["location"],
        weather_data["temperature"],
        weather_data["conditions"],
        weather_data["humidity"],
        weather_data["wind_speed"],
        datetime.now()
    ))

weather_readings = [
    ("Dublin", 12.5, "Cloudy", 75, 4.5, datetime.now()),
    ("London", 15.2, "Rain", 85, 6.2, datetime.now()),
    ("Paris", 18.0, "Sunny", 60, 3.1, datetime.now())
]

with sqlite3.connect('weather.db') as conn:
    conn.executemany("""
        INSERT INTO weather_history (location, temperature, conditions, humidity, wind_speed, timestamp)
        VALUES (?, ?, ?, ?, ?, ?)
    """, weather_readings)
    print(f"Inserted {len(weather_readings)} weather readings")

import sqlite3

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("SELECT * FROM weather_history")
    all_weather = cursor.fetchall()
    
    for row in all_weather:
        print(row)

(1, 'Dublin', 12.5, 'Cloudy', 75, 4.5, '2024-03-15 14:30:22')
(2, 'London', 15.2, 'Rain', 85, 6.2, '2024-03-15 14:35:10')
(3, 'Paris', 18.0, 'Sunny', 60, 3.1, '2024-03-16 10:22:33')

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, temperature, conditions 
        FROM weather_history
    """)
    
    for location, temp, conditions in cursor:
        print(f"{location}: {temp}°C, {conditions}")

Dublin: 12.5°C, Cloudy
London: 15.2°C, Rain
Paris: 18.0°C, Sunny

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT temperature, conditions, timestamp
        FROM weather_history
        WHERE location = ?
    """, ("Dublin",))
    
    print("Dublin weather history:")
    for temp, conditions, timestamp in cursor:
        print(f"  {timestamp}: {temp}°C, {conditions}")

Dublin weather history:
  2024-03-15 14:30:22: 12.5°C, Cloudy
  2024-03-16 09:15:44: 13.1°C, Partly Cloudy

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, temperature, conditions
        FROM weather_history
        WHERE temperature > 15 AND temperature < 20
    """)
    
    print("Moderate temperatures (15-20°C):")
    for location, temp, conditions in cursor:
        print(f"  {location}: {temp}°C")

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, temperature, conditions, timestamp
        FROM weather_history
        WHERE timestamp > datetime('now', '-24 hours')
    """)
    
    print("Weather from last 24 hours:")
    for location, temp, conditions, timestamp in cursor:
        print(f"  {timestamp} | {location}: {temp}°C")

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, temperature, conditions
        FROM weather_history
        ORDER BY temperature DESC
    """)
    
    print("Hottest to coldest:")
    for location, temp, conditions in cursor:
        print(f"  {location}: {temp}°C")

Hottest to coldest:
  Paris: 18.0°C
  London: 15.2°C
  Dublin: 13.1°C
  Dublin: 12.5°C

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT temperature, conditions, timestamp
        FROM weather_history
        WHERE location = ?
        ORDER BY timestamp DESC
        LIMIT 1
    """, ("Dublin",))
    
    result = cursor.fetchone()
    if result:
        temp, conditions, timestamp = result
        print(f"Latest Dublin weather: {temp}°C, {conditions} (at {timestamp})")
    else:
        print("No weather data found for Dublin")

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, temperature, timestamp
        FROM weather_history
        ORDER BY temperature DESC
        LIMIT 5
    """)
    
    print("Top 5 hottest readings:")
    for location, temp, timestamp in cursor:
        print(f"  {location}: {temp}°C")

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT AVG(temperature), MIN(temperature), MAX(temperature)
        FROM weather_history
        WHERE location = ?
    """, ("Dublin",))
    
    avg_temp, min_temp, max_temp = cursor.fetchone()
    print(f"Dublin temperature statistics:")
    print(f"  Average: {avg_temp:.1f}°C")
    print(f"  Range: {min_temp}°C to {max_temp}°C")

Dublin temperature statistics:
  Average: 12.8°C
  Range: 12.5°C to 13.1°C

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, COUNT(*) as reading_count
        FROM weather_history
        GROUP BY location
        ORDER BY reading_count DESC
    """)
    
    print("Weather readings per city:")
    for location, count in cursor:
        print(f"  {location}: {count} readings")

Weather readings per city:
  Dublin: 2 readings
  London: 1 reading
  Paris: 1 reading

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT date(timestamp) as day, AVG(temperature) as avg_temp
        FROM weather_history
        WHERE location = ?
        GROUP BY date(timestamp)
        ORDER BY day
    """, ("Dublin",))
    
    print("Dublin daily average temperatures:")
    for day, avg_temp in cursor:
        print(f"  {day}: {avg_temp:.1f}°C")

with sqlite3.connect('weather.db') as conn:
    conn.execute("""
        UPDATE weather_history
        SET temperature = ?, conditions = ?
        WHERE id = ?
    """, (14.0, "Partly Cloudy", 1))
    
    print("Updated weather reading #1")

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        DELETE FROM weather_history
        WHERE timestamp < datetime('now', '-30 days')
    """)
    
    deleted_count = cursor.rowcount
    print(f"Deleted {deleted_count} old weather readings")

with sqlite3.connect('weather.db') as conn:
    conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_location 
        ON weather_history(location)
    """)
    print("Created index on location column")

with sqlite3.connect('weather.db') as conn:
    conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_location_timestamp 
        ON weather_history(location, timestamp DESC)
    """)
    print("Created composite index on location and timestamp")

with sqlite3.connect('weather.db') as conn:
    # Locations table
    conn.execute("""
        CREATE TABLE IF NOT EXISTS locations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            city TEXT NOT NULL,
            country TEXT NOT NULL,
            timezone TEXT
        )
    """)
    
    # Weather readings reference locations by ID
    conn.execute("""
        CREATE TABLE IF NOT EXISTS weather_readings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            location_id INTEGER NOT NULL,
            temperature REAL,
            conditions TEXT,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (location_id) REFERENCES locations(id)
        )
    """)

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT locations.city, locations.country, 
               weather_readings.temperature, weather_readings.conditions,
               weather_readings.timestamp
        FROM weather_readings
        JOIN locations ON weather_readings.location_id = locations.id
        ORDER BY weather_readings.timestamp DESC
        LIMIT 5
    """)
    
    print("Recent weather readings:")
    for city, country, temp, conditions, timestamp in cursor:
        print(f"  {city}, {country}: {temp}°C, {conditions} ({timestamp})")

import sqlite3

# Open connection
conn = sqlite3.connect('weather.db')

# Use the connection
cursor = conn.execute("SELECT * FROM weather_history LIMIT 5")
for row in cursor:
    print(row)

# Close connection
conn.close()

import sqlite3

def test_weather_cache():
    """Test the weather cache logic without creating files."""
    # Use :memory: instead of a filename
    with sqlite3.connect(':memory:') as conn:
        # Create the schema
        conn.execute("""
            CREATE TABLE weather_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                location TEXT NOT NULL,
                temperature REAL,
                conditions TEXT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Insert test data
        conn.execute("""
            INSERT INTO weather_history (location, temperature, conditions)
            VALUES (?, ?, ?)
        """, ("Dublin", 12.5, "Cloudy"))
        
        # Query it back
        cursor = conn.execute("""
            SELECT temperature, conditions 
            FROM weather_history 
            WHERE location = ?
        """, ("Dublin",))
        
        result = cursor.fetchone()
        assert result[0] == 12.5
        assert result[1] == "Cloudy"
        
        print("✓ Test passed!")
    
    # Database is gone now - no cleanup needed

test_weather_cache()

import sqlite3
from datetime import datetime, timedelta, timezone

def test_cache_freshness():
    """Test that cache correctly identifies fresh vs stale data."""
    with sqlite3.connect(':memory:') as conn:
        conn.execute("""
            CREATE TABLE weather_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                location TEXT NOT NULL,
                temperature REAL,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Insert fresh data (recent)
        conn.execute("""
            INSERT INTO weather_history (location, temperature, timestamp)
            VALUES (?, ?, ?)
        """, ("Dublin", 12.5, datetime.now(timezone.utc)))
        
        # Insert stale data (2 hours ago)
        stale_timestamp = datetime.now(timezone.utc) - timedelta(hours=2)
        conn.execute("""
            INSERT INTO weather_history (location, temperature, timestamp)
            VALUES (?, ?, ?)
        """, ("London", 15.0, stale_timestamp))
        
        # Check Dublin cache (should be fresh)
        cursor = conn.execute("""
            SELECT temperature 
            FROM weather_history 
            WHERE location = ? 
              AND timestamp > datetime('now', '-1 hour')
            ORDER BY timestamp DESC 
            LIMIT 1
        """, ("Dublin",))
        
        dublin_result = cursor.fetchone()
        assert dublin_result is not None, "Dublin cache should be fresh"
        print(f"✓ Dublin cache is fresh: {dublin_result[0]}°C")
        
        # Check London cache (should be stale/missing)
        cursor = conn.execute("""
            SELECT temperature 
            FROM weather_history 
            WHERE location = ? 
              AND timestamp > datetime('now', '-1 hour')
            ORDER BY timestamp DESC 
            LIMIT 1
        """, ("London",))
        
        london_result = cursor.fetchone()
        assert london_result is None, "London cache should be stale"
        print("✓ London cache correctly identified as stale")

test_cache_freshness()

import sqlite3
import tempfile
import os

def test_with_temp_file():
    """Test using a temporary file instead of in-memory."""
    # Create temporary database file
    temp_db_path = tempfile.mktemp(suffix='.db')
    
    try:
        conn = sqlite3.connect(temp_db_path)
        
        # Create schema and run tests
        conn.execute("""
            CREATE TABLE test_table (id INTEGER PRIMARY KEY, value TEXT)
        """)
        conn.execute("INSERT INTO test_table (value) VALUES (?)", ("test",))
        
        cursor = conn.execute("SELECT value FROM test_table")
        assert cursor.fetchone()[0] == "test"
        
        print("✓ Temporary file test passed")
        
        conn.close()
    finally:
        # Clean up: delete the temporary database file
        if os.path.exists(temp_db_path):
            os.remove(temp_db_path)

test_with_temp_file()

import sqlite3

conn = sqlite3.connect('weather.db')

# Create cursor explicitly
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT location, temperature FROM weather_history WHERE temperature > ?", (15,))

# Fetch results
results = cursor.fetchall()
for location, temp in results:
    print(f"{location}: {temp}°C")

cursor.close()
conn.close()

import sqlite3

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT temperature, conditions, timestamp
        FROM weather_history
        WHERE location = ?
        ORDER BY timestamp DESC
        LIMIT 1
    """, ("Dublin",))
    
    result = cursor.fetchone()
    if result:
        temp, conditions, timestamp = result
        print(f"Latest Dublin weather: {temp}°C, {conditions}")
    else:
        print("No weather data found for Dublin")

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, AVG(temperature) as avg_temp
        FROM weather_history
        GROUP BY location
        ORDER BY avg_temp DESC
    """)
    
    all_results = cursor.fetchall()
    
    print("Average temperatures by city:")
    for location, avg_temp in all_results:
        print(f"  {location}: {avg_temp:.1f}°C")

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("SELECT * FROM weather_history")
    
    batch_size = 100
    while True:
        rows = cursor.fetchmany(batch_size)
        if not rows:
            break
        
        # Process this batch
        for row in rows:
            # Do something with each row (export to CSV, send to API, etc.)
            process_row(row)
        
        print(f"Processed {len(rows)} rows")

import sqlite3

conn = sqlite3.connect('weather.db')

# Insert data
conn.execute("""
    INSERT INTO weather_history (location, temperature, conditions, timestamp)
    VALUES (?, ?, ?, ?)
""", ("Dublin", 14.2, "Sunny", "2024-03-17 10:00:00"))

# Changes are pending - not yet written to disk
# You must commit to make them permanent
conn.commit()

conn.close()

import sqlite3

conn = sqlite3.connect('weather.db')

try:
    # Insert multiple records as a transaction
    conn.execute("INSERT INTO weather_history (location, temperature) VALUES (?, ?)", 
                 ("Dublin", 14.2))
    conn.execute("INSERT INTO weather_history (location, temperature) VALUES (?, ?)", 
                 ("London", 16.5))
    conn.execute("INSERT INTO weather_history (location, temperature) VALUES (?, ?)", 
                 ("Paris", 18.0))
    
    # Commit all changes together
    conn.commit()
    print("Successfully inserted 3 weather records")
    
except sqlite3.Error as e:
    # Something went wrong - discard all changes
    conn.rollback()
    print(f"Error occurred: {e}. Changes rolled back.")
    
finally:
    conn.close()

import sqlite3

with sqlite3.connect('weather.db') as conn:
    conn.execute("""
        INSERT INTO weather_history (location, temperature, conditions)
        VALUES (?, ?, ?)
    """, ("Dublin", 14.2, "Sunny"))
    
    # Commit happens automatically if no exception occurs
    # Connection closes automatically when the with block ends

conn = sqlite3.connect('weather.db')
try:
    conn.execute("INSERT INTO weather_history (...) VALUES (...)", (...))
    conn.commit()
except Exception as e:
    conn.rollback()
    raise
finally:
    conn.close()

with sqlite3.connect('weather.db') as conn:
    conn.execute("INSERT INTO weather_history (...) VALUES (...)", (...))

import sqlite3

def save_weather(location, temperature, conditions):
    try:
        with sqlite3.connect('weather.db') as conn:
            conn.execute("""
                INSERT INTO weather_history (location, temperature, conditions, timestamp)
                VALUES (?, ?, ?, datetime('now'))
            """, (location, temperature, conditions))
            print(f"Saved weather for {location}")
            
    except sqlite3.IntegrityError as e:
        # Constraint violation (duplicate key, foreign key, NOT NULL, etc.)
        print(f"Cannot save weather: {e}")
        print("Check that all required fields are provided and constraints are satisfied")
        
    except sqlite3.OperationalError as e:
        # Database locked, table doesn't exist, syntax error
        print(f"Database operation failed: {e}")
        print("Ensure the database file is accessible and not locked by another process")
        
    except sqlite3.DatabaseError as e:
        # Generic database error (catches all sqlite3 errors)
        print(f"Database error: {e}")
        
    except Exception as e:
        # Unexpected error (network issues, disk full, etc.)
        print(f"Unexpected error: {e}")

# DANGEROUS - vulnerable to SQL injection
location = user_input  # Could be: "Dublin'; DROP TABLE weather_history; --"
conn.execute(f"SELECT * FROM weather_history WHERE location = '{location}'")

# SAFE - parameterized query prevents SQL injection
location = user_input
conn.execute("SELECT * FROM weather_history WHERE location = ?", (location,))

def save_weather(location, temperature, conditions):
    # Validate data before database operation
    if not location:
        raise ValueError("Location is required")
    
    if not isinstance(temperature, (int, float)):
        raise ValueError(f"Temperature must be numeric, got {type(temperature)}")
    
    if temperature < -100 or temperature > 100:
        raise ValueError(f"Temperature {temperature}°C is outside valid range")
    
    # Now insert with confidence
    with sqlite3.connect('weather.db') as conn:
        conn.execute("""
            INSERT INTO weather_history (location, temperature, conditions, timestamp)
            VALUES (?, ?, ?, datetime('now'))
        """, (location, temperature, conditions))

with sqlite3.connect('weather.db') as conn:
    cursor = conn.execute("""
        SELECT location, temperature, conditions, timestamp 
        FROM weather_history 
        WHERE location = ?
        ORDER BY timestamp DESC 
        LIMIT 1
    """, ("Dublin",))

    row = cursor.fetchone()
    if row:
        # Which index is which? You have to count columns in the SELECT
        print(f"{row[0]}: {row[1]}°C, {row[2]}")
        # What if you change the SELECT order? All indices break

with sqlite3.connect('weather.db') as conn:
    # Enable Row factory for dictionary-like access
    conn.row_factory = sqlite3.Row

    cursor = conn.execute("""
        SELECT location, temperature, conditions, timestamp 
        FROM weather_history 
        WHERE location = ?
        ORDER BY timestamp DESC 
        LIMIT 1
    """, ("Dublin",))

    row = cursor.fetchone()
    if row:
        # Column names are explicit - much clearer!
        print(f"{row['location']}: {row['temperature']}°C, {row['conditions']}")

        # You can still use indices if needed
        print(f"First column: {row[0]}")

        # Works with dict() conversion for templates
        weather_data = dict(row)
        # {'location': 'Dublin', 'temperature': 12.5, 'conditions': 'cloudy', ...}

import sqlite3

def create_tables():
    """Create the weather cache table and indexes."""
    with sqlite3.connect('weather_cache.db') as conn:
        # Main weather table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS weather_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                location TEXT NOT NULL,
                temperature REAL NOT NULL,
                conditions TEXT,
                humidity INTEGER,
                wind_speed REAL,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                fetched_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Index for cache lookups (location + recent timestamp)
        conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_location_timestamp 
            ON weather_history(location, timestamp DESC)
        """)
        
        # Index for location-only queries
        conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_location 
            ON weather_history(location)
        """)
        
        print("Database tables and indexes created successfully")

# Run this once to set up the database
create_tables()

import requests
from datetime import datetime, timezone

API_KEY = "YOUR_OPENWEATHERMAP_API_KEY"  # Get free key at openweathermap.org

def fetch_weather_from_api(location):
    """Fetch current weather from OpenWeatherMap API."""
    try:
        response = requests.get(
            "https://api.openweathermap.org/data/2.5/weather",
            params={
                "q": location,
                "appid": API_KEY,
                "units": "metric"
            },
            timeout=10
        )
        response.raise_for_status()
        
        data = response.json()
        
        # Extract weather data with defensive parsing
        weather = {
            "location": location,
            "temperature": data.get("main", {}).get("temp"),
            "conditions": data.get("weather", [{}])[0].get("description", "Unknown"),
            "humidity": data.get("main", {}).get("humidity"),
            "wind_speed": data.get("wind", {}).get("speed"),
            "timestamp": datetime.now(timezone.utc)
        }
        
        return weather
        
    except requests.exceptions.Timeout:
        print(f"Request timed out for {location}")
        return None
        
    except requests.exceptions.RequestException as e:
        print(f"Error fetching weather for {location}: {e}")
        return None

def save_weather(weather):
    """Save weather data to the database."""
    if not weather or not weather.get("temperature"):
        print("Invalid weather data - not caching")
        return False
    
    try:
        with sqlite3.connect('weather_cache.db') as conn:
            conn.execute("""
                INSERT INTO weather_history 
                (location, temperature, conditions, humidity, wind_speed, timestamp, fetched_at)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                weather["location"],
                weather["temperature"],
                weather["conditions"],
                weather["humidity"],
                weather["wind_speed"],
                weather["timestamp"],
                datetime.now(timezone.utc)
            ))
            
        print(f"Cached weather for {weather['location']}: {weather['temperature']}°C")
        return True
        
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return False

def get_cached_weather(location, max_age_minutes=60):
    """
    Get weather from cache if fresh, otherwise return None.
    
    Args:
        location: City name
        max_age_minutes: Consider cache fresh if data is newer than this (default 60 minutes)
    
    Returns:
        Dictionary with weather data if cache is fresh, None otherwise
    """
    try:
        with sqlite3.connect('weather_cache.db') as conn:
            conn.row_factory = sqlite3.Row
            
            cursor = conn.execute("""
                SELECT location, temperature, conditions, humidity, wind_speed, 
                       timestamp, fetched_at
                FROM weather_history
                WHERE location = ?
                  AND fetched_at > datetime('now', ? || ' minutes')
                ORDER BY fetched_at DESC
                LIMIT 1
            """, (location, -max_age_minutes))
            
            row = cursor.fetchone()
            
            if row:
                print(f"Cache HIT for {location} (age: {max_age_minutes} min threshold)")
                return dict(row)
            else:
                print(f"Cache MISS for {location} (no recent data)")
                return None
                
    except sqlite3.Error as e:
        print(f"Database error: {e}")
        return None

def get_weather(location, max_age_minutes=60):
    """
    Get weather for a location, using cache if fresh or fetching from API if stale.
    
    This is the main function users call. It handles the cache-or-fetch logic
    automatically and always returns weather data (or None on total failure).
    """
    # Try cache first
    cached = get_cached_weather(location, max_age_minutes)
    if cached:
        return cached
    
    # Cache miss or stale - fetch from API
    print(f"Fetching fresh weather for {location} from API...")
    weather = fetch_weather_from_api(location)
    
    if weather:
        # Save to cache for next time
        save_weather(weather)
        return weather
    else:
        print(f"Failed to fetch weather for {location}")
        return None

# First call - fetches from API
weather = get_weather("Dublin")
if weather:
    print(f"\n{weather['location']}: {weather['temperature']}°C, {weather['conditions']}")

# Second call (immediate) - returns from cache
weather = get_weather("Dublin")
if weather:
    print(f"\n{weather['location']}: {weather['temperature']}°C, {weather['conditions']}")

Cache MISS for Dublin (no recent data)
Fetching fresh weather for Dublin from API...
Cached weather for Dublin: 12.5°C

Dublin: 12.5°C, light rain

Cache HIT for Dublin (age: 60 min threshold)

Dublin: 12.5°C, light rain

def get_temperature_trend(location, days=7):
    """Get daily average temperature for the last N days."""
    with sqlite3.connect('weather_cache.db') as conn:
        cursor = conn.execute("""
            SELECT date(timestamp) as day, 
                   AVG(temperature) as avg_temp,
                   COUNT(*) as reading_count
            FROM weather_history
            WHERE location = ?
              AND timestamp >= date('now', ? || ' days')
            GROUP BY date(timestamp)
            ORDER BY day
        """, (location, -days))
        
        results = cursor.fetchall()
        
        if not results:
            print(f"No historical data for {location} in the last {days} days")
            return []
        
        return results

def show_temperature_trend(location, days=7):
    """Display temperature trend for a location."""
    print(f"\n{location} - Temperature Trend (Last {days} Days)")
    print("=" * 50)
    
    trend = get_temperature_trend(location, days)
    
    if not trend:
        return
    
    for day, avg_temp, count in trend:
        print(f"{day}: {avg_temp:.1f}°C (from {count} readings)")
    
    # Calculate overall statistics
    temps = [avg_temp for _, avg_temp, _ in trend]
    if temps:
        print(f"\nOverall Average: {sum(temps) / len(temps):.1f}°C")
        print(f"Range: {min(temps):.1f}°C to {max(temps):.1f}°C")

# Show trend after collecting data for a week
show_temperature_trend("Dublin", days=7)

Dublin - Temperature Trend (Last 7 Days)
==================================================
2024-03-11: 11.2°C (from 24 readings)
2024-03-12: 12.8°C (from 24 readings)
2024-03-13: 13.5°C (from 24 readings)
2024-03-14: 12.1°C (from 24 readings)
2024-03-15: 14.3°C (from 24 readings)
2024-03-16: 13.9°C (from 24 readings)
2024-03-17: 12.6°C (from 18 readings)

Overall Average: 12.9°C
Range: 11.2°C to 14.3°C

def compare_cities():
    """Compare average temperatures across all cached cities."""
    with sqlite3.connect('weather_cache.db') as conn:
        cursor = conn.execute("""
            SELECT location,
                   AVG(temperature) as avg_temp,
                   MIN(temperature) as min_temp,
                   MAX(temperature) as max_temp,
                   COUNT(*) as total_readings
            FROM weather_history
            WHERE timestamp >= date('now', '-7 days')
            GROUP BY location
            ORDER BY avg_temp DESC
        """)
        
        print("\nCity Comparison (Last 7 Days)")
        print("=" * 70)
        print(f"{'City':<15} {'Avg Temp':<12} {'Min':<10} {'Max':<10} {'Readings':<10}")
        print("-" * 70)
        
        for location, avg_temp, min_temp, max_temp, count in cursor:
            print(f"{location:<15} {avg_temp:>8.1f}°C  {min_temp:>8.1f}°C  {max_temp:>8.1f}°C  {count:>8}")

compare_cities()

City Comparison (Last 7 Days)
======================================================================
City            Avg Temp     Min        Max        Readings  
----------------------------------------------------------------------
Paris               16.8°C      14.2°C      19.1°C       168
London              14.3°C      11.5°C      16.8°C       168
Dublin              12.9°C      11.2°C      14.3°C       162

"""
Weather API Cache - Complete Example
Demonstrates SQLite database integration with API calls
"""
import sqlite3
import requests
from datetime import datetime, timezone

API_KEY = "YOUR_OPENWEATHERMAP_API_KEY"

# [Include all the functions defined above:
#  - create_tables()
#  - fetch_weather_from_api()
#  - save_weather()
#  - get_cached_weather()
#  - get_weather()
#  - get_temperature_trend()
#  - show_temperature_trend()
#  - compare_cities()
# ]

def main():
    """Demonstrate the weather cache in action."""
    # Set up database
    print("Setting up weather cache database...")
    create_tables()
    
    # Track weather for multiple cities
    cities = ["Dublin", "London", "Paris"]
    
    print("\nFetching weather for cities...")
    for city in cities:
        weather = get_weather(city)
        if weather:
            print(f"  {city}: {weather['temperature']}°C, {weather['conditions']}")
    
    # Demonstrate cache hit
    print("\nFetching same cities again (should hit cache)...")
    for city in cities:
        weather = get_weather(city)
        if weather:
            print(f"  {city}: {weather['temperature']}°C, {weather['conditions']}")
    
    # Show analytics (after collecting data for a while)
    print("\n" + "=" * 70)
    compare_cities()

if __name__ == "__main__":
    main()

-- Good: INTEGER for countable values
play_count INTEGER,
track_duration_ms INTEGER,
release_year INTEGER,
user_age INTEGER

-- Bad: TEXT for numeric values
play_count TEXT,  -- Can't do: WHERE play_count > 10
release_year TEXT  -- Can't calculate age: 2024 - release_year

-- Good: REAL preserves decimals
temperature REAL,        -- 12.5, 12.7, 13.2
price REAL,             -- 9.99, 10.50
track_popularity REAL   -- 0.75, 0.82

-- Bad: INTEGER loses precision
temperature INTEGER     -- 12.5 becomes 12, 12.7 becomes 12

-- TEXT handles strings of any length
artist_name TEXT,
track_title TEXT,
album_url TEXT,
spotify_uri TEXT,
raw_api_response TEXT  -- Can store entire JSON responses

-- Good: DATETIME with DEFAULT for automatic timestamps
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
played_at DATETIME,
last_updated DATETIME

-- Works with date functions
WHERE played_at > datetime('now', '-7 days')
WHERE date(created_at) = date('now')

-- Frequent query: find tracks by artist
SELECT * FROM tracks WHERE artist_name = 'Radiohead'

-- Create index to speed it up
CREATE INDEX idx_artist_name ON tracks(artist_name)

-- Frequent query: get recent plays
SELECT * FROM listening_history ORDER BY played_at DESC LIMIT 20

-- Index optimized for descending order
CREATE INDEX idx_played_at ON listening_history(played_at DESC)

-- Frequent query: get Dublin's recent weather
SELECT * FROM weather_history 
WHERE location = 'Dublin' 
ORDER BY timestamp DESC 
LIMIT 1

-- Composite index covering both columns
CREATE INDEX idx_location_timestamp 
ON weather_history(location, timestamp DESC)

-- This index helps queries filtering by location alone
-- But doesn't help queries filtering by timestamp alone

-- Check how SQLite executes a query
EXPLAIN QUERY PLAN
SELECT * FROM weather_history 
WHERE location = 'Dublin' 
ORDER BY timestamp DESC 
LIMIT 10

QUERY PLAN
`--SCAN weather_history
   `--USE TEMP B-TREE FOR ORDER BY

-- Create the index
CREATE INDEX idx_location_timestamp 
ON weather_history(location, timestamp DESC);

-- Check the query plan again
EXPLAIN QUERY PLAN
SELECT * FROM weather_history 
WHERE location = 'Dublin' 
ORDER BY timestamp DESC 
LIMIT 10

QUERY PLAN
`--SEARCH weather_history USING INDEX idx_location_timestamp (location=?)

CREATE TABLE weather_history (
    id INTEGER PRIMARY KEY,
    location TEXT,           -- "Dublin" repeated 1000 times
    country TEXT,            -- "Ireland" repeated 1000 times
    latitude REAL,           -- 53.3498 repeated 1000 times
    longitude REAL,          -- -6.2603 repeated 1000 times
    timezone TEXT,           -- "Europe/Dublin" repeated 1000 times
    temperature REAL,
    conditions TEXT,
    timestamp DATETIME
)

-- Locations table (3 rows for Dublin, London, Paris)
CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    city TEXT NOT NULL,
    country TEXT NOT NULL,
    latitude REAL,
    longitude REAL,
    timezone TEXT
)

-- Weather readings reference locations by ID
CREATE TABLE weather_readings (
    id INTEGER PRIMARY KEY,
    location_id INTEGER NOT NULL,     -- Just stores 1, 2, or 3
    temperature REAL,
    conditions TEXT,
    timestamp DATETIME,
    FOREIGN KEY (location_id) REFERENCES locations(id)
)

SELECT locations.city, locations.country,
       weather_readings.temperature, weather_readings.conditions
FROM weather_readings
JOIN locations ON weather_readings.location_id = locations.id
WHERE locations.city = 'Dublin'
ORDER BY weather_readings.timestamp DESC

def migrate_add_feels_like_column():
    """Add 'feels_like' temperature column to weather_history."""
    with sqlite3.connect('weather_cache.db') as conn:
        # Check if column already exists
        cursor = conn.execute("PRAGMA table_info(weather_history)")
        columns = [row[1] for row in cursor.fetchall()]
        
        if 'feels_like' not in columns:
            conn.execute("""
                ALTER TABLE weather_history 
                ADD COLUMN feels_like REAL
            """)
            print("Added 'feels_like' column")
        else:
            print("'feels_like' column already exists")

migrate_add_feels_like_column()

def migrate_rename_conditions_to_description():
    """Rename 'conditions' column to 'description'."""
    with sqlite3.connect('weather_cache.db') as conn:
        try:
            # Modern SQLite (3.25+)
            conn.execute("""
                ALTER TABLE weather_history 
                RENAME COLUMN conditions TO description
            """)
            print("Renamed 'conditions' to 'description'")
        except sqlite3.OperationalError:
            print("Column rename not supported - update SQLite or use table recreation")

def migrate_change_temperature_precision():
    """
    Change temperature from REAL to storing as INTEGER (celsius * 10).
    This increases precision and saves space.
    """
    with sqlite3.connect('weather_cache.db') as conn:
        # Step 1: Create new table with updated schema
        conn.execute("""
            CREATE TABLE weather_history_new (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                location TEXT NOT NULL,
                temperature_x10 INTEGER NOT NULL,  -- Store celsius * 10
                conditions TEXT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Step 2: Copy data with transformation
        conn.execute("""
            INSERT INTO weather_history_new (id, location, temperature_x10, conditions, timestamp)
            SELECT id, location, CAST(temperature * 10 AS INTEGER), conditions, timestamp
            FROM weather_history
        """)
        
        # Step 3: Drop old table
        conn.execute("DROP TABLE weather_history")
        
        # Step 4: Rename new table to original name
        conn.execute("ALTER TABLE weather_history_new RENAME TO weather_history")
        
        # Step 5: Recreate indexes
        conn.execute("""
            CREATE INDEX idx_location_timestamp 
            ON weather_history(location, timestamp DESC)
        """)
        
        print("Migration complete: temperature now stored as INTEGER * 10")

CREATE TABLE tracks (
    id INTEGER PRIMARY KEY,
    spotify_id TEXT NOT NULL UNIQUE,      -- Must exist, must be unique
    title TEXT NOT NULL,                  -- Must exist
    artist_id INTEGER NOT NULL,           -- Must exist
    duration_ms INTEGER CHECK(duration_ms > 0),  -- Must be positive
    FOREIGN KEY (artist_id) REFERENCES artists(id)  -- Must reference real artist
)

-- Listening history tracks when user played each song
-- One row per play event (not aggregated - we calculate stats in queries)
-- Kept denormalized (track title, artist name stored here) for simpler queries
-- and because track metadata rarely changes after initial insert
CREATE TABLE listening_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    track_id TEXT NOT NULL,           -- Spotify track ID
    track_name TEXT NOT NULL,         -- Denormalized for query simplicity
    artist_name TEXT NOT NULL,        -- Denormalized for query simplicity
    played_at DATETIME NOT NULL,      -- When user played this track
    duration_ms INTEGER               -- Track length (for skip detection)
)

import sqlite3

with sqlite3.connect('music.db') as conn:
    # Query 1: Get all artists
    cursor = conn.execute("SELECT id, name FROM artists")
    artists = cursor.fetchall()
    
    # Queries 2-101: Get track count for each artist (100 separate queries!)
    for artist_id, artist_name in artists:
        cursor = conn.execute("""
            SELECT COUNT(*) FROM tracks WHERE artist_id = ?
        """, (artist_id,))
        track_count = cursor.fetchone()[0]
        print(f"{artist_name}: {track_count} tracks")

# Total: 101 database queries for 100 artists
# With 1000 artists? 1001 queries. This does not scale.

import sqlite3

with sqlite3.connect('music.db') as conn:
    # Single query gets everything at once
    cursor = conn.execute("""
        SELECT artists.name, COUNT(tracks.id) as track_count
        FROM artists
        LEFT JOIN tracks ON artists.id = tracks.artist_id
        GROUP BY artists.id, artists.name
        ORDER BY track_count DESC
    """)
    
    for artist_name, track_count in cursor:
        print(f"{artist_name}: {track_count} tracks")

# Total: 1 database query for any number of artists
# 10x-100x faster than the N+1 approach

import sqlite3

tracks = [
    ("Song 1", "Artist 1", 180000),
    ("Song 2", "Artist 2", 210000),
    # ... 1000 more tracks
]

# Opening connection inside loop - creates 1000 connections!
for track_name, artist_name, duration in tracks:
    with sqlite3.connect('music.db') as conn:
        conn.execute("""
            INSERT INTO tracks (track_name, artist_name, duration_ms)
            VALUES (?, ?, ?)
        """, (track_name, artist_name, duration))
        # Commits after EVERY insert (1000 disk writes)

# This takes seconds instead of milliseconds

import sqlite3

tracks = [
    ("Song 1", "Artist 1", 180000),
    ("Song 2", "Artist 2", 210000),
    # ... 1000 more tracks
]

# Single connection, single transaction for all inserts
with sqlite3.connect('music.db') as conn:
    for track_name, artist_name, duration in tracks:
        conn.execute("""
            INSERT INTO tracks (track_name, artist_name, duration_ms)
            VALUES (?, ?, ?)
        """, (track_name, artist_name, duration))
    
    # Commits once after all inserts complete (1 disk write)

# 50x-100x faster than committing per insert