-
Notifications
You must be signed in to change notification settings - Fork 0
/
newsSourceCode.py
120 lines (101 loc) · 3.97 KB
/
newsSourceCode.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
# !/usr/bin/env python3
# docstrings (the triple quotation mark method)
"""
The first line in this file should be your shebang, which specifies
which Python interpreter should be used to run your code .
The shebang tells the OS which interpreter to use when your script
is run directly. For example, if you run `` .
"""
# importing DB-API module
import psycopg2
# this function for printing result as a table style
def header_style(question, headr1, header2):
print("\n\n"+question+"\n\n"
"------------------------------------+--------------------\n"
"\t "+headr1+"\t | "+header2+" \n"
"------------------------------------+--------------------")
# this is connection function to connect with postgres DB
def conn():
conn_db_obj = psycopg2.connect(database="news")
cursor = conn_db_obj.cursor()
return conn_db_obj, cursor
# the first question function implementation
def question_1():
# connect with DB
conn_db_obj, cursor = conn()
# execute query
cursor.execute("""
select count(*) as GetAccessTimes ,
articles.title as ArtName
from articles inner join log on log.path
= '/article/' || articles.slug
where log.status = '200 OK'
group by ArtName
order by GetAccessTimes desc limit 3
""")
# fetching all results from database
results = cursor.fetchall()
# close connection of database
conn_db_obj.close()
# printing the results in table
header_style('1. What are the most popular three articles of all time?',
'Articles Name', 'Access Times')
for GetAccessTimes, ArtName in results:
print("%s | %d" % (ArtName, GetAccessTimes))
# the second question function implementation
def question_2():
# connect with DB
conn_db_obj, cursor = conn()
# execute query
cursor.execute("""
select authors.name as authName ,
count(*) as GetAccessTimes
from authors inner join articles
on articles.author = authors.id
inner join log on log.path
like concat('%',articles.slug,'%')
where log.status = '200 OK'
group by authName
order by GetAccessTimes desc
""")
# fetching all results from database
results = cursor.fetchall()
# close connection of database
conn_db_obj.close()
# printing the results in table
header_style('2. Who are the most popular article authors of all time?',
'Author Name', 'Access Times')
for authName, GetAccessTimes in results:
print("%s \t\t | %d" % (authName, GetAccessTimes))
# the third question function implementation
def question_3():
# connect with DB
conn_db_obj, cursor = conn()
# execute query
cursor.execute("""
select * from (
select date(log.time) as day ,
100.0 *
sum(case log.status when '200 OK' then 0 else 1 end)
/count(date(log.time)) as percentage from log
group by day ) as virtualTable
where percentage > 1
""")
# fetching all results from database
results = cursor.fetchall()
# close connection of database
conn_db_obj.close()
# printing the results in table
header_style("3. On which days did "
"more than 1% of requests lead to errors?",
'Date of Day', 'Percentage')
for day, percentage in results:
print("%s\t\t\t | %.1f %%" % (day, percentage))
# calling of all of the tree function to get the result
""" To make sure the main subroutine is
only run when this program is executed directly,
and not when it is imported as a module"""
if __name__ == '__main__':
question_1()
question_2()
question_3()