Python script to fetch precious metal & top crypto prices every minute and store in MySQL

Question

Write a Python script that continuously fetches the following prices every 60 seconds and stores them in a MySQL database:

**Precious Metals:** Gold, Silver, Platinum, Palladium
**Crypto:** Bitcoin, Ethereum, BNB, Solana, XRP (current top 5 by market cap)

Requirements:
- Use a free, publicly available API (no paid API key required, or at minimum a free tier with generous limits). Clearly state which API(s) you're using and any signup steps needed.
- Fetch all prices in USD.
- Store results in a MySQL database with appropriate table schema(s). Include the SQL to create the table(s).
- Each record should include the asset name/symbol, price, and timestamp.
- The script should run indefinitely, polling every 60 seconds.
- Handle common errors gracefully (API timeouts, connection failures, rate limits) — log errors but don't crash.
- Use clear, well-structured code with comments.
- List all required Python packages (e.g., `requests`, `mysql-connector-python`, etc.) and how to install them.

Claude Sonnet 4.5 · Accepted Answer

# Python Price Tracker for Precious Metals & Cryptocurrencies

## Overview

This script fetches precious metal and cryptocurrency prices every 60 seconds and stores them in MySQL. It uses two free APIs:

- **Metals API**: [metals-api.com](https://metals-api.com/) - Free tier (100 requests/month). **Note**: This is quite limited, so I'll also provide an alternative using [goldapi.io](https://www.goldapi.io/) which offers 100 requests/month for free.
- **CoinGecko API**: [coingecko.com/api](https://www.coingecko.com/en/api) - No signup required, generous free tier (10-50 calls/minute)

**Best Alternative (recommended)**: Use CoinGecko for crypto AND metals since they support precious metals too!

## Required Python Packages

```bash
pip install requests mysql-connector-python python-dotenv
```

## MySQL Database Schema

```sql
-- Create the database
CREATE DATABASE IF NOT EXISTS price_tracker;
USE price_tracker;

-- Create the prices table
CREATE TABLE IF NOT EXISTS asset_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asset_symbol VARCHAR(10) NOT NULL,
    asset_name VARCHAR(50) NOT NULL,
    asset_type ENUM('metal', 'crypto') NOT NULL,
    price_usd DECIMAL(20, 8) NOT NULL,
    timestamp DATETIME NOT NULL,
    INDEX idx_symbol_timestamp (asset_symbol, timestamp),
    INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

## Python Script

```python
import requests
import mysql.connector
from mysql.connector import Error
import time
import logging
from datetime import datetime
from typing import Dict, List, Optional
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('price_tracker.log'),
        logging.StreamHandler()
    ]
)

logger = logging.getLogger(__name__)

# Database configuration
DB_CONFIG = {
    'host': os.getenv('DB_HOST', 'localhost'),
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', 'your_password'),
    'database': os.getenv('DB_NAME', 'price_tracker')
}

# Asset configurations
CRYPTO_ASSETS = {
    'bitcoin': 'BTC',
    'ethereum': 'ETH',
    'binancecoin': 'BNB',
    'solana': 'SOL',
    'ripple': 'XRP'
}

METAL_ASSETS = {
    'gold': 'XAU',
    'silver': 'XAG',
    'platinum': 'XPT',
    'palladium': 'XPD'
}

# API endpoints
COINGECKO_BASE_URL = 'https://api.coingecko.com/api/v3'

class PriceTracker:
    """Main class for tracking and storing asset prices"""
    
    def __init__(self):
        self.connection = None
        self.connect_to_database()
    
    def connect_to_database(self) -> None:
        """Establish connection to MySQL database"""
        try:
            self.connection = mysql.connector.connect(**DB_CONFIG)
            if self.connection.is_connected():
                logger.info("Successfully connected to MySQL database")
        except Error as e:
            logger.error(f"Error connecting to MySQL: {e}")
            raise
    
    def ensure_connection(self) -> None:
        """Ensure database connection is alive, reconnect if needed"""
        try:
            if self.connection is None or not self.connection.is_connected():
                logger.warning("Database connection lost, reconnecting...")
                self.connect_to_database()
        except Error as e:
            logger.error(f"Error ensuring connection: {e}")
    
    def fetch_crypto_prices(self) -> List[Dict]:
        """Fetch cryptocurrency prices from CoinGecko API"""
        try:
            # CoinGecko API endpoint for multiple coins
            ids = ','.join(CRYPTO_ASSETS.keys())
            url = f'{COINGECKO_BASE_URL}/simple/price'
            params = {
                'ids': ids,
                'vs_currencies': 'usd'
            }
            
            response = requests.get(url, params=params, timeout=10)
            response.raise_for_status()
            data = response.json()
            
            prices = []
            timestamp = datetime.now()
            
            for coin_id, symbol in CRYPTO_ASSETS.items():
                if coin_id in data and 'usd' in data[coin_id]:
                    prices.append({
                        'symbol': symbol,
                        'name': coin_id.capitalize(),
                        'type': 'crypto',
                        'price': data[coin_id]['usd'],
                        'timestamp': timestamp
                    })
            
            logger.info(f"Successfully fetched {len(prices)} crypto prices")
            return prices
            
        except requests.exceptions.RequestException as e:
            logger.error(f"Error fetching crypto prices: {e}")
            return []
        except Exception as e:
            logger.error(f"Unexpected error fetching crypto prices: {e}")
            return []
    
    def fetch_metal_prices(self) -> List[Dict]:

Python Price Tracker for Precious Metals & Cryptocurrencies

Overview

Required Python Packages

MySQL Database Schema

Python Script

Environment Configuration (.env file)

Alternative: Using goldapi.io for Metals

Usage Instructions

Notes