Create script query_sledrun_list.

[philipp/winterrodeln/wrpylib.git] / scripts / query_sledrun_list.py

diff --git a/scripts/query_sledrun_list.py b/scripts/query_sledrun_list.py
new file mode 100644 (file)
index 0000000..e4ac49e
--- /dev/null
+++ b/scripts/query_sledrun_list.py
@@ -0,0 +1,92 @@
+#!/usr/bin/python3
+"""Uses the wrsledrunjsoncache table to generate a list of sledruns containing
+* sledrun name
+* coordinates
+* link to Winterrodeln.org article
+
+Ensure you have called updatewrsledrunjsoncache.py previously so that wrsledrunjsoncache is up to date.
+
+This script corresponds to the following SQL statement:
+
+    SELECT
+        JSON_EXTRACT(sledrun_json, '$.name') as Name,
+        JSON_EXTRACT(sledrun_json, '$.position.longitude') as Longitude,
+        JSON_EXTRACT(sledrun_json, '$.position.latitude') as Latitude,
+        JSON_EXTRACT(sledrun_json, '$.entry_under_construction') as InArbeit,
+        CONCAT('https://www.winterrodeln.org/wiki/', page_title) as Url
+    FROM wrsledrunjsoncache JOIN page ON sledrun_page_id=page_id
+    ORDER BY JSON_EXTRACT(sledrun_json, '$.name');
+
+
+Command line usage:
+$ python query_sledrun_list.py inifile1.ini ...
+
+One or more .ini configuration files can be given,
+their format is described in https://www.winterrodeln.org/trac/wiki/ConfigIni.
+At the end, the following entries have to be present:
+
+    [mysql]
+    host=localhost
+    dbname=philipp_winterrodeln_wiki
+    user_name=philipp_www
+    user_pass=YYYYYY
+
+    [mediawiki]
+    server=https://www.winterrodeln.org/
+    article_path=wiki
+"""
+import argparse
+import configparser
+import csv
+import json
+import sys
+from typing import List
+import urllib.parse
+
+from sqlalchemy.engine import create_engine, Connection
+
+
+def create_sledrun_list(connection: Connection, api_url: str):
+    sql = "SELECT " \
+        "JSON_EXTRACT(sledrun_json, '$.name') as name, " \
+        "JSON_EXTRACT(sledrun_json, '$.position.longitude') as latitude, " \
+        "JSON_EXTRACT(sledrun_json, '$.position.latitude') as longitude, " \
+        "JSON_EXTRACT(sledrun_json, '$.entry_under_construction') as under_construction, " \
+        "page_title as title " \
+        "FROM wrsledrunjsoncache JOIN page ON sledrun_page_id=page_id " \
+        "ORDER BY JSON_EXTRACT(sledrun_json, '$.name');"
+    rows = connection.execute(sql)
+
+    writer = csv.writer(sys.stdout, lineterminator='\n')
+    writer.writerow(['Name', 'Longitude', 'Latitude', 'InArbeit', 'Url'])
+    for row in rows:
+        title = row['title'].decode('utf-8')
+        url = urllib.parse.urljoin(api_url, title)
+        writer.writerow([json.loads(row['name']), row['latitude'], row['longitude'], row['under_construction'], url])
+
+
+def query_sledrun_list(inifiles: List[str]):
+    """
+    :param inifiles: filename of an .ini file or a list of .ini files.
+    """
+    config = configparser.ConfigParser()
+    config.read(inifiles)
+
+    wiki_url = config.get('mediawiki', 'server')
+    article_path = config.get('mediawiki', 'article_path')
+    wiki_path = urllib.parse.urljoin(wiki_url, article_path)
+
+    host = config.get('mysql', 'host')
+    dbname = config.get('mysql', 'dbname')
+    user = config.get('mysql', 'user_name')
+    passwd = config.get('mysql', 'user_pass')
+
+    engine = create_engine(f'mysql://{user}@{host}:3306/{dbname}?passwd={passwd}&charset=utf8mb4')
+    create_sledrun_list(engine.connect(), wiki_path)
+
+
+if __name__ == '__main__':
+    parser = argparse.ArgumentParser(description='Generates a sledrun list in CSV format.')
+    parser.add_argument('inifile', nargs='+', help='inifile.ini, see: https://www.winterrodeln.org/trac/wiki/ConfigIni')
+    args = parser.parse_args()
+    query_sledrun_list(args.inifile)