def init_system():
#导入初始token
sql = "insert into app(id, unique_key, public_key, token, token_expired) values(1,'tester_app','-----BEGIN RSA PUBLIC KEY-----\nMIIBCgKCAQEAvLWMYgTwkLMI8ZSw8Pd7NBKUVr0kbyqHijKOOQmR5/EKHOwgak0u\nu3+wBsllmIgfa4cT0zp4Gdd4hx2UmpIjG4eHwCgUCHHmCedu87/zEQhzE2do9p09\nBzPs7GG/azuynPJp6mZFxycaGZaoHH1d3FNWJ+yRBQ5UliFw01Tby3j7cV5u9fNU\nOjSZRGBNkHLxUi56kkbIZ46Wz14DVCjfZh6HRcwWKZHnQTDaIJKGKDbJoAbY/EIi\nrUc8OQl57PNq35hc0AJdFHa5oDQ5WtsCXx3q7XNhKjZdR/Vs4kljns5k9/zylJLn\nXI5ly2j46nz+feMaGVP1BdJpPUVWrAcgFQIDAQAB\n-----END RSA PUBLIC KEY-----','wbsllmigfa4ct0zp4gdd4hx2umpijg4e', '2017-03-22 17:01:53');"
db.exec(sql)
#@系统敏感词库
pipingType = 'filterword'
row = db.fetchone(
"select * from piping_extend where task_id=:task_id and piping_type=:piping_type limit 1",
{
'task_id': 0,
'piping_type': pipingType
})
if not row:
insertRow = {
"app_id": 0,
"site_id": 0,
"task_id": 0,
"piping_type": pipingType,
"data": "",
"status": 1
}
extendId = db.insert("piping_extend", insertRow)
else:
extendId = row["id"]
content = read("%s/doc/sensitive_word.txt" % PATH_ROOT)
db.updatebyid("piping_extend", {
"data": content.strip(),
"status": 1
}, extendId)
#@系统异常状态码
pipingType = 'err_http_code'
row = db.fetchone(
"select * from piping_extend where task_id=:task_id and piping_type=:piping_type limit 1",
{
'task_id': 0,
'piping_type': pipingType
})
if not row:
insertRow = {
"app_id": 0,
"site_id": 0,
"task_id": 0,
"piping_type": pipingType,
"data": "",
"status": 1,
}
extendId = db.insert("piping_extend", insertRow)
else:
extendId = row["id"]
content = "\n".join(
['401', '402', '403', '404', '405', '500', '501', '502', '503', '504'])
db.updatebyid("piping_extend", {
"data": content.strip(),
"status": 1
}, extendId)
def task_start(taskid):
try:
task = db.fetchone('select * from task where id=:id', {'id': taskid})
if not task: return False
startUrls = json.loads(task['start_urls'])
executedata = {
'site_id': task['site_id'],
'task_id': task['id'],
'app_id': task['app_id'],
'task_type': task['type'],
'start_urls': task['start_urls'],
'domain': getDomainNoPort(startUrls[0]),
'exec_level': task['exec_level'],
'limit_depth': task['limit_depth'],
'limit_total': task['limit_total'],
'limit_time': task['limit_time'],
'limit_subdomain': task['limit_subdomain'],
'limit_image': task['limit_image'],
'limit_js': task['limit_js'],
'limit_jsevent': task['limit_jsevent'],
'exclude_urls': task['exclude_urls'],
'url_unique_mode': task['url_unique_mode'],
'notify_url': task['notify_url'],
'source_ip': task['source_ip'],
'proxies': task['proxies'],
'status': 0,
}
executeid = db.insert('task_execute', executedata)
return executeid
except Exception as e:
logger.exception(e)
return False
def result_save(execute, piping, results):
#数据入库
pipingResult = {}
pipingResult['app_id'] = execute['app_id']
pipingResult['site_id'] = execute['site_id']
pipingResult['task_id'] = execute['task_id']
pipingResult['execute_id'] = execute['id']
pipingResult['piping_id'] = piping['id']
pipingResult['type'] = piping['type']
'''
处理的结果以json字符串的形式保存
包含敏感词,关键字,指纹,错误状态码,暗链
'''
pipingResult['result'] = json.dumps(results, ensure_ascii=False)
pipingResult['status'] = 1
pipingResult['audit_status'] = 0
resultOld = db.fetchone('select id from task_piping_result where execute_id=:eid and piping_id=:pid', {'eid': execute['id'], 'pid': piping['id']})
if resultOld:
resultId = resultOld['id']
db.updatebyid('task_piping_result', pipingResult, resultId)
else:
resultId = db.insert('task_piping_result', pipingResult)
bNotify.save(execute['id'], 'piping_%s' % piping['type'], {'piping_status':'ok'})
return resultId
def save(executeid, eventType = '', extData = {}):
'''抓取完成通知
executeid 执行ID
eventType 事件类型，包括：spider_ok,piping_filterword,piping_fingerprint,piping_keywor,piping_error_http_code,piping_ok
extData 附加数据，字典格式
'''
execute = db.getbyid('task_execute', executeid)
startAt = formatTimestamp(execute['start_at']) if execute['start_at'] else ''
endAt = formatTimestamp(execute['end_at']) if execute['end_at'] else ''
requestData = {'status':execute['status'], 'start_at':startAt, 'end_at':endAt}
requestData = dict(extData, **requestData)
data = {
'site_id': execute['site_id'],
'task_id': execute['task_id'],
'app_id': execute['app_id'],
'execute_id': execute['id'],
'event_type': eventType,
'task_type': execute['task_type'],
'notify_url': execute['notify_url'],
'request_data': json.dumps(requestData, ensure_ascii=False),
}
notifyId = db.insert('task_notify', data)
data['id'] = notifyId
data[mqidKey] = notifyId
data[batchKey] = execute['id']
Mq.produce([data], 'notify')
return notifyId
def test_insert(self):
from common.db import insert
sql = """
INSERT INTO test VALUES(
0,
'test_text'
)"""
self.assertTrue(insert(self.conn, sql))
def _getSiteid(url):
'''获取域名ID'''
domain = urlparse(url)[1]
if ':' in domain: domain = domain.split(':')[0]
domainMain = getDomainMain(domain)
siteDict = db.fetchone('select * from site where domain=:domain',
{'domain': domainMain})
siteid = siteDict['id'] if siteDict else db.insert('site',
{'domain': domainMain})
if domain != domainMain:
domainDict = db.fetchone(
'select * from domain where subdomain=:domain', {'domain': domain})
if not domainDict:
db.insert('domain', {'site_id': siteid, 'subdomain': domain})
return siteid
def save(params = None):
if not params['unique_key']: return False
appdata = {
'unique_key': params['unique_key'],
'public_key': params['public_key'],
}
if params['id']:
appid = params['id']
db.updatebyid('app', appdata, appid)
else:
appid = db.insert('app', appdata)
return appid
def post(self):
title = self.get_argument('title', '')
class_id = self.get_argument('class_id', '')
photo = self.get_argument('photo', '')
summary = self.get_argument('summary', '')
res = {
'code': 0
}
try:
title_check = FiledsCheck(title, msg='文章标题', max_length=30)
title_check.check_null()
title_check.check_length()
class_id_check = FiledsCheck(class_id, msg='所属分类')
class_id_check.check_null()
except FiledsError as msg:
res['code'] = 1
res['msg'] = str(msg)
logger.warning('[ERROR] %s' % str(msg))
return self.finish(res)
data = {
'title': title,
'class_id': class_id,
'photo': photo,
'summary': summary,
'author': 'admin',
'create_time': datetime.datetime.now()
}
try:
sql = 'insert into article (title, class_id, photo, summary, author, create_time) ' \
'values ("{title}", {class_id}, "{photo}", "{summary}", "{author}", "{create_time}")'
count = db.insert(sql.format(**data))
if count:
logger.info('[SUCCESS] %s 添加成功' % title)
res['msg'] = '添加成功!'
except MysqlError as e:
logger.error('[ERROR] %s 添加失败' % title)
res['code'] = 1
res['msg'] = '添加失败，请重新添加!'
print(e)
except Exception as e:
logger.error('[ERROR] %s 添加失败' % title)
res['code'] = 1
res['msg'] = '添加失败，请重新添加!'
print(e)
return self.finish(res)
def save(params=None):
if not params['name'] or not params['key'] or not params['value']:
return False
settingdata = {
'name': params['name'],
'key': params['key'],
'value': params['value'],
'note': _defaultValue(params, 'note', ''),
}
if params['id']:
settingid = params['id']
db.updatebyid('setting', settingdata, settingid)
else:
settingid = db.insert('setting', settingdata)
return settingid
def save(params=None):
if not params['ip'] or not params['port']:
return False
proxydata = {
'ip': params['ip'],
'port': params['port'],
'username': _defaultValue(params, 'username', ''),
'passwd': _defaultValue(params, 'passwd', ''),
}
if params['id']:
proxyid = params['id']
db.updatebyid('proxy', proxydata, proxyid)
else:
proxydata['status'] = 0
proxyid = db.insert('proxy', proxydata)
return proxyid
def snapshot_insert(executeid, piping, urlRow, result, snapshot):
'''插入快照'''
pipingSnapshot = {}
pipingSnapshot['app_id'] = piping['app_id']
pipingSnapshot['site_id'] = piping['site_id']
pipingSnapshot['task_id'] = piping['task_id']
pipingSnapshot['execute_id'] = executeid
pipingSnapshot['piping_id'] = piping['id']
pipingSnapshot['type'] = piping['type']
pipingSnapshot['url_id'] = urlRow['id']
pipingSnapshot['url'] = urlRow['url']
pipingSnapshot['snapshot'] = snapshot if type(snapshot) == str else "\n".join(snapshot)
pipingSnapshot['result'] = json.dumps(result, ensure_ascii=False)
pipingSnapshot['status'] = 1
pipingSnapshot['audit_status'] = 0
snapshotId = db.insert('task_piping_snapshot', pipingSnapshot)
return snapshotId
def init_app():
'''只系统上线，初始化时使用，谨慎操作'''
return True
#tsgz
appid = 2
app = {
'id': appid,
'unique_key': 'tsgz',
'public_key':
"-----BEGIN RSA PUBLIC KEY-----\nMIIBCgKCAQEA9Vp7hhFpJe2zYuGDDBQ2wb0e7tKHwfHdE6e8ZUJDkMgPLKBEbHwo\nSuvLXgrtGqjclVSIn6Py+NmQbtWxnOZuV/2O/jzhnflu8vVoXVwEuj4gj3+jGZV4\nB0MFICeZ/+qM2UcqrquxQrLhV1gU8InaaTgkMtC4Iag38YdDUy6MdBH7yOQzmUuq\nd5PhbsZeb45Y2OSuq2jhg3d1Xu1vHIrj1A0jSs99d5lOdubpCu7l1JC3WrjVBISj\nlQnrQmUATVy6Tr0Wvv8n1hqaZVNGpAM6pI4UtF+OldU7MrNqQzc+8a5hj2A2SGZE\nfPgyjaS8p+/K4tECY0STfXtB7wjg8oU8bQIDAQAB\n-----END RSA PUBLIC KEY-----",
'token': '9a684815a09c65edb52b7612cda4b1ad',
'token_expired': now_format(),
}
row = db.getbyid('app', appid)
if row:
db.updatebyid('app', app, appid)
else:
db.insert('app', app)
#homev5_apiv4
appid = 3
app = {
'id': appid,
'unique_key': 'homev5_apiv4',
'public_key':
"-----BEGIN RSA PUBLIC KEY-----\nMIIBCgKCAQEA7LRjexk787YP48ZiWOwHNa93VF+J0H/pdINSvqIqWU6yAarpkLWq\nKV9Xd27QCcK6z459b0v/S6QplOPWks7m0MCFrflxkAEjd6MtXJiq3a6rcnX1w0vu\nPozNcM8ibLQI6XoSWNx2sUlQDpDdT9JvdGsnoCfY+pAS3gycgAHzFJH9UbY68igk\nn1cqFuADso3YLXZssK+eslnsfK20iZPiobmSWACLz0vi0gxTABSLqXM3ovJBZgiB\n0QUqvKJY1pM0dHpVpnj73y3CutqH+v255x32y2DVfG4AC6hxCojIhQDx8vAqsKc1\nHYcKxCTPGGVGGvmDUDevwvmvF+GjDZ0SQQIDAQAB\n-----END RSA PUBLIC KEY-----",
'token': '20d812f96badf9f811cde6f9916d5a50',
'token_expired': now_format(),
}
row = db.getbyid('app', appid)
if row:
db.updatebyid('app', app, appid)
else:
db.insert('app', app)
#homev5_apiv4_mirror
appid = 4
app = {
'id': appid,
'unique_key': 'homev5_apiv4_mirror',
'public_key':
"-----BEGIN RSA PUBLIC KEY-----\nMIIBCgKCAQEAwT90eOKM9YaUDYM1v2WR1TL7Qf1t3e3ogCkFSSbH0D4IBn/bVOi9\nCq7jDRZH9F75j6uGXymMLGF841kgrgn8NdyalqaLGRrufw+K971UfNToT/SEAW9O\n+HlZLIV+itAVbBly5/LJFc16aPUH2L47r8qFIFB0PfjLSAsHhbRRs6jLyuZTtzGi\no4iod7/5R+ip216fu7cxiAE3wBhfKTT7IYnAnW7+tYLPqlGcszJkSJtZozHcxudw\n4nVRu+2pkP9ud1YnbWSVGDADMQ33YaKSrm4O+dCDw5EqhmYo+0xH39TNS/2GjCK2\n83R0ZvuS9KkYCNhSYYEKVKiyTuavTpsWWwIDAQAB\n-----END RSA PUBLIC KEY-----",
'token': '69866dde69bced6b006708b936e038c3',
'token_expired': now_format(),
}
row = db.getbyid('app', appid)
if row:
db.updatebyid('app', app, appid)
else:
db.insert('app', app)
return True
def post(self):
username = self.get_argument('username', '')
password = self.get_argument('password', '')
res = {
'code': 0,
}
msg = make_check(username, password)
if msg:
res['code'] = 1
res['msg'] = msg
return self.finish(res)
sql = 'select username from users where username = "******"' % (username)
data = db.get_one(sql)
if data:
logger.warning('[ERROR] %s 用户名已存在' % username)
res['msg'] = '用户名已存在!'
else:
try:
sql = 'insert into users (username, password) values ("%s", "%s")' % (username, encryption(password))
count = db.insert(sql)
if count:
logger.info('[SUCCESS] %s 注册成功' % username)
res['msg'] = '注册成功!'
else:
raise MysqlError
except MysqlError as e:
logger.error('[ERROR] %s 注册失败' % username)
res['code'] = 1
res['msg'] = '注册失败，请重新注册!'
print(e)
except Exception as e:
logger.error('[ERROR] %s 注册失败' % username)
res['code'] = 1
res['msg'] = '注册失败，请重新注册!'
print(e)
return self.finish(res)
def post(self):
name = self.get_argument('name', '')
res = {'code': 0}
try:
name_check = FiledsCheck(name,
msg='分类名称',
min_lenght=2,
max_length=10)
name_check.check_null()
name_check.check_length()
except FiledsError as msg:
res['code'] = 1
res['msg'] = str(msg)
logger.warning('[ERROR] %s' % str(msg))
return self.finish(res)
try:
sql = 'insert into article_class (name) values ("%s")' % name
count = db.insert(sql)
if count:
logger.info('[SUCCESS] %s 添加成功' % name)
res['msg'] = '添加成功!'
else:
raise MysqlError
except MysqlError as e:
logger.error('[ERROR] %s 添加失败' % name)
res['code'] = 1
res['msg'] = '添加失败，请重新添加!'
print(e)
except Exception as e:
logger.error('[ERROR] %s 添加失败' % name)
res['code'] = 1
res['msg'] = '添加失败，请重新添加!'
print(e)
return self.finish(res)
def piping_save(rows=None,taskid=None):
task = db.fetchone('select * from task where id=:id', {'id':taskid})
for row in rows:
taskPiping = db.fetchone('select * from task_piping where task_id=:tid and type=:type', {'tid':taskid, 'type':row['type']})
extendId = 0
pipingExtendOld = None
if taskPiping:
extendId = taskPiping['extend_id']
pipingExtendOld = db.fetchone('select * from piping_extend where id=:id', {'id': extendId})
# wordId 值为0，则取系统默认词库
if row['type'] == 'darklink':
pipingExtend={}
pipingExtend['app_id'] = task['app_id']
pipingExtend['site_id'] = task['site_id']
pipingExtend['task_id'] = taskid
pipingExtend['piping_type'] = row['type']
white_list = json.dumps(row['white_list'], ensure_ascii=False) if row['white_list'] else '[]'
white_list = {'white_list':eval(white_list)}
black_list = json.dumps(row['black_list'], ensure_ascii=False) if row['black_list'] else '[]'
black_list = {'black_list':eval(black_list)}
pipingExtend['data'] = json.dumps(dict(white_list, **black_list))
pipingExtend['status'] = 1
if pipingExtendOld:
db.updatebyid('piping_extend', pipingExtend, extendId)
else:
extendId = db.insert('piping_extend', pipingExtend)
if row['type'] == 'filterword' and 'filterwords' in row.keys() and 'filterword_operate' in row.keys():
words = []
wordsOld = []
wordsNew = row['filterwords'].replace(' ', '').split("\n")
if pipingExtendOld:
extendId = pipingExtendOld['id']
wordsOld = pipingExtendOld['data'].split("\n") if pipingExtendOld['data'] else []
# 覆盖自有词库
if row['filterword_operate'] == 'own':
words = wordsNew
# 加词
if row['filterword_operate'] == 'plus':
words.extend(wordsNew)
if wordsOld: words.extend(wordsOld)
# 减词
if row['filterword_operate'] == 'reduce' and wordsOld:
wordsCommon = list(set(wordsNew) & set(wordsOld))
for word in wordsCommon: wordsOld.remove(word)
words = wordsOld
if '' in words: words.remove('')
words = list(set(words))
pipingExtend = {}
pipingExtend['app_id'] = task['app_id']
pipingExtend['site_id'] = task['site_id']
pipingExtend['task_id'] = taskid
pipingExtend['piping_type'] = row['type']
pipingExtend['data'] = "\n".join(words)
pipingExtend['status'] = 1
if pipingExtendOld:
db.updatebyid('piping_extend', pipingExtend, extendId)
else:
extendId = db.insert('piping_extend', pipingExtend)
# 处理关键字
if row['type'] == 'keyword' and 'keywords' in row.keys():
pipingExtend = {}
pipingExtend['app_id'] = task['app_id']
pipingExtend['site_id'] = task['site_id']
pipingExtend['task_id'] = taskid
pipingExtend['piping_type'] = row['type']
pipingExtend['data'] = json.dumps(row['keywords'], ensure_ascii=False) if row['keywords'] else ''
pipingExtend['status'] = 1
if pipingExtendOld:
db.updatebyid('piping_extend', pipingExtend, extendId)
else:
extendId = db.insert('piping_extend', pipingExtend)
# 处理错误状态吗
if row['type'] == 'error_http_code' and 'http_codes' in row.keys():
pipingExtend = {}
pipingExtend['app_id'] = task['app_id']
pipingExtend['site_id'] = task['site_id']
pipingExtend['task_id'] = taskid
pipingExtend['piping_type'] = row['type']
pipingExtend['data'] = row['http_codes']
pipingExtend['status'] = 1
if pipingExtendOld:
db.updatebyid('piping_extend', pipingExtend, extendId)
else:
extendId = db.insert('piping_extend', pipingExtend)
wordType = row['filterword_type'] if 'filterword_type' in row.keys() else ''
status = row['status'] if 'status' in row.keys() else 1
piping = {}
piping['status'] = status
piping['extend_id'] = extendId
piping['filterword_type'] = wordType
if taskPiping:
pipingId = db.updatebyid('task_piping', piping, taskPiping['id'])
else:
piping['app_id'] = task['app_id']
piping['site_id'] = task['site_id']
piping['task_id'] = taskid
piping['type'] = row['type']
pipingId = db.insert('task_piping', piping)
return True
def task_save(params=None):
if not params['id'] and not params['start_urls']: return False
if params['start_urls']:
startUrls = params['start_urls'].split("\n")
params['start_urls'] = json.dumps(startUrls, ensure_ascii=False)
else:
params['start_urls'] = ''
#默认值
defaultKeys = {
'app_id': 0,
'type': 'spider',
'start_urls': '',
'exec_level': 0,
'limit_depth': 2,
'limit_total': 1000,
'limit_time': 0,
'limit_subdomain': 0,
'limit_image': 0,
'limit_js': 0,
'url_unique_mode': 'url-query',
'notify_url': '',
'exec_level': 0,
'source_ip': '',
'exclude_urls': '',
'proxies': '',
'crontab': '',
'status': 0,
}
#处理定时任务
rundate = None
if 'execute_at' in params.keys() and params['execute_at']:
rundate = datetime.strptime(params['execute_at'], '%Y-%m-%d %H:%M:%S')
if 'execute_delay' in params.keys() and params['execute_delay']:
rundateStr = getTime('%Y-%m-%d %H:%M:%S',
getTime() + params['execute_delay'])
rundate = datetime.strptime(rundateStr, '%Y-%m-%d %H:%M:%S')
#保存数据
taskdata = {}
keys = defaultKeys.keys()
if params['id']:
taskid = params['id']
for key in keys:
if key in params.keys() and params[key]:
taskdata[key] = params[key]
result = db.updatebyid('task', taskdata, taskid)
else:
taskdata['site_id'] = _getSiteid(startUrls[0])
for key in keys:
if key in params.keys() and params[key]:
taskdata[key] = params[key]
else:
taskdata[key] = defaultKeys[key]
taskid = db.insert('task', taskdata)
#定时任务
func_name = task_start
jobid = 'task_%s' % taskid
if rundate:
job = db.getbyid('scheduler', jobid)
if job:
db.updatebyid('scheduler', {'run_date': rundate}, jobid)
else:
scheduler = {
'id': jobid,
'name': jobid,
'func': 'business.task:task_start',
'args': '[' + str(taskid) + ']',
'trigger_type': 'date',
'run_date': rundate,
'coalesce': 0,
'next_run_time': rundate,
'max_instances': 3,
'executor': 'default',
'misfire_grace_time ': 1,
}
db.insert(scheduler)
return taskid
#非计划任务
task = db.fetchone("select * from task where id=:id", {'id': taskid})
if not task['crontab']:
task_start(taskid)
return taskid
#删除计划任务
if taskdata['status'] < 1 and taskdata['crontab']:
db.exec('delete from scheduler where id=:id', {'id': jobid})
return taskid
#添加或修改计划任务
job = db.getbyid('scheduler', jobid)
cs = params['crontab'].split(' ')
if job:
crontab = '0 ' + task['crontab'] + ' * *,SMHdmwWY'
db.updatebyid('scheduler', {'crontab': crontab}, jobid)
else:
tz = pytz.timezone('Asia/Shanghai')
scheduler = {
'id': jobid,
'name': jobid,
'func': 'business.task:task_start',
'args': '[' + str(taskid) + ']',
'trigger_type': 'cron',
'crontab': '0 ' + task['crontab'] + ' * *,SMHdmwWY',
'coalesce': 0,
'next_run_time':
datetime.now(tz=tz).strftime('%Y-%m-%d %H:%M:%S%z'),
'max_instances': 3,
'executor': 'default',
'misfire_grace_time ': 1,
}
db.insert('scheduler', scheduler)
return taskid
from common import db
from common.db import open_db, close_db
class DataConns():
"""上下文操作数据库"""
def __init__(self):
open_db()
def __enter__(self):
pass
def __exit__(self, exc_t, exc_v, traceback):
close_db()
if __name__ == "__main__":
with DataConns() as Conn:
db.insert('','','')
pass
