-
Notifications
You must be signed in to change notification settings - Fork 8
/
loganalysisdb.py
110 lines (90 loc) · 2.66 KB
/
loganalysisdb.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
#! /usr/bin/env python
import psycopg2
DBNAME = "news"
def run_query(query):
"""Connects to the database, runs the query passed to it,
and returns the results"""
db = psycopg2.connect('dbname=' + DBNAME)
c = db.cursor()
c.execute(query)
rows = c.fetchall()
db.close()
return rows
def get_top_articles():
"""Returns top 3 most read articles"""
# Build Query String
query = """
SELECT articles.title, COUNT(*) AS num
FROM articles
JOIN log
ON log.path LIKE concat('/article/%', articles.slug)
GROUP BY articles.title
ORDER BY num DESC
LIMIT 3;
"""
# Run Query
results = run_query(query)
# Print Results
print('\nTOP THREE ARTICLES BY PAGE VIEWS:')
count = 1
for i in results:
number = '(' + str(count) + ') "'
title = i[0]
views = '" with ' + str(i[1]) + " views"
print(number + title + views)
count += 1
def get_top_article_authors():
"""returns top 3 most popular authors"""
# Build Query String
query = """
SELECT authors.name, COUNT(*) AS num
FROM authors
JOIN articles
ON authors.id = articles.author
JOIN log
ON log.path like concat('/article/%', articles.slug)
GROUP BY authors.name
ORDER BY num DESC
LIMIT 3;
"""
# Run Query
results = run_query(query)
# Print Results
print('\nTOP THREE AUTHORS BY VIEWS:')
count = 1
for i in results:
print('(' + str(count) + ') ' + i[0] + ' with ' + str(i[1]) + " views")
count += 1
def get_days_with_errors():
"""returns days with more than 1% errors"""
# Build Query String
query = """
SELECT total.day,
ROUND(((errors.error_requests*1.0) / total.requests), 3) AS percent
FROM (
SELECT date_trunc('day', time) "day", count(*) AS error_requests
FROM log
WHERE status LIKE '404%'
GROUP BY day
) AS errors
JOIN (
SELECT date_trunc('day', time) "day", count(*) AS requests
FROM log
GROUP BY day
) AS total
ON total.day = errors.day
WHERE (ROUND(((errors.error_requests*1.0) / total.requests), 3) > 0.01)
ORDER BY percent DESC;
"""
# Run Query
results = run_query(query)
# Print Results
print('\nDAYS WITH MORE THAN 1% ERRORS:')
for i in results:
date = i[0].strftime('%B %d, %Y')
errors = str(round(i[1]*100, 1)) + "%" + " errors"
print(date + " -- " + errors)
print('Calculating Results...\n')
get_top_articles()
get_top_article_authors()
get_days_with_errors()