diff options
| -rw-r--r-- | python/backfill_index.py | 24 |
1 files changed, 13 insertions, 11 deletions
diff --git a/python/backfill_index.py b/python/backfill_index.py index 4504c416..22b7d96c 100644 --- a/python/backfill_index.py +++ b/python/backfill_index.py @@ -1,25 +1,27 @@ from sqlalchemy import MetaData, create_engine, Table -engine = create_engine('postgresql://serenitas_user@debian/serenitasdb') +from db import dbconn +serenitasdb = dbconn('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] +series=[18] #tenor = ['3yr', '5yr', '7yr', '10yr'] -tenor = ['3yr', '5yr', '7yr'] -index_type ='HY' +tenor = ['5yr', '7yr', '10yr'] +index_type ='IG' 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) + 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() |