CODEWITHSUNDEEP
pythondatabasesqlite
tinydancer9454
tinydancer9454

Reputation: 401

SQLIte3 querying a database

I'm trying to write a program to query a database. The database is golfDB and it consists of one table called players with 5 fields:

My program needs to output the player with the most pars, the average score (totalGross/ totalRounds) for an inputed player, and to list the players in order of total gross score, lowest to highest.

I'm really not sure how to output the player with the most pars. Right now the code I have is outputting a list of lists, each with a player and their pars. I'm not sure how to order them by their pars and then select the highest ones because everything I've tried is just selecting the numbers out and then I cannot get back to which player they belong to.

Does anyone know how to order the list of lists by just the number of pars and then it can just print the player?

import sqlite3

def getDBCursor(DB):
    """obtain and return a cursor for the database DB"""
    conn= sqlite3.connect('/Users/tinydancer9454/Documents/python/golfDB')
    cursor= conn.cursor()
    return cursor

def queryDBpars(cursor):
    """find out which player had the most pars"""
    cursor.execute('select name, pars from players where pars >= 0')
    playerPars= cursor.fetchall()


def queryDBavgScore(cursor):
    """find the average score of inputed player"""
    player= input("Please enter the player's name: ")
    cursor.execute('select totalGross from players where name = ?', (player,))
    totalGrossScore = cursor.fetchone()
    cursor.execute('select totalRounds from players where name = ?', (player,))
    totalRoundsScore = cursor.fetchone()
    answer = totalGrossScore[0]/ totalRoundsScore[0]
    print('The average score for', player, 'is', answer,)

def queryDBplayers(cursor):
    """lists the players in order of their total gross score"""


def main():
    """obtain cursor and query the database: golfDB"""
    cursor= getDBCursor('golfDB')
    queryDBpars(cursor)
    queryDBavgScore(cursor)
    queryDBplayers(cursor)
    cursor.close()

And this is the output:

[('Ruth', 16), ('Elena', 12), ('Jane', 12), ('Ezgi', 13), ('Ricki', 9), ('Margaret', 10), ('Rosalia', 16), ('Betty', 14)]

Upvotes: 0

Views: 119

Answers (2)

Daniel Roseman
Daniel Roseman

Reputation: 600051

This is a simple SQL question. For the straight answer to your question, you just need:

SELECT name, pars FROM players WHERE pars >= 0 ORDER BY pars DESC

But that still leaves you selecting all the players where you don't need to. To prevent that, just add a LIMIT clause:

SELECT name, pars FROM players WHERE pars >= 0 ORDER BY pars DESC LIMIT 1

and you would then use cursor.fetchone() to get just that single row.

Edit

You say that there could be more than one person with the most pars. That makes the query significantly more complex: now we're into subqueries:

SELECT name, pars FROM players WHERE pars >= 0 WHERE pars = (
    SELECT MAX(pars) FROM players)

So what this is doing is finding the maximum value for 'pars', then selecting the rows that have this value.

Upvotes: 1

Felipe
Felipe

Reputation: 3149

You can either use the ORDER BY SQL keyword in your query, or if you want to do it in python you can do it a couple of ways:

def queryPars(cursor):
    """find out which player had the most pars"""
    cursor.execute('select pars, name from players where pars >= 0')
    ## Note flipped order ^^^^^^^^^^^^
    playerPars = cursor.fetchall()
    pars, player_most_pars = max(playerPars)
    # Now do something useful with it ;-)

The docs describe how tuples are compared. (Basically, entry by entry). If you cannot change the order of your query, you can also do something like player, pars = max(playerPars, key=lambda p: p[1])

Upvotes: 0

Related Questions