import datetime import itertools import json from sqlalchemy import orm from wrpylib.wrorm import Page, WrIntermapsSledrun, WrIntermapsReport, WrIntermapsReportHistory def investigate(filename: str, write_back_formatted: bool): """Displays statistics about a Intermaps json file.""" 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 if write_back_formatted: with open(filename, 'w') as fp: json.dump(content, fp, indent='\t') class UpdateError(RuntimeError): pass def find_intermaps_sledrun(intermaps_sledrun_id: str, json_content: dict): """Returns iterator of tuples (region, sledrun) for given sledrun_id""" regions = json_content['items'] for region in regions: for sledrun in region['sledruns']['items']: if sledrun['id'] == intermaps_sledrun_id: yield region, sledrun 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, 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') >>> Session = orm.sessionmaker(bind=engine) >>> with open('2019-12-06_090500.json') as fp: >>> content = json.load(fp) >>> update_wrintermapssledrun(Session(), content) """ 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: region_json, sledrun_json = \ next(find_intermaps_sledrun(intermaps_sledrun.intermaps_sledrun_id, json_content), (None, 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.wr_page_title is None: page = session.query(Page).get(intermaps_sledrun.wr_page_id) intermaps_sledrun.wr_page_title = page.page_title session.commit() def update_wrintermapsreport(session, json_content: dict): # add current content of intermaps_report to intermaps_history pass # delete content of intermaps_report session.query(WrIntermapsReport).delete() # add JSON content to intermaps_report 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 = datetime.datetime.now() report.utc_offset = 1 session.add(report) 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') Session = orm.sessionmaker(bind=engine) filename = '/home/philipp/daten/Winterrodeln/Intermaps/intermaps_winterrodeln_2019-12-06_090501.json' with open(filename) as fp: content = json.load(fp) session = Session() update_wrintermapssledrun(session, content) update_wrintermapsreport(session, content)