def input_domain(file, commitNum=200):
"""
将域名导入数据库
"""
from db_opreation import DataBase
from WhoisData.domain_analyse import DomainAnalyse
DB = DataBase()
DB.get_connect()
DB.execute("""USE malicious_domain_sys""")
f = open(file, 'r')
count = 0
for line in f.readlines():
domain = line.split(' ')[0].strip()
judge_flag = line.split(' ')[1].strip()
D = DomainAnalyse(domain)
print D.get_punycode_domain(),
print judge_flag
result = SQL = """INSERT INTO domain_index SET ID = {id}, domain = '{d}', judge_flag = {f} ;""".format(
id=hash(D.get_punycode_domain().strip()),
d=D.get_punycode_domain().strip(),
f=judge_flag)
if count >= commitNum:
DB.db_commit()
count = 0
print D.get_punycode_domain().strip()
DB.execute(SQL)
f.close()
DB.db_commit()
DB.db_close()
def getDatabaseTime():
""":return 数据库当前时间"""
DB = DataBase()
DB.get_connect()
SQL = """SELECT now()"""
currentTime = DB.execute(SQL)[0][0]
DB.db_close()
return currentTime
def update_black_list():
"""
将反查数据更新到数据库中
"""
DB = DataBase()
DB2 = DataBase()
DB.db_connect()
DB2.db_connect()
DB.execute_no_return("""USE malicious_domain_sys""")
DB2.execute_no_return("""USE malicious_domain_sys""")
for results in DB.execute_Iterator("""SELECT info,info_type FROM info_reverse_search"""):
for info, info_type in results:
SQL = """INSERT IGNORE INTO reg_info_black_lists SET info = '{i}',type = {t},domain_count = -1""".format(
i=info, t=info_type)
DB2.execute_no_return(SQL)
DB2.db_commit()
DB2.db_commit()
for results in DB.execute_Iterator("""SELECT info,type FROM reg_info_black_lists WHERE flag < 0"""):
for info, type in results:
if type == 2:
reg_info_type = 'reg_name'
elif type == 3:
reg_info_type = 'reg_email'
else:
reg_info_type = 'reg_phone'
SQL2 = """SELECT judge_flag,COUNT(*) FROM whois INNER JOIN domain_index ON whois.ID = domain_index.ID WHERE {info_type} = '{info}' GROUP BY judge_flag """.format(
info_type=reg_info_type, info=info
)
domain_count = 0
malicious_count = 0
results_sql2 = DB2.execute(SQL2)
if not results_sql2:
continue
for judge_flag, count in DB2.execute(SQL2):
domain_count += count
if judge_flag < 0:
malicious_count += count
SQL3 = """UPDATE reg_info_black_lists SET domain_count = {d}, malicious_count={m}, flag = 1 WHERE info = '{info}'""".format(
d=domain_count, m=malicious_count, info=info
)
print info
DB2.execute_no_return(SQL3)
DB2.db_commit()
DB2.db_commit()
DB.db_close()
DB2.db_close()
def OldWhois2NewFlga():
"""
工作函数
将就数据库的有内容的数据在新库中进行标记
"""
commit_count = 0
no_exist_count = 0
ODB = DataBase(section='Old_DataBase')
NDB = DataBase(section='New_DataBase')
NDB.get_connect()
for domain in GetOldDatabaseDomain(ODB):
# print domain
SQL_serch = SQL_refactor.isFlag_100(domain)
result = NDB.execute(SQL_serch)
if result:
flag = str(NDB.execute(SQL_serch)[0][0])
if int(flag) <= -98:
# 将暂时未处理的domain的flag改为-97
SQL_update = SQL_refactor.Update_by_domain(domain,
UpdateCon=['flag'],
UpdateVal=['-97'])
# print SQL_update
NDB.execute(SQL_update) # 操作
# print domain,
# print 'flag'+str(flag)+'->97'
commit_count += 1
if commit_count == 1000: # 一定数量操作开始提交事物
NDB.db_commit() # 提交事物
print '[Commit]数据库提交'
commit_count = 0
else:
print '[Domain]新库中不存在的域名:',
print domain
no_exist_count += 1
log.write('[Domain]新库中不存在的域名:')
log.write(domain)
log.write('\n')
# 关闭
NDB.db_commit()
NDB.db_close()
log.write('[Domain]新库中不存在的域名个数 : ')
log.write(str(no_exist_count))
log.write('\n')
log.close()
def OldWhois2NewFlga():
"""
工作函数
将就数据库的有内容的数据在新库中进行标记
"""
commit_count = 0
no_exist_count = 0
ODB = DataBase(section='Old_DataBase')
NDB = DataBase(section='New_DataBase')
NDB.get_connect()
for domain in GetOldDatabaseDomain(ODB):
# print domain
SQL_serch = SQL_refactor.isFlag_100(domain)
result = NDB.execute(SQL_serch)
if result:
flag = str(NDB.execute(SQL_serch)[0][0])
if int(flag) <= -98:
# 将暂时未处理的domain的flag改为-97
SQL_update = SQL_refactor.Update_by_domain(domain, UpdateCon=['flag'], UpdateVal=['-97'])
# print SQL_update
NDB.execute(SQL_update) # 操作
# print domain,
# print 'flag'+str(flag)+'->97'
commit_count += 1
if commit_count == 1000: # 一定数量操作开始提交事物
NDB.db_commit() # 提交事物
print '[Commit]数据库提交'
commit_count = 0
else:
print '[Domain]新库中不存在的域名:',
print domain
no_exist_count += 1
log.write('[Domain]新库中不存在的域名:')
log.write(domain)
log.write('\n')
# 关闭
NDB.db_commit()
NDB.db_close()
log.write('[Domain]新库中不存在的域名个数 : ')
log.write(str(no_exist_count))
log.write('\n')
log.close()
def GetWhoisInfo(tld=None):
"""
:param tld: 需要获取的tld的数据 / 不支持列表
:return: 此tld在数据库的数据信息
"""
db = DataBase()
db.get_connect()
finsh_num = 0
for table_num in range(1, TABLE_NUM + 1):
SQL = """SELECT COUNT(*) FROM domain_whois.domain_whois_{num} WHERE`tld` = '{tld}' AND `flag` > 0""".format(
num='1', tld=tld)
finsh_num += db.execute(SQL)[0][0]
print finsh_num
un_finsh_num = 0
for table_num in range(1, TABLE_NUM + 1):
SQL = """SELECT COUNT(*) FROM domain_whois.domain_whois_{num} WHERE`tld` = '{tld}' AND `flag` < 0""".format(
num='1', tld=tld)
un_finsh_num += db.execute(SQL)[0][0]
print un_finsh_num
def calculateClientSpeed(self):
""" 计算客户端时间
:return @clientSpeedInfo 各个客户端速度信息组成的字符串
:return @client2_status 二类客户端状态信息"""
clientSpeedInfo = '' # 客户端速度信息
client2_status = '' # 清空状态信息
currentTime = self.getDatabaseTime() # 当前数据库时间
earlyTime = currentTime - datetime.timedelta(days=1) # 前一天数据库时间
DB = DataBase() # 实例化数据库对象
DB.get_connect()
for ip in self.client1_ip_list:
SQL = """SELECT max(`count`),min(`count`) from whois_sys_log.client_count_log_{client_num}
WHERE insert_time > '{early_time}' AND insert_time < '{current_time}';""".format(
client_num=ip.split('.')[-1],
early_time=earlyTime,
current_time=currentTime)
max_count, min_count = DB.execute(SQL)[0]
clientSpeed_perHour = (max_count - min_count) / 23.5 # 客户端平均每小时速度
clientSpeedInfo += ip.split('.')[-1] + "客户端平均处理速度为：\t" + str(
clientSpeed_perHour)[:7] + "(个/小时)\n"
for ip in self.client2_ip_list:
SQL = """SELECT max(`count`),min(`count`) from whois_sys_log.client_count_log_{client_num}
WHERE insert_time > '{early_time}' AND insert_time < '{current_time}';""".format(
client_num=ip.split('.')[-1],
early_time=earlyTime,
current_time=currentTime)
max_count, min_count = DB.execute(SQL)[0]
clientSpeed_perHour = (max_count - min_count) / 23.5 # 客户端平均每小时速度
client2_status += ip.split('.')[-1] + "客户端状态：\t"
if clientSpeed_perHour > 100:
client2_status += "正常\n"
else:
client2_status += "崩溃！\n"
clientSpeedInfo += ip.split('.')[-1] + "客户端平均处理速度为：\t" + str(
clientSpeed_perHour)[:7] + "(个/小时)\n"
return clientSpeedInfo, client2_status
class GetTLD:
"""TLD内容更新类"""
def __init__(self):
"""实例化对象"""
self.db = DataBase() # 实例数据库对象
self.INNAspider = spider() # 实例化一个爬虫对象
self.staic = ConfigParser.ConfigParser() # 实例化配置文件
self.staic.read('GetTLDs.conf')
self.Intervals = self.staic.getfloat('Spider', 'getIntervals') # 获取间隔
self.delta = self.staic.getint('Spider', 'delta') # 更新间隔
self.INNA_url = 'http://www.iana.org/domains/root/db' # TLD数据地址
self.db.get_connect() # 连接到数据库
def getCurrentTime(self):
""":return 当前时间(datetime)"""
return datetime.datetime.now()
def isexist(self, tld):
"""判断这个TLD是否存在
:return False-不存在 or insertTime：上次更新时间-存在"""
SQL = """SELECT * FROM domain_whois_summary.whois_tld_summary
WHERE TLD = '{tld}'""".format(tld=tld) # SQL语句
result = self.db.execute(SQL)
if result is None:
return False
elif result[0][4] == '':
return 'No-whois'
else:
return self.db.execute(SQL)[0][6]
def SQL_Generate(self, GenType='INSERT', **TLDinfo):
"""SQL语句生成
:return 生成插入TLD信息的SQL代码"""
TLD = TLDinfo['TLD']
punycode = TLDinfo['punycode']
Type = TLDinfo['type']
WhoisSrv = TLDinfo['WhoisSrv']
SponsoringOrganisation = TLDinfo['SponsoringOrganisation']
# 生成SQL语句
if GenType == 'INSERT':
SQL = """INSERT domain_whois_summary.whois_tld_summary """
SQL += """(`TLD`,`Punycode`,`Type`,`whois_addr`,`SponsoringOrganization`)"""
SQL += """VALUES('{T}','{PC}','{Ty}','{WS}','{SO}');""".\
format(T=TLD, PC=punycode, Ty=Type, WS=WhoisSrv, SO=SponsoringOrganisation)
elif GenType == 'UPDATE':
SQL = """UPDATE domain_whois_summary.whois_tld_summary """
SQL += """SET `whois_addr`='{WS}' """.format(WS=WhoisSrv)
SQL += """WHERE `TLD` = '{T}';""".format(T=TLD)
else:
print "[Error_SQL]未预计到的生成SQL语句模式"
return None
return SQL
def insertInfo(self, getIntervals=3):
"""插入信息
:param @getIntervals 获取间隔"""
# 获取基础页面信息
print "[ HTTP ]获取页面信息中...",
try:
HtmlData = self.INNAspider.getPageText(self.INNA_url)
except Exception as e:
print "失败！"
print "[Error_HTTP] 获取内容出现问题"
self.db.db_close()
print e
print "成功"
# 处理信息
for TLDinfo in self.INNAspider.getTLDinfo(
HtmlData, intervalsTime=self.Intervals):
Curtime = self.getCurrentTime()
existFlag = self.isexist(TLDinfo['TLD'])
delta = datetime.timedelta(days=15) # 更新期限
# 判断应该进行的操作
if not existFlag:
TLDinfo = spider.getTLDWhoisSrv(**TLDinfo)
SQL = self.SQL_Generate(**TLDinfo)
print "[INSERT]获取了" + str(TLDinfo['TLD']) + "的相关信息"
elif existFlag == 'No-whois' or delta < Curtime - existFlag:
TLDinfo = spider.getTLDWhoisSrv(**TLDinfo)
SQL = self.SQL_Generate(GenType='UPDATE', **TLDinfo)
print "[UPDATE]更新了" + str(TLDinfo['TLD']) + "的相关信息"
else:
SQL = None
print "[ SKIP ]跳过了" + str(TLDinfo['TLD']) + "的相关信息"
# 数据库更新
if SQL is not None:
try:
self.db.execute(SQL)
self.db.db_commit()
except MySQLdb.Error as e:
print "[Error_DB] 数据库操作出现问题"
print e
self.db.db_close()
self.db.db_commit() # 一轮循环提交一次事物