EGD-mine Changeset - d72a89609095

Changeset - d72a89609095

Parent rev.

Child rev.

[Not reviewed]

default

0 2 0

Laman - 7 years ago 2018-03-27 12:34:06

timeRank query

2 files changed with 31 insertions and 6 deletions:

egd.py

queries.py

0 comments (0 inline, 0 general)

egd.py

➞

Show inline comments

@@ @@ -52,24 +52,29 @@ def loadPlayers(filename,enc="iso8859_2" @@
 			(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))""")
 	cursor.execute("""
 		create view players_performance_tournaments (pin,p_country_code,rating_before,rating_after,t_country_code,datum) as
 			select pin,pp.country_code,rating_before,rating_after,ts.country_code,datum
 				from players_performance as pp join tournaments as ts on pp.tournament_code=ts.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(

queries.py

➞

Show inline comments

 from egd import connection
 cursor=connection.cursor()
 def yearStart(year):
 	return "{0}-01-01".format(year)
 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(?)
 			order by datum asc
 	"""
 	date1="{0}-01-01".format(year)
 	date2="{0}-01-01".format(year+1)
 	date1=yearStart(year)
 	date2=yearStart(year+1)
 	res=cursor.execute(query,(name,date1,date2))
 	print("jméno\tdatum\tpočet výher\tpočet partií\trating po turnaji\tzměna")
 	for rec in res:
 		print("\t".join([str(x) for x in rec]))
 def yearByCountry(year):
 	date1="{0}-01-01".format(year)
 	date2="{0}-01-01".format(year+1)
 	date1=yearStart(year)
 	date2=yearStart(year+1)
 	query="""
 		select country_code,count(pin) from (
 			select pin,count(*),pp.country_code from
 				players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
 				where datum>=date(?) and datum<date(?)
 				group by pin
+			)
 			group by country_code
 			order by country_code asc;
 		"""
 	res=cursor.execute(query,(date1,date2))
 	print("kód země\tpočet aktivních hráčů")
 	for rec in res:
 		print("\t".join([str(x) for x in rec]))
 def timeRank(year,country):
 	date1=yearStart(year)
 	date2=yearStart(year+1)
 	query="""
 		select ppt.pin,date(?)-min_datum-1,min_datum,ppt.rating_after as rating
 			from
 				(select pin,min(datum) as min_datum,max(datum) as max_datum from players_performance_tournaments where datum<date(?) group by pin having max_datum>=date(?)) as subq
 				join players_performance_tournaments as ppt on subq.pin=ppt.pin and subq.max_datum=ppt.datum
 			where ppt.p_country_code=?"""
 	res=cursor.execute(query,(date2,date2,date1,country))
 	print("pin\tcelé roky hraní\tprvní turnaj\trating")
 	for rec in res:
 		print("\t".join([str(x) for x in rec]))
 def populationStrength():
 	pass
 def populationAge():
 	pass
 # playerYear("Kotowski Jaroslav",2017)
 # yearByCountry(2016)
 # yearByCountry(2017)
 timeRank(2017,"CZ")

0 comments (0 inline, 0 general)