diff options
| -rw-r--r-- | python/cusip_universe.py | 61 |
1 files changed, 29 insertions, 32 deletions
diff --git a/python/cusip_universe.py b/python/cusip_universe.py index b2b261a2..f90523b9 100644 --- a/python/cusip_universe.py +++ b/python/cusip_universe.py @@ -21,7 +21,7 @@ conn = psycopg2.connect(database="ET", cursor = conn.cursor() # cursor.execute("delete from cusip_universe") -workdate = '2013-01-04' +workdate = '2013-01-15' count = 0 def sanitize_float(intex_float): @@ -32,6 +32,7 @@ def sanitize_float(intex_float): intex_float = float(intex_float) return intex_float +data = [] for cusip_universe_file in os.listdir(os.path.join(root, "data", "Trinfo_" + workdate)): with open( os.path.join(root, "data", "Trinfo_" + workdate, cusip_universe_file), "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') @@ -43,39 +44,35 @@ for cusip_universe_file in os.listdir(os.path.join(root, "data", "Trinfo_" + wor else: continue line["dealname"] = line["dealname"].lower() - # for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point', - # 'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon']: - # if line[key]: - # line[key] = sanitize_float(line[key]) - for key in ['Curr Balance', 'Curr Attachment Point', 'Curr Detachment Point', 'Factor']: - if line[key]: - line[key] = sanitize_float(line[key]) - line[key] = convertToNone(line[key]) - - # if "," in str(line['Floater Spread']): - # line['Floater Spread'] = line['Floater Spread'].split(",")[0] - - # if line['Floater Spread']: - # line['Floater Spread'] = sanitize_float(line['Floater Spread']) - - # sqlstring = "INSERT INTO cusip_universe(cusip, ISIN, dealname, tranche," \ - # "coupon, Orig_Balance, Curr_Balance, Factor, Orig_moody, Curr_moody, " \ - # "Orig_attach, Orig_detach, Curr_attach, Curr_detach, Floater_Index," \ - # "Spread) " \ - # "VALUES (%(CUSIP)s, %(ISIN)s, %(dealname)s, %(tranche)s, %(Coupon)s, " \ - # "%(Orig Balance)s, %(Curr Balance)s, %(Factor)s, %(Orig Moody)s, %(Curr Moody)s, " \ - # "%(Orig Attachment Point)s, %(Orig Detachment Point)s, %(Curr Attachment Point)s," \ - # "%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s)" - - sqlstring = "UPDATE cusip_universe SET Curr_Balance = %(Curr Balance)s, Factor = %(Factor)s" \ - ", Curr_moody = %(Curr Moody)s, Curr_attach = %(Curr Attachment Point)s WHERE cusip = %(CUSIP)s" - - try: - cursor.execute(sqlstring, line) - except psycopg2.DataError as error: - pdb.set_trace() + sqlstring = "SELECT * FROM cusip_universe WHERE cusip = %s" + cursor.execute(sqlstring, (line['CUSIP'],)) + line = {k: convertToNone(v) for k, v in line.iteritems()} + if cursor.fetchone(): + for key in ['Curr Balance', 'Curr Attachment Point', 'Curr Detachment Point', 'Factor']: + if line[key]: + line[key] = sanitize_float(line[key]) + line[key] = convertToNone(line[key]) + sqlstring = "UPDATE cusip_universe SET Curr_Balance = %(Curr Balance)s, " \ + "Factor = %(Factor)s, Curr_moody = %(Curr Moody)s, " \ + "Curr_attach = %(Curr Attachment Point)s WHERE cusip = %(CUSIP)s" + else: + for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point', + 'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon']: + if line[key]: + line[key] = sanitize_float(line[key]) + line[key] = convertToNone(line[key]) + sqlstring = "INSERT INTO cusip_universe(cusip, ISIN, dealname, tranche," \ + "coupon, Orig_Balance, Curr_Balance, Factor, Orig_moody, Curr_moody, " \ + "Orig_attach, Orig_detach, Curr_attach, Curr_detach, Floater_Index," \ + "Spread) " \ + "VALUES (%(CUSIP)s, %(ISIN)s, %(dealname)s, %(tranche)s, %(Coupon)s, " \ + "%(Orig Balance)s, %(Curr Balance)s, %(Factor)s, %(Orig Moody)s, %(Curr Moody)s, " \ + "%(Orig Attachment Point)s, %(Orig Detachment Point)s, %(Curr Attachment Point)s," \ + "%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s)" + cursor.execute(sqlstring, line) print count conn.commit() + cursor.close() conn.close() |