diff options
Diffstat (limited to 'python/cusip_universe.py')
| -rw-r--r-- | python/cusip_universe.py | 37 |
1 files changed, 18 insertions, 19 deletions
diff --git a/python/cusip_universe.py b/python/cusip_universe.py index b8d2d100..15102f2b 100644 --- a/python/cusip_universe.py +++ b/python/cusip_universe.py @@ -7,6 +7,7 @@ import csv import pdb import sys import common +from common import query_db def convertToNone(s): return None if s=="" else s @@ -21,13 +22,14 @@ def sanitize_float(intex_float): intex_float = float(intex_float) return intex_float -def upload_data(workdate, conn, cursor): +def upload_data(workdate, conn): dealupdate = {} for cusip_universe_file in os.listdir(os.path.join(common.root, "data", "Trinfo_" + workdate)): with open( os.path.join(common.root, "data", "Trinfo_" + workdate, cusip_universe_file), "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') data = [] deals_to_update = [] + c = conn.cursor() for line in dr: if "ISIN" not in line: line['ISIN'] = None @@ -39,12 +41,12 @@ def upload_data(workdate, conn, cursor): dealname = line['dealname'] line = {k: convertToNone(v) for k, v in line.items()} if dealname not in dealupdate: - cursor.execute("SELECT \"Latest Update\" FROM clo_universe " \ - "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,)) - dealupdate[dealname] = cursor.fetchone()[0] + dealupdate[dealname] = query_db("SELECT \"Latest Update\" FROM clo_universe " \ + "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", + params = (dealname,))[0] + sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s" - cursor.execute(sqlstring, (line['CUSIP'],)) - curr_date = cursor.fetchone() + curr_date = query_db(sqlstring, params = (line['CUSIP'],)) if not curr_date or curr_date[0] < dealupdate[dealname]: if dealname not in deals_to_update: deals_to_update.append(dealname) @@ -71,23 +73,21 @@ def upload_data(workdate, conn, cursor): "%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s, " \ "%(updatedate)s)" try: - cursor.execute(sqlstring, line) + c.execute(sqlstring, line) except psycopg2.DataError: pdb.set_trace() print("uploaded: {0}".format(line['CUSIP'])) conn.commit() + for dealname in deals_to_update: - try: - cursor.execute("SELECT p_cusip, p_curr_subordination, "\ - "p_curr_thickness from et_deal_subordination(%s)", - (dealname,)) - except psycopg2.DataError: - pdb.set_trace() - data = cursor.fetchall() + data = query_db("SELECT p_cusip, p_curr_subordination, "\ + "p_curr_thickness from et_deal_subordination(%s)", + params = (dealname,), + one = False) data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in data] - cursor.executemany("UPDATE cusip_universe SET subordination = %s, " - "thickness = %s WHERE cusip = %s AND " - "updatedate = %s", data) + c.executemany("UPDATE cusip_universe SET subordination = %s, " + "thickness = %s WHERE cusip = %s AND " + "updatedate = %s", data) conn.commit() if __name__=="__main__": @@ -96,6 +96,5 @@ if __name__=="__main__": else: workdate = str(datetime.date.today()) - upload_data(workdate, common.conn, common.cursor) - common.cursor.close() + upload_data(workdate, common.conn) common.conn.close() |