示例#1
0
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
示例#2
0
def upload_to_mysql(data, cols):

    mysql = MySQL()

    mysql.batch_insert('nsh_sanhuangua', cols, data)