How to Fetch Powerball Results Using Python

# Create virtual environment
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install required libraries
pip install requests pandas

# Export API key
export US_LOTTERY_API_KEY="usl_live_your_secret_key_here"

import os
import sys
import sqlite3
import time
import requests
import pandas as pd

API_BASE_URL = "https://uslotteryapi.com/v1"
DB_FILE = "powerball_history.db"

def get_api_key():
    key = os.environ.get("US_LOTTERY_API_KEY")
    if not key:
        print("Error: US_LOTTERY_API_KEY environment variable is missing.")
        print("Run: export US_LOTTERY_API_KEY='your_api_key'")
        sys.exit(1)
    return key

def fetch_powerball_draws(limit=10):
    """
    Fetch Powerball draw records from US Lottery API.
    """
    api_key = get_api_key()
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Accept": "application/json",
        "User-Agent": "Python-Powerball-ETL/1.0"
    }
    url = f"{API_BASE_URL}/results/powerball?limit={limit}"

    print(f"Fetching Powerball data from {url}...")
    
    # Retry mechanism for HTTP rate limits (429) or transient errors
    for attempt in range(1, 4):
        try:
            response = requests.get(url, headers=headers, timeout=10)
            if response.status_code == 200:
                json_data = response.json()
                return json_data.get("data", [])
            elif response.status_code == 429:
                print(f"Rate limited (429). Retrying in {attempt * 3} seconds...")
                time.sleep(attempt * 3)
            else:
                print(f"HTTP Error {response.status_code}: {response.text}")
                break
        except requests.RequestException as e:
            print(f"Network error on attempt {attempt}: {e}")
            time.sleep(2)
            
    return []

def init_database():
    """Initialize SQLite table for Powerball draws."""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS powerball_draws (
            draw_date TEXT PRIMARY KEY,
            wb1 INTEGER,
            wb2 INTEGER,
            wb3 INTEGER,
            wb4 INTEGER,
            wb5 INTEGER,
            powerball INTEGER,
            multiplier TEXT,
            estimated_jackpot TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    return conn

def save_draws_to_db(draws):
    """Insert or replace draw records into SQLite."""
    if not draws:
        print("No draw records to save.")
        return

    conn = init_database()
    cursor = conn.cursor()

    count = 0
    for d in draws:
        nums = d.get("winning_numbers", [0, 0, 0, 0, 0])
        cursor.execute("""
            INSERT OR REPLACE INTO powerball_draws 
            (draw_date, wb1, wb2, wb3, wb4, wb5, powerball, multiplier, estimated_jackpot)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            d.get("draw_date"),
            nums[0] if len(nums) > 0 else 0,
            nums[1] if len(nums) > 1 else 0,
            nums[2] if len(nums) > 2 else 0,
            nums[3] if len(nums) > 3 else 0,
            nums[4] if len(nums) > 4 else 0,
            d.get("powerball"),
            d.get("multiplier", "1x"),
            d.get("estimated_jackpot", "N/A")
        ))
        count += 1

    conn.commit()
    conn.close()
    print(f"Successfully saved {count} Powerball draw records to {DB_FILE}!")

def export_to_pandas():
    """Load draw database into a Pandas DataFrame and display summary statistics."""
    conn = sqlite3.connect(DB_FILE)
    df = pd.read_sql_query("SELECT * FROM powerball_draws ORDER BY draw_date DESC", conn)
    conn.close()
    
    print("
--- Recent Powerball Draws (Pandas DataFrame) ---")
    print(df[['draw_date', 'wb1', 'wb2', 'wb3', 'wb4', 'wb5', 'powerball', 'multiplier', 'estimated_jackpot']])
    return df

if __name__ == "__main__":
    draws = fetch_powerball_draws(limit=5)
    save_draws_to_db(draws)
    export_to_pandas()

# Open crontab editor
crontab -e

# Add schedule (Runs at 11:15 PM every Monday, Wednesday, and Saturday)
15 23 * * 1,3,6export US_LOTTERY_API_KEY="usl_live_xxx" && /usr/bin/python3 /path/to/fetch_powerball.py >> /var/log/powerball.log 2>&1

import pandas as pd
import sqlite3

conn = sqlite3.connect("powerball_history.db")
df = pd.read_sql_query("SELECT wb1, wb2, wb3, wb4, wb5 FROM powerball_draws", conn)

# Unroll white ball columns into a single series to find most frequent numbers
all_balls = pd.concat([df['wb1'], df['wb2'], df['wb3'], df['wb4'], df['wb5']])
top_5_frequent = all_balls.value_counts().head(5)

print("Top 5 Most Frequent Powerball White Balls:")
print(top_5_frequent)