import pandas as pd
import pymysql
import json

def con_sql(db,sql):
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        df = pd.DataFrame(list(result))
    except Exception:
        print("发生异常", Exception)
        df = pd.DataFrame()
    finally:
        db.close()
    return df

def json_format(x):
    a = json.loads(x)
    return set([x["appName"] for x in a])

def sort_app():
    db = pymysql.connect(host='10.66.157.22', port=4000, user='root', passwd='3SYz54LS9#^9sBvC', db='jerry_prod')
    sql = "select device_id,app_list from device_id_applist limit 6"
    df = con_sql(db, sql)
    a = df[1].values.tolist()
    print(type(a[0]))
    df[1]=df[1].apply(json_format)
    dianshang = {"美团","京东","淘宝"}
    df[2] = df[1].apply(lambda x:1 if len(x&dianshang)>0 else 0)
    print(df[2].unique())

if __name__ == "__main__":
    sort_app()
