diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/parse_emails.py | 20 | ||||
| -rw-r--r-- | python/parse_gs_exchange.py | 22 |
2 files changed, 12 insertions, 30 deletions
diff --git a/python/parse_emails.py b/python/parse_emails.py index 795e1c2d..11c36c23 100644 --- a/python/parse_emails.py +++ b/python/parse_emails.py @@ -2,6 +2,8 @@ import pandas as pd import re import os import pdb +from db import dbconn +import psycopg2.sql from download_emails import update_emails import datetime import logging @@ -330,22 +332,20 @@ def parse_email(email): subject, email.name)) def write_todb(swaption_stack, index_data): - 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 conn = dbconn('serenitasdb') - psycopg2.extensions.register_adapter(float, nan_to_null) + sql_str = "INSERT INTO swaption_ref_quotes({}) VALUES({}) " \ + "ON CONFLICT DO NOTHING" + cols = index_data.columns + sql_str = sql_str.format(sql.SQL(", ").join(sql.Identifier(c) for c in cols), + sql.SQL(", ").join(sql.PlaceHolder() * len(cols))) 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" + sql_str = sql.SQL("INSERT INTO swaption_quotes({}) VALUES({}) ON CONFLICT DO NOTHING") cols = swaption_stack.columns - sql_str = sql_str.format(",".join(cols), - ",".join(["%s"]* len(cols))) + sql_str = sql_str.format(sql.SQL(", ").join(sql.Identifier(c) for c in cols), + sql.SQL(", ").join(sql.PlaceHolder() * len(cols))) with conn.cursor() as c: c.executemany(sql_str, swaption_stack.itertuples(index=False)) conn.commit() diff --git a/python/parse_gs_exchange.py b/python/parse_gs_exchange.py index 9194a2a5..847132c7 100644 --- a/python/parse_gs_exchange.py +++ b/python/parse_gs_exchange.py @@ -1,6 +1,7 @@ from db import dbconn from exchangelib import Credentials, Mailbox, Configuration, Account, DELEGATE from pytz import timezone +from parse_emails import write_todb import datetime import json @@ -112,25 +113,6 @@ def clean_df(all_df): all_df['quote_source'] = 'GS' return all_df -def insert_data(swaption_quotes, index_df): - conn = dbconn('serenitasdb') - format_str = "INSERT INTO swaption_ref_quotes({}) VALUES({}) " \ - "ON CONFLICT DO NOTHING" - sqlstr = format_str.format(",".join(index_df.columns), - ",".join(["%s"] * len(index_df.columns))) - with conn.cursor() as c: - c.executemany(sqlstr, index_df.itertuples(index=False)) - conn.commit() - - format_str = "INSERT INTO swaption_quotes({}) VALUES({}) " \ - "ON CONFLICT DO NOTHING" - sqlstr = format_str.format(",".join(swaption_quotes.columns), - ",".join(["%s"] * len(swaption_quotes.columns))) - with conn.cursor() as c: - c.executemany(sqlstr, swaption_quotes.itertuples(index=False)) - conn.commit() - conn.close() - if __name__=="__main__": fwd_index = [] swaption_quotes = {} @@ -141,4 +123,4 @@ if __name__=="__main__": swaption_quotes = clean_df(swaption_quotes) index_df = pd.DataFrame(fwd_index) index_df = index_df.drop_duplicates(['quotedate', 'index', 'series', 'expiry']) - insert_data(swaption_quotes, index_df) + write_todb(swaption_quotes, index_df) |