Can't loop through barset of alpaca-py to store price data into sqlite table

Alpaca Market Data
1

I am trying to download the historical stock prices from alpaca using the alpaca-py library and store this data into a sqlite table. When downloading the historical data symbol by symbol everything works fine. But when I try to download the data in a chunk of 200 symbols at a time, I get the error:

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

I tried so many things and still get many other errors… How can I correctly load the stock prices into the database? Note that I have another table for stock tickers, this is why in the code below I need to reference: stock_id = symbol_dic[bar.symbol]
This is the part of my code that produces the error (I included all the libraries I am using in my file):

import sqlite3
from alpaca.data.historical import StockHistoricalDataClient
from alpaca.data.requests import StockBarsRequest
from alpaca.data.timeframe import TimeFrame, TimeFrameUnit
from appfiles.alpaca.alpaca_config import *
from appfiles.indicators import *
from datetime import datetime
import pandas as pd
import pytz
import json
from tqdm import tqdm

# Define chunk of symbols to download with every server call.
symbols=['AIU', '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))

Below is a sample of the data returned from “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}, {   'close': 3.7,
    'high': 3.74,
    'low': 3.6,
    'open': 3.7,
    'symbol': 'AIU',
    'timestamp': datetime.datetime(2021, 1, 6, 5, 0, tzinfo=datetime.timezone.utc),
    'trade_count': 798.0,
    'volume': 120423.0,
    'vwap': 3.653867}, {   'close': 3.61,
    'high': 3.69,
    'low': 3.58,
    'open': 3.62,
    'symbol': 'AIU',
    'timestamp': datetime.datetime(2021, 1, 7, 5, 0, tzinfo=datetime.timezone.utc),
    'trade_count': 1029.0,
    'volume': 164226.0,
    'vwap': 3.628806}, {   'close': 3.67,
    'high': 3.7473,
    'low': 3.6,
    'open': 3.62,
    'symbol': 'AIU',
    'timestamp': datetime.datetime(2021, 1, 8, 5, 0, tzinfo=datetime.timezone.utc),
    'trade_count': 1398.0,
    'volume': 191745.0,
    'vwap': 3.666181},....

Code to create the SQLite prices table:

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
    )
""")
2

I did fix it using the following code:

    daily_bars = client.get_stock_bars(request_parameters)
    for bar in daily_bars:
        data = bar[1]
        for symbol_data in data.values():
            for data_point in symbol_data:
                try:
                    stock_id = symbol_dic[data_point.symbol]
                    cursor.execute("""INSERT INTO alpaca_stock_prices_1D (stock_id, date, open, high, low, close,
                                    volume) VALUES (?, ?, ?, ?, ?, ?, ?)""",
                                   (stock_id, data_point.timestamp.date(),
                                    data_point.open, data_point.high, data_point.low, data_point.close,
                                    data_point.volume))
                except AttributeError:
                    print(
                        f"The 'data_point' object does not have a 'symbol' or 'timestamp' attribute: {data_point}")
                except KeyError:
                    print(f"The symbol '{data_point.symbol}' is not present in the symbol_dic dictionary")
3

@EGYXee Pandas has a very nice to_sql method that makes writing a dataframe to an sql database very easy. Check out the docs.

Here is code to fetch daily bar data for a list of symbols and store that data into an sqlalchemy database. A sqlite3 database is also supported by the to_sql method and could be used. Note this eliminates all the, rather non-pythonic, loops.

!pip install alpaca-py

import pandas as pd

from alpaca.data.historical import StockHistoricalDataClient
from alpaca.data.requests import StockBarsRequest
from alpaca.data.timeframe import TimeFrame

from sqlalchemy import create_engine

ALPACA_API_KEY_ID = 'xxxxx'
ALPACA_API_SECRET_KEY = 'xxxxx'

# Create a database engine
db_engine = create_engine('sqlite://', echo=False)

# Create a data client to fetch historical data
data_client = StockHistoricalDataClient(ALPACA_API_KEY_ID, ALPACA_API_SECRET_KEY)

# Set parameters
symbols=['AIU', 'AMD', 'MSFT', 'NVDA', 'TOVX']
start_date = pd.to_datetime("2022-01-01").tz_localize('America/New_York')

request_parameters = StockBarsRequest(
                symbol_or_symbols=symbols,
                timeframe=TimeFrame.Day,
                start=start_date,
                end=None,
                adjustment='raw'
             )

# Fetch data and convert to dataframe
daily_bars = data_client.get_stock_bars(request_parameters).df

# Write dataframe to sql db (name the db daily_data)
daily_bars.to_sql('daily_data', con=db_engine)

# Verify the columns/keys which were created
db_engine.execute("SELECT * FROM daily_data").keys()

# Read database to see if it worked
db_engine.execute("SELECT * FROM daily_data").fetchall()
1 Like
4

The SQL database should look like this

['symbol', 'timestamp', 'open', 'high', 'low', 'close', 'volume', 'trade_count', 'vwap']

 ('MSFT', '2022-01-03 05:00:00.000000', 335.35, 338.0, 329.78, 334.75, 28899988.0, 394908.0, 333.380091),
 ('MSFT', '2022-01-04 05:00:00.000000', 334.83, 335.2, 326.12, 329.01, 32663003.0, 517781.0, 329.447177),
 ('MSFT', '2022-01-05 05:00:00.000000', 325.86, 326.07, 316.0, 316.38, 39731813.0, 637313.0, 320.053335),
 ('MSFT', '2022-01-06 05:00:00.000000', 313.15, 318.7, 311.49, 313.88, 39788894.0, 626724.0, 315.214676),
1 Like