from sqlalchemy import MetaData, create_engine, Table
from db import dbconn
serenitasdb = dbconn('serenitasdb')
import pandas as pd

#series = [9, 11, 13, 15, 17, 19, 21]
series=[18]
#tenor = ['3yr', '5yr', '7yr', '10yr']
tenor = ['5yr', '7yr', '10yr']
index_type ='IG'

for s in series:
    for t in tenor:
        index = "{0}{1}".format(index_type.lower(), s)
        df = pd.read_excel('/home/share/CorpCDOs/index historical.xlsx', "{0} {1}".format(index, t))
        df.rename(columns={k: k.lower() for k in df.columns.tolist()}, inplace=True)
        df['index']= index_type
        df['series']=s
        df['tenor']=t
        stmt = """INSERT INTO index_quotes(date, index, series, version, tenor, closeprice, closespread,
        modelprice, modelspread) VALUES(%(date)s, %(index)s, %(series)s, %(version)s, %(tenor)s,
        %(closeprice)s, %(closespread)s, %(modelprice)s, %(modelspread)s)
        ON CONFLICT DO NOTHING"""
        with serenitasdb.cursor() as c:
            c.executemany(stmt, df.to_dict('record'))
        serenitasdb.commit()
serenitasdb.close()