diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/load_intex_collateral.py | 47 |
1 files changed, 24 insertions, 23 deletions
diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py index 4f937be7..9e3a1c07 100644 --- a/python/load_intex_collateral.py +++ b/python/load_intex_collateral.py @@ -18,17 +18,25 @@ fields = ['Asset Name', 'Issuer', 'Contributed Balance', 'Maturity Date', 'Asset def convertToNone(s): return None if s=='' else s +def sanitize_float(intex_float): + intex_float = intex_float.replace(",", "") + if "(" in intex_float: + intex_float = - float(intex_float[1:-1]) + else: + intex_float = float(intex_float) + return intex_float + conn = psycopg2.connect(database="ET", user="et_user", password="Serenitas1", host="192.168.1.108") cursor = conn.cursor() -prog = re.compile("\((.*)\)") -workdate = "2012-12-11" +workdate = "2013-01-04" 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, "data", "Collaterals_" + workdate)): #dealname, updatedate = line.rstrip().split() # updatedate = datetime.datetime.strptime(updatedate, '%m/%d/%Y') @@ -39,10 +47,10 @@ for dealname in os.listdir(os.path.join(root, "data", "Collaterals_" + workdate) dealname = dealname.lower() data = [] for line in dr: - missingfields = list(set(fields) - set(dr.fieldnames)) + missingfields = set(fields) - set(dr.fieldnames) for field in missingfields: line[field] = None - if line['Fixed or Float'] is not None: + if line['Fixed or Float']: line['Fixed or Float'] = line['Fixed or Float'].upper() if 'LoanX ID' in dr.fieldnames and len(line['LoanX ID']) >8: print "dubious id found: {0}".format(line['LoanX ID']) @@ -54,19 +62,10 @@ for dealname in os.listdir(os.path.join(root, "data", "Collaterals_" + workdate) break except AttributeError as detail: print dealname - #convert (x) to -x - for field in ['Spread', 'Gross Coupon']: - if line[field] and prog.match(line[field]): - line[field] = - float(prog.match(line[field]).group(1)) - if line['Market Price'] != '' and isinstance(line['Market Price'], str): - line['Market Price'] = float(line['Market Price'].replace(',','')) - if isinstance(line['Contributed Balance'], str): - line['Contributed Balance'] = line['Contributed Balance'].replace(',','') - if prog.match(line['Contributed Balance']): - line['Contributed Balance'] = -float(prog.match(line['Contributed Balance']).group(1)) - else: - line['Contributed Balance'] = float(line['Contributed Balance']) + for field in ['Spread', 'Gross Coupon', 'Market Price', 'Contributed Balance']: + if line[field]: + line[field] = sanitize_float(line[field]) try: r = [convertToNone(line[field]) for field in fields] except KeyError as detail: @@ -89,7 +88,7 @@ for dealname in os.listdir(os.path.join(root, "data", "Collaterals_" + workdate) cursor.execute(sqlstr, (dealname, updatedate)) currlen = cursor.fetchone()[0] if currlen <> len(data): - print "{0} has {1} rows in the database and current collateral file has {2}".format(dealname, currlen, data) + print "{0} has {1} rows in the database and current collateral file has {2}".format(dealname, currlen, len(data)) sqlstr = "DELETE FROM et_collateral where dealname = %s and updatedate = %s" cursor.execute(sqlstr, (dealname, updatedate)) conn.commit() @@ -98,12 +97,14 @@ for dealname in os.listdir(os.path.join(root, "data", "Collaterals_" + workdate) tag = 0 for row in data: # print dealname, row - sqlstr = "INSERT INTO ET_COLLATERAL(dealname, updatedate, name, IssuerName," \ - "CurrentBalance, Maturity, AssetSubtype, AssetType, GrossCoupon," \ - "Spread, Frequency, NextPaydate, SecondLien, LoanXID, Cusip, IntexPrice," \ - "IntexPriceSource, IntexPriceDate, FixedOrFloat, DefaultedFlag, CovLite, isCDO)" \ - "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \ - "%s, %s, %s)" + sql_fields = ["dealname", "updatedate", "name", "IssuerName", "CurrentBalance", + "Maturity", "AssetSubtype", "AssetType", "GrossCoupon", + "Spread", "Frequency", "NextPaydate", "SecondLien", "LoanXID", + "Cusip", "IntexPrice", "IntexPriceSource", "IntexPriceDate", + "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO"] + + sqlstr = "INSERT INTO ET_COLLATERAL({0}) VALUES({1})".format(",".join(sql_fields), + ",".join(["%s"] * len(sql_fields))) try: cursor.execute(sqlstr, (dealname, updatedate) + tuple(row)) except psycopg2.DataError as detail: |