使用alpaca-py批量下载股票数据至SQLite遇AttributeError问题

使用alpaca-py库从Alpaca下载股票历史数据并存储到SQLite表中，单个股票代码下载正常，但批量下载（示例中每次2个）时触发错误：

AttributeError: 'tuple' object has no attribute 'symbol'

错误代码片段 #

# Define chunk of symbols to download with every server call.
symbols=['AMD', 'MSFT', 'NVDA', 'TOVX']
chunk_size = 2
for i in tqdm(range(0, len(symbols), chunk_size), desc='Downloading daily Data'):
    symbol_chunk = symbols[i:i + chunk_size]
    # Downloading 1D time-frame data...
    request_parameters = StockBarsRequest(
                    symbol_or_symbols=symbol_chunk,
                    timeframe=TimeFrame.Day,
                    start=datetime.strptime("2022-01-01", '%Y-%m-%d'),
                    end=None,
                    adjustment='raw'
             )
    daily_bars = client.get_stock_bars(request_parameters)
    for bar in daily_bars:
        stock_id = symbol_dic[bar.symbol]
        cursor.execute("""INSERT INTO alpaca_stock_prices_1D (stock_id, date, open, high, low, close, volume)
            VALUES (?, ?, ?, ?, ?, ?, ?)""",
                       (stock_id, bar.timestamp.date(), bar.open, bar.high, bar.low, bar.close, bar.volume))

返回数据示例 #

批量请求时daily_bars的结构如下：

data={'AIU': [{   'close': 3.66,
    'high': 3.75,
    'low': 3.64,
    'open': 3.65,
    'symbol': 'AIU',
    'timestamp': datetime.datetime(2021, 1, 5, 5, 0, tzinfo=datetime.timezone.utc),
    'trade_count': 661.0,
    'volume': 126252.0,
    'vwap': 3.67104}, ...]

SQLite表创建代码 #

cursor.execute("""
    CREATE TABLE IF NOT EXISTS alpaca_stock_prices_1D (
        id INTEGER PRIMARY KEY,
        stock_id INTEGER,
        date NOT NULL,
        open NOT NULL,
        high NOT NULL,
        low NOT NULL,
        close NOT NULL,
        volume NOT NULL,
        CONSTRAINT fk_alpaca_stocks_list FOREIGN KEY (stock_id) REFERENCES alpaca_stocks_list (id)
        ON DELETE CASCADE
    )
""")

错误原因 #

批量请求与单个请求的返回结构存在差异：

• 单个股票请求时，client.get_stock_bars()返回单个Bar对象的迭代器，直接遍历即可拿到每个Bar；
• 批量请求时，返回的是以股票代码为键、对应Bar列表为值的字典结构，遍历daily_bars会得到(symbol, bars_list)的元组，而非单个Bar对象，因此调用bar.symbol会触发tuple无symbol属性的错误。

修正后的代码 #

修改遍历daily_bars的逻辑，先拆解键值对，再遍历每个股票的Bar列表：

# Define chunk of symbols to download with every server call.
symbols=['AMD', 'MSFT', 'NVDA', 'TOVX']
chunk_size = 2
for i in tqdm(range(0, len(symbols), chunk_size), desc='Downloading daily Data'):
    symbol_chunk = symbols[i:i + chunk_size]
    # Downloading 1D time-frame data...
    request_parameters = StockBarsRequest(
                    symbol_or_symbols=symbol_chunk,
                    timeframe=TimeFrame.Day,
                    start=datetime.strptime("2022-01-01", '%Y-%m-%d'),
                    end=None,
                    adjustment='raw'
             )
    daily_bars = client.get_stock_bars(request_parameters)
    # 遍历每个股票的代码和对应的K线列表
    for symbol, bars_list in daily_bars.items():
        stock_id = symbol_dic[symbol]
        # 遍历该股票的每一条K线数据
        for bar in bars_list:
            cursor.execute("""INSERT INTO alpaca_stock_prices_1D (stock_id, date, open, high, low, close, volume)
                VALUES (?, ?, ?, ?, ?, ?, ?)""",
                           (stock_id, bar.timestamp.date(), bar.open, bar.high, bar.low, bar.close, bar.volume))

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