querying an sqlite3 database

Question

So I am querying a database that is called golfDB and it consists of one table called players with 5 fields:

name (player's name)
totalGross (sum of the gross scores from each round)
totalRounds (number of rounds played)
pars (total number of pars made)
birdies (total number of birdies made)

The function I'm working on is supposed to list the players in descending order according to their average score (totalGross/ totalRounds).

I'm not entirely sure how to go about doing this, my code is currently separating all the components (players, total gross score, and total rounds) into their own lists. I was thinking then I could divide each total gross score list item by each item in the total rounds list, but I'm not really sure how to then link those scores back to their corresponding player so that they can be ordered.

I don't know if it is even possible to do it this way, so does anyone have any suggestions or ideas?

def queryDBplayers(cursor):
    """lists the players in order of their total gross score"""
    cursor.execute('select name, totalGross, totalRounds from players')
    answer= cursor.fetchall()
    players = list()
    for items in answer:
        players.append(items[0])
    totalGrossScore = list()
    for items in answer:
        totalGrossScore.append(items[1])
    totalRoundsScore = list()
    for items in answer:
        totalRoundsScore.append(items[2])

Daniel Roseman · Accepted Answer · 2013-04-25 16:25:47Z

3

You're making this far more complicated than it needs to be.

Firstly, I don't understand why you're mucking around with separate lists. If you have a single list of players you can sort them very simply with a key function:

players.sort(key=lambda p: float(p[1]) / float(p[2]))

But, you really shouldn't be doing this in Python at all. The best place to do sorting is in the database:

SELECT name, totalGross, totalRounds ORDER BY totalGross/totalRounds

As with your previous question, it seems that you would benefit from learning some basic SQL.

answered Apr 25, 2013 at 16:25

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joran Beasley Over a year ago

I think he probably needs to cast those to decimal in the query (+1 all the same) otherwise he will get truncated ints i suspect wich will mess up the sort

tinydancer9454 Over a year ago

Yeah, I don't know much about SQL. This is for a class and we're supposed to be learning it mostly on our own but I'm having a really hard time understanding it. I didn't realize that you could do the actual division in the statement.

Joran Beasley · Accepted Answer · 2013-04-25 16:23:50Z

0

cursor.execute('select name, totalGross, totalRounds from players')
answer= cursor.fetchall()

print sorted(answer,key=lambda x:float(x[1])/float(x[2]))

I would think would work ... I dont know but you might be able to craft the query to sort this for you

on a side note its easier to separate the columns like col1,col2,col3 = zip(*big_list) in your case this would be

players,grosses,rounds = zip(*answer)

answered Apr 25, 2013 at 16:23

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

Collectives™ on Stack Overflow

querying an sqlite3 database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related