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']: