<jupyter_start><jupyter_text>Batch Process Historical Daily & Hourly Stock Data<jupyter_code>import pandas as pd
import pymysql
from yitian.datasource import *

# Set up cloud sql connections
password = ''
database = EQUITY
connection = pymysql.connect(host=PRIVATE_HOST,
                             user=USER,
                             password=password,
                             db=database)

# Grab tickers from dim tables
nasdaq_pd = pd.read_sql("SELECT * FROM equity.nasdaq", connection)
nasdaq_tickers = nasdaq_pd.symbol

nyse_pd = pd.read_sql("SELECT * FROM equity.nyse", connection)
nyse_tickers = nyse_pd.symbol

# updates ticker in 'tickers'
tickers = nasdaq_tickers<jupyter_output><empty_output><jupyter_text>UPDATING - ETL daily & hourly stock data into GCP Cloud Storage and mySQL<jupyter_code># ETL current year daily & hourly stock data

current_year = 2020
mode = 'update'

for t in tickers:
    ticker = t
    print(ticker)
    
    with connection.cursor() as cursor:
        cursor.execute(f" DELETE FROM stock_daily WHERE ticker='{ticker}' AND year={str(current_year)};")
        cursor.execute(f" DELETE FROM stock_hourly WHERE ticker='{ticker}' AND year={str(current_year)};")
    
        connection.commit()
    
    %run -i 'notebooks/etl/yfinance/stock_etl.py'
    
# Close connection
connection.close()<jupyter_output><empty_output><jupyter_text>HISTORICAL - ETL daily & hourly stock data into Cloud Storage and mySQL<jupyter_code># ETL historical daily & hourly stock data before current_year

current_year = 2020
mode = 'history'

for t in tickers:
    
    ticker = t
    print(ticker)
    
    %run -i 'notebooks/etl/yfinance/stock_etl.py'<jupyter_output><empty_output>