import yahoo_finance, time, pymysql.cursors

FTSE100=['^FTSE','AAL.L','ABF.L','ADM.L','ADN.L','AHT.L','ANTO.L','ARM.L','AV.L','AZN.L','BA.L','BAB.L','BARC.L','BATS.L','BDEV.L','BLND.L','BLT.L','BNZL.L','BP.L','BRBY.L','BT-A.L','CCH.L','CCL.L','CNA.L','CPG.L','CPI.L','CRH.L','DC.L','DGE.L','DLG.L','EXPN.L','EZJ.L','FRES.L','GFS.L','GKN.L','GLEN.L','HIK.L','HL.L','HMSO.L','HSBA.L','IAG.L','IHG.L','III.L','IMT.L','INTU.L','ISAT.L','ITRK.L','ITV.L','JMAT.L','KGF.L','LAND.L','LGEN.L','LLOY.L','LSE.L','MERL.L','MGGT.L','MKS.L','MNDI.L','MRW.L','NG.L','NXT.L','OML.L','PRU.L','PSN.L','PSON.L','RB.L','RBS.L','RDSA.L','RDSB.L','REL.L','RIO.L','RMG.L','RR.L','RRS.L','RSA.L','SAB.L','SBRY.L','SDR.L','SGE.L','SHP.L','SKY.L','SL.L','SMIN.L','SN.L','SPD.L','SSE.L','STAN.L','STJ.L','SVT.L','TRK.L','TSCO.L','TUI.L','TW.L','ULVR.L','UU.L','WEIR.L','WOS.L','WPP.L','WTB.L']

PW = 'HZAU8RAAvnQYrrCN'
US = 'User'
SQL = "INSERT INTO FTSE100Prices (StockTicker, Price, PriceIncrease, Time) VALUES (%s, %s, %s, %s)"

while True:
    connection = pymysql.connect(host='localhost',
                                user=US,
                                password=PW,
                                charset='utf8mb4',
                                db='Stocks',
                                cursorclass=pymysql.cursors.DictCursor)
    for count in range(0, len(FTSE100)):
        print(count)
        Time = str(time.strftime("%D %H:%M:%S", time.gmtime()))
        print(Time)
        if count <= len(FTSE100):
            stock = yahoo_finance.Share(FTSE100[count])
            change = stock.get_change()
            price = stock.get_price()
            print("{}:{}:{}".format(FTSE100[count],price,change))
            with connection.cursor() as cursor:
                cursor.execute(SQL, (FTSE100[count], price, change, Time),)
                connection.commit()

    connection.close()