diff options
Diffstat (limited to 'python/citco.py')
| -rw-r--r-- | python/citco.py | 20 |
1 files changed, 17 insertions, 3 deletions
diff --git a/python/citco.py b/python/citco.py index 8bfc38ae..ab5d9aec 100644 --- a/python/citco.py +++ b/python/citco.py @@ -1,6 +1,8 @@ import pandas as pd from serenitas.utils.env import DAILY_DIR -from serenitas.utils.db import dawn_engine +from serenitas.utils.db import dawn_engine, dbconn +from serenitas.utils.remote import SftpClient +import datetime def load_citco_report(fdir): @@ -8,10 +10,22 @@ def load_citco_report(fdir): df["row"] = df.index df.columns = df.columns.str.lower() df.columns = df.columns.str.replace(" ", "_") + df["period_end_date"] = pd.to_datetime(df["period_end_date"]) return df if __name__ == "__main__": - fdir = DAILY_DIR / "Selene/onboarding/reporting/sample_report.csv" - df = load_citco_report(fdir) + date = datetime.date.today() + reports_dir = DAILY_DIR / str(date) / "Reports" + sftp = SftpClient.from_creds("citco") + for f in sftp.client.listdir("/outgoing"): + if "SPOS4X" in f: + sftp.client.get( + f"/outgoing/{f}", localpath=reports_dir / f"Valuation_Report_ISOSEL.csv" + ) + df = load_citco_report(reports_dir / f"Valuation_Report_ISOSEL.csv") + conn = dbconn("dawndb") + with conn.cursor() as c: + c.execute("DELETE from citco_reports where period_end_date =%s", (date,)) + conn.commit() df.to_sql("citco_reports", dawn_engine, if_exists="append", index=False) |