-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathlogs.py
More file actions
executable file
·86 lines (77 loc) · 2.75 KB
/
logs.py
File metadata and controls
executable file
·86 lines (77 loc) · 2.75 KB
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
#!/usr/bin/env python3
# a reporting tool that prints out reports based on the data in the News DB
import sys
import psycopg2
def connect(db_name = "news"):
"""
Connects to the PostgreSQL database and returns the cursor and db variables
"""
try:
db = psycopg2.connect(database="news")
c = db.cursor()
return db, c
except psycopg2.Error as e:
"""
If connecting to the database fails, print a message and exit Program
"""
print("Unable to make database connection. Program exiting")
sys.exit(1)
def fetch_query(query):
"""
Connects to the database, query, fetch results, close connection, return
results
"""
db, c = connect()
c.execute(query)
results = c.fetchall()
db.close()
return results
def most_popular_articles():
"""
Fetch the most popular articles using the fetch_query helper function and
print the results
"""
print("What are the most popular articles of all time?")
results = fetch_query("select title, count(status) as num " +
"from articles, log " +
"where concat('/article/', slug) = path and status = '200 OK' " +
"group by articles.title " +
"order by num desc limit 3;")
for row in results:
print(row[0], " - ", row[1], "views")
print("\n")
def most_popular_authors():
"""
Fetch the most popular authors using the fetch_query helper function and
print the results
"""
print("What are the most popular authors of all time?")
results = fetch_query("select authors.name, count(status) as num " +
"from articles, authors, log " +
"where concat('/article/', slug) = path " +
"and status = '200 OK' " +
"and articles.author = authors.id " +
"group by authors.name " +
"order by num desc;")
for row in results:
print(row[0], " - ", row[1], "views")
print("\n")
def most_error_days():
"""
Fetch the days with more than 1% of bad requests and print the
results
"""
print("On which day did more than 1% of requests lead to errors?")
results = fetch_query("select to_char(total_requests.day, " +
"'FMMonth FMDD, YYYY') as day, " +
"round(bad_requests.requests * 100.0 / " +
"total_requests.requests, 2) as percent " +
"from bad_requests, total_requests " +
"where total_requests.day = bad_requests.day and " +
"bad_requests.requests * 100 / total_requests.requests > 1.0;")
for row in results:
print(row[0], " - ", str(row[1]) + "%")
if __name__ == '__main__':
most_popular_articles()
most_popular_authors()
most_error_days()