diff options
Diffstat (limited to 'python/intex')
| -rw-r--r-- | python/intex/load_indicative.py | 34 |
1 files changed, 21 insertions, 13 deletions
diff --git a/python/intex/load_indicative.py b/python/intex/load_indicative.py index 84b7f653..cbbdef57 100644 --- a/python/intex/load_indicative.py +++ b/python/intex/load_indicative.py @@ -28,11 +28,17 @@ def insert_new_cusip(conn, line): line[key] = convertToNone(line[key]) to_insert += (line['Orig Balance'], line.get('Orig Moody'), line['Orig Attachment Point'], line['Orig Detachment Point'], - line['Floater Index'], line['Floater Spread/Margin'], line['Type']) - sqlstr = "INSERT INTO cusip_ref VALUES({0})".format(",".join(["%s"]*13)) + line['Floater Index'], line['Floater Spread/Margin'], + line['Type']) + sqlstr = ("INSERT INTO cusip_ref(Cusip, ISIN, bloomberg_ticker, dealname, " + "tranche, paripassu_tranches, Orig_Balance, Orig_Moody, Orig_Attach, " + "Orig_Detach, Floater_Index, Spread, type) VALUES({0}) " + "RETURNING cusip_id".format(",".join(["%s"] * 13))) with conn.cursor() as c: try: c.execute(sqlstr, to_insert) + cusip_id, = c.fetchone() + return cusip_id except psycopg2.DataError as e: logger.error(e) conn.commit() @@ -49,7 +55,7 @@ def upload_cusip_data(conn, filename): if "ISIN" not in line: line['ISIN'] = None sp = line["Tranche"].split(",") - if len(sp)==2: + if len(sp) == 2: line["dealname"], line["tranche"] = sp else: continue @@ -58,25 +64,27 @@ def upload_cusip_data(conn, filename): line = {k: convertToNone(v) for k, v in line.items()} if dealname not in dealupdate: with conn.cursor() as c: - c.execute("SELECT max(\"Latest Update\") FROM clo_universe " \ + c.execute("SELECT max(\"Latest Update\") FROM clo_universe " "WHERE dealname = %s", (dealname,)) try: dealupdate[dealname], = c.fetchone() except TypeError: - logging.error('deal:{0} not in database'.format(dealname)) + logging.error(f'deal:{dealname} not in database') continue - sqlstring = "SELECT cusip FROM cusip_ref WHERE cusip=%s" + sqlstring = "SELECT cusip_id FROM cusip_ref WHERE cusip=%s and dealname=%s" with conn.cursor() as c: - c.execute(sqlstring, (line['CUSIP'],)) + c.execute(sqlstring, (line['CUSIP'], dealname)) r = c.fetchone() if r is None: try: - insert_new_cusip(conn, line) + cusip_id = insert_new_cusip(conn, line) except ValueError: continue - sqlstring = "SELECT max(updatedate) FROM cusip_update WHERE cusip = %s" + else: + cusip_id = r[0] + sqlstring = "SELECT max(updatedate) FROM cusip_update WHERE cusip_id = %s" with conn.cursor() as c: - c.execute(sqlstring, (line['CUSIP'],)) + c.execute(sqlstring, (cusip_id,)) curr_date, = c.fetchone() if curr_date is None or curr_date < dealupdate[dealname]: try: @@ -89,7 +97,7 @@ def upload_cusip_data(conn, filename): continue line['Curr Moody'] = line.get('Curr Moody') or line.get('Orig Moody') sqlstring = "INSERT INTO cusip_update VALUES({0})".format(",".join(["%s"] * 8)) - to_insert = (line['CUSIP'], line['Curr Balance'], line['Factor'], line['Coupon'], + to_insert = (cusip_id, line['Curr Balance'], line['Factor'], line['Coupon'], line['Curr Moody'], line['Curr Attachment Point (def at MV)'], line['Curr Detachment Point (def at MV)'], dealupdate[dealname]) with conn.cursor() as c: @@ -110,7 +118,7 @@ def upload_deal_data(conn, filename): c.execute(sqlstr) deallist2 = [d[0] for d in c] conn.commit() - with open( filename, "r") as fh: + with open(filename, "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') data = [] for line in dr: @@ -181,7 +189,7 @@ def upload_deal_data(conn, filename): pdb.set_trace() conn.commit() -if __name__=="__main__": +if __name__ == "__main__": if len(sys.argv) > 1: workdate = sys.argv[1] else: |