diff options
Diffstat (limited to 'python/load_intex_collateral.py')
| -rw-r--r-- | python/load_intex_collateral.py | 52 |
1 files changed, 33 insertions, 19 deletions
diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py index 1b1a0861..a568c48e 100644 --- a/python/load_intex_collateral.py +++ b/python/load_intex_collateral.py @@ -1,9 +1,8 @@ import psycopg2 import os.path import re -import datetime import csv -from datetime import date +from datetime import date, datetime import pdb if os.name=='nt': @@ -11,8 +10,6 @@ if os.name=='nt': elif os.name=='posix': root = "/home/share/CorpCDOs/" -root = root + "Collaterals_2012-11-20" - fields = ['Asset Name', 'Issuer', 'Current Balance', 'Maturity Date', 'Asset Subtype', \ 'Asset Type', 'Gross Coupon', 'Spread', 'Frequency', 'Next Paydate', 'Second Lien', \ 'LoanX ID', 'CUSIP', 'Market Price', 'Market Price Source', 'Price Date', 'Fixed or Float', \ @@ -25,11 +22,17 @@ conn = psycopg2.connect(database="ET", user="et_user", password="Serenitas1", ho cursor = conn.cursor() prog = re.compile("\((.*)\)") -for dealname in os.listdir(root): +workdate = "2012-11-20" +with open(os.path.join(root, "data", "clo_universe_intex_" + workdate + ".txt")) as fh: + dr = csv.DictReader(fh, dialect='excel-tab') + deal_table = {line['Deal,Tr/CUSIP/ISIN']: datetime.strptime(line['Latest Update'], '%b %d, %Y').date() \ + for line in dr if 'Paid' not in line['Latest Update']} + +for dealname in os.listdir(os.path.join(root, "Collaterals_" + workdate)): #dealname, updatedate = line.rstrip().split() # updatedate = datetime.datetime.strptime(updatedate, '%m/%d/%Y') #dealname = dealname.upper() + ",AD.txt" - with open( os.path.join(root, dealname), "r") as fh: + with open( os.path.join(root, "Collaterals_" + workdate, dealname), "r") as fh: dr = csv.DictReader(fh, dialect = 'excel-tab') dealname , ending = dealname.split(',') dealname = dealname.lower() @@ -64,17 +67,23 @@ for dealname in os.listdir(root): print detail pdb.set_trace() data.append(r) - cursor.execute( "select \"Latest Update\" from clo_universe " - "where dealname='{0}'".format(dealname)) + + cursor.execute( "select distinct(updatedate) from et_collateral where dealname='{0}'".format(dealname)) + old_update_dates = [date[0] for date in cursor.fetchall()] + try: - updatedate = cursor.fetchone()[0] - except TypeError: + updatedate = deal_table[dealname] + except KeyError as detail: + print detail pdb.set_trace() - cursor.execute( "select max(updatedate) from et_collateral where dealname='{0}'".format(dealname)) - updatedate2 = cursor.fetchone()[0] - if updatedate2 is None: - pdb.set_trace() - if updatedate>updatedate2: + if updatedate in old_update_dates: + sqlstr = "SELECT count(*) from et_collateral where dealname = %s and updatedate= %s" + cursor.execute(sqlstr, (dealname, updatedate)) + currlen = cursor.fetchone()[0] + if currlen <> len(data): + print dealname + if not old_update_dates or updatedate not in old_update_dates: + tag = 0 for row in data: # print dealname, row sqlstr = "INSERT INTO ET_COLLATERAL(dealname, updatedate, name, IssuerName," \ @@ -85,17 +94,22 @@ for dealname in os.listdir(root): "%s, %s, %s)" try: cursor.execute(sqlstr, (dealname, updatedate) + tuple(row)) - except psycopg2.DataError: + except psycopg2.DataError as detail: + print detail pdb.set_trace() - except psycopg2.IntegrityError: + except psycopg2.IntegrityError as detail: # crazy hack intex unique id is not really unique - if dealname in ["caval1", "octag11", "windriv2"]: + if dealname in ["caval1", "octag11", "octagon8", "windriv2"]: conn.rollback() - row[0] = "Affinion Group - Tranche B Term Loan - 18" + # make sure the loan name is unique by tagging it + row[0] = row[0] + str(tag) + tag = tag+1 cursor.execute(sqlstr, (dealname, updatedate) + tuple(row)) else: + print detail pdb.set_trace() conn.commit() + cursor.close() conn.close() |