/ TECHNOLOGY

Insert Crypto Price Data Using SQLAlchemy, Python & Pandas

Learn how to insert free minutely crypto price data into PostgreSQL using SQLAlchemy, Python, and Pandas.

Steps to Insert the Crypto Price Data

Below is the Python code demonstrated in the video and Jupyter notebook.

1. Get Symbols

We get the unique index values for the ticker component of the MultiIndex.

tickers = bars1m.index.get_level_values(1).unique()
tickers

We can then filter active tickers by making the assumption that an active ticker would have traded on the most recent day.

active_tickers = bars1m['2020-12-30':].index.get_level_values(1).unique()
active_tickers

2. Create Symbols Dataframe

We then create a dataframe for our symbol class using np.where to set active and inactive tickers.

symbols = pd.DataFrame(tickers)
symbols['name'] = symbols['ticker']
symbols['market'] = 'crypto'
symbols['active'] = np.where(symbols['ticker'].isin(active_tickers), True, False)
symbols = symbols.sort_values(by='ticker')
symbols

3. Create MinuteBar DataFrame

We reorganize the dataframe to make it easier to upload a ticker and its associated minute data.

minute_bars = bars1m.reset_index().sort_values(by=['ticker','date']).set_index(['ticker','date'])
minute_bars

4. Insert Data

We then insert the data, making sure to commit the symbol to get an id for the minute bar insertion.

from psql import db, session
from models import Symbol, MinuteBar
for i, r in symbols.iterrows():
    symbol = Symbol(ticker=r['ticker'],
                    name=r['name'],
                    market=r['market'],
                    active=r['active'])
    session.add(symbol)
    session.commit()
    
    bars = minute_bars.xs(r['ticker']).reset_index()
    bars['symbol_id'] = symbol.id
    
    session.bulk_insert_mappings(MinuteBar,
                                bars.to_dict(orient='records'))
    session.commit()
leo

Leo Smigel

Based in Pittsburgh, Analyzing Alpha is a blog by Leo Smigel exploring what works in the markets.