1 files changed, 15 insertions, 21 deletions
diff --git a/python/database_consistency.py b/python/database_consistency.py
index d2ba762a..b05e9d81 100644
--- a/python/database_consistency.py
+++ b/python/database_consistency.py
@@ -1,24 +1,18 @@
-from db import query_db
-import pdb
+import pandas as pd
 import datetime
+from sqlalchemy import create_engine
 
 if __name__=="__main__":
-    sqlstr1 = "select dealname, \"Curr Collat Bal\", \"Latest Update\" from clo_universe"
-    sqlstr2 = "select dealname, sum(currentbalance), updatedate from et_collateral group by dealname, updatedate"
-
-    data1 = query_db(sqlstr1, one=False)
-    data2 = query_db(sqlstr2, one=False)
-    dict1 = {(e[0], e[2]): e[1] for e in data1}
-    dict2 = {(e[0], e[2]): e[1] for e in data2}
-    res = []
-    for k, v in dict1.items():
-        try:
-            if abs(dict2[k] -v)>1:
-                pass
-                res.append([k, v, dict2[k]])
-        except KeyError:
-            #res.append([k, v])
-            pass
-    res = sorted(res, key=lambda x: x[0][1])
-    for e in res:
-        print("{0} {1} {2} {3}".format(e[0][0], e[0][1], e[1], e[2]))
+    engine = create_engine('postgresql://et_user@debian/ET')
+    data1 = pd.read_sql("""SELECT dealname, "Curr Collat Bal" AS bal, "Latest Update" AS date
+    FROM clo_universe""", engine, parse_dates=["date"], index_col=["dealname", "date"])
+    data1bis = pd.read_sql("""SELECT dealname, "Curr Deal Bal" AS bal, "Latest Update" AS date
+    FROM clo_universe""", engine, parse_dates=["date"], index_col=["dealname", "date"])
+    data2 = pd.read_sql("""SELECT dealname, sum(currentbalance) AS bal, updatedate
+    AS date FROM et_collateral GROUP BY dealname, updatedate""", engine,
+                        parse_dates=["date"], index_col=["dealname", "date"])
+    data3 = pd.read_sql("""SELECT dealname, sum(curr_balance) AS bal, updatedate AS date
+    FROM cusip_universe GROUP BY dealname, updatedate""", engine, index_col=["dealname", "date"])
+    data12 = pd.merge(data1, data2, left_index=True, right_index=True)
+    #data13 = pd.merge(data1bis, data3, left_index=True, right_index=True)
+    print(data12.query('bal_x-bal_y>1|bal_x-bal_y<-1').sortlevel(level=1))