20240327_add_created_table.py

import re
import csv
import dataset
from datetime import datetime
import rabbit_config as rcfg


def fromisoformat(date_string: str):
    regex = re.compile(
        r"(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})\.(\d{1,6})"
    )
    mo = re.search(regex, date_string)
    return datetime(*[int(m) for m in mo.groups()])


def upgrade():
    db_path = rcfg.db_path
    db_name = "user_reg.db"
    csv_filename = "./migrations/account-registration-history.csv"

    db = dataset.connect(f"sqlite:///{db_path}/{db_name}")
    table = db["users"]

    # Get all username and last_update
    usernames = db.query("SELECT username, last_update FROM users")
    users = {}
    for u in usernames:
        users[u["username"]] = {
            "username": u["username"],
            "date": fromisoformat(u["last_update"]),
            "source": "account-web",
        }

    # Get created from csv file
    with open(csv_filename) as f:
        reader = csv.reader(f, delimiter=",")
        # Skip header
        next(reader)
        for row in reader:
            if row[0] in users:
                users[row[0]]["date"] = fromisoformat(row[1])
                users[row[0]]["source"] = row[2]

    # Create new columns
    table.create_column("date", db.types.datetime)
    table.create_column("source", db.types.text)

    # Update database in chunk
    table.update_many(list(users.values()), ["username"], ensure=True)


if __name__ == "__main__":
    upgrade()