-
Notifications
You must be signed in to change notification settings - Fork 2
/
import_mysql_changelog.py
executable file
·48 lines (40 loc) · 1.38 KB
/
import_mysql_changelog.py
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#!/usr/bin/env python3
import mysql.connector
import config
import pandas as pd
import sys
from tqdm.auto import tqdm
db = mysql.connector.connect(
host="localhost",
user=config.user,
password=config.password,
database="qonqr",
autocommit=True
)
if len(sys.argv) > 1:
files = sys.argv[1:]
cur = db.cursor(dictionary=True)
cur.execute(f"SELECT MAX(LastUpdateDateUtc) as latest FROM changelog")
latest = cur.fetchone()["latest"]
print(f"Latest change recorded {latest}")
keys = "ZoneId, ZoneControlState, DateCapturedUtc, LegionCount, SwarmCount, FacelessCount, LastUpdateDateUtc".split(", ")
for f in files:
print(f"Loading {f}")
df = pd.read_csv(f)
df = df[~pd.isnull(df.LastUpdateDateUtc)]
df.DateCapturedUtc = pd.to_datetime(df.DateCapturedUtc).dt.floor("s")
df.LastUpdateDateUtc = pd.to_datetime(df.LastUpdateDateUtc).dt.floor("s")
df = df[df.LastUpdateDateUtc > latest]
print(f"{len(df)} rows to apply")
updates = 0
for i, row in tqdm(df.iterrows(), total=len(df)):
row = {k: row[k] for k in keys}
for k, v in row.items():
if pd.isnull(v):
row[k] = "NULL"
else:
row[k] = f'"{v}"'
sql = f"""REPLACE INTO changelog ({','.join(row.keys())}) VALUES ({','.join(row.values())})"""
updates += 1
cur.execute(sql)
print(f"Did {updates} updates")