from sqlalchemy import create_engine from urllib import parse import pandas as pd from utils.code_dict import age_dict import yaml def update_dict(x, y): # 用字典y,来更新x # key 存在则更新,不存在则新增 x.update(y) return x def get_db_engine(db_info): db_con_str = 'mysql+pymysql://%s:%s@%s:%d/%s' % \ (db_info['username'], parse.quote_plus(db_info['password']), db_info['host'], db_info['port'], db_info['database']) engine = create_engine(db_con_str, connect_args={'charset': 'utf8'}) return engine def is_contains_region(val1, val2): """ level1 region long[] 可选 地域 传值为[]表示不限;传递上一级ID时,childrenID可以不传;不允许同时传parentID和childrenID; 仅计划的campaign_type为5时,支持设置三级地域(例:山西-大同-左云,左云是三级地域) :param val1: 推荐的城市等级组合,如 "一线城市|新一线城市|二线城市|三线城市" , "不限" :param val2: 从账户配置信息表里读取的地区信息, 如 '[]', None, '[14,15,21,22,23,31,32]' :return: """ # 如果val1为空,表示该字段没有参与定向组合运算,就直接取客户投放策略里面的值 if val1 is None: return True, {'region': val2} val_split = val1.split('|') val_tuple = tuple(val_split*2) if len(val_split) == 1 else tuple(val_split) sql = """ select t1.city_name, t2.level, t2.region_id, t2.parent from ctop_kuaishou_city_level t1 left join ctop_kuaishou_region_list_parent t2 on t1.city_name = t2.name where t1.city_level in %s """ % (val_tuple,) with open('/data/pythonProject/ai_target/config/config.yaml', mode='r', encoding='utf-8') as f: config = yaml.load(f.read(), Loader=yaml.FullLoader) city_df = pd.read_sql(sql, get_db_engine(config['jeecg_product_db'])) # 如果【吉林】在city_level里面的话,在ctop_kuaishou_region_list_parent会同时把 吉林省和吉林市过滤出来 # 按level进行降序排列后,按city_name进行去重,保留低等级的值 city_df = city_df.sort_values(by='level', ascending=False) city_df.drop_duplicates(subset=['city_name'], keep='first', inplace=True) if not val2 or val2 == '不限' or val2 == '[]': return True, {'region': list(city_df['region_id'].values)} else: if val1 == '不限': return False, -1 else: val = [] # level1 if set(city_df[city_df.level == 1].region_id.values).issubset(set(eval(val2))): val.extend(city_df[city_df.level == 1].region_id.values) else: return False, -1 # level2 # 元素的父级是否存在于 val2,如果存在则为包含于的关系 # 元素本身是否存在于 val2 ,如果存在则为包含于的关系 # 以上都不满足,val1 不包含于 val2 for item in city_df[city_df.level == 2].region_id.values: parent = city_df[city_df.region_id == item].parent.values[0] if parent in eval(val2): val.append(item) elif item in eval(val2): val.append(item) else: return False, -1 # level3 for item in city_df[city_df.level == 3].region_id.values: parent = city_df[city_df.region_id == item].parent.values[0] if parent in eval(val2): val.append(item) elif item in eval(val2): val.append(item) else: return False, -1 return True, {'region': val} def is_contains_gender(val1, val2): """ :param val1: ctop_ai_kuaishou_signature_recommended_target_combine 中的 gender 字段: '男'、'女'、'不限'、None :param val2: ctop_ai_kuaishou_advertiser_strategy 中的 gender 字段: 1:女性, 2:男性,0表示不限 :return: val1 是否为 val2的子集,以及传递给快手后台的值 """ if val1 is None: # 表示该字段没有参与贝叶斯的计算, 直接返回客户投放策略里的值 return True, {'gender': val2} if val1 == '男' and val2 in (2, 0): return True, {'gender': 2} if val1 == '女' and val2 in (1, 0): return True, {'gender': 1} if val1 == '不限' and val2 == 0: return True, {'gender': 0} def is_contains_age(val1, val2_min, val2_max, val2_range): """ :param val1: 推荐的年龄等级组合,如 "31-40岁|41-49岁|50+岁" , "不限" age struct 可选 自定义年龄段 不传值表示不限,传值具体见下方表格;与ages_range不能同时传 min int 必填 年龄最小限制 年龄区间最小为18岁 max int 必填 年龄最大限制 年龄区间最大为55岁,且年龄最大限制须大于等于年龄最小限制 :param val2_min: age_min :param val2_max: age_max :param val2_range: ctop_ai_kuaishou_advertiser_strategy 中的 ages_range [] varchar 与age不能同时传;【18:表示18-23岁】;【24:表示24-30岁】;【31:表示31-40岁】;【41:表示41-49岁】;【50:表示50-100岁】 :return: """ # val2_range 为字符串类型,修改为 list 类型 if val2_range == '[]': val2_range = None if val2_range: val2_range = eval(val2_range) # 如果val1为空,表示该字段没有参与定向组合运算,就直接取客户投放策略里面的值 if val1 is None: return True, {'age_min': val2_min, 'age_max': val2_max, 'ages_range': val2_range} if val1 == '不限': if (not val2_min) and (not val2_max) and (not val2_range or val2_range == '不限'): return True, {'age_min': val2_min, 'age_max': val2_max, 'ages_range': val2_range} else: return False, -1 else: val1 = [age_dict[ele] for ele in val1.split('|')] if (not val2_min) and (not val2_max) and (not val2_range or val2_range == '不限'): return True, {'ages_range': val1} if (val2_min is None and val2_max is None) and val2_range: if set(val1).issubset(set(val2_range)): return True, {'ages_range': val1} else: return False, -1 if (val2_min and val2_max) and (not val2_range): if val1[0] >= val2_min and val1[-1] <= val2_max: return True, {'age_min': val1[0], 'age_max': val1[-1]} else: return False, -1 def age_code_transform(val): if (val is None) or (val == '不限'): ages_range = None else: ages_range = [age_dict[item] for item in val.split('|')] return ages_range def gender_code_transform(val): if (val is None) or (val == '不限'): gender = 0 elif val == '女': gender = 1 else: gender = 2 return gender def city_code_transform(val): if (val is None) or (val == '不限'): region = None else: sql = """select t1.city_name, t2.level, t2.region_id, t2.parent from ctop_kuaishou_city_level t1 left join ctop_kuaishou_region_list_parent t2 on t1.city_name = t2.name where t1.city_level in %s """ % (tuple(val.split('|')),) with open('/data/pythonProject/ai_target/config/config.yaml', mode='r', encoding='utf-8') as f: config = yaml.load(f.read(), Loader=yaml.FullLoader) city_df = pd.read_sql(sql, get_db_engine(config['jeecg_product_db'])) # 如果【吉林】在city_level里面的话,在ctop_kuaishou_region_list_parent会同时把 吉林省和吉林市查询出来 # 按level进行降序排列后,按city_name进行去重,保留高等级的值 city_df = city_df.sort_values(by='level', ascending=False) city_df.drop_duplicates(subset=['city_name'], keep='first', inplace=True) region = city_df.region_id.values return region