'''
   mysqlsample.py
   Jeff Ondich, 12 April 2012

   A simple demonstration of the MySQLdb Python module, using my sample city
   population database. The tables in this database are:

'''

import MySQLdb
import dbconstants


def getConnection():
    connection = MySQLdb.connect(host=dbconstants.host,
                                 user=dbconstants.user,
                                 passwd=dbconstants.passwd,
                                 db=dbconstants.db)
    return connection

def getTopPopulations(dbConnection, year, nPopulations):
    ''' Returns a list of (city name, population) 2-tuples for each of the most populous
        cities for the specified year, as represented in our population database.
        At most nPopulations cities are returned, sorted in decreasing
        order of population. '''
    cursor = dbConnection.cursor()
    query = "SELECT city, population FROM populations WHERE year=%d" % (year)\
                + " ORDER BY population DESC LIMIT %d" % (nPopulations)
    cursor.execute(query)
    rows = cursor.fetchall()
    cityList = []
    for row in rows:
        assert len(row) == 2
        cityList.append(tuple(row))
    cursor.close()
    return cityList

def printTopPopulations(dbConnection, year, nPopulationsToShow):
    ''' Prints the city name and population for each of the most populous
        cities for the specified year, as represented in our population database.
        At most nPopulationsToShow cities are printed, sorted in decreasing
        order of population. '''
    heading = 'Top populations for %d' % year
    print heading
    print '=' * len(heading)

    topPopulations = getTopPopulations(dbConnection, year, nPopulationsToShow)
    for (city, population) in topPopulations:
        print '%-18s%6d' % (city, population)
    print

def main():
    connection = getConnection()
    printTopPopulations(connection, 1850, 5)
    printTopPopulations(connection, 1900, 5)
    printTopPopulations(connection, 1950, 5)
    printTopPopulations(connection, 2000, 5)
    connection.close()

if __name__ == '__main__':
    main()