Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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>')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.