def insert_mobile_ad_list(project,
url,
src,
src_url,
submitter,
utm_source,
utm_medium,
utm_campaign,
utm_content,
utm_term,
expired_at=2147483647):
timenow = int(time.time())
sql = """insert mobile_ad_list (`project`,`url`,`expired_at`,`created_at`,`src`,`src_url`,`submitter`,`utm_source`,`utm_medium`,`utm_campaign`,`utm_content`,`utm_term`) values ('{project}','{url}',{expired_at},{created_at},'{src}','{src_url}','{submitter}','{utm_source}','{utm_medium}','{utm_campaign}','{utm_content}','{utm_term}')""".format(
project=project,
url=url,
expired_at=expired_at,
created_at=timenow,
src=src,
src_url=src_url,
submitter=submitter,
utm_source=utm_source,
utm_medium=utm_medium,
utm_campaign=utm_campaign,
utm_content=utm_content,
utm_term=utm_term).replace("'None'", "Null").replace("None", "Null")
result, count = do_tidb_exe(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='insert_mobile_ad_list',
result=str(result) + sql)
return '', 0
return result, count
def count_shortcut(filters=''):
sql = """SELECT count(*) FROM `shortcut` {filters} """.format(filters=filters)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='show_shortcut',result=str(result)+sql)
return '',0
return result[0]
def send_auto_noti():
miss = 1 #初始化没有命中的次数
while True:
missTime = 0
#延迟器，用来降低数据库压力，每次找不到，则增加1秒的重试等待时间。当重试等待超过2分钟后，不再增加重试等待时间。以保证2分钟至少会查询一次。
projects_result, project_count = select_scheduler_enable_project()
write_to_log(filename='messenger',
defname='send_auto_noti',
result='获取启用定时器任务的项目' +
(str(project_count) if project_count else '0'))
for project in projects_result:
result = select_noti_auto(project=project[0])
if result[1] > 0:
miss = 1
for noti in result[0]:
send_manual(project=project[0], noti=noti)
else:
miss = miss * 2
missTime = missTime + 1
print(project[0] + '暂无自动消息,miss:' + str(miss))
if missTime == project_count and miss >= 0 and miss <= 120:
time.sleep(abs(miss))
elif missTime == project_count and miss > 120:
time.sleep(120)
elif missTime < project_count:
miss = 1
def get_datas():
try:
return get_data()
except Exception:
error = traceback.format_exc()
write_to_log(filename='api', defname='get_datas', result=error)
return error
def post_wechat_notification(data):
post_data_json = {
"touser": data['wechat_openid'],
"template_id": data['wechat_template_id'],
"data": data['data']
}
if 'target_url' in data:
post_data_json['url'] = data['target_url']
if "miniprogram" in data:
post_data_json['miniprogram'] = {
"appid": data['miniprogram_id'],
"pagepath": data['miniprogram_pagepath']
}
try:
result = get_json_from_postjson(
url=
'https://api.weixin.qq.com/cgi-bin/message/template/send?access_token='
+ getWeChatToken(),
data=post_data_json)
if result['errcode'] == 0:
return 'success'
else:
return str(result)
except Exception:
error = traceback.format_exc()
write_to_log(filename='wechat',
defname='post_wechat_notification',
result=error)
return 'check_fail_log'
def show_check(project,date,hour,order,start,limit,add_on_where):
sql = """select distinct_id,event,type,all_json,host,user_agent,ip,url,remark,from_unixtime(created_at) from {project} where date = '{date}' and hour = {hour} {add_on_where} order by created_at {order} limit {start},{limit}""".format(project=project,date=date,hour=hour,order=order,start=start,limit=limit,add_on_where=add_on_where)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='show_check',result=str(result)+sql)
return '',0
return result[0],result[1]
def disable_single():
#禁用单挑推送
start_time = time.time()
password = get_url_params('password')
project = get_url_params('project')
noti_id = get_url_params('noti_id')
if password == admin.admin_password and project and request.method == 'POST' and noti_id:
try:
result = disable_noti_db(project=project, noti_id=noti_id)
time_cost = round(time.time() - start_time, 2)
if result[1] > 0:
returnjson = {
'result': 'success',
'results_count': result[1],
'timecost': time_cost
}
return jsonify(returnjson)
else:
returnjson = {
'result': 'fail',
'results_count': result[1],
'timecost': time_cost
}
return jsonify(returnjson)
except Exception:
error = traceback.format_exc()
write_to_log(filename='api_noti',
defname='manual_send',
result=error)
returnjson = {'result': 'fail', 'error': error}
return jsonify(returnjson)
def ghost_check():
start_time = time.time()
password = request.form.get('password')
project = request.form.get('project',None)
if password == admin.admin_password and project:#只有正确的密码才能触发动作
remark = request.form.get('remark',None)
event = request.form.get('event',None)
date = request.form.get('date',time.strftime("%Y-%m-%d", time.localtime()))
hour = int(request.form.get('hour',time.strftime("%H", time.localtime())))
order = request.form.get('order','desc')
distinct_id = request.form.get('distinct_id',None)
start = int(request.form.get('start','0'))
limit = int(request.form.get('limit','10'))
add_on_where = ''
if distinct_id:
add_on_where = add_on_where+' and distinct_id = \''+distinct_id+'\''
if event:
add_on_where = add_on_where+' and event = \''+event+'\''
if remark:
add_on_where = add_on_where+' and remark = \''+remark+'\''
try:
results,results_count= show_check(project=project,date=date,hour=hour,order=order,start=start,limit=limit,add_on_where=add_on_where)
pending_result = []
for item in results:
row = {'distinct_id':item[0],'event':item[1],'type':item[2],'all_json':json.loads(item[3]),'host':item[4],'user_agent':item[5],'ip':item[6],'url':item[7],'remark':item[8],'created_at':item[9],}
# pending_result.append(dict(zip(key,item)))
pending_result.append(row)
time_cost = time.time() - start_time
returnjson = {'result':'success','order':order,'results_count':results_count,'timecost':time_cost,'data':pending_result}
return jsonify(returnjson)
except Exception:
error = traceback.format_exc()
write_to_log(filename='api',defname='ghost_check',result=error)
def insert_shortcut(project,short_url,long_url,expired_at,src,src_short_url,submitter,utm_source,utm_medium,utm_campaign,utm_content,utm_term):
timenow = int(time.time())
sql = """insert into shortcut (`project`,`short_url`,`long_url`,`expired_at`,`created_at`,`src`,`src_short_url`,`submitter`,`utm_source`,`utm_medium`,`utm_campaign`,`utm_content`,`utm_term`) values ('{project}','{short_url}','{long_url}',{expired_at},{created_at},'{src}','{src_short_url}','{submitter}','{utm_source}','{utm_medium}','{utm_campaign}','{utm_content}','{utm_term}')""".format(project=project,short_url=short_url,long_url=long_url,expired_at=expired_at,created_at=timenow,src=src,src_short_url=src_short_url,submitter=submitter,utm_source=utm_source,utm_medium=utm_medium,utm_campaign=utm_campaign,utm_content=utm_content,utm_term=utm_term).replace("'None'","Null").replace("None","Null")
result = do_tidb_exe(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='insert_shortcut',result=result+sql)
return result[1]
def show_shortcut(page,length,filters='',sort='`shortcut`.created_at',way='desc'):
sql = """SELECT `shortcut`.project,`shortcut`.short_url,`shortcut`.long_url,from_unixtime(`shortcut`.expired_at),from_unixtime(`shortcut`.created_at),`shortcut`.src,`shortcut`.src_short_url,`shortcut`.submitter,`shortcut`.utm_source,`shortcut`.utm_medium,`shortcut`.utm_campaign,`shortcut`.utm_content,`shortcut`.utm_term,shortcut.created_at,shortcut.expired_at,count(shortcut_history.created_at) as visit_times,count(shortcut_read.created_at) as read_times FROM `shortcut` left join `shortcut_history` on `shortcut`.short_url = `shortcut_history`.`short_url` left join `shortcut_read` on `shortcut`.short_url = `shortcut_read`.`short_url` {filters} GROUP BY `shortcut`.project,`shortcut`.short_url,`shortcut`.long_url,from_unixtime(`shortcut`.expired_at),from_unixtime(`shortcut`.created_at),`shortcut`.src,`shortcut`.src_short_url,`shortcut`.submitter,`shortcut`.utm_source,`shortcut`.utm_medium,`shortcut`.utm_campaign,`shortcut`.utm_content,`shortcut`.utm_term,shortcut.created_at,shortcut.expired_at ORDER BY {sort} {way} Limit {start_pageline},{length}""".format(start_pageline=(page-1)*length if page>1 else 0,length=length,filters=filters,sort=sort,way=way)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='show_shortcut',result=str(result)+sql)
return '',0
return result[0],result[1]
def insert_usergroup(project,group_id,data,list_desc,jobs_id,init_time=int(time.time())):
#重新执行该分群
index_id = check_lastest_usergroup_list_index(project=project,group_id=group_id)
try:
list_init_count = insert_usergroup_list(project=project,group_id=group_id,group_index=index_id+1,status=2,list_desc=list_desc,jobs_id=jobs_id)
list_id = check_list_id(project=project, group_id=group_id, group_list_index=index_id+1)
data_index = 0
for item in data['data_list']:
data_index = data_index + 1
if 'json' in item :
item['json']['group_id'] = group_id
item['json']['key'] = item['key']
insert_usergroup_data(project=project, group_list_id=list_id[0][0], data_index=data_index, key=item['key'], json=json.dumps(item['json']), enable=item['enable'])
insert_usergroup_list(project=project,group_id=group_id,group_index=index_id+1,list_init_date=init_time,status=3,complete_at=0,apply_temple_times=0,item_add=1,created_at=None,updated_at=None,jobs_id=jobs_id)
insert_usergroup_list(project=project,group_id=group_id,group_index=index_id+1,status=5,complete_at=int(time.time()),jobs_id=jobs_id)
update_usergroup_plan(project=project, plan_id=group_id, latest_data_list_index=index_id+1,updated_at=int(time.time()), repeat_times_add=1, latest_data_time=int(time.time()))
return 5,index_id+1
except Exception:
error = traceback.format_exc()
list_info = check_list_id(project=project, group_id=group_id, group_list_index=index_id+1)
if list_info and list_info[0][1]>0:
insert_usergroup_list(project=project,group_id=group_id,group_index=index_id+1,status=4,complete_at=int(time.time()),jobs_id=jobs_id)
write_to_log(filename='etl_model',defname='insert_usergroup',result=error)
return 4,index_id+1
else :
insert_usergroup_list(project=project,group_id=group_id,group_index=index_id+1,status=6,complete_at=int(time.time()),jobs_id=jobs_id)
write_to_log(filename='etl_model',defname='insert_usergroup',result=error)
return 6,0
def exesqlsc(sql, retry=10):
# 生产库
project = "production" #神策项目名
token = "your_token_here" #项目Token，在神策原生管理员账号下查看
output_format = "json" #返回数据格式
base_url = "https://your_sensors_domain:8107/api/sql/query" #神策页面的域名
try:
req = requests.post(url=base_url,
params={
"project": project,
"token": token,
"q": sql,
"format": output_format
})
result = req.text
except Exception:
error = traceback.format_exc()
write_to_log(filename='sensorsSQL',
defname='exesqlsc',
result=sql + error)
if retry > 0:
retry -= 1
time.sleep(1)
return exesqlsc(sql=sql, retry=retry)
else:
result = error
return result
def read_mobile_ad_list(page,
length,
filters='',
sort='created_at',
way='desc'):
sort = 'mobile_ad_list.' + sort
sql = """select mobile_ad_list.project,
mobile_ad_list.url,
mobile_ad_list.src,
mobile_ad_src.src_name,
mobile_ad_list.src_url,
mobile_ad_list.submitter,
mobile_ad_list.utm_source,
mobile_ad_list.utm_medium,
mobile_ad_list.utm_campaign,
mobile_ad_list.utm_content,
mobile_ad_list.utm_term,
from_unixtime(mobile_ad_list.created_at,'%Y-%m-%d'),
from_unixtime(mobile_ad_list.expired_at,'%Y-%m-%d')
from mobile_ad_list left join mobile_ad_src on mobile_ad_list.src=mobile_ad_src.src {filters} ORDER BY {sort} {way} Limit {start_pageline},{length}""".format(
start_pageline=(page - 1) * length,
length=length,
filters=filters,
sort=sort,
way=way)
print(sql)
result, count = do_tidb_select(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='read_mobile_ad_list',
result=str(result) + sql)
return '', 0
return result, count
def play_all(self):
try:
self.sample()
if self.project and self.project == 'sample':
self.sample()
except Exception:
error = traceback.format_exc()
write_to_log(filename='trigger', defname='play_all', result=error)
def insert_mobile_ad_src(src,src_name,src_args,utm_source,utm_medium,utm_campaign,utm_content,utm_term):
timenow = int(time.time())
sql = """insert mobile_ad_src (`src`,`src_name`,`src_args`,`created_at`,`updated_at`,`utm_source`,`utm_medium`,`utm_campaign`,`utm_content`,`utm_term`) values ('{src}','{src_name}','{src_args}',{created_at},{updated_at},'{utm_source}','{utm_medium}','{utm_campaign}','{utm_content}','{utm_term}') ON DUPLICATE KEY UPDATE `src_name`='{src_name}',`src_args`='{src_args}',`updated_at`={updated_at},`utm_source`='{utm_source}',`utm_medium`='{utm_medium}',`utm_campaign`='{utm_campaign}',`utm_content`='{utm_content}',`utm_term`='{utm_term}' """.format(src=src,src_name=src_name,src_args=src_args,created_at=timenow,updated_at=timenow,utm_source=utm_source,utm_medium=utm_medium,utm_campaign=utm_campaign,utm_content=utm_content,utm_term=utm_term).replace("'None'","Null").replace("None","Null")
result = do_tidb_exe(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='insert_mobile_ad_src',result=str(result)+sql)
return '',0
return result[0],result[1]
def read_mobile_ad_src_list(add_on_where=''):
timenow = int(time.time())
sql = """select src,src_name,src_args,from_unixtime(created_at),from_unixtime(updated_at),if({timenow}-created_at<=604800,1,0),utm_source,utm_medium,utm_campaign,utm_content,utm_term from mobile_ad_src {add_on_where} order by src_name""".format(add_on_where=add_on_where,timenow=timenow)
result = do_tidb_select(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='read_mobile_ad_src_list',result=str(result)+sql)
return '',0
return result[0],result[1]
def show_usergroup_data():
#查询分群内容
start_time = time.time()
password = get_url_params('password')
project = get_url_params('project')
list_id = get_url_params('list_id')
length = get_url_params('length')
page = get_url_params('page')
everywhere = get_url_params('everywhere')
length = int(length) if length else 500
page = int(page) if page else 1
if password == admin.admin_password and project and request.method == 'POST' and list_id: #只有正确的密码才能触发动作
add_on_where = f'''and concat({project}_usergroup_data.data_key,{project}_usergroup_data.data_json) like "%{everywhere}%"''' if everywhere and everywhere != '' and everywhere != ' ' else ''
try:
results = select_usergroup_data_for_api(project=project,
list_id=list_id,
length=length,
page=page,
everywhere=add_on_where)
resultscount = select_usergroup_datacount_for_api(
project=project,
list_id=list_id,
length=length,
page=page,
everywhere=add_on_where)
temp_json = []
for item in results[0]:
temp_json.append({
"group_id": item[0],
"list_id": item[1],
"data_id": item[2],
"data_index": item[3],
"data_key": item[4],
"data_json": json.loads(item[5]),
"enable_policy_id": item[6],
"enable_policy_name": item[7]
})
time_cost = round(time.time() - start_time, 2)
total_count = resultscount[0][0][0] if resultscount[1] > 0 else 0
returnjson = {
'result': 'success',
'results_count': results[1],
'timecost': time_cost,
'data': temp_json,
'total_count': total_count,
'page': page,
'length': length
}
return jsonify(returnjson)
except Exception:
error = traceback.format_exc()
write_to_log(filename='api_noti',
defname='show_usergroup_data',
result=error)
returnjson = {'result': 'fail', 'error': error}
return jsonify(returnjson)
def show_temples():
#查询模板列表
start_time = time.time()
password = get_url_params('password')
project = get_url_params('project')
mode = get_url_params('mode')
if password == admin.admin_password and project and request.method == 'POST': #只有正确的密码才能触发动作
# remark = request.form.get('remark',None)k+'\''
try:
results = show_temples_db(project=project)
temp_json = []
for item in results[0]:
if mode and mode == 'cli':
temp_json.append({
"temple_id": item[0],
"temple_name": item[1],
"temple_desc": item[2],
"apply_times": item[5],
"lastest_apply_time": item[6],
"lastest_apply_list_desc": item[8],
"lastest_apply_group_name": item[9],
"created_at": item[10],
"updated_at": item[11]
})
else:
temp_json.append({
"temple_id": item[0],
"temple_name": item[1],
"temple_desc": item[2],
"temple_args": json.loads(item[3]),
"temple_content": json.loads(item[4]),
"apply_times": item[5],
"lastest_apply_time": item[6],
"lastest_apply_list_id": item[7],
"lastest_apply_list_desc": item[8],
"lastest_apply_group_name": item[9],
"created_at": item[10],
"updated_at": item[11]
})
time_cost = round(time.time() - start_time, 2)
returnjson = {
'result': 'success',
'results_count': results[1],
'timecost': time_cost,
'data': temp_json
}
# print(returnjson)
return jsonify(returnjson)
except Exception:
error = traceback.format_exc()
write_to_log(filename='api_noti',
defname='show_temples',
result=error)
returnjson = {'result': 'fail', 'error': error}
return jsonify(returnjson)
def show_project():
sql = """select project_name,FROM_UNIXTIME(created_at),FROM_UNIXTIME(expired_at) from project_list order by project_name"""
result, count = do_tidb_select(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='show_project',
result=str(result) + sql)
return '', 0
return result, count
def insert_user(project, data_decode, created_at=None):
distinct_id = data_decode['distinct_id']
lib = None
if 'lib' in data_decode:
if '$lib' in data_decode['lib']:
lib = data_decode['lib']['$lib']
elif 'properties' in data_decode:
if '$lib' in data_decode['properties']:
lib = data_decode['properties']['$lib']
map_id = data_decode['map_id'] if 'map_id' in data_decode else ''
original_id = data_decode[
'original_id'] if 'original_id' in data_decode else ''
if 'userId' in data_decode['properties']:
if data_decode['properties']['userId'] != '' and data_decode[
'properties']['userId']:
user_id = data_decode['properties']['userId']
elif 'user_id' in data_decode['properties']:
if data_decode['properties']['user_id'] != '' and data_decode[
'properties']['user_id']:
user_id = data_decode['properties']['user_id']
elif 'uid' in data_decode['properties']:
if data_decode['properties']['uid'] != '' and data_decode[
'properties']['uid']:
user_id = data_decode['properties']['uid']
else:
user_id = None
all_user_profile = json.dumps(
data_decode['properties']
) if data_decode['type'] == 'profile_set' else None
update_content = []
if user_id:
update_content.append(
"user_id = %(user_id)s") #.format(user_id=user_id))
if all_user_profile:
update_content.append("all_user_profile = %(all_user_profile)s"
) #.format(all_user_profile=all_user_profile))
update_params = ''
for item in update_content:
#更新更新时间
update_params = update_params + ' , ' + item
#不更新更新时间
try:
insert_count = insert_user_db(project=project,
distinct_id=distinct_id,
lib=lib,
map_id=map_id,
original_id=original_id,
user_id=user_id,
all_user_profile=all_user_profile,
update_params=update_params,
created_at=created_at)
print('插入或更新user' + str(insert_count) + '条')
except Exception:
error = traceback.format_exc()
write_to_log(filename='api_tools', defname='insert_user', result=error)
def get_json_from_postjson(url, data):
headers = {'User-agent': admin.who_am_i}
try:
req = requests.post(url=url, headers=headers, json=data)
result = req.json()
return result
except Exception:
error = traceback.format_exc()
write_to_log(filename='api_req',
defname='get_json_from_postjson',
result=error)
def count_mobile_ad_list(filters=''):
sql = """SELECT count(*) FROM `mobile_ad_list` {filters} """.format(
filters=filters)
result, count = do_tidb_select(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='count_mobile_ad_list',
result=str(result) + sql)
return '', 0
return result
def do_insert(msg):
try:
data = json.loads(msg.value.decode('utf-8'))['data']
offset = msg.offset
print(offset)
# print(data['project'])
insert_data(project=data['project'], data_decode=data['data_decode'], User_Agent=data['User_Agent'], Host=data['Host'], Connection=data['Connection'], Pragma=data['Pragma'], Cache_Control=data['Cache_Control'], Accept=data['Accept'], Accept_Encoding=data['Accept_Encoding'], Accept_Language=data['Accept_Language'], ip=data['ip'], ip_city=data['ip_city'],
ip_asn=data['ip_asn'], url=data['url'], referrer=data['referrer'], remark=data['remark'], ua_platform=data['ua_platform'], ua_browser=data['ua_browser'], ua_version=data['ua_version'], ua_language=data['ua_language'], ip_is_good=data['ip_is_good'], ip_asn_is_good=data['ip_asn_is_good'], created_at=data['created_at'], updated_at=data['updated_at'], use_kafka=False)
except Exception:
error = traceback.format_exc()
write_to_log(filename='kafka_consumer',
defname='do_insert', result=error)
def get_suoim_short_url(long_url):
req = requests.get(url=suoim_shorten_url,params={'key':suoim_key,'format':'json','url':long_url})
result = req.json()
# print(type(result['err']))
if result['err'] is None or result['err'] == '':
short_url = result['url']
return short_url,'ok'
# print(short_url)
else:
# print(result['err'])
return '','fail'
write_to_log(filename='shorturl',defname='get_suoim_short_url',result=result['err'])
def recall_baidu_bdvid(uid, project, newType=99, convertValue=0,token="your_token_here"):
try:
timenow = int(time.time())
sql_check_all_did = """select if(original_id='',distinct_id,original_id)as did from {project}_user where distinct_id='{uid}' GROUP BY did""".format(
uid=uid, project=project)
all_did, did_count = select_tidb(sql=sql_check_all_did)
did_list = []
for did in all_did:
did_list.append("'"+did[0]+"'")
did_str = (',').join(did_list)
sql_find_last_bdvid = """select date,created_at,distinct_id,SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_EXTRACT(all_json, '$."properties"."$url"'),'bd_vid=',-1),'"',1)as bdvid,JSON_EXTRACT(all_json, '$."properties"."$url"'),all_json from {project} where distinct_id in ({dids}) and `event`='$pageview' and JSON_EXTRACT(all_json, '$."properties"."$url"') like '%bd_vid%' having LENGTH(bdvid)>0 ORDER BY created_at desc limit 1""".format(
dids=did_str, project=project)
print(sql_find_last_bdvid)
bdvid_result, bdvid_count = select_tidb(sql=sql_find_last_bdvid)
if bdvid_count > 0:
latest_date = bdvid_result[0][0]
latest_created_at = bdvid_result[0][1]
latest_distinct_id = bdvid_result[0][2]
latest_bdvid = bdvid_result[0][3]
latest_url = json.loads(bdvid_result[0][4])
jsondata = json.loads(bdvid_result[0][5])
req_data = {"token": token,
"conversionTypes": [
{
"logidUrl": latest_url,
"newType": newType
}]
}
if convertValue > 0:
req_data['conversionTypes'][0]['convertValue'] = convertValue
# print(req_data)
json_result = get_json_from_postjson(
url='https://ocpc.baidu.com/ocpcapi/api/uploadConvertData', data=req_data)
all_json = {}
all_json['req_data'] = req_data
all_json['bdvid_result'] = jsondata
all_json['latest_date'] = str(latest_date)
all_json['latest_created_at'] = latest_created_at
all_json['latest_distinct_id'] = latest_distinct_id
all_json['latest_bdvid'] = latest_bdvid
all_json['latest_url'] = latest_url
all_json['recall_result'] = json_result
all_json['all_did'] = did_list
insert_count = insert_event(table=project, alljson=json.dumps(all_json, ensure_ascii=False), track_id=0, distinct_id=uid.replace('"', ''), lib='ghost_sa', event='$is_channel_callback_event', type_1='ghost_sa_func', User_Agent=None, Host=None, Connection=None,
Pragma=None, Cache_Control=None, Accept=None, Accept_Encoding=None, Accept_Language=None, ip=None, ip_city=None, ip_asn=None, url=None, referrer=None, remark='normal', ua_platform=None, ua_browser=None, ua_version=None, ua_language=None, created_at=timenow)
# print(all_json)
# print(json.dumps(all_json,ensure_ascii=False))
except Exception:
error = traceback.format_exc()
write_to_log(filename='sample',defname='recall_baidu_bdvid',result=sql_find_last_bdvid+error)
def play_all(self):
try:
print(self.noti_type)
if self.noti_type == 23:
return self.via_email()
elif self.noti_type == 24:
return self.sms()
except Exception:
error = traceback.format_exc()
write_to_log(filename='messenger',
defname='play_all',
result=error)
return 'failed,please check logs'
def do_tidb_select(sql, args=None, retrycount=5):
try:
results, result_count = select_tidb(sql=sql, args=args)
return results, result_count
except Exception:
error = traceback.format_exc()
write_to_log(filename='db_op', defname='do_tidb_select', result=error)
if retrycount > 0:
retrycount -= 1
time.sleep(1)
return do_tidb_select(sql=sql, args=args, retrycount=retrycount)
else:
return 'sql_err', 0
def last_1500_email(args):
date = args['date']
days = args['days'] if 'days' in args and args['days'] else 1
count = args['count'] if 'count' in args and args['count'] else 50
timenow = int(time.time())
stauts = 0
index_id = 0
job_id = args['job_id']
try:
start_day = get_display_day(start_day=date, calc=1 - days)
sql_find_user = """select tvcbook.distinct_id,max(tvcbook.created_at) as max_c from tvcbook left join tvcbook_user on tvcbook.distinct_id=tvcbook_user.distinct_id where tvcbook.date>=DATE_SUB('{start_day}',INTERVAL 1 DAY) and date<=DATE_SUB('{end_day}',INTERVAL 1 DAY) and event = '$pageview' and tvcbook_user.original_id is not null and tvcbook_user.original_id != '' GROUP BY tvcbook.distinct_id order by max_c desc limit {count}""".format(
start_day=start_day, end_day=date, count=count)
users_result = do_tidb_select(sql=sql_find_user)
# print(users_count)
total_insert = 0
all_data = {'data_list': []}
for user in users_result[0]:
sql_email = """select email,nickname from yii2_user left join yii2_user_profile on yii2_user.id = yii2_user_profile.user_id where uid = '{uid}';""".format(
uid=user[0])
sql_email_result, sql_email_count = do_tvcbook_select(
sql=sql_email)
if sql_email_count > 0 and sql_email_result[0][
0] is not None and sql_email_result[0][
0] != '' and total_insert < count:
all_data['data_list'].append({
'key': user[0],
'enable': 9,
'json': {
'email': sql_email_result[0][0],
'nickname': sql_email_result[0][1],
'last_active_time': get_time_str(inttime=user[1])
}
})
total_insert = total_insert + 1
# print(total_insert,user[0],sql_email_result[0][0],sql_email_result[0][1],get_time_str(inttime=user[1]))
# print(all_data)
status, index_id = insert_usergroup(project='tvcbook',
group_id=4301,
data=all_data,
init_time=timenow,
list_desc=date,
jobs_id=job_id)
return status, index_id
except Exception:
error = traceback.format_exc()
write_to_log(filename='tvcbook',
defname='last_1500_email',
result=error)
return 20, index_id
def send_email(to_addr='*****@*****.**',from_addr=None,subject='鬼策测试邮件标题',html="""<p>鬼策测试邮件正文</p><p><a href="https://github.com/white-shiro-bai/ghost_sa/">这是一个测试链接</a></p>"""):
#使用Python发送HTML格式的邮件
message = MIMEText(html, 'html', 'utf-8')
message['From'] = Header(from_addr, 'utf-8') #括号里的对应发件人邮箱昵称（随便起）、发件人邮箱账号
message['To'] = Header(to_addr, 'utf-8') #括号里的对应收件人邮箱昵称、收件人邮箱账号
message['Subject'] = Header(subject, 'utf-8')
try:
smtpObj = smtplib.SMTP_SSL(host=email.mail_host, port=email.mail_port)
smtpObj.login(email.mail_user, email.mail_pass)
smtpObj.sendmail(from_addr, to_addr, message.as_string())
return 'success'
except smtplib.SMTPException as e:
write_to_log(filename='email',defname='send_email',result=str(e))
return str(e)
def do_all_task():
task_count = 0
start_time = int(time.time())
miss = 1
while task_count < 1 and int(time.time()) - start_time <= 1800:
#连续30分钟拿不到任务，就退出，会重新搜索plan。
priority = get_priority()
task_result, task_count = check_next_scheduler_job(priority=priority)
write_to_log(filename='scheduler',
defname='do_all_task',
result='查询优先级' + str(priority) + '获取任务数' +
str(task_count))
if task_count == 0 and priority == 13:
#如果遇到低优先级无任务，则休息5分钟以减少数据库请求的次数。此处没做低优先级没命中继续上查高优先级如果也没有再休息的功能。如果写了，效率可以更高，按照高优先级，中优先级，低优先级1:1:1的比例排任务，最高每1000次任务执行能减少10分钟的等待时间。
miss = miss * 2
#延迟器，用来降低数据库压力，每次找不到，则增加1秒的重试等待时间。当重试等待超过5分钟后，不再增加重试等待时间。以保证5分钟至少会查询一次。
if miss >= 0 and miss <= 300:
time.sleep(abs(miss))
elif miss > 300:
time.sleep(300)
print('miss记数器', miss)
elif task_count >= 1:
miss = 1
data = json.loads(task_result[0][4])
data['group_id'] = task_result[0][2]
update_scheduler_job(jobid=task_result[0][0], status=17)
py = importlib.import_module(data['func']['dir'])
ff = getattr(py, data['func']['name'])
# print(data['func']['args'])
update_scheduler_job(jobid=task_result[0][0], status=18)
for arg in data['func']['args']:
if type(data['func']['args']
[arg]) is str and '___' in data['func']['args'][arg]:
data['func']['args'][arg] = data[data['func']['args']
[arg].replace('___', '')]
# print(data['func']['args'])
data['func']['args']['job_id'] = task_result[0][0]
data['func']['args']['group_id'] = task_result[0][2]
try:
write_to_log(filename='scheduler',
defname='do_all_task',
result='优先级' + str(priority) + '任务id' +
str(task_result[0][0]) + '拼接的任务参数' + str(data) +
'开始执行')
func_result, list_index = ff(data['func']['args'])
update_scheduler_job(jobid=task_result[0][0],
list_index=list_index,
status=19)
write_to_log(filename='scheduler',
defname='do_all_task',
result='优先级' + str(priority) + '任务id' +
str(task_result[0][0]) + '拼接的任务参数' + str(data) +
'执行完毕')
except Exception:
error = traceback.format_exc()
write_to_log(filename='scheduler',
defname='do_all_task',
result=error)
update_scheduler_job(jobid=task_result[0][0], status=21)
task_count = task_count - 1
