import json
import re
from sqlalchemy import orm
+from sqlalchemy.orm.session import Session
from wrpylib.wrorm import Page, WrIntermapsSledrun, WrIntermapsReport, WrIntermapsReportHistory
return {region['id']: region_to_region_with_sledrun_dict(region) for region in json_content['items']}
-def update_wrintermapssledrun(session, json_content: dict):
+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
session.commit()
-def update_wrintermapsreport(session, json_content: dict, last_check: datetime.datetime):
- # add current content of intermaps_report to intermaps_history
- # the following lines are equivilent to
+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
report_history.utc_offset = report.utc_offset
session.add(report_history)
- # delete content of intermaps_report
- session.query(WrIntermapsReport).delete()
- # add JSON content to intermaps_report
+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)
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)
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)
+ SessionMaker = 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()
+ session = SessionMaker()
update_wrintermapssledrun(session, content)
update_wrintermapsreport(session, content, datetime.datetime.now())