1 files changed, 61 insertions, 2 deletions
diff --git a/python/figi_backfill.py b/python/figi_backfill.py
index 96e72bed..12d33453 100644
--- a/python/figi_backfill.py
+++ b/python/figi_backfill.py
@@ -15,7 +15,7 @@ def chunk(jobs):
 
 
 class Figi:
-    openfigi_url = "https://api.openfigi.com/v1/mapping"
+    openfigi_url = "https://api.openfigi.com/v3/mapping"
     openfigi_headers = {
         "Content-Type": "text/json",
         "X-OPENFIGI-APIKEY": "c1de8a8f-7208-4601-9f2e-6665c88ca617",
@@ -93,6 +93,65 @@ def populate_cusip_ref(conn):
         conn.commit()
 
 
+def get_jobs_et_collateral(conn, d):
+    with conn.cursor() as c:
+        c.execute("SELECT cusip, array_agg(id) FROM et_collateral GROUP BY cusip")
+        for (cusip, arr) in c:
+            d[cusip] = arr
+            yield {"idType": "ID_CUSIP", "idValue": cusip}
+
+
+def get_figi(r):
+    if "data" in r:
+        return r["data"][0]["figi"]
+    elif "warning" in r:
+        logging.warning(r["warning"])
+        raise ValueError
+    elif "error" in r:
+        logging.error(r["error"])
+        raise ValueError
+
+
+def populate_et_collateral(conn):
+    d = {}
+    for job, response in submit(get_jobs_et_collateral(conn, d)):
+        with conn.cursor() as c:
+            for j, r in zip(job, response):
+                try:
+                    figi = get_figi(r)
+                except ValueError:
+                    continue
+                else:
+                    c.execute(
+                        "UPDATE et_collateral SET figi=%s WHERE id=ANY(%s)",
+                        (figi, d[j["idValue"]]),
+                    )
+        conn.commit()
+
+
+def get_jobs_et_tranche_model_numbers(conn):
+    with conn.cursor() as c:
+        c.execute("SELECT distinct figi FROM et_tranche_model_numbers")
+        for (cusip,) in c:
+            yield {"idType": "ID_CUSIP", "idValue": cusip}
+
+
+def populate_et_tranche_model_numbers(conn):
+    for job, response in submit(get_jobs_et_tranche_model_numbers(conn)):
+        with conn.cursor() as c:
+            for j, r in zip(job, response):
+                try:
+                    figi = get_figi(r)
+                except ValueError:
+                    continue
+                else:
+                    c.execute(
+                        "UPDATE et_tranche_model_numbers SET figi=%s WHERE figi=%s",
+                        (figi, j["idValue"]),
+                    )
+            conn.commit()
+
+
 if __name__ == "__main__":
     conn = dbconn("etdb")
-    populate_cusip_ref(conn)
+    populate_et_collateral(conn)