如何处理KeyError(key)错误？针对股票代码引发的数据批量获取入库失败问题的解决方案咨询

Let's break down solutions for each of your questions, while also fixing the ValueError: column must be nonempty error you're seeing right now (that happens because your code keeps deleting metrics until none are left).

1. Identify Faulty Ticker Symbols with Logging #

The main issue with your current batch approach is that you can't easily isolate which ticker is causing failures. Instead of using get_data_batch, process each ticker individually and log errors for any that fail. This way you'll know exactly which tickers to remove.

First, set up proper logging to track issues:

import logging

# Configure logging to write to a file and print to console
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('ticker_errors.log'),
        logging.StreamHandler()
    ]
)

Then, process each ticker one by one, catching errors specific to each ticker:

# Initialize empty list to hold valid data
valid_dfs = []

for ticker in tickers:
    try:
        # Get data for individual ticker
        ticker_data = client.get_data(company=ticker, metrics=metrics, period='FQ90:FQ')
        # Convert to DataFrame and add ticker column for tracking
        df_ticker = pd.DataFrame(ticker_data)
        df_ticker['ticker'] = ticker
        valid_dfs.append(df_ticker)
        logging.info(f"Successfully fetched data for {ticker}")
    except Exception as e:
        # Log the error and the problematic ticker
        logging.error(f"Failed to fetch data for {ticker}: {str(e)}")
        continue

# Combine all valid data into a single DataFrame
if valid_dfs:
    df = pd.concat(valid_dfs, ignore_index=True)
else:
    logging.warning("No valid data fetched from any ticker")
    sys.exit(1)

Now check ticker_errors.log to see which tickers are failing—those are the ones you can remove from your list.

2. Ignore Failed Tickers & Write Valid Data to Database #

The above approach already skips faulty tickers and collects only valid data. Now, to ensure the write operation doesn't fail due to missing metrics, we'll first filter the DataFrame to only keep metrics that actually exist (to avoid the empty column error):

# Filter metrics to only those present in the DataFrame
existing_metrics = [col for col in metrics if col in df.columns]
if not existing_metrics:
    logging.error("No valid metrics left after filtering")
    sys.exit(1)

# Explode the valid metrics
ex = df.explode(existing_metrics)

# Write to database
try:
    engine = create_engine('postgresql://user:9999@localhost:9999/schema')
    ex.to_sql('TECHNOLOGY_SOFTWARE_INFRASTRUCTURE', engine, schema='COLLECT', if_exists='append')
    logging.info("Successfully wrote valid data to database")
except Exception as e:
    logging.error(f"Failed to write data to database: {str(e)}")

Using if_exists='append' ensures that if you run the script multiple times, it won't overwrite existing data—just add new valid entries.

3. Alternative Ways to Write Partial Data to Database #

If you want to ensure even partial data gets written (instead of waiting for all tickers to process), here are two solid alternatives:

Alternative 1: Write Each Ticker's Data Immediately #

Instead of collecting all valid data first, write each successful ticker's data to the database right after fetching it. This way, even if the script crashes later, you won't lose data from previously processed tickers:

engine = create_engine('postgresql://user:9999@localhost:9999/schema')

for ticker in tickers:
    try:
        ticker_data = client.get_data(company=ticker, metrics=metrics, period='FQ90:FQ')
        df_ticker = pd.DataFrame(ticker_data)
        df_ticker['ticker'] = ticker
        existing_metrics = [col for col in metrics if col in df_ticker.columns]
        if existing_metrics:
            ex = df_ticker.explode(existing_metrics)
            ex.to_sql('TECHNOLOGY_SOFTWARE_INFRASTRUCTURE', engine, schema='COLLECT', if_exists='append')
            logging.info(f"Wrote data for {ticker} to database")
        else:
            logging.warning(f"No valid metrics found for {ticker}, skipping write")
    except Exception as e:
        logging.error(f"Skipping {ticker}: {str(e)}")
        continue

Alternative 2: Handle Missing Metrics Gracefully #

Instead of deleting metrics that cause errors, fill missing metric values with NaN (or a default value) so you can still write the data. This keeps all your desired metrics in the output, even if some are missing for certain tickers:

# After fetching data for a ticker, ensure all metrics are present
df_ticker = pd.DataFrame(ticker_data)
for metric in metrics:
    if metric not in df_ticker.columns:
        df_ticker[metric] = pd.NA  # or 0, depending on the metric type

This way, you won't end up with an empty column list when exploding, and you can write the full structure to the database (with missing values marked appropriately).

内容的提问来源于stack exchange，提问作者kevin.c