for k in range(0, len(rank)): i = k + 1 for l in range(0, k): if rank[k][0] == rank[k - l][0]: i = k - l + 1 print('[tr][td]', i, '[/td][td]', rank[k][0], '[/td][td]', rank[k][2], '[/td][td]', rank[k][1], '[/td][/tr]') if k > 100: break print('[/table]') print('[/spoiler]') sys.stdout.close() cur = WCA_Database.query( "SELECT best, average, pos, competitionId, eventId, roundTypeId FROM Results WHERE (pos = 1 or pos = 2 or pos = 3) AND eventId = '333' GROUP BY competitionId, roundTypeId, average" ) rows = cur.fetchall() # Adding the results of top3 of this round and sorting by lowest sum res = [] for i in range(0, len(rows)): sum = 0 if rows[i]['pos'] == 1 and rows[i]['average'] != 0: for k in range(i, i + 3): sum = sum + rows[k]['average'] res.append((sum, rows[i]['competitionId'], rows[i]['roundTypeId'])) else: continue
for k in range(0, len(rank)): i = k + 1 for l in range(0, k): if rank[k][1] == rank[k - l][1]: i = k - l + 1 print('[tr][td]', i, '[/td][td]', rank[k][0], '[/td][td]', rank[k][1], '[/td][td]', rank[k][2], '[/td][td]', rank[k][3], '[/td][/tr]') if k > 100: break print('[/table]') print('[/spoiler]') sys.stdout.close() cur = WCA_Database.query( "SELECT eventId, personName, value1, value2, value3, value4, value5 FROM Results WHERE eventId = '444bf'" ) rows = cur.fetchall() results = {} res = [] # Get everyone's results for row in rows: for i in ('value1', 'value2', 'value3', 'value4', 'value5'): if row[i] > 0 or row[i] == -1: results.setdefault(row['personName'], []).append(row[i]) # Find longest success streak for i in results:
# Print table def table(rank, format): sys.stdout = open("best4place.txt", "w") print('[spoiler=Best results in fourth place]') print('[table]') print('[tr][td][td]Name[/td][td]Streak[/td][/tr]') # If more than 100 people have an average, just take top 100 for k in range(0, len(rank)): i = k + 1 for l in range(0, k): if rank[k][format] == rank[k - l][format]: i = k - l + 1 print('[tr][td]', i, '[/td][td]', rank[k][format], '[/td][td]', rank[k]['personName'], '[/td][td]', rank[k]['competitionId'], '[/td][/tr]') if k > 100: break print('[/table]') print('[/spoiler]') sys.stdout.close() cur = WCA_Database.query( "SELECT best, average, personName, competitionId, pos FROM Results WHERE pos = 4 AND eventId = '333fm' AND best > 0 AND average = 0 ORDER BY best" ) rows = cur.fetchall() table(rows, 'best')
# If more than 100 people have an average, just take top 100 for k in range(0, len(rank)): i = k+1 for l in range(0,k): if rank[k][0] == rank[k-l][0]: i = k-l+1 print('[tr][td]', i, '[/td][td]', rank[k][0], '[/td][td]', rank[k][1], '[/td][td]', rank[k][2], '[/td][td]', rank[k][3], '[/td][td]', rank[k][4], '[/td][td]', rank[k][5], '[/td][/tr]') if k > 100: break print('[/table]') print('[/spoiler]') sys.stdout.close() # Query to get all results of each competitor to determine the streaks cur = WCA_Database.query("SELECT res.competitionId, res.eventId, res.roundTypeId, res.pos, res.personName, res.personId, res.best, res.average, res.personCountryId, comp.year, comp.month, comp.day FROM Results AS res INNER JOIN Competitions AS comp ON res.competitionId = comp.id GROUP BY eventId, competitionId, roundTypeId, personId ORDER BY personId, comp.year, comp.month, comp.day") row = cur.fetchall() # Determine Number of rounds for each competitor counting = 1 # Number of rounds per competitor, safed in 'competitorslength' competitorslength = [] # List of Number of rounds attended by each competitor for k in range(1,len(row)): if row[k]['personId'] == row[k-1]['personId']: counting = counting +1 if k == (len(row)-1): competitorslength.append((row[k-1]['personId'], counting, row[k-1]['personName'])) counting = 1 elif row[k]['personId'] != row[k-1]['personId']: competitorslength.append((row[k-1]['personId'], counting, row[k-1]['personName'])) counting = 1
#!/usr/bin/python from db import WCA_Database cur = WCA_Database.query( "SELECT personName, COUNT(DISTINCT competitionID) AS companzahl FROM Results WHERE competitionId LIKE '%2017' GROUP BY personName ORDER BY companzahl DESC Limit 100" ) rows = cur.fetchall() for k in rows: print(k)
#!/usr/bin/python from db import WCA_Database cur = WCA_Database.query( "SELECT eventId, personId, best FROM RanksSingle WHERE eventId = '333bf' GROUP BY personId" ) bld = cur.fetchall() cur.execute( "SELECT eventId, personId, best FROM RanksSingle WHERE eventId = '444bf' GROUP BY personId" ) fobf = cur.fetchall() cur.execute( "SELECT eventId, personId, best FROM RanksSingle WHERE eventId = '555bf' GROUP BY personId" ) fibf = cur.fetchall() #Check for 5BLD > 4BLD if 4BLD success print('5BLD > 4BLD:') for i in range(0, len(fobf)): for k in range(0, len(fibf)): if fobf[i]['personId'] == fibf[k]['personId']: if fobf[i]['best'] > fibf[k]['best']: print(fobf[i]['personId'], fobf[i]['best'], fibf[k]['best']) #Check for 4BLD > 3BLD if 3BLD success
for l in range(0, k): if rank[k][event] == rank[k - l][event]: i = k - l + 1 print('[tr][td]', i, '[/td][td]', rank[k][event], '[/td][td]', rank[k]['personName'], '[/td][td]', rank[k]['competitionId'], '[/td][td]', place[k], '[/td][/tr]') if k > 10: break print('[/table]') print('[/spoiler]') sys.stdout.close() # Get the results of interest from the database: one query for all results of fourth places in this category, the second query for the actual ranking cur = WCA_Database.query( "SELECT best, average, personName, competitionId, pos FROM Results WHERE pos = 4 AND eventId = (%s) AND average > 0 ORDER BY average", (category)) rows = cur.fetchall() cur.execute("SELECT best, eventId from RanksAverage WHERE eventId = (%s)", (category)) row = cur.fetchall() # Check the results of the fourth place against the current ranking ranking = [] for i in range(0, len(rows)): for k in range(0, len(row)): if rows[i][getit] <= row[k]['best']: