budget.py

import MySQLdb

from pprint import pprint

db = MySQLdb.connect(
        host="127.0.0.1",
        user="[redacted]",
        passwd="[redacted]",
        db="httparchive"
    )


c = db.cursor()

c.execute('SELECT COUNT(*) FROM pages')

total = c.fetchone()[0]

averages_query ="""
SELECT COUNT(x.pageid) AS Rows,
       AVG(x.PageSpeed) AS PageSpeed,
       AVG(x.SpeedIndex) AS SpeedIndex,
       AVG(x.reqTotal) AS reqTotal,
       AVG(x.bytesTotal) AS bytesTotal,
       AVG(x.onLoad) AS onLoad

FROM (SELECT pageid,
             PageSpeed,
             SpeedIndex,
             reqTotal,
             bytesTotal,
             onLoad
FROM pages ORDER BY %s %s LIMIT %%s OFFSET %%s) x;"""

def get_data(column, start, end):
    """
    column: Column on which to sort and sort order.
    start: Initial row to start query.
    end: Limit number of rows to query.
    """
    c.execute(averages_query % (column), (round(end), round(start)))
    result = c.fetchone()

    data = {
        'PageSpeed': int(round(result[1])),
        'SpeedIndex': int(round(result[2])),
        'reqTotal': int(round(result[3])),
        'KBTotal': int(round(result[4]/1000)),
        'onLoad': int(round(result[5])),
    }

    return data

columns = (
            ('onLoad', 'ASC'),
            ('PageSpeed', 'DESC'),
            ('SpeedIndex', 'ASC'),
            ('reqTotal', 'ASC'),
            ('bytesTotal', 'ASC')
          )

for column in columns:
    print('<h3>%s</h3>' % (column[0]))
    print('<table><thead><tr><th>Data</th><th>onLoad time (ms)</th><th>Total data (KB)</th><th>Number of HTTP requests</th><th>Page Speed score (%)</th><th>Speed Index</th></tr></thead><tbody>')
    row = get_data(column, 0, total * 0.05)
    print('<tr><td>0%%-5%%</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' %
            (row['onLoad'], row['KBTotal'], row['reqTotal'], row['PageSpeed'], row['SpeedIndex']))
    row = get_data(column, total * 0.05, total * 0.05)
    print('<tr><td>5%%-10%%</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' %
            (row['onLoad'], row['KBTotal'], row['reqTotal'], row['PageSpeed'], row['SpeedIndex']))
    row = get_data(column, total * 0.1, total * 0.15)
    print('<tr><td>10%%-25%%</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' %
            (row['onLoad'], row['KBTotal'], row['reqTotal'], row['PageSpeed'], row['SpeedIndex']))
    row = get_data(column, total * 0.25, total * 0.25)
    print('<tr><td>25%%-50%%</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' %
            (row['onLoad'], row['KBTotal'], row['reqTotal'], row['PageSpeed'], row['SpeedIndex']))
    row = get_data(column, total * 0.5, total * 0.5)
    print('<tr><td>50%%-100%%</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' %
            (row['onLoad'], row['KBTotal'], row['reqTotal'], row['PageSpeed'], row['SpeedIndex']))
    print('</tbody></table>')