-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathnewslog.py
180 lines (154 loc) · 5.08 KB
/
newslog.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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
#!/usr/bin/env python
from __future__ import division
import psycopg2
import webbrowser
import os
"""Database queries and functions(python2 with PostgreSQL and psycopg2)
views: 1)create view author_id_views as
select articles.author,
count(log.path) as num
from log
right join articles on log.path
like concat('%',articles.slug)
and log.status = '200 OK'
group by articles.author
order by num desc;
2)create view errors_by_date as
select time ::timestamp::date,
count(status) as errors
from log
where status = '404 NOT FOUND'
group by time ::timestamp::date;
3)create view status_by_date as
select time ::timestamp::date,
count(status) as status
from log
group by time ::timestamp::date;
"""
# Styles and scripting for the page
main_page_head = '''
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Log Analysis</title>
<style type="text/css" media="screen">
p {
white-space: pre-wrap;
color: #4d4d4d;
}
h1 {
color: #b74e40;
font-family: Arial;
}
h2 {
color: #999;
font-family: Arial;
}
.root {
max-width: 400px;
margin: 0 auto;
}
</style>
<script type="text/javascript" charset="utf-8">
</script>
</head>
'''
# The main page layout and title bar
main_page_content = '''
<body>
<div class="root">
<h1>Newspaper Log Analysis</h1>
{result_content}
</div>
</body>
</html>
'''
result_content = '''
<div id="main_container">
<h2>3 most popular articles of all time</h2>
<p>{most_popular_articles}</p>
<h2>Most popular authors</h2>
<p>{most_popular_authors}</p>
<h2>Dates with more than 1% of bad requests</h2>
<p>{bad_requests}</p>
</div>
'''
def create_result():
# Create html content for results
content = ''
content += result_content.format(
most_popular_articles=popular_articles(),
most_popular_authors=popular_authors(),
bad_requests=errors()
)
return content
def open_log():
# Create or open the output file
output_file = open('log_analysis.html', 'w')
rendered_content = main_page_content.format(
result_content=create_result())
# Output the file
output_file.write(main_page_head + rendered_content)
output_file.close()
# open the output file in the browser (in a new tab, if possible)
url = os.path.abspath(output_file.name)
webbrowser.open('file://' + url, new=2)
def popular_articles():
"""Queries database for 3 most popular articles
of all time returns a single string with 3 titles and their views
"""
db = psycopg2.connect(database="news")
cursor = db.cursor()
cursor.execute("""select articles.title,
count(log.path) as num
from log
right join articles on log.path
= concat('/article/',articles.slug)
and log.status = '200 OK'
group by articles.title
order by num desc limit 3""")
logged_paths = cursor.fetchall()
db.close()
res = ''
for article in logged_paths:
res = res + article[0] + " has " + str(article[1]) + " views \n"
return res
def popular_authors():
"""Queries database for authors and how many times their
articles have been read with the help of view nr. 1
returns a single string containing with
authors and views sorted by descending views
"""
db = psycopg2.connect(database="news")
cursor = db.cursor()
cursor.execute("""select name, num
from authors, author_id_views
where authors.id = author_id_views.author""")
authors = cursor.fetchall()
db.close()
res = ''
for author in authors:
res = res + author[0] + " has " + str(author[1]) + " views \n"
return res
def errors():
"""Queries database for percentage of bad requests by date
with the hep of views nr. 2 and 3 returns single string
with dates and percentages of bad requests
"""
db = psycopg2.connect(database="news")
cursor = db.cursor()
cursor.execute("""select errors_by_date.time,
errors_by_date.errors::float / status_by_date.status * 100
from errors_by_date, status_by_date
where errors_by_date.time = status_by_date.time""")
err = cursor.fetchall()
db.close()
res = ''
for current_date in err:
if current_date[1] >= 1:
res = res + current_date[0].strftime('On %d, %b %Y there were ') \
+ str(round(current_date[1], 2)) + "% of bad requests. \n"
return res
open_log()