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.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 _connect_mysql(self): """ :return: """ self.connection = get_mysql_connection()
def __init__(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()