def sanhuangua_join_profile(data, ds):
mysql = MySQL()
# delete
delete_sql = 'delete from nsh_sanhuangua_tmp'
mysql.execute(delete_sql)
# insert
mysql.batch_insert('nsh_sanhuangua_tmp', ['role_id', 'suspect_score'], data)
PROFILE_SQL = """
select a.suspect_score, b.*
from anti_plugin.nsh_sanhuangua_tmp a
join luoge_nsh_mid.mid_role_portrait_all_d b on a.role_id = b.role_id
where b.ds = '{ds}'
"""
sql = PROFILE_SQL.format(ds=ds)
logging.info(sql)
params = {
'sql': sql,
'needReturn': 'true'
}
# 关联画像请求，返回结果
result = requests.post(SHUYUAN_URL, timeout=6000, json=params)
# json转成字典
id_profile_dict = {}
for line in result.json()['data']:
profile_dict = {}
for k, v in line.items():
k = k.split('.')[-1]
v = '0' if v is None else v
profile_dict[k] = v
profiles = [profile_dict.get(col, '') for col in PROFILE_COLS]
role_id = profile_dict['role_id']
id_profile_dict[role_id] = profiles
# 结果字典
profile_data = list()
for role_id, profiles in id_profile_dict.items():
row = [role_id] + profiles
profile_data.append(row)
return profile_data
def upload_to_mysql(data, cols):
mysql = MySQL()
mysql.batch_insert('nsh_sanhuangua', cols, data)