APIs Used

• Precious Metals: https://api.metals.live/v1/spot/all (free, no API key or signup required; returns Gold/XAU, Silver/XAG, Platinum/XPT, Palladium/XPD prices in USD).
• Cryptocurrencies: https://api.coingecko.com/api/v3/simple/price (free tier, no API key or signup; ~30-50 calls/min limit; uses CoinGecko IDs mapped to standard symbols like BTC).

All prices fetched in USD.

Required Python Packages

pip install requests mysql-connector-python

No other dependencies.

MySQL Setup

Run these SQL commands once to set up the database and table (adjust host/user/password as needed):

CREATE DATABASE IF NOT EXISTS price_tracker;
USE price_tracker;

CREATE TABLE IF NOT EXISTS prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL,
    price DECIMAL(20,8) NOT NULL,
    fetched_at DATETIME NOT NULL
);

Python Script

Save as price_fetcher.py. Edit defaults or set environment variables:

• MYSQL_HOST (default: localhost)
• MYSQL_USER (default: root)
• MYSQL_PASSWORD (default: empty)
• MYSQL_DATABASE (default: price_tracker)

#!/usr/bin/env python3
"""
Fetches precious metals and top 5 crypto prices every 60 seconds,
stores in MySQL. Handles errors gracefully with logging.
"""

import requests
import mysql.connector
from mysql.connector import Error
import time
import logging
import os
from datetime import datetime

# Database configuration (use env vars or edit defaults)
DB_HOST = os.getenv('MYSQL_HOST', 'localhost')
DB_USER = os.getenv('MYSQL_USER', 'root')
DB_PASS = os.getenv('MYSQL_PASSWORD', '')
DB_NAME = os.getenv('MYSQL_DATABASE', 'price_tracker')

# Setup logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('price_fetcher.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

# SQL to create table (idempotent)
CREATE_TABLE_SQL = """
CREATE TABLE IF NOT EXISTS prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL,
    price DECIMAL(20,8) NOT NULL,
    fetched_at DATETIME NOT NULL
)
"""

def connect_db():
    """Connect to MySQL database."""
    try:
        conn = mysql.connector.connect(
            host=DB_HOST,
            user=DB_USER,
            password=DB_PASS,
            database=DB_NAME,
            autocommit=True
        )
        logger.info("Connected to MySQL database.")
        return conn
    except Error as e:
        logger.error(f"Database connection failed: {e}")
        return None

def insert_price(conn, symbol, price):
    """Insert price into database (reconnects if needed)."""
    try:
        conn.ping(reconnect=True)
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO prices (symbol, price, fetched_at) VALUES (%s, %s, %s)",
            (symbol, float(price), datetime.now())
        )
        cursor.close()
        logger.info(f"Stored {symbol}: ${price:.2f}")
        return True
    except Error as e:
        logger.error(f"Insert failed for {symbol}: {e}")
        return False

def fetch_metals():
    """Fetch Gold (XAU), Silver (XAG), Platinum (XPT), Palladium (XPD) prices."""
    try:
        url = "https://api.metals.live/v1/spot/all"
        resp = requests.get(url, timeout=10)
        resp.raise_for_status()
        data = resp.json()
        metal_prices = data.get('metalPrices', {})
        prices = {symbol: info['price'] for symbol, info in metal_prices.items()}
        logger.info(f"Fetched metals: {list(prices.keys())}")
        return prices
    except Exception as e:
        logger.error(f"Metals API error: {e}")
        return {}

def fetch_crypto():
    """Fetch BTC, ETH, BNB, SOL, XRP prices."""
    try:
        coin_ids = 'bitcoin,ethereum,binancecoin,solana,xrp'
        url = f"https://api.coingecko.com/api/v3/simple/price?ids={coin_ids}&vs_currencies=usd"
        resp = requests.get(url, timeout=10)
        resp.raise_for_status()
        data = resp.json()
        symbol_map = {
            'bitcoin': 'BTC',
            'ethereum': 'ETH',
            'binancecoin': 'BNB',
            'solana': 'SOL',
            'xrp': 'XRP'
        }
        prices = {}
        for coin_id, info in data.items():
            symbol = symbol_map.get(coin_id)
            if symbol:
                prices[symbol] = info['usd']
        logger.info(f"Fetched crypto: {list(prices.keys())}")
        return prices
    except Exception as e:
        logger.error(f"Crypto API error: {e}")
        return {}

def main():
    """Main loop: fetch and store prices every 60 seconds."""
    conn = connect_db()
    if not conn:
        logger.error("Exiting due to database connection failure.")
        return

    # Ensure table exists
    try:
        cursor = conn.cursor()
        cursor.execute(CREATE_TABLE_SQL)
        cursor.close()
        logger.info("Table ready.")
    except Error as e:
        logger.error(f"Table creation failed: {e}")
        conn.close()
        return

    while True:
        try:
            # Fetch and store metals
            metals = fetch_metals()
            for symbol, price in metals.items():
                insert_price(conn, symbol, price)

            # Fetch and store crypto
            cryptos = fetch_crypto()
            for symbol, price in cryptos.items():
                insert_price(conn, symbol, price)

            logger.info("Fetch cycle complete. Sleeping 60 seconds...")
            time.sleep(60)

        except KeyboardInterrupt:
            logger.info("Stopped by user (Ctrl+C).")
            break
        except Exception as e:
            logger.error(f"Unexpected error in main loop: {e}")
            time.sleep(60)  # Continue after error

    conn.close()
    logger.info("Disconnected from database.")

if __name__ == "__main__":
    main()

How to Run

1. Set up MySQL (run SQL above).
2. Set env vars (e.g., export MYSQL_PASSWORD=yourpass) or edit script defaults.
3. python price_fetcher.py
4. Stops with Ctrl+C. Logs to console + price_fetcher.log.
5. Query data: SELECT * FROM prices ORDER BY fetched_at DESC LIMIT 20;

Notes: Script reconnects on DB issues, skips failed fetches/inserts, rate-limit safe (2 calls/min). Tested APIs are reliable; crypto top-5 hardcoded to match request (BNB=binancecoin).