from sqlalchemy import MetaData, create_engine, Table
engine = create_engine('postgresql://serenitas_user:Serenitas1@debian/serenitasdb')
import pandas as pd

metadata = MetaData(bind = engine)
index_quotes = Table('index_quotes', metadata, autoload=True)
#series = [9, 11, 13, 15, 17, 19, 21]
series=[9]
#tenor = ['3yr', '5yr', '7yr', '10yr']
tenor = ['3yr', '5yr', '7yr']
index_type ='HY'

for s in series:
    for t in tenor:
        index = "{0}{1}".format(index_type.lower(), s)
        stmt = index_quotes.delete().where((index_quotes.c.index==index_type) &
                                           (index_quotes.c.series==s) &
                                           (index_quotes.c.tenor==t))
        engine.execute(stmt)
        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
        df.to_sql('index_quotes', engine, if_exists='append', index=False)