<filename>olympics/olympics.py
#!/usr/bin/env python3
'''
    olympics.py
    <NAME>, 21 October 2021
    Some code borrowed from <NAME> and <NAME>
'''
import psycopg2
import config
import argparse
import sys


def main(argv):

    arguments = get_parsed_arguments()

    # Connect to the database
    try:
        connection = psycopg2.connect(
            database=config.database, user=config.user, password=config.password)
    except Exception as e:
        print(e)
        exit()

    # Query the database, leaving you with a "cursor"--an object you can
    # use to iterate over the rows generated by your query.
    try:
        cursor = connection.cursor()
    except Exception as e:
        print(e)
        exit()

    if arguments.athletes_from:
        NOC_search_string = arguments.athletes_from
        if arguments.sort_alphabetically:
            list_NOC_athletes_alphabetically(cursor, NOC_search_string)
        else:
            list_NOC_athletes_by_golds(cursor, NOC_search_string)

    else:
        if arguments.sort_alphabetically:
            list_NOCs_alphabetically(cursor)
        else:
            list_NOCs_by_golds(cursor)

    connection.close()


def list_NOC_athletes_alphabetically(cursor, search_string):
    query = '''SELECT DISTINCT athletes.name
            FROM athletes, athletes_teams, medals
            WHERE athletes.athlete_id = athletes_teams.athlete_id
            AND medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            AND athletes_teams.NOC LIKE %s
            ORDER BY athletes.name;'''

    try:
        cursor.execute(query, (search_string,))
    except Exception as e:
        print(e)
        exit()

    print('===== Athletes from NOC {0}, alphabetized ====='.format(
        search_string))
    for row in cursor:
        print(row[0])
    print()


def list_NOC_athletes_by_golds(cursor, search_string):
    query = '''SELECT DISTINCT athletes.name, COUNT(*)
            FROM medals, athletes_teams, athletes
            WHERE medals.year = athletes_teams.year
            AND medals.season = athletes_teams.season
            AND medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            AND athletes.athlete_id = athletes_teams.athlete_id
            AND athletes_teams.NOC LIKE %s
            GROUP BY athletes.name
            ORDER BY COUNT(*) desc;'''

    try:
        cursor.execute(query, (search_string,))
    except Exception as e:
        print(e)
        exit()

    print('===== Athletes from NOC {0} by gold medals won ====='.format(
        search_string))
    for row in cursor:
        print(row[0], " -- ", row[1])
    print()


def list_NOCs_alphabetically(cursor):
    query = '''SELECT DISTINCT athletes_teams.NOC
            FROM athletes_teams, medals
            WHERE medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            ORDER BY athletes_teams.NOC;'''

    try:
        cursor.execute(query)
    except Exception as e:
        print(e)
        exit()

    print('===== NOCs, alphabetized =====')
    for row in cursor:
        print(row[0])
    print()


def list_NOCs_by_golds(cursor):
    query = '''SELECT DISTINCT athletes_teams.NOC, COUNT(DISTINCT(medals.year, medals.event))
            FROM medals, athletes_teams
            WHERE medals.year = athletes_teams.year
            AND medals.season = athletes_teams.season
            AND medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            GROUP BY athletes_teams.NOC
            ORDER BY COUNT(DISTINCT(medals.year, medals.event)) desc;'''

    try:
        cursor.execute(query)
    except Exception as e:
        print(e)
        exit()

    print('===== NOCs by gold medals won =====')
    for row in cursor:
        print(row[0], " -- ", row[1])
    print()


def get_parsed_arguments():
    parser = argparse.ArgumentParser(
        description='Lists olympic athlete and NOC information. By default, lists all NOCs that have won at least one gold medal, sorted by # of gold medals won.')

    parser.add_argument('--athletes_from', '-a', type=str,
                        help='given an NOC, prints all athletes that have played for that NOC and have won at least one gold medal, sorted by # of gold medals won.')
    parser.add_argument('--sort_alphabetically', '-s', action='store_true',
                        help='overwrite default sorting method and instead sort alphabetically.')

    return parser.parse_args()


if __name__ == '__main__':
    main(sys.argv[1:])

<filename>olympics/olympics.py
#!/usr/bin/env python3
'''
    olympics.py
    <NAME>, 21 October 2021
    Some code borrowed from <NAME> and <NAME>
'''
import psycopg2
import config
import argparse
import sys


def main(argv):

    arguments = get_parsed_arguments()

    # Connect to the database
    try:
        connection = psycopg2.connect(
            database=config.database, user=config.user, password=config.password)
    except Exception as e:
        print(e)
        exit()

    # Query the database, leaving you with a "cursor"--an object you can
    # use to iterate over the rows generated by your query.
    try:
        cursor = connection.cursor()
    except Exception as e:
        print(e)
        exit()

    if arguments.athletes_from:
        NOC_search_string = arguments.athletes_from
        if arguments.sort_alphabetically:
            list_NOC_athletes_alphabetically(cursor, NOC_search_string)
        else:
            list_NOC_athletes_by_golds(cursor, NOC_search_string)

    else:
        if arguments.sort_alphabetically:
            list_NOCs_alphabetically(cursor)
        else:
            list_NOCs_by_golds(cursor)

    connection.close()


def list_NOC_athletes_alphabetically(cursor, search_string):
    query = '''SELECT DISTINCT athletes.name
            FROM athletes, athletes_teams, medals
            WHERE athletes.athlete_id = athletes_teams.athlete_id
            AND medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            AND athletes_teams.NOC LIKE %s
            ORDER BY athletes.name;'''

    try:
        cursor.execute(query, (search_string,))
    except Exception as e:
        print(e)
        exit()

    print('===== Athletes from NOC {0}, alphabetized ====='.format(
        search_string))
    for row in cursor:
        print(row[0])
    print()


def list_NOC_athletes_by_golds(cursor, search_string):
    query = '''SELECT DISTINCT athletes.name, COUNT(*)
            FROM medals, athletes_teams, athletes
            WHERE medals.year = athletes_teams.year
            AND medals.season = athletes_teams.season
            AND medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            AND athletes.athlete_id = athletes_teams.athlete_id
            AND athletes_teams.NOC LIKE %s
            GROUP BY athletes.name
            ORDER BY COUNT(*) desc;'''

    try:
        cursor.execute(query, (search_string,))
    except Exception as e:
        print(e)
        exit()

    print('===== Athletes from NOC {0} by gold medals won ====='.format(
        search_string))
    for row in cursor:
        print(row[0], " -- ", row[1])
    print()


def list_NOCs_alphabetically(cursor):
    query = '''SELECT DISTINCT athletes_teams.NOC
            FROM athletes_teams, medals
            WHERE medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            ORDER BY athletes_teams.NOC;'''

    try:
        cursor.execute(query)
    except Exception as e:
        print(e)
        exit()

    print('===== NOCs, alphabetized =====')
    for row in cursor:
        print(row[0])
    print()


def list_NOCs_by_golds(cursor):
    query = '''SELECT DISTINCT athletes_teams.NOC, COUNT(DISTINCT(medals.year, medals.event))
            FROM medals, athletes_teams
            WHERE medals.year = athletes_teams.year
            AND medals.season = athletes_teams.season
            AND medals.athlete_id = athletes_teams.athlete_id
            AND medals.medal LIKE 'Gold'
            GROUP BY athletes_teams.NOC
            ORDER BY COUNT(DISTINCT(medals.year, medals.event)) desc;'''

    try:
        cursor.execute(query)
    except Exception as e:
        print(e)
        exit()

    print('===== NOCs by gold medals won =====')
    for row in cursor:
        print(row[0], " -- ", row[1])
    print()


def get_parsed_arguments():
    parser = argparse.ArgumentParser(
        description='Lists olympic athlete and NOC information. By default, lists all NOCs that have won at least one gold medal, sorted by # of gold medals won.')

    parser.add_argument('--athletes_from', '-a', type=str,
                        help='given an NOC, prints all athletes that have played for that NOC and have won at least one gold medal, sorted by # of gold medals won.')
    parser.add_argument('--sort_alphabetically', '-s', action='store_true',
                        help='overwrite default sorting method and instead sort alphabetically.')

    return parser.parse_args()


if __name__ == '__main__':
    main(sys.argv[1:])

#!/usr/bin/env python3 olympics.py     <NAME>, 21 October 2021     Some code borrowed from <NAME> and <NAME> # Connect to the database # Query the database, leaving you with a "cursor"--an object you can # use to iterate over the rows generated by your query. SELECT DISTINCT athletes.name             FROM athletes, athletes_teams, medals             WHERE athletes.athlete_id = athletes_teams.athlete_id             AND medals.athlete_id = athletes_teams.athlete_id             AND medals.medal LIKE 'Gold'             AND athletes_teams.NOC LIKE %s             ORDER BY athletes.name; SELECT DISTINCT athletes.name, COUNT(*)             FROM medals, athletes_teams, athletes             WHERE medals.year = athletes_teams.year             AND medals.season = athletes_teams.season             AND medals.athlete_id = athletes_teams.athlete_id             AND medals.medal LIKE 'Gold'             AND athletes.athlete_id = athletes_teams.athlete_id             AND athletes_teams.NOC LIKE %s             GROUP BY athletes.name             ORDER BY COUNT(*) desc; SELECT DISTINCT athletes_teams.NOC             FROM athletes_teams, medals             WHERE medals.athlete_id = athletes_teams.athlete_id             AND medals.medal LIKE 'Gold'             ORDER BY athletes_teams.NOC; SELECT DISTINCT athletes_teams.NOC, COUNT(DISTINCT(medals.year, medals.event))             FROM medals, athletes_teams             WHERE medals.year = athletes_teams.year             AND medals.season = athletes_teams.season             AND medals.athlete_id = athletes_teams.athlete_id             AND medals.medal LIKE 'Gold'             GROUP BY athletes_teams.NOC             ORDER BY COUNT(DISTINCT(medals.year, medals.event)) desc; # of gold medals won.') # of gold medals won.')