diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/common.py | 24 | ||||
| -rw-r--r-- | python/load_indicative.py | 23 | ||||
| -rw-r--r-- | python/load_intex_collateral.py | 13 |
3 files changed, 30 insertions, 30 deletions
diff --git a/python/common.py b/python/common.py index f0cc3fdf..4124b669 100644 --- a/python/common.py +++ b/python/common.py @@ -11,23 +11,21 @@ conn = psycopg2.connect(database="ET", password="Serenitas1", host="debian") -def query_db(sqlstr, **kwargs): - c = conn.cursor() +def query_db(conn, sqlstr, **kwargs): + params = kwargs.get('params', None) one = kwargs.get('one', True) try: - if params: - c.execute(sqlstr, params) - else: - c.execute(sqlstr) + with conn.cursor() as c: + if params: + c.execute(sqlstr, params) + else: + c.execute(sqlstr) + if one: + return c.fetchone() + else: + return c.fetchall() except psycopg2.Error: c.close() conn.rollback() return None - - if one: - result = c.fetchone() - else: - result = c.fetchall() - c.close() - return result diff --git a/python/load_indicative.py b/python/load_indicative.py index 2ec69385..807c73a3 100644 --- a/python/load_indicative.py +++ b/python/load_indicative.py @@ -28,7 +28,7 @@ def upload_cusip_data(filename, conn): dr = csv.DictReader(fh, dialect='excel-tab') data = [] deals_to_update = [] - c = conn.cursor() + for line in dr: if "ISIN" not in line: line['ISIN'] = None @@ -40,12 +40,12 @@ def upload_cusip_data(filename, conn): dealname = line['dealname'] line = {k: convertToNone(v) for k, v in line.items()} if dealname not in dealupdate: - dealupdate[dealname] = query_db("SELECT \"Latest Update\" FROM clo_universe " \ + dealupdate[dealname] = query_db(conn, "SELECT \"Latest Update\" FROM clo_universe " \ "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", params = (dealname,))[0] sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s" - curr_date = query_db(sqlstring, params = (line['CUSIP'],)) + curr_date = query_db(conn, sqlstring, params = (line['CUSIP'],)) if not curr_date or curr_date[0] < dealupdate[dealname]: if dealname not in deals_to_update: deals_to_update.append(dealname) @@ -72,26 +72,28 @@ def upload_cusip_data(filename, conn): "%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s, " \ "%(updatedate)s)" try: - c.execute(sqlstring, line) + with conn.cursor() as c: + c.execute(sqlstring, line) except psycopg2.DataError: pdb.set_trace() print("uploaded: {0}".format(line['CUSIP'])) conn.commit() for dealname in deals_to_update: - data = query_db("SELECT p_cusip, p_curr_subordination, "\ + data = query_db(conn, "SELECT p_cusip, p_curr_subordination, "\ "p_curr_thickness from et_deal_subordination(%s)", params = (dealname,), one = False) data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in data] - c.executemany("UPDATE cusip_universe SET subordination = %s, " - "thickness = %s WHERE cusip = %s AND " - "updatedate = %s", data) + with conn.cursor() as c: + c.executemany("UPDATE cusip_universe SET subordination = %s, " + "thickness = %s WHERE cusip = %s AND " + "updatedate = %s", data) conn.commit() def upload_deal_data(filename, conn): sqlstr = "select dealname, max(\"Latest Update\") from clo_universe group by dealname" - deallist = dict(query_db(sqlstr, one=False)) + deallist = dict(query_db(conn, sqlstr, one=False)) with open( filename, "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') data = [] @@ -135,7 +137,8 @@ def upload_deal_data(filename, conn): "%(Deal Termination Date)s, %(Deal Next Pay Date)s," \ "%(Reinv End Date)s, %(Latest Update)s, %(Deal CUSIP List)s, %(Paid Down)s)" try: - c.execute(sqlstring, line) + with conn.cursor() as c: + c.execute(sqlstring, line) deallist[dealname] = line['Latest Update'] except psycopg2.DataError as detail: print(detail) diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py index a7a0336b..e78444cd 100644 --- a/python/load_intex_collateral.py +++ b/python/load_intex_collateral.py @@ -87,22 +87,21 @@ def upload_data(dealnames, workdate, conn): data.append(r) sqlstr = "select distinct(updatedate) from et_collateral where dealname= %s" - old_update_dates = [date[0] for date in query_db(sqlstr, params=(dealname,), one=False)] + old_update_dates = [date[0] for date in query_db(conn, sqlstr, params=(dealname,), one=False)] sqlstr = 'select max("Latest Update") from clo_universe where dealname= %s' - updatedate = query_db(sqlstr, params=(dealname,))[0] + updatedate = query_db(conn, sqlstr, params=(dealname,))[0] reinsert = False if updatedate in old_update_dates: sqlstr = "SELECT count(*) FROM et_collateral where dealname = %s and updatedate= %s" - currlen = query_db(sqlstr, params = (dealname, updatedate))[0] + currlen = query_db(conn, sqlstr, params = (dealname, updatedate))[0] if currlen != len(data): print("{0} has {1} rows in the database and current collateral file has {2}".format(dealname, currlen, len(data))) - c = conn.cursor() - sqlstr = "DELETE FROM et_collateral where dealname = %s and updatedate = %s" - c.execute(sqlstr, (dealname, updatedate)) + with conn.cursor() as c: + sqlstr = "DELETE FROM et_collateral where dealname = %s and updatedate = %s" + c.execute(sqlstr, (dealname, updatedate)) conn.commit() - c.close() reinsert = True if reinsert or not old_update_dates or updatedate not in old_update_dates: tag = 0 |