CODEWITHSUNDEEP
pythonsqlite
tinydancer9454
tinydancer9454

Reputation: 401

querying an sqlite3 database

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

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])

Upvotes: 0

Views: 69

Answers (2)

Daniel Roseman
Daniel Roseman

Reputation: 599460

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.

Upvotes: 3

Joran Beasley
Joran Beasley

Reputation: 113930

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)

Upvotes: 0

Related Questions