def authors():
db_connection = connection.get_connection()
cursor = db_connection.cursor()
popular_authors = """
SELECT name,title,path,count(title) AS views
FROM articles,log,authors
WHERE log.path = CONCAT('/article/', articles.slug)
AND articles.author = authors.id
GROUP BY path, title, name ORDER BY views DESC LIMIT 3;"""
cursor.execute(popular_authors)
return cursor.fetchall()
def get_top_authors():
conn = connection.get_connection()
conn.execute('''
SELECT
COUNT(authors.id) AS totalViews,
authors.name
FROM log, authors, articles
WHERE log.path != '/' AND
articles.slug =
SUBSTR(log.path, LENGTH('/article/') + 1)
AND articles.author = authors.id
GROUP BY
authors.name,
authors.id
ORDER BY totalViews DESC
''')
return conn
def articles():
#print("data{}".format(connection)) # string interpolation !/(comprehension/literals)
#i CAN Add more objects bracket notation, separated by comma
db_connection = connection.get_connection()
#print("data{}".format(cursor))
cursor = db_connection.cursor()
#python uses indentation to identify code blocks
popular_articles = ("""
SELECT title, path, COUNT(path) AS hits, authors.name
FROM articles
JOIN authors
ON articles.author = authors.id
JOIN log
ON log.path = concat('/article/', articles.slug)
GROUP BY title, path, authors.name
ORDER BY hits DESC LIMIT 3;
""")
cursor.execute(popular_articles)
return cursor
def error_status():
db_connection = connection.get_connection()
# You need to join the logs to the path
cursor = db_connection.cursor()
error_reports = """
SELECT time::DATE,
SUM (
CASE WHEN status = '200 OK'
THEN 1
ELSE 0
END) AS success,
SUM (
CASE WHEN status = '404 NOT FOUND.'
THEN 1
ELSE 0
END) AS error
FROM log GROUP BY time::DATE;
"""
cursor.execute(error_reports)
return cursor.fetchall()
def get_errors_per_day():
conn = connection.get_connection()
conn.execute('''
WITH t AS
(SELECT DATE(log.time) AS failureDate,
ROUND((SUM(CASE WHEN
SUBSTRING(log.status, 0, 4)::INTEGER >= 400
THEN 1
ELSE 0
END
) * 100.0)::DECIMAL /
(COUNT(log.status)), 1) AS totalFailures
FROM log GROUP BY DATE(log.time)
)
SELECT CONCAT(t.totalFailures, '%') AS failure,
TO_CHAR(t.failureDate, 'Month DD, YYYY') AS date
FROM t
GROUP BY t.totalFailures, t.failureDate
HAVING t.totalFailures > 1
''')
return conn