Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import psycopg2
- import datetime
- def connect(dbname, user, host, password)
- """ Attempts to connect to a PostgreSQL database """
- try:
- conn = psycopg2.connect(
- "dbname='{0}' user='{1}' host='{2}' password='{3}",
- "'".format(dbname, user, host, password))
- except:
- print "Cannot connect to 'news' database"
- def top_articles(limit):
- """ Top news articles sorted by popularity """
- cursor = conn.cursor()
- cursor.execute(
- "SELECT COUNT(id), path FROM log WHERE status = '200 OK' ",
- "AND NOT path = '/' GROUP BY path ",
- "ORDER BY COUNT(id) DESC LIMIT {0};".format(limit))
- print "Top {0} articles sorted by popularity: ".format(limit)
- print
- articles = cursor.fetchall()
- for article in articles:
- cursor.execute(
- "SELECT title FROM articles WHERE ",
- "('/article/' || slug) ='" + article[1] + "';")
- title = cursor.fetchone()
- print '"{0}" - {1} views'.format(title[0], article[0])
- def top_authors(limit):
- """ Top authors sorted by total article views """
- cursor = conn.cursor()
- cursor.execute("SELECT name, id FROM authors LIMIT {0};".format(limit))
- print "Top {0} authors sorted by page views: ".format(limit)
- print
- authors = cursor.fetchall()
- for author in authors:
- cursor.execute(
- "SELECT slug FROM articles WHERE author ='{0}';".format(author[1]))
- slugs = cursor.fetchall()
- total_views = 0
- for slug in slugs:
- cursor.execute(
- "SELECT COUNT(id) FROM log WHERE status = '200 OK' ",
- "AND substring(path, 10) = '{0}';".format(slug[0]))
- art_count = cursor.fetchone()
- total_views += art_count[0]
- print "{0} - {1} views".format(author[0], total_views)
- def bad_request_days():
- """ Days where there were bad requests over 1% """
- cursor = conn.cursor()
- cursor.execute(
- "SELECT COUNT(id), time, status FROM log GROUP BY ",
- "time, status ORDER BY time;")
- last_row = None
- total_bad = 0.0
- total_good = 0.0
- print "Days with more than 1% errors:"
- print
- for row in cursor:
- if last_row is None:
- last_row = row[1]
- if last_row.month == row[1].month and last_row.day == row[1].day:
- if row[2] == "404 NOT FOUND":
- total_bad += row[0]
- elif row[2] == "200 OK":
- total_good += row[0]
- else:
- result = float(total_bad / (total_bad + total_good) * 100)
- if result > 1.0:
- print "{0}/{1}/{2} - {3:.2f}%",
- "%".format(last_row.month, last_row.day,
- last_row.year, result)
- last_row = None
- total_bad = 0.0
- total_good = 0.0
- if __name__ == "__main__":
- connect("news", "postgres", "localhost", "pass")
- top_articles(3)
- print
- top_authors(4)
- print
- bad_request_days()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement