diff options
| -rw-r--r-- | python/parse_emails.py | 41 |
1 files changed, 24 insertions, 17 deletions
diff --git a/python/parse_emails.py b/python/parse_emails.py index f0ea1885..b7922435 100644 --- a/python/parse_emails.py +++ b/python/parse_emails.py @@ -268,8 +268,10 @@ def parse_gs(fh, indextype, series, quotedate, ref): expiry = pd.to_datetime(expiry, format='%d%b%y') d.update({'fwdspread': fwdspread, 'fwdprice': fwdprice, 'expiry': expiry}) - fwd_index.append(d) - option_stack[expiry] = parse_gs_block(fh, indextype) + fwd_index.append(d.copy()) + option_stack[expiry] = parse_gs_block(fh, indextype) + else: + logging.error("Can't parse expiry line:", line) fwd_index = pd.DataFrame.from_records(fwd_index, index='quotedate') return option_stack, fwd_index @@ -303,8 +305,8 @@ def parse_email(email): return (quotedate, indextype, series), \ parse_fun(fh, indextype, series, quotedate) elif source == "GS": - return (quotedate, indextype, series), \ - parse_fun(fh, indextype, series, quotedate, ref) + return (quotedate, indextype, series), \ + parse_fun(fh, indextype, series, quotedate, ref) else: option_stack = parse_fun(fh, indextype, expiration_dates) fwd_index = pd.DataFrame({'quotedate': quotedate, @@ -318,17 +320,26 @@ def parse_email(email): subject, email.name)) def write_todb(swaption_stack, index_data): - from sqlalchemy import MetaData, Table - from db import dbengine, nan_to_null + sql_str = "INSERT INTO swaption_ref_quotes({}) VALUES({}) ON CONFLICT DO NOTHING" + cols = index_data.columns + sql_str = sql_str.format(",".join(cols), + ",".join(["%s"]* len(cols))) + from db import dbconn, nan_to_null import psycopg2 - serenitasdb = dbengine('serenitasdb') + conn = dbconn('serenitasdb') psycopg2.extensions.register_adapter(float, nan_to_null) - meta = MetaData(bind=serenitasdb) - swaption_quotes = Table('swaption_quotes', meta, autoload=True) - for r in swaption_stack.to_dict(orient='records'): - serenitasdb.execute(swaption_quotes.insert(), r) - #ins = swaption_quotes.insert().values(swaption_stack.to_dict(orient='records')).execute() - index_data.to_sql('swaption_ref_quotes', serenitasdb, if_exists='append', index=False) + with conn.cursor() as c: + c.executemany(sql_str, index_data.itertuples(index=False)) + conn.commit() + + sql_str = "INSERT INTO swaption_quotes({}) VALUES({}) ON CONFLICT DO NOTHING" + cols = swaption_stack.columns + sql_str = sql_str.format(",".join(cols), + ",".join(["%s"]* len(cols))) + with conn.cursor() as c: + c.executemany(sql_str, swaption_stack.itertuples(index=False)) + conn.commit() + conn.close() def get_email_list(date): """returns a list of email file names for a given date @@ -385,10 +396,6 @@ if __name__=="__main__": index_data['index'] = index_data['index'].astype('category') swaption_stack = pd.concat(swaption_stack, names=['quotedate', 'index', 'series']) - # import feather - # feather.write_dataframe(swaption_stack, '../../data/swaptions.fth') - # feather.write_dataframe(index_data, '../../data/index_data.fth') - swaption_stack = swaption_stack.reset_index() swaption_stack = swaption_stack.drop_duplicates(['quotedate', 'index', 'series', 'expiry', 'strike']) index_data = index_data.reset_index() |