diff options
| -rw-r--r-- | python/clo_universe.py | 16 | ||||
| -rw-r--r-- | sql/et_tables.sql | 9 |
2 files changed, 5 insertions, 20 deletions
diff --git a/python/clo_universe.py b/python/clo_universe.py index ae3f0988..2590dd21 100644 --- a/python/clo_universe.py +++ b/python/clo_universe.py @@ -58,13 +58,10 @@ with open( os.path.join(root, universe), "r") as fh: line[key] = line[key].replace(",", "") dealname = line['Deal,Tr/CUSIP/ISIN'] line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"] + line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",") if dealname not in deallist or line['Latest Update'] > deallist[dealname]: sqlstring = \ - "INSERT INTO clo_universe(dealname, \"Deal Name\", Manager, \"Orig Deal Bal\"," \ - "\"Curr Deal Bal\", \"Orig Collat Bal\", \"Curr Collat Bal\", \"Tranche Factor\"," \ - "\"Principal Bal\", \"Interest Bal\",\"CDO Percentage\", \"Defaulted Bal\", \"Curr Coupon\"," \ - "\"Deal Issue Date\", Maturity, \"Deal Next Pay Date\", \"Reinv End Date\"," \ - "\"Latest Update\", \"Deal Cusip List\", PaidDown)" \ + "INSERT INTO clo_universe " \ "VALUES (%(Deal,Tr/CUSIP/ISIN)s, %(Deal Name)s, %(Collateral Manager)s, %(Orig Deal Bal)s," \ "%(Curr Deal Bal)s, %(Tranche Orig Bal)s, %(Tranche Curr Bal)s, %(Tranche Factor)s," \ "%(Collection Account Principal Balance)s," \ @@ -79,14 +76,5 @@ with open( os.path.join(root, universe), "r") as fh: conn.commit() -# cursor.execute("select dealname,\"Deal Cusip List\" from clo_universe"); -# cursor2 = conn.cursor() -# for record in cursor: -# for cusip in record[1].split(","): -# tuple = (record[0], cusip) -# cursor2.execute("INSERT INTO dealcusipmapping(dealname, cusip) VALUES(%s, %s)", tuple) -# cursor2.close() -# conn.commit() - cursor.close() conn.close() diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 797468cf..bc430547 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -150,7 +150,7 @@ CREATE TABLE clo_universe ( "Deal Next Pay Date" date, "Reinv End Date" date, "Latest Update" date, - "Deal Cusip List" text, + "Deal Cusip List" text[], PaidDown date, PRIMARY KEY (dealname, "Latest Update") ); @@ -187,11 +187,8 @@ CREATE VIEW latest_clo_universe AS GRANT ALL ON latest_clo_universe TO et_user; -CREATE TABLE dealcusipmapping ( - dealname varchar(10), - Cusip varchar(9), - PRIMARY KEY(Cusip) -); +CREATE VIEW dealcusipmapping AS + SELECT dealname, unnest("Deal Cusip List") AS Cusip from latest_clo_universe; GRANT ALL ON dealcusipmapping TO et_user; |