def update_base(self) -> bool:
"""
:return:
"""
json_string = self.load_data_from_rpki_server(PRKI_JSON_URL)
if not json_string:
return False
data_array = json.loads(json_string)['roas']
as_prefix = re.compile('^AS(.*?)$')
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
for index in data_array:
match = as_prefix.findall(index['asn'])
if match:
asn = int(match[0])
else:
asn = 0
run_sql = """SELECT id FROM rpki
WHERE prefix = '%s' AND maxLength = '%s' AND asn = '%s'""" % (index['prefix'],
index['maxLength'],
asn)
cursor.execute(run_sql)
rpki_id = cursor.fetchone()
if not rpki_id:
run_sql = """INSERT INTO rpki(prefix, maxLength, asn, ta, last_update)
VALUE('%s', '%s', '%s', '%s', NOW()) """ % (index['prefix'],
index['maxLength'],
asn, index['ta'])
else:
run_sql = "UPDATE rpki SET last_update = NOW(), load_today = 'Y', ta = '%s' " % (index['ta']) \
+ "WHERE prefix = '%s' AND maxLength = '%s' AND asn = '%s'" % (index['prefix'],
index['maxLength'],
asn)
try:
cursor.execute(run_sql)
connection.commit()
except Exception:
# try again
time.sleep(5)
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(run_sql)
connection.commit()
connection.close()
self.delete_not_updated_today()
return True
def __init__(self):
"""
:return:
"""
self.connection = get_mysql_connection()
self.count_ptheread = 0
self.process_list = []
self.today = datetime.date.today()
def __init__(self):
"""
:return:
"""
self.connection = get_mysql_connection()
self.count_ptheread = 0
self.process_list = []
self.today = datetime.date.today()
def __init__(self):
"""
:return:
"""
self.resolver = dns.resolver.Resolver()
self.resolver.timeout = 1
self.resolver.lifetime = 1
self.re_plus = re.compile('\s+')
self.re_all = re.compile('\s*')
self.connection = get_mysql_connection()
def __init__(self):
"""
:return:
"""
self.resolver = dns.resolver.Resolver()
self.resolver.timeout = 1
self.resolver.lifetime = 1
self.re_plus = re.compile('\s+')
self.re_all = re.compile('\s*')
self.connection = get_mysql_connection()
def delete_not_updated_today(count_all_domain=False):
"""
:type count_all_domain: bool|dict
:return:
"""
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
sql_trigger_enable = "SET @TRIGGER_DISABLED = 0"
sql_trigger_disable = "SET @TRIGGER_DISABLED = 1"
if not count_all_domain:
sql = "DELETE FROM domain WHERE load_today = 'N'"
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_disable)
sql = "UPDATE domain SET load_today = 'N'"
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_enable)
else:
for key_tld, tld_count_in_file in count_all_domain.iteritems():
cursor.execute(
"SELECT count(*) as domain_count FROM domain WHERE tld = '%s'"
% str(key_tld))
count_in_base = cursor.fetchone()
BColor.process("Count zone (%s) in file %s, in base %s" %
(str(key_tld), str(tld_count_in_file),
str(count_in_base['domain_count'])))
if count_in_base and int(count_in_base['domain_count']) >= int(
tld_count_in_file):
sql = "DELETE FROM domain WHERE load_today = 'N' AND tld = '%s'" % str(
key_tld)
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_disable)
sql = "UPDATE domain SET load_today = 'N' WHERE tld = '%s'" % str(
key_tld)
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_enable)
else:
BColor.error(
"TLD %s - count in file %s, count in base %s" %
(str(key_tld), str(count_in_base),
str(tld_count_in_file)))
connection.commit()
connection.close()
def create_db_if_not_exist(table_definition: str):
"""
:return:
"""
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
sql = "SET sql_notes = 0"
cursor.execute(sql)
sql = table_definition
cursor.execute(sql)
sql = "SET sql_notes = 1"
cursor.execute(sql)
connection.close()
def delete_not_updated_today(
count_all_domain: dict or bool = False) -> None:
"""
:type count_all_domain: bool|dict
:return:
"""
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
sql_trigger_enable = "SET @TRIGGER_DISABLED = 0"
sql_trigger_disable = "SET @TRIGGER_DISABLED = 1"
if not count_all_domain:
sql = "DELETE FROM domain WHERE load_today = 'N'"
cursor.execute(sql)
cursor.execute(sql_trigger_disable)
sql = "UPDATE domain SET load_today = 'N'"
cursor.execute(sql)
cursor.execute(sql_trigger_enable)
else:
for key_tld, tld_count_in_file in count_all_domain.items():
cursor.execute(
"SELECT count(*) as domain_count FROM domain WHERE tld = %i"
% PREFIX_LIST_ZONE[key_tld])
count_in_base = cursor.fetchone()
BColor.process("Count zone (%s) in file %s, in base %s" %
(str(key_tld), str(tld_count_in_file),
str(count_in_base['domain_count'])))
sql = "DELETE FROM domain WHERE load_today = 'N' AND tld = %i" % PREFIX_LIST_ZONE[
key_tld]
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_disable)
sql = "UPDATE domain SET load_today = 'N' WHERE tld = %i" % PREFIX_LIST_ZONE[
key_tld]
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_enable)
connection.commit()
connection.close()
def delete_not_updated_today(count_all_domain=False):
"""
:type count_all_domain: bool|dict
:return:
"""
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
sql_trigger_enable = "SET @TRIGGER_DISABLED = 0"
sql_trigger_disable = "SET @TRIGGER_DISABLED = 1"
if not count_all_domain:
sql = "DELETE FROM domain WHERE load_today = 'N'"
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_disable)
sql = "UPDATE domain SET load_today = 'N'"
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_enable)
else:
for key_tld, tld_count_in_file in count_all_domain.iteritems():
cursor.execute("SELECT count(*) as domain_count FROM domain WHERE tld = '%s'" % str(key_tld))
count_in_base = cursor.fetchone()
BColor.process("Count zone (%s) in file %s, in base %s"
% (str(key_tld), str(tld_count_in_file), str(count_in_base['domain_count'])))
if count_in_base and int(count_in_base['domain_count']) >= int(tld_count_in_file):
sql = "DELETE FROM domain WHERE load_today = 'N' AND tld = '%s'" % str(key_tld)
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_disable)
sql = "UPDATE domain SET load_today = 'N' WHERE tld = '%s'" % str(key_tld)
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_enable)
else:
BColor.error("TLD %s - count in file %s, count in base %s"
% (str(key_tld), str(count_in_base), str(tld_count_in_file)))
connection.commit()
connection.close()
def delete_not_updated_today():
"""
:return:
"""
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
sql_trigger_enable = "SET @TRIGGER_DISABLED = 0"
sql_trigger_disable = "SET @TRIGGER_DISABLED = 1"
sql = "DELETE FROM rpki WHERE load_today = 'N'"
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_disable)
sql = "UPDATE rpki SET load_today = 'N'"
BColor.process(sql)
cursor.execute(sql)
cursor.execute(sql_trigger_enable)
connection.commit()
connection.close()
def update_as(self, number, show_log=False):
"""
Обновляем информацию об AS в базе данных
:type number: int
:return:
"""
try:
as_info = self._get_asn_description(number)
except:
as_info = {'AS': number,
'COUNTRY': '',
'ORGANIZATION': '',
'DATE_REGISTER': '',
'DESCRIPTION': ''}
if show_log:
print "AS Number %s" % number
pprint.pprint(as_info)
cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute("SELECT COUNT(*) as count FROM as_list WHERE id = %s" % str(number))
except:
self.connection = get_mysql_connection()
cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT COUNT(*) as count FROM as_list WHERE id = %s" % str(number))
count = cursor.fetchone()
if as_info['DATE_REGISTER'] == '':
as_info['DATE_REGISTER'] = '2001-01-01'
if as_info['COUNTRY'] == '':
as_info['COUNTRY'] = 'undef'
if as_info['ORGANIZATION'] == '':
as_info['ORGANIZATION'] = 'undef'
if count['count'] == 0:
cursor.execute(
"""INSERT INTO as_list(id,
descriptions,
country,
date_register,
organization_register)
VALUE(%s, %s, %s, %s, %s)""", (str(number),
as_info['DESCRIPTION'],
as_info['COUNTRY'],
as_info['DATE_REGISTER'],
as_info['ORGANIZATION']))
else:
cursor.execute(
"""UPDATE as_list SET
descriptions = %s,
country = %s,
date_register = %s,
organization_register = %s
WHERE id = %s""", (as_info['DESCRIPTION'],
as_info['COUNTRY'],
as_info['DATE_REGISTER'],
as_info['ORGANIZATION'],
str(number)))
self.connection.commit()
return True
def update_as(self, number, show_log=False):
"""
Обновляем информацию об AS в базе данных
:type number: int
:return:
"""
try:
as_info = self._get_asn_description(number)
except:
as_info = {
'AS': number,
'COUNTRY': '',
'ORGANIZATION': '',
'DATE_REGISTER': '',
'DESCRIPTION': ''
}
if show_log:
print "AS Number %s" % number
pprint.pprint(as_info)
cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
try:
cursor.execute(
"SELECT COUNT(*) as count FROM as_list WHERE id = %s" %
str(number))
except:
self.connection = get_mysql_connection()
cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(
"SELECT COUNT(*) as count FROM as_list WHERE id = %s" %
str(number))
count = cursor.fetchone()
if as_info['DATE_REGISTER'] == '':
as_info['DATE_REGISTER'] = '2001-01-01'
if as_info['COUNTRY'] == '':
as_info['COUNTRY'] = 'undef'
if as_info['ORGANIZATION'] == '':
as_info['ORGANIZATION'] = 'undef'
if count['count'] == 0:
cursor.execute(
"""INSERT INTO as_list(id,
descriptions,
country,
date_register,
organization_register)
VALUE(%s, %s, %s, %s, %s)""",
(str(number), as_info['DESCRIPTION'], as_info['COUNTRY'],
as_info['DATE_REGISTER'], as_info['ORGANIZATION']))
else:
cursor.execute(
"""UPDATE as_list SET
descriptions = %s,
country = %s,
date_register = %s,
organization_register = %s
WHERE id = %s""",
(as_info['DESCRIPTION'], as_info['COUNTRY'],
as_info['DATE_REGISTER'], as_info['ORGANIZATION'],
str(number)))
self.connection.commit()
return True
def _connect_mysql(self) -> MySQLdb.connect:
"""
:return:
"""
self.connection = get_mysql_connection()
return self.connection
def __init__(self):
self.connection = get_mysql_connection()
pass
def __init__(self, show_log=False):
"""
:return:
"""
self.connection = get_mysql_connection()
self.show_log = show_log
def __init__(self, show_log: bool = False):
"""
:return:
"""
self.connection = get_mysql_connection()
self.show_log = show_log
def _update_ns_domain_group_count_statistic_python(self,
date,
today,
zone,
count=COUNT_THREAD):
"""
Реализация на питоне с использованием процессов
:type date: date
:type today: date
:type zone: unicode
:return:
"""
self.connection.close()
while date <= today:
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
print(("date is %s" % date))
ns_array = {}
provider_array = {}
sql_has_data = """SELECT count(*) as count FROM ns_count_statistic
WHERE tld = '%s' AND date = '%s'""" % (zone,
date)
cursor.execute(sql_has_data)
data = cursor.fetchone()
if data['count'] == 0:
# Вариант когда записей нет в статистикие по NS серверам
for i in range(1, 5):
sql = """SELECT ns%s as ns, count(*) as count FROM domain_history
WHERE delegated = 'Y' AND tld = '%s' AND date_start <= '%s' AND date_end >= '%s'
GROUP BY ns%s
HAVING count(*) > %s
ORDER BY count(*) desc""" % (
i, zone, date, date, i, MINIMUM_DOMAIN_COUNT)
cursor.execute(sql)
data = cursor.fetchall()
for row in data:
if row['ns'] in ns_array:
ns_array[row['ns']] += row['count']
else:
ns_array[row['ns']] = row['count']
else:
# вариант с использованием уже подготовленных данных
sql = """SELECT ns, count FROM ns_count_statistic
WHERE tld = '%s' AND date= '%s'""" % (zone, date)
cursor.execute(sql)
data = cursor.fetchall()
for row in data:
ns_array[row['ns']] = row['count']
for key in ns_array:
try:
if key is not None:
split_ns = key.split('.')
if len(split_ns) > 2:
if '.net.ru.' in key or '.com.ua.' in key:
provider = split_ns[-4]
else:
provider = split_ns[-3]
if provider in provider_array:
provider_array[provider] += ns_array[key]
else:
provider_array[provider] = ns_array[key]
except Exception as e:
# ну что бывает, не запоминаем это значение
print(("Got an exception: %s" % e.message))
print((traceback.format_exc()))
sql = """SELECT ns1, ns2, ns3, ns4 FROM domain_history
WHERE delegated = 'Y' AND tld = '%s' AND date_start <= '%s' AND date_end >= '%s'
""" % (zone, date, date)
cursor.execute(sql)
data = cursor.fetchall()
connection.close()
process_list = []
i = 0
for key in provider_array:
search_string = key
print(("%s Search key is %s" % (i, search_string)))
worker = GroupProviderStatistic(i, search_string, data, zone,
date)
worker.daemon = True
process_list.append(worker)
worker.start()
i += 1
if i != 0 and i % count == 0:
for process in process_list:
try:
# timeout 2 days
process.join(1728000)
except KeyboardInterrupt:
return
process_list = []
for process in process_list:
try:
# timeout 2 days
process.join(1728000)
except KeyboardInterrupt:
return
date += datetime.timedelta(days=1)
self.connection = get_mysql_connection()
def __init__(self):
self.connection = get_mysql_connection()
pass
def _connect_mysql(self):
"""
:return:
"""
self.connection = get_mysql_connection()
def __init__(self):
"""
:return:
"""
self.connection = get_mysql_connection()
def _connect_mysql(self):
"""
:return:
"""
self.connection = get_mysql_connection()
def _update_ns_domain_group_count_statistic_python(self, date, today, zone, count=COUNT_THREAD):
"""
Реализация на питоне с использованием процессов
:type date: date
:type today: date
:type zone: unicode
:return:
"""
self.connection.close()
while date <= today:
connection = get_mysql_connection()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
print("date is %s" % date)
ns_array = {}
provider_array = {}
sql_has_data = """SELECT count(*) as count FROM ns_count_statistic
WHERE tld = '%s' AND date = '%s'""" % (zone, date)
cursor.execute(sql_has_data)
data = cursor.fetchone()
if data['count'] == 0:
# Вариант когда записей нет в статистикие по NS серверам
for i in range(1, 5):
sql = """SELECT ns%s as ns, count(*) as count FROM domain_history
WHERE delegated = 'Y' AND tld = '%s' AND date_start <= '%s' AND date_end >= '%s'
GROUP BY ns%s
HAVING count(*) > %s
ORDER BY count(*) desc""" % (i, zone, date, date, i, MINIMUM_DOMAIN_COUNT)
cursor.execute(sql)
data = cursor.fetchall()
for row in data:
if row['ns'] in ns_array:
ns_array[row['ns']] += row['count']
else:
ns_array[row['ns']] = row['count']
else:
# вариант с использованием уже подготовленных данных
sql = """SELECT ns, count FROM ns_count_statistic
WHERE tld = '%s' AND date= '%s'""" % (zone, date)
cursor.execute(sql)
data = cursor.fetchall()
for row in data:
ns_array[row['ns']] = row['count']
for key in ns_array:
try:
if key is not None:
split_ns = key.split('.')
if len(split_ns) > 2:
if '.net.ru.' in key or '.com.ua.' in key:
provider = split_ns[-4]
else:
provider = split_ns[-3]
if provider in provider_array:
provider_array[provider] += ns_array[key]
else:
provider_array[provider] = ns_array[key]
except Exception as e:
# ну что бывает, не запоминаем это значение
print("Got an exception: %s" % e.message)
print(traceback.format_exc())
sql = """SELECT ns1, ns2, ns3, ns4 FROM domain_history
WHERE delegated = 'Y' AND tld = '%s' AND date_start <= '%s' AND date_end >= '%s'
""" % (zone, date, date)
cursor.execute(sql)
data = cursor.fetchall()
connection.close()
process_list = []
i = 0
for key in provider_array:
search_string = key
print("%s Search key is %s" % (i, search_string))
worker = GroupProviderStatistic(i, search_string, data, zone, date)
worker.daemon = True
process_list.append(worker)
worker.start()
i += 1
if i != 0 and i % count == 0:
for process in process_list:
try:
# timeout 2 days
process.join(1728000)
except KeyboardInterrupt:
return
process_list = []
for process in process_list:
try:
# timeout 2 days
process.join(1728000)
except KeyboardInterrupt:
return
date += datetime.timedelta(days=1)
self.connection = get_mysql_connection()
