# #

#!/usr/bin/env python3
# final project udacity’s “introduction into programming”
# specialization “Back-end development”

import psycopg2

We Will Write a Custom Essay Specifically
For You For Only \$13.90/page!

order now

# What are the most popular three articles of all time?
question_1 = ‘What are the most popular three articles of all time?’
query_1 = “””
SELECT articles.title, count(*) AS views
FROM log, articles
WHERE log.path= ‘/article/’ || articles.slug
GROUP BY articles.title
ORDER BY views DESC
LIMIT 3;
“””

# Who are the most popular article authors of all time?
question_2 = ‘Who are the most popular article authors of all time?’
query_2 = “””
SELECT authors.name, COUNT(*) AS views
FROM articles INNER JOIN authors ON articles.author = authors.id
INNER JOIN log ON concat(‘/article/’, articles.slug) = log.path
WHERE log.status LIKE ‘%200%’
GROUP BY authors.name
ORDER BY views DESC
“””

# On which days did more than 1% of requests lead to errors
# Formula to calculate from stackoverflow
# https://stackoverflow.com/questions/31237856/calculating-ratios-with-sql
# Information on conditional expressions from postgresql guide
# https://www.postgresql.org/docs/9.5/static/functions-conditional.html
question_3 = ‘On which days did more than 1% of requests lead to errors?’
query_3 = “””
WITH error_percentage as(
SELECT log.time::timestamp::date as date, round( 100.0 * avg(
AS percentage FROM log
GROUP BY date)
SELECT * from error_percentage
WHERE percentage>=1
ORDER by percentage DESC;
“””

class Issue:
# Connects to the database and returns a database connection.
def __init__(self):
try:
self.db = psycopg2.connect(‘dbname=news’)
self.cursor = self.db.cursor()
except:
print(“Database connection failure.”)
return none

def run_query(self, query):
self.cursor.execute(query)
return self.cursor.fetchall()

def solve(self, question, query, suffix=’views’):
query = query.replace(‘\n’, ‘ ‘)
result = self.run_query(query)
print question
for n in range(len(result)):
print ‘\t’, n + 1, ‘.’, resultn0, ‘–‘, resultn1, suffix
print ”

def exit(self):
self.db.close()

if __name__ == ‘__main__’:
issue = Issue()
issue.solve(question_1, query_1)
issue.solve(question_2, query_2)
issue.solve(question_3, query_3, ‘% error’)
issue.exit()