diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/bbg_prices.py | 48 |
1 files changed, 22 insertions, 26 deletions
diff --git a/python/bbg_prices.py b/python/bbg_prices.py index 6299fd1e..921713c1 100644 --- a/python/bbg_prices.py +++ b/python/bbg_prices.py @@ -1,10 +1,11 @@ from bbg_helpers import init_bbg_session, retreive_data, process_msgs -from sqlalchemy import create_engine +from sqlalchemy import create_engine, MetaData, Table, bindparam from db import conn import numpy as np import pandas as pd engine = create_engine('postgresql://et_user@debian/ET') +metadata = MetaData(bind = engine) session = init_bbg_session('192.168.0.14', 8194) fields_update = ["LN_ISSUE_STATUS", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT", @@ -22,39 +23,34 @@ data = retreive_data(session, securities, fields_update) df = process_msgs(data, fields_update) df.security = df.security.str.slice(0,9) df.set_index(['security'], inplace=True) -df['id_bb_unique'] = cusips['id_bb_unique'] +df['ID_BB_UNIQUE'] = cusips['id_bb_unique'].values df.reset_index(inplace=True) -with conn.cursor() as c: - for i in range(df.shape[0]): - c.execute("UPDATE bloomberg_corp_ref set defaulted = %s, default_date = %s, " \ - "called=%s, called_date = %s, status=%s " \ - "where id_bb_unique=%s", - (df.iloc[i]['DEFAULTED'], df.iloc[i]['DEFAULT_DATE'], df.iloc[i]['CALLED'], - df.iloc[i]['CALLED_DT'], df.iloc[i]['LN_ISSUE_STATUS'], df.iloc[i]['id_bb_unique'])) -conn.commit() +bloomberg_corp_ref = Table('bloomberg_corp_ref', metadata, autoload=True) +stmt = bloomberg_corp_ref.update().\ + where(bloomberg_corp_ref.c.id_bb_unique == bindparam('ID_BB_UNIQUE')).\ + values(defaulted = bindparam('DEFAULTED'), + default_date = bindparam('DEFAULT_DATE'), + called = bindparam('CALLED'), + called_date = bindparam('CALLED_DT'), + status = bindparam('LN_ISSUE_STATUS')) +conn = engine.connection() +conn.execute(stmt,df[['DEFAULTED', 'DEFAULT_DATE', 'CALLED', + 'CALLED_DT', 'LN_ISSUE_STATUS', 'ID_BB_UNIQUE']].to_dict('records')) currentdata = pd.read_sql_query("SELECT id_bb_unique, pricingdate from bloomberg_corp", engine, parse_dates=["pricingdate"], index_col=['id_bb_unique', 'pricingdate']) #no need to insert empty prices -df.dropna(subset=['PX_LAST'], inplace=True) -df.set_index(['id_bb_unique', 'LAST_UPDATE_DT'], inplace=True) +df.dropna(subset=['PX_LAST', 'LAST_UPDATE_DT'], inplace=True) +df.set_index(['ID_BB_UNIQUE', 'LAST_UPDATE_DT'], inplace=True) df = df.ix[df.index.difference(currentdata.index)] -df.index.names = ['id_bb_unique', 'LAST_UPDATE_DT'] +df.index.names = ['ID_BB_UNIQUE', 'LAST_UPDATE_DT'] df.reset_index(inplace=True) -sqlstr = "INSERT INTO bloomberg_corp VALUES(%s, %s, %s, %s, %s, %s)" -with conn.cursor() as c: - for i in range(df.shape[0]): - margin = df.iloc[i]['LN_CURRENT_MARGIN'] - if np.isnan(margin): - margin = None - amt_outstanding = df.iloc[i]['AMT_OUTSTANDING'] - if np.isnan(amt_outstanding): - amt_outstanding = None - c.execute(sqlstr, (df.iloc[i]['id_bb_unique'], df.iloc[i]['LAST_UPDATE_DT'], - df.iloc[i]['PX_LAST'], margin, amt_outstanding, - df.iloc[i]['PRICING_SOURCE'])) -conn.commit() +to_insert = df[['ID_BB_UNIQUE','LAST_UPDATE_DT','PX_LAST','LN_CURRENT_MARGIN', + 'AMT_OUTSTANDING', 'PRICING_SOURCE']] +to_insert.columns = ['id_bb_unique', 'pricingdate', 'price', 'loan_margin', + 'amount_outstanding', 'source'] +to_insert.to_sql('bloomberg_corp', engine, if_exists='append', index=False) |