调用yfinance批量获取财报数据失败，如何异步填充数据库？

核心问题分析 #

• Yahoo Finance存在API请求频率限制，批量/多进程高频请求会触发限流，导致earnings_history返回None
• yfinance的Ticker实例在多进程环境下可能存在连接复用冲突，引发请求异常
• 同步执行时请求密度过高，直接触发限流，完全无法获取数据

解决方案 #

1. 添加请求延迟与重试机制 #

给每个请求添加随机延迟避免触发限流，同时对返回None的请求进行重试，确保数据获取成功率。

import time
import random
import yfinance as yf
from tenacity import retry, stop_after_attempt, wait_exponential_jitter

@retry(stop=stop_after_attempt(3), wait=wait_exponential_jitter(jitter=1))
def get_earnings_history(ticker):
    # 随机延迟0.5-2秒，分散请求频率
    time.sleep(random.uniform(0.5, 2))
    yahoo_stock_obj = yf.Ticker(ticker.upper())
    earnings_history = yahoo_stock_obj.earnings_history
    if earnings_history is None:
        raise ValueError(f"Failed to fetch earnings for {ticker}")
    return earnings_history

2. 用多线程替代多进程（优化并发逻辑） #

yfinance请求属于IO密集型任务，多线程比多进程更高效，且能避免进程间连接冲突。同时严格控制并发数，降低限流风险。

from concurrent.futures import ThreadPoolExecutor
from django.db import transaction, connection

def process_ticker(ticker):
    # 重置Django数据库连接，避免多线程下连接复用异常
    connection.close()
    try:
        earnings_df = get_earnings_history(ticker)
        # 事务内保存数据，保证数据一致性
        with transaction.atomic():
            for _, row in earnings_df.iterrows():
                # 替换为你的Django模型及字段逻辑
                EarningsReport.objects.update_or_create(
                    ticker=ticker.upper(),
                    period=row['period'],
                    defaults={
                        'eps_actual': row['epsActual'],
                        'eps_estimate': row['epsEstimate'],
                        # 其他字段按需添加
                    }
                )
        return f"Success: {ticker}"
    except Exception as e:
        return f"Failed: {ticker} - {str(e)}"

def batch_process_tickers(ticker_list):
    # 控制并发数在10-20之间，避免触发限流
    with ThreadPoolExecutor(max_workers=15) as executor:
        results = executor.map(process_ticker, ticker_list)
        for result in results:
            print(result)

3. 复用自定义会话（可选） #

通过自定义requests会话，减少连接创建开销，同时模拟浏览器请求头降低被识别为爬虫的概率。

import requests

def create_session():
    session = requests.Session()
    session.headers.update({
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36'
    })
    return session

# 修改get_earnings_history函数，传入会话
@retry(stop=stop_after_attempt(3), wait=wait_exponential_jitter(jitter=1))
def get_earnings_history(ticker, session):
    time.sleep(random.uniform(0.5, 2))
    yahoo_stock_obj = yf.Ticker(ticker.upper(), session=session)
    earnings_history = yahoo_stock_obj.earnings_history
    if earnings_history is None:
        raise ValueError(f"Failed to fetch earnings for {ticker}")
    return earnings_history

# 线程内创建独立会话，避免线程安全问题
def process_ticker_with_session(ticker):
    session = create_session()
    try:
        connection.close()
        earnings_df = get_earnings_history(ticker, session)
        # 数据库保存逻辑...
        return f"Success: {ticker}"
    except Exception as e:
        return f"Failed: {ticker} - {str(e)}"
    finally:
        session.close()

注意事项 #

• 并发数不要超过20，过高会大幅提升限流概率
• 若仍出现限流，可适当增大延迟范围（比如调整为1-3秒）
• 重试次数控制在3次以内，避免给服务器造成不必要压力
• 数据库操作必须用事务包裹，防止部分数据写入失败导致数据不一致

内容的提问来源于stack exchange，提问作者codyc4321