diff options
| -rw-r--r-- | python/intex/load_indicative.py | 34 | ||||
| -rw-r--r-- | sql/et_tables.sql | 31 |
2 files changed, 40 insertions, 25 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: diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 3713d535..b039edb5 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -341,10 +341,11 @@ CREATE TABLE cusip_universe_old ( GRANT ALL ON cusip_universe TO et_user; CREATE TABLE cusip_ref( - Cusip varchar(9) PRIMARY KEY, + cusip_id serial PRIMARY KEY, + Cusip varchar(9) NOT NULL, ISIN varchar(12), bloomberg_ticker text, - dealname varchar(10), + dealname varchar(10) NOT NULL, tranche text, paripassu_tranches text[], Orig_Balance float, @@ -353,10 +354,11 @@ CREATE TABLE cusip_ref( Orig_Detach float, Floater_Index text, Spread float, - type text); + type text + UNIQUE (Cusip, dealname)); CREATE TABLE cusip_update( - Cusip varchar(9) REFERENCES cusip_ref, + cusip_id int REFERENCES cusip_ref, curr_balance float, factor float, coupon float, @@ -367,9 +369,13 @@ CREATE TABLE cusip_update( PRIMARY KEY(cusip, updatedate)); CREATE OR REPLACE VIEW cusip_universe AS - SELECT a.*, b.curr_balance, b.factor, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, + SELECT a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, + a.paripassu_tranches, a.orig_balance, a.orig_moody, + a.orig_attach, a.orig_detach, a.floater_index, a.spread, a.type, + b.curr_balance, b.factor, b.coupon, b.curr_moody, + b.curr_attach, b.curr_detach, b.updatedate FROM cusip_ref a - JOIN cusip_update b USING (cusip); + JOIN cusip_update b USING (cusip_id); CREATE OR REPLACE VIEW latest_clo_universe AS SELECT b.dealname, "Deal Name", Manager, b.orig_deal_bal, b."Curr Deal Bal", @@ -386,13 +392,14 @@ GRANT ALL ON latest_clo_universe TO et_user; CREATE OR REPLACE VIEW latest_cusip_universe AS WITH latest_cusip_update AS - (SELECT DISTINCT ON (cusip) * FROM cusip_update - ORDER BY cusip, updatedate DESC) - SELECT a.*, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, + (SELECT DISTINCT ON (cusip_id) * FROM cusip_update + ORDER BY cusip_id, updatedate DESC) + SELECT a.cusip, a.isin, a.bloomberg_ticker, a.dealname, + a.tranche, a.paripassu_tranches, a.orig_balance, a.orig_moody, + a.orig_attach, a.orig_detach, a.floater_index, a.spread, + a.type, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, b.updatedate FROM cusip_ref a - JOIN latest_cusip_update b USING (cusip); - -GRANT ALL ON latest_cusip_universe TO et_user; + JOIN latest_cusip_update b USING (cusip_id); CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_date date) RETURNS SETOF latest_clo_universe AS $$ |