from pandas_datareader.data import Options
import mysql.connector
import datetime
import os

#convert multi-index df into normal df and  necessary data process
def option_convert(symbol):
    stock_option = Options(symbol, "yahoo")
    data = stock_option.get_all_data()
    data.reset_index(inplace=True)
    adj_last = []
    price_data = data.ix[:, ['Bid', 'Ask']]
    last = data["Last"]
    data = data.drop(["Chg", "PctChg", "IV", "Root", "IsNonstandard", "Underlying", "Underlying_Price", "Quote_Time", "Last_Trade_Date", "JSON"], 1)
    for i in range(data.shape[0]):
        if last[i] in list(price_data.ix[i, :]):
            adj_last.append(last[i])
        else:
            adj_last.append(sum(price_data.ix[i, :]) / 2)
    data["Adj_last"] = adj_last
    return data

def main():
    os.chdir("/home/ec2-user/data/options")
 # init mysql connection
    # mysql -h sfdata.cf6ulue4mzq9.us-west-2.rds.amazonaws.com -P 3306 -u spr1ngf0rward -p
    cnx = mysql.connector.connect(user='spr1ngf0rward', password='DTXpecgdTQzijTMg2',
                                  host='sfdata.cf6ulue4mzq9.us-west-2.rds.amazonaws.com',
                                  database='sfdata')
    # queries
    get_symbols = ("SELECT symbol_id, symbol FROM symbol WHERE exchange_id in (1,2,24)")
    symbols = []

    ## get symbol list
    cursor = cnx.cursor()
    cursor.execute(get_symbols)
    for (symbol_id, symbol) in cursor:
        symbols.append((symbol_id, symbol))
    cursor.close()

    output = 'options.csv'
    for (symbol_id, symbol) in symbols:
        try:
            data=option_convert(symbol)
            data["Symbol"] = symbol
            data["symbol_id"] = symbol_id
            data["created_on"]= datetime.datetime.now().date()
            #print data.head(5)
            order = ['symbol_id', 'Strike', 'Expiry', 'Type', 'Symbol', 'Last', 'Bid', 'Ask', 'Vol', 'Open_Int', 'Adj_last', 'created_on']
            data = data.ix[:, order]
            #print data.head(5)
            data.to_csv(output, index = False, mode = 'a', header = False)
            print ("download %s success" % (symbol))
        except:
            print ("download %s fails" % (symbol))

if __name__ == '__main__':
    main()