-
Notifications
You must be signed in to change notification settings - Fork 13
/
asCountStatistic.py
77 lines (62 loc) · 2.57 KB
/
asCountStatistic.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
__author__ = 'Alexey Y Manikin'
from classes.statistic_worker.statisticBaseClass import StatisticBaseClass
import MySQLdb
import datetime
from config.main import MINIMUM_DOMAIN_COUNT, PREFIX_LIST_ZONE
class AsCountStatistic(StatisticBaseClass):
def __init__(self, number: int, data: datetime, today: datetime, zone: str):
"""
:param number:
"""
StatisticBaseClass.__init__(self, number, "as_count_")
self.today = today
self.data = data
self.zone_id = PREFIX_LIST_ZONE[zone]
def _get_data(self, date: datetime) -> list:
"""
В зависимости от дня статистики обращаемся или к domain_history или к domain
:return:
"""
cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
if date == datetime.date.today():
sql = """SELECT asn1 as as_number, count(*) as count FROM domain
WHERE delegated = 'Y' AND tld = %i
GROUP BY asn1
HAVING count(*) > %i
ORDER BY count(*) desc""" % (self.zone_id, MINIMUM_DOMAIN_COUNT)
else:
sql = """SELECT asn1 as as_number, count(*) as count FROM domain_history
WHERE delegated = 'Y' AND tld = %i AND date_start <= '%s' AND date_end > '%s'
GROUP BY asn1
HAVING count(*) > %i
ORDER BY count(*) desc""" % (self.zone_id, date, date, MINIMUM_DOMAIN_COUNT)
cursor.execute(sql)
return cursor.fetchall()
def _update(self):
"""
:return:
"""
date = self.data
today = self.today
cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
while date <= today:
sql_insert = ''
as_array = {}
data = self._get_data(date)
for row in data:
if row['as_number'] is None:
asn = 0
else:
asn = row['as_number']
as_array[asn] = row['count']
for key in as_array:
sql_insert_date = " ('%s', %i, '%s', '%s')" % (date, self.zone_id, key, as_array[key])
if len(sql_insert) > 5:
sql_insert += ', ' + sql_insert_date
else:
sql_insert += sql_insert_date
if len(sql_insert) > 1:
sql = 'INSERT INTO as_count_statistic(`date`, `tld`, `asn`, `count`) VALUE ' + sql_insert
cursor.execute(sql)
self.connection.commit()
date += datetime.timedelta(days=1)