blob: ab5d9aec1584d70bc85281093871257c109fefa1 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
import pandas as pd
from serenitas.utils.env import DAILY_DIR
from serenitas.utils.db import dawn_engine, dbconn
from serenitas.utils.remote import SftpClient
import datetime
def load_citco_report(fdir):
df = pd.read_csv(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__":
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)
|
