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.

DeepSeek V3.2 · Accepted Answer

I'll provide a comprehensive Python script that meets all your requirements. This solution uses free APIs with no required signup for crypto and a free tier for metals.

## **API Selection**

1. **Crypto**: CoinGecko API (free, no API key required, 50 calls/minute limit)
2. **Precious Metals**: Metal Prices API (free tier: 100 requests/month, requires free API key signup)

**Signup for Metals API** (optional but recommended for better reliability):
- Visit: https://metalpriceapi.com/
- Create a free account
- Get your API key (100 free requests/month)

## **Required Python Packages**

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

## **Database Schema**

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

-- Table for storing price data
CREATE TABLE IF NOT EXISTS price_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asset_type ENUM('crypto', 'metal') NOT NULL,
    asset_name VARCHAR(50) NOT NULL,
    asset_symbol VARCHAR(10) NOT NULL,
    price_usd DECIMAL(20, 8) NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_asset_symbol (asset_symbol),
    INDEX idx_timestamp (timestamp)
);

-- Optional: Table for tracking API calls (for monitoring)
CREATE TABLE IF NOT EXISTS api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_name VARCHAR(50),
    status VARCHAR(20),
    error_message TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

## **Python Script**

Create a `.env` file in the same directory:
```env
DB_HOST=localhost
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=asset_prices
METALS_API_KEY=your_metalpriceapi_key_here  # Optional, leave empty to use fallback
```

Here's the complete Python script:

```python
"""
Asset Price Fetcher
Fetches precious metal and cryptocurrency prices every 60 seconds and stores in MySQL.
"""

import os
import time
import logging
import requests
import mysql.connector
from mysql.connector import Error
from datetime import datetime
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_fetcher.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

class DatabaseManager:
    """Handles database connections and operations"""
    
    def __init__(self):
        self.host = os.getenv('DB_HOST', 'localhost')
        self.user = os.getenv('DB_USER', 'root')
        self.password = os.getenv('DB_PASSWORD', '')
        self.database = os.getenv('DB_NAME', 'asset_prices')
        self.connection = None
        
    def connect(self):
        """Establish database connection"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database
            )
            logger.info("Database connection established")
            return True
        except Error as e:
            logger.error(f"Database connection failed: {e}")
            return False
    
    def disconnect(self):
        """Close database connection"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            logger.info("Database connection closed")
    
    def insert_price(self, asset_type, asset_name, asset_symbol, price_usd):
        """Insert price data into database"""
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return False
        
        try:
            cursor = self.connection.cursor()
            query = """
                INSERT INTO price_history 
                (asset_type, asset_name, asset_symbol, price_usd, timestamp)
                VALUES (%s, %s, %s, %s, %s)
            """
            timestamp = datetime.now()
            cursor.execute(query, (asset_type, asset_name, asset_symbol, price_usd, timestamp))
            self.connection.commit()
            cursor.close()
            logger.debug(f"Inserted {asset_symbol}: ${price_usd}")
            return True
        except Error as e:
            logger.error(f"Database insert failed for {asset_symbol}: {e}")
            return False
    
    def log_api_call(self, api_name, status, error_message=None):
        """Log API calls for monitoring"""
        if not self.connection or not self.connection.is_connected():
            if not self.connect():
                return
        
        try:
            cursor = self.connection.cursor()
            query = """
                INSERT INTO api_logs (api_name, status, error_message)
                VALUES (%s, %s, %s)
            """
            cursor.execute(query, (api_name, status, error_message))
            self.connection.commit()

API Selection

Required Python Packages

Database Schema

Python Script

Installation and Setup Instructions

Key Features

Production Considerations