I am creating a database to store house points for our school running events. I would like to be able to tally all the points for the houses and it is working fine, however, I think it may be a bit verbose at this point.
Is there a simpler/better way with maybe more efficient code?
Races are stored in a races table (competitorID, raceID, time).
Student data is stored in a competitor table (competitorID, names, house).
The code will query the DB and bring back unique races, and then assign a point value based on their position (calculated by their race time).
import sqlite3
conn = sqlite3.connect('house.db')
conn.row_factory = sqlite3.Row
# House Totals
blueTotal = 0
greenTotal = 0
redTotal = 0
yellowTotal = 0
# Assign points from 1st = 15 through to 10-Last being 1
def points():
global pointvalue
if count == 0:
pointvalue = 15
places()
if count == 1:
pointvalue = 12
places()
if count == 2:
pointvalue = 10
places()
if count == 3:
pointvalue = 8
places()
if count == 4:
pointvalue = 7
places()
if count == 5:
pointvalue = 6
places()
if count == 6:
pointvalue = 5
places()
if count == 7:
pointvalue = 4
places()
if count == 8:
pointvalue = 3
places()
if count == 9:
pointvalue = 2
places()
if count > 10:
pointvalue = 1
places()
#Add points to houses
def places():
global blueTotal, greenTotal, redTotal, yellowTotal, pointvalue
if competitors['house'] == "blueTotal":
blueTotal += pointvalue
if competitors['house'] == "redTotal":
redTotal += pointvalue
if competitors['house'] == "yellowTotal":
yellowTotal += pointvalue
if competitors['house'] == "greenTotal":
greenTotal += pointvalue
for x in range(1,50):
competitorDetails = conn.execute('SELECT firstname, surname, house '
'FROM race, competitor '
'WHERE competitor.competitorID = race.competitorID '
'AND race.raceID = ' + str(x))
count=0
for competitors in competitorDetails:
points()
count += 1
print("blueTotal " + str(blueTotal))
print("greenTotal " + str(greenTotal))
print("redTotal " + str(redTotal))
print("yellowTotal " + str(yellowTotal))
timebeing used, as you described in your preliminary text. You should update your SQL to include this. Also, much of this work can be done in SQL rather than Python. Can you explain why you're using Python for this instead? \$\endgroup\$