-import itertools
-import json
-from sqlalchemy import schema, orm
-from wrpylib import mwdb, wrmwdb
-
-
-def main():
- filename = '/home/philipp/daten/Winterrodeln/Intermaps/intermaps_winterrodeln_2019-12-06_090501.json'
- with open(filename) as fp:
- content = json.load(fp)
- regions = content['items']
- sledruns = list(itertools.chain.from_iterable(region['sledruns']['items'] for region in regions))
- sledrun_ids_list = [sledrun['id'] for sledrun in sledruns]
- sledrun_ids_set = set(sledrun_ids_list)
- duplicates = {sledrun_id for sledrun_id in sledrun_ids_set if sum(sr_id == sledrun_id for sr_id in sledrun_ids_list) >= 2}
- print(duplicates)
- print(max(map(len, sledrun_ids_set))) # 36
- with open(filename, 'w') as fp:
- json.dump(content, fp, indent='\t')
+import datetime
+import re
+from sqlalchemy import orm
+from sqlalchemy.orm.session import Session
+from wrpylib.wrorm import Page, WrIntermapsSledrun, WrIntermapsReport, WrIntermapsReportHistory
class UpdateError(RuntimeError):
yield region, sledrun
-def update_wrintermapssledrun(engine, json_content: dict):
- """Updates the wrintermapssledrun table from the wiki. If convert errors occur, an UpdateError exception
+def region_to_region_with_sledrun_dict(region: dict):
+ region = region.copy()
+ region.update({'sledruns': {sledrun['id']: sledrun for sledrun in region['sledruns']['items']}})
+ return region
+
+
+def json_content_to_region_dict(json_content: dict) -> dict:
+ return {region['id']: region_to_region_with_sledrun_dict(region) for region in json_content['items']}
+
+
+def update_wrintermapssledrun(session: Session, json_content: dict):
+ """Updates the wrintermapssledrun table from the wiki. By filling in the missing fields of rows as long as
+ intermaps_sledrun_id and wr_page_id are set.
+ If convert errors occur, an UpdateError exception
is raised. No other exception type should be raised under normal circumstances.
>>> import json
>>> from sqlalchemy.engine import create_engine
- >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
+ >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8mb4')
+ >>> Session = orm.sessionmaker(bind=engine)
>>> with open('2019-12-06_090500.json') as fp:
>>> content = json.load(fp)
- >>> update_wrintermapssledrun(engine, content)
+ >>> update_wrintermapssledrun(Session(), content)
"""
- class Page:
- pass
-
- class IntermapsSledrun:
- pass
-
- metadata = schema.MetaData()
- wrintermapssledrun_table = wrmwdb.wrintermapssledrun_table(metadata)
- page_table = mwdb.page_table(metadata)
-
- Session = orm.sessionmaker(bind=engine)
- session = Session()
- orm.mapper(IntermapsSledrun, wrintermapssledrun_table)
- orm.mapper(Page, page_table)
-
- q = session.query(IntermapsSledrun)
+ q = session.query(WrIntermapsSledrun)
for intermaps_sledrun in q:
if intermaps_sledrun.intermaps_sledrun_name is None or intermaps_sledrun.intermaps_region_id is None or \
intermaps_sledrun.intermaps_region_name is None or intermaps_sledrun.intermaps_country is None:
if region_json is None:
raise UpdateError(
f'Sledrun with intermaps id {intermaps_sledrun.intermaps_sledrun_id} not found in JSON')
- if intermaps_sledrun.intermaps_sledrun_name is None:
- intermaps_sledrun.intermaps_sledrun_name = sledrun_json['name']
- if intermaps_sledrun.intermaps_region_id is None:
- intermaps_sledrun.intermaps_region_id = region_json['id']
- if intermaps_sledrun.intermaps_region_name is None:
- intermaps_sledrun.intermaps_region_name = region_json['names']['de']
- if intermaps_sledrun.intermaps_country is None:
- intermaps_sledrun.intermaps_country = region_json['countryCode']
+ if intermaps_sledrun.intermaps_sledrun_name is None:
+ intermaps_sledrun.intermaps_sledrun_name = sledrun_json['name']
+ if intermaps_sledrun.intermaps_region_id is None:
+ intermaps_sledrun.intermaps_region_id = region_json['id']
+ if intermaps_sledrun.intermaps_region_name is None:
+ intermaps_sledrun.intermaps_region_name = region_json['names']['de']
+ if intermaps_sledrun.intermaps_country is None:
+ intermaps_sledrun.intermaps_country = region_json['countryCode']
if intermaps_sledrun.wr_page_title is None:
page = session.query(Page).get(intermaps_sledrun.wr_page_id)
session.commit()
-if __name__ == '__main__':
- from sqlalchemy.engine import create_engine
- engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
- filename = '/home/philipp/daten/Winterrodeln/Intermaps/intermaps_winterrodeln_2019-12-06_090501.json'
- with open(filename) as fp:
- content = json.load(fp)
- update_wrintermapssledrun(engine, content)
+def update_wrintermapsreporthistory(session: Session):
+ """add current content of wrintermapsreport to wrintermapshistory but does not commit session"""
+ # the following lines are equivalent to
+ # insert into wrintermapsreporthistory (intermaps_sledrun_id, status, last_update, first_check, utc_offset)
+ # select intermaps_sledrun_id, status, last_update, last_check, utc_offset from wrintermapsreport r
+ # where not exists (select 1 from wrintermapsreporthistory h
+ # where h.intermaps_sledrun_id=r.intermaps_sledrun_id and h.status=r.status and
+ # h.last_update=r.last_update and h.utc_offset=r.utc_offset);
+ q = session.query(WrIntermapsReportHistory) \
+ .filter(WrIntermapsReportHistory.intermaps_sledrun_id == WrIntermapsReport.intermaps_sledrun_id) \
+ .filter(WrIntermapsReportHistory.status == WrIntermapsReport.status) \
+ .filter(WrIntermapsReportHistory.last_update == WrIntermapsReport.last_update) \
+ .filter(WrIntermapsReportHistory.utc_offset == WrIntermapsReport.utc_offset)
+ for report in session.query(WrIntermapsReport).filter(~q.exists()):
+ report_history = WrIntermapsReportHistory()
+ report_history.intermaps_sledrun_id = report.intermaps_sledrun_id
+ report_history.status = report.status
+ report_history.last_update = report.last_update
+ report_history.first_check = report.last_check
+ report_history.utc_offset = report.utc_offset
+ session.add(report_history)
+
+
+def insert_json_to_intermapsreport(session: Session, json_content: dict, last_check: datetime.datetime):
+ """add JSON content to intermaps_report. Does not commit the session"""
+ regions = json_content_to_region_dict(json_content)
+ for sledrun in session.query(WrIntermapsSledrun):
+ region = regions.get(sledrun.intermaps_region_id)
+ if region is None:
+ continue
+ sledrun_json = region['sledruns'].get(sledrun.intermaps_sledrun_id)
+ if sledrun_json is None:
+ continue
+ report = WrIntermapsReport()
+ report.intermaps_sledrun_id = sledrun.intermaps_sledrun_id
+ report.status = sledrun_json['status']
+ report.last_update = region['lastUpdate']
+ report.last_check = last_check
+ report.utc_offset = int(re.match(r'UTC([-+]\d\d)\d\d', region['timeZone']).group(1))
+ session.add(report)
+
+
+def update_wrintermapsreport(session: Session, json_content: dict, last_check: datetime.datetime):
+ # add current content of wrintermapsreport to wrintermapshistory
+ update_wrintermapsreporthistory(session)
+
+ # delete content of intermaps_report
+ session.query(WrIntermapsReport).delete()
+
+ # add JSON content to intermaps_report
+ insert_json_to_intermapsreport(session, json_content, last_check)
ToastFreeware / philipp / winterrodeln / wrpylib.git / blobdiff