diff --git a/.hgignore b/.hgignore new file mode 100644 --- /dev/null +++ b/.hgignore @@ -0,0 +1,5 @@ +^\. +all.hst +tlist.html +egd.db +__pycache__ diff --git a/egd.py b/egd.py new file mode 100644 --- /dev/null +++ b/egd.py @@ -0,0 +1,87 @@ +import sys +import re +import datetime +import sqlite3 + + +T_CODE=r"[WEGT]\d{6}[A-Z]*" +NAME_CHARS=r"[\w().#_`?°-]" + +connection=sqlite3.connect("egd.db") + + +def loadTourneys(filename,enc="iso8859_2"): + # code class rounds players (country) description + regexp=re.compile(r"(%s)\s+([A-D])\s+(\d+)\s+(\d+)\s+\(([A-Z]{2})\)\s+(.*)" % T_CODE) + tournaments=dict() + + for line in open(filename,encoding=enc): + line=line.strip() + match=regexp.match(line) + if match is None: + print("ignored:",line,file=sys.stderr) + continue + (code,cls,rc,pc,cc,dsc)=match.groups() + t=dict(zip( + ["code","class","roundCount","playerCount","countryCode","description"], + (code,cls,int(rc),int(pc),cc,dsc) + )) + d=datetime.datetime.strptime(t["code"][1:7],"%y%m%d") + t["date"]=d.date().isoformat() + + tournaments[t["code"]]=t + return tournaments + + +def loadPlayers(filename,enc="iso8859_2"): + # pin name country club rank tournamentCode wins games gor1 gor2 + regexp=re.compile(r"(\d+)\s+(%s+(?: %s+)*)\s+([A-Z]{2})\s+([\w?.=\\-]+)\s+(\d+[kdp])\s+(%s)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)" % (NAME_CHARS,NAME_CHARS,T_CODE)) + players=[] + + for line in open(filename,encoding=enc): + line=line.strip() + if not line: continue + + match=regexp.match(line) + if match is None: + print("ignored:",line,file=sys.stderr) + continue + (pin,name,cc,cl,rank,tcode,wc,gc,r1,r2)=match.groups() + record=dict(zip( + ["pin","name","countryCode","club","rank","tournamentCode","winCount","gameCount","rating1","rating2"], + (int(pin),name,cc,cl,rank,tcode,int(wc),int(gc),int(r1),int(r2)) + )) + players.append(record) + return players + + +def setupDB(): + cursor=connection.cursor() + + cursor.execute("""create table tournaments (code text primary key, datum text, class text, round_count integer, player_count integer, country_code text, description text)""") + cursor.execute("""create table players (pin integer primary key, name text)""") + cursor.execute("""create table players_performance (pin integer references players(pin), tournament_code text references tournaments(code), country_code text, club text, win_count integer, game_count integer, rating_before real, rating_after real, primary key (pin,tournament_code))""") + + connection.commit() + + +def fillDB(tournaments,players): + cursor=connection.cursor() + + cursor.execute("""delete from players_performance""") + cursor.execute("""delete from players""") + cursor.execute("""delete from tournaments""") + + cursor.executemany( + """insert into tournaments (code,datum,class,round_count,player_count,country_code,description) values (:code,:date,:class,:roundCount,:playerCount,:countryCode,:description)""", + tournaments.values() + ) + + cursor.executemany("""insert or ignore into players (pin,name) values (:pin,:name)""", players) + + cursor.executemany( + """insert into players_performance (pin,tournament_code,country_code,club,win_count,game_count,rating_before,rating_after) values (:pin,:tournamentCode,:countryCode,:club,:winCount,:gameCount,:rating1,:rating2)""", + players + ) + + connection.commit() diff --git a/queries.py b/queries.py new file mode 100644 --- /dev/null +++ b/queries.py @@ -0,0 +1,52 @@ +from egd import connection + + +cursor=connection.cursor() + + +def playerYear(name,year): + query=""" + select name, datum, win_count, game_count, rating_after, rating_after-rating_before + from players natural join players_performance as pp join tournaments as ts on pp.tournament_code=ts.code + where name=? and datum>=date(?) and datum=date(?) and datum