From 88e486a61fd563b3d30d34e1962269eae5a931bf Mon Sep 17 00:00:00 2001 From: =?utf8?q?Isma=C3=ABl=20Bouya?= Date: Sat, 24 Mar 2018 10:27:39 +0100 Subject: [PATCH] Add task to migrate reports to database --- tasks/import_reports_to_database.py | 50 +++++++++++++++++++++++++++++ 1 file changed, 50 insertions(+) create mode 100644 tasks/import_reports_to_database.py diff --git a/tasks/import_reports_to_database.py b/tasks/import_reports_to_database.py new file mode 100644 index 0000000..152c762 --- /dev/null +++ b/tasks/import_reports_to_database.py @@ -0,0 +1,50 @@ +import sys +import os +import simplejson as json +from datetime import datetime +from decimal import Decimal as D +import psycopg2 + +sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))) +from main import parse_config + +config = sys.argv[1] +reports = sys.argv[2:] + +pg_config, report_path = parse_config(config) + +connection = psycopg2.connect(**pg_config) +cursor = connection.cursor() + +report_query = 'INSERT INTO reports("date", "market_config_id", "debug") VALUES (%s, %s, %s) RETURNING id;' +line_query = 'INSERT INTO report_lines("date", "report_id", "type", "payload") VALUES (%s, %s, %s, %s);' + +user_id_to_market_id = { + 2: 1, + 1: 3, + } + +for report in reports: + with open(report, "rb") as f: + json_content = json.load(f, parse_float=D) + basename = os.path.basename(report) + date, rest = basename.split("_", 1) + user_id, rest = rest.split(".", 1) + + date = datetime.strptime(date, "%Y-%m-%dT%H:%M:%S.%f") + market_id = user_id_to_market_id[int(user_id)] + debug = any("debug" in x and x["debug"] for x in json_content) + print(market_id, date, debug) + cursor.execute(report_query, (date, market_id, debug)) + report_id = cursor.fetchone()[0] + + for line in json_content: + date = datetime.strptime(line["date"], "%Y-%m-%dT%H:%M:%S.%f") + type_ = line["type"] + del(line["date"]) + del(line["type"]) + + cursor.execute(line_query, (date, report_id, type_, json.dumps(line))) +connection.commit() +cursor.close() +connection.close() -- 2.41.0