def rate_analyze(id, comment_shopid, date, comment_content): ''' 对评论数据进行分词识别出相应的类别,然后写入数据库中 :param id: 评论ID :param date: 当前日期 :param comment_content:评论的内容 :return: ''' time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') key_vaule = get_config() db2 = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'big_data', 'utf8mb4')) sql2 = "insert into comment_flag VALUES" seg_list = jieba.cut(comment_content) #利用jieba分词,取出句子中所有的分词内容 print("评论内容:", comment_content) flag = 0 #标志位,0:分词中没有差评信息 1:分词中存在差评信息 keywords = ' ' for item in seg_list: #对一条评论的分词结果进行匹配 for j in range(len(key_vaule)): #在词库中进行配对 if key_vaule.iloc[j, 0] == item: print("有差评内容:", item) keywords = item comment_class = int(key_vaule.iloc[j, 1]) #将评论的标签置为相应的值 flag = 1 break if flag == 0: keywords = r' ' comment_class = 0 #没有差评的时候标签置为0 print("评论ID:" + str(id), "所属分类:" + str(comment_class)) batch = BatchSql(sql2) #写入数据库 batch.addBatch( [0, id, comment_shopid, date, comment_class, keywords, time, None]) db2.update(batch)
def __init__(self, city_short, date_short): ''' :param city_short: 城市简写 :param date_short: 保存日期简写 ''' self.city_short = city_short self.date_short = date_short self.db = DBUtils(('192.168.1.200', 3306, 'njjs_test', 'njjs1234', 'datatest', 'utf8mb4'))
def get_rating_info(date): ''' 获取当天的评论,一次性从数据库中读取出来,不断产生评论数据 :param date: :return: 满足要求的评论数据 ''' db = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'compass_prod', 'utf8mb4')) sql = "select t1.id,t1.shop_id,t1.rate_content from order_comment t1 where t1.created_at like %s and t1.rate_content != ''" date = date + '%' result = db.queryForList(sql, [date]) return result
def parse(save_path, city, rest_area, date): db = DBUtils( ('192.168.1.200', 3306, 'njjs', 'njjs1234', 'areadata', 'utf8mb4')) #店铺品类信息入库 category_path = os.path.join(save_path, 'category.pickle') categoryParse = ParseCategoryObject.ParseCategoryObject( category_path, city, date, rest_area, db) categoryParse.parse() err_rest_ids = categoryParse.err_rest_ids print(len(err_rest_ids)) #热搜数据入库 hotWordPath = os.path.join(save_path, 'hot_word.pickle') hotWordParse = ParseHotWordObject.ParseHotWordObject( hotWordPath, city, date, rest_area, err_rest_ids, db) hotWordParse.parse() #菜品数据入库 menuPath = os.path.join(save_path, 'menu.pickle') menuParse = ParseMenuObject.ParseMenuObject(menuPath, city, date, rest_area, err_rest_ids, db) menuParse.parse() #评论标签数据入库 ratingTagPath = os.path.join(save_path, 'rating_tag.pickle') ratingTagParse = ParseRatingTagObject.ParseRatingTagObject( ratingTagPath, city, date, rest_area, err_rest_ids, db) ratingTagParse.parse() #店铺评分数据入库评论 scorePath = os.path.join(save_path, 'score.pickle') scoreParse = ParseScoreObject.ParseScoreObject(scorePath, city, date, rest_area, err_rest_ids, db) scoreParse.parse() # 店铺数据入库 # ratingPath = os.path.join(save_path, 'rating.pickle') # ratingParse = ParseRatingObject.ParseRatingObject(ratingPath, city, date, rest_area, db) # ratingParse.parse() # 店铺配送数据入库 restInfoPath = os.path.join(save_path, 'rest_info.pickle') ratingParse = ParseDeliveryMode.ParseDeliveryMode(restInfoPath, city, date, rest_area, db) ratingParse.parse() print('解析数据结束') print('调用存储过程,把import表中数据导入到业务表中') db.callProcedure('deal_import_table')
def getForRestIds(self): config = ('192.168.0.200', 3306, 'njjs', 'njjs1234', 'areadata', 'utf8mb4') db = DBUtils(config) date = getTodayLater(1) sql = "select DISTINCT t1.rest_id from t_e_rest_list_area t1 where t1.date = '%s' and t1.city = '%s' and t1.rest_area='%s'" % ( date, self.city, self.rest_area) data = db.queryForListBylimit(sql, 0, 10000) i = 1 while data: for item in data: yield item[0] data = db.queryForListBylimit(sql, i * 10000, 10000)
def run(): db = DBUtils( ('192.168.1.200', 3306, 'njjs', 'njjs1234', 'areadata', 'utf8mb4')) p = ParseDeliveryMode( "D:\\crawl_data\\店圈监控\\饿了么\\南京\\谢恒兴\\2017-11-29\\rest_info.pickle", '南京', '2017-11-29', '谢恒兴', db) p.parse()
def __init__(self, commentSummary, businessStatistics, TrafficStats, customerAnalysis, customerPerfer, effectiveOrders, todaystatistics, activitylist, customer_reminderInfo, customer_reminder, history_consume, PeersCompareAnalysis, getCouponLabel, shopid): self.commentSummary = commentSummary self.businessStatistics = businessStatistics self.TrafficStats = TrafficStats self.customerAnalysis = customerAnalysis self.customerPerfer = customerPerfer self.effectiveOrders = effectiveOrders self.todaystatistics = todaystatistics self.activitylist = activitylist self.customer_reminderInfo = customer_reminderInfo self.customer_reminder = customer_reminder self.history_consume = history_consume self.PeersCompareAnalysis = PeersCompareAnalysis self.getCouponLabel = getCouponLabel self.shopid = shopid self.db = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'big_data', 'utf8mb4')) self.db2 = DBUtils(('192.168.1.200', 3306, 'njjs_zsz', 'njjs1234', 'zszdata', 'utf8mb4')) self.db3 = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'wmds', 'utf8mb4')) self.date = datetime.datetime.now().strftime('%Y-%m-%d') self.yesterday = datetime.date.today() - datetime.timedelta(days=1) self.time = datetime.datetime.now().strftime('%H:%M:%S') self.platform = 2 #平台标签 self.shopname = self.get_shopname(self.shopid) self.WMDS_shopid = self.get_WMDS_shopid()
def Parser(): ''' 数据入库操作 :return: ''' db = DBUtils(('192.168.1.200', 3306, 'njjs_test', 'njjs1234', 'datatest', 'utf8mb4')) result = get_toDB_config(config="config.ini") print(result) date_flag = result[0][5] #记录保存日期简写 for city_path, city_py, city_short, city_cn, save_date, save_date_short in result: print("创建各城市的菜品类数据库>>>") create_menu = sql_util(city_short, save_date_short) create_menu.create_menu_table() parsecategory = ParseCategory(city_path, city_py, city_cn, save_date, save_date_short, db) err_rest_ids = parsecategory.run() parsemenu = ParseMenu(city_path, city_py, save_date, city_short, save_date_short, err_rest_ids, db) parsemenu.run() parseratingtag = ParseRating(city_path, city_py, save_date, save_date_short, err_rest_ids, db) parseratingtag.run() parsescore = ParseScore(city_path, city_py, save_date, save_date_short, err_rest_ids, db) parsescore.run() parsehotword = ParseHotword(city_path, city_py, save_date, save_date_short, err_rest_ids, db) parsehotword.run() parsedeliverymode = ParseDeliveymode(city_path, city_py, save_date, save_date_short, db) parsedeliverymode.run() print("最后关闭JVM") jpype.shutdownJVM()
def UpdateRestList(date_short): db = DBUtils(('192.168.1.200', 3306, 'njjs_test', 'njjs1234', 'datatest', 'utf8mb4')) update_sql = """ CREATE TABLE t_e_rest_list_city_%s as SELECT a.city, a.date, a.rest_id, a.rest_name, a.phone, a.address, a.avg_cost, b.delivery_id, a.delivery_fee, a.min_delivery_price, a.is_new, a.is_premium, a.latitude, a.longitude, a.order_month_sales, a.area_id FROM t_e_rest_list_city_pre_%s a LEFT JOIN t_e_delivery_mode_city_pre_%s b ON a.city = b.city AND a.date = b.date AND a.rest_id = b.rest_id; """ % (date_short, date_short, date_short) del_sql = """ DROP TABLE t_e_rest_list_city_pre_%s; DROP TABLE t_e_delivery_mode_city_pre_%s; """ % (date_short, date_short) print("更新CityRestList表>>>", update_sql) db.deal_sql(update_sql) time.sleep(1) print("删除两张旧表>>>", del_sql) db.deal_sql(del_sql)
def run(): db = DBUtils() p = ParseScoreObject( "F:\\crawler_data\\数据监控\\饿了么\\谢恒兴监控\\2017-10-28\\score.pickle", '南京', '2017-10-28', '谢恒兴', db) p.parse()
# @Time: 2018/1/29 下午2:09 ''' 处理外卖大师web端的店圈监控的数据,分别获取该店圈的1.5,3,5公里范围的所有店铺数据 ''' from util.DB.DAO import DBUtils,BatchSql import datetime import json ''' 数据库信息,数据库1:web端用来保存外卖大师的数据 数据库2:查询城市数据 ''' db1 = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'wmds', 'utf8mb4')) db2 = DBUtils(('192.168.1.200', 3306, 'njjs', 'njjs1234', 'exdata_2018', 'utf8mb4')) time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') ''' 获取配置信息:店铺名称,店铺id,店铺经纬度,店圈范围半径类型 ''' area_shop_name = ['谢恒兴奇味鸡煲(义乌店)','谢恒兴奇味鸡煲(河西万达店)','谢恒兴奇味鸡煲(同曦鸣城店)','谢恒兴奇味鸡煲(殷巷店)','谢恒兴奇味鸡煲(明发广场店)','谢恒兴奇味鸡煲(小市店)','谢恒兴奇味鸡煲(油坊桥店)','谢恒兴奇味鸡煲(元通店)','奇味鸡煲(四方新村店)'] area_shop_id = [160279990,161289358,161378073,161313783,161341608,156753255,4166456,161507150,161506911] shop_latitude = [31.937813,32.03445,31.9427300,31.9099300,31.9776340,32.0933240,31.9664130,31.992613,32.0185100] shop_longitude = [118.8760630,118.74473,118.8231500,118.8357800,118.7976980,118.7895010,118.7213890,118.709933,118.8420300] type = [1500,3000,5000] def deal_quan_position():
def run(): db = DBUtils() p = ParseHotWordObject("F:\\crawler_data\\数据监控\\饿了么\\谢恒兴监控\\2017-10-28\\hot_word.pickle", '南京', '2017-10-28', '谢恒兴', db) p.parse()
from SeleniumBackstage.meituan_web.Clientlogin import Clientlogin from apscheduler.schedulers.blocking import BlockingScheduler import time import datetime import traceback from util.DB.DAO import DBUtils, BatchSql db = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'big_data', 'utf8mb4')) sql = "insert into meituan_backstage VALUES " sql2 = "insert into rating_score VALUES" sql3 = "insert into daily_activity_data VALUES" def crawler(client, account, password): """ 爬取商家后台的数据 :param client: 浏览器相应操作 :param account: 用户账号 :param password:用户密码 :return: """ print("开始爬取商家:", account) #分别保存每组爬取的数据 result = [] result1 = [] result1_1 = [] result2 = [] coupon_name = [] coupon_content = [] rating_data = []
class PaerseMeituanbackstage(): ''' 解析美团商家后台数据,并写入相应的数据库 ''' def __init__(self, commentSummary, businessStatistics, TrafficStats, customerAnalysis, customerPerfer, effectiveOrders, todaystatistics, activitylist, customer_reminderInfo, customer_reminder, history_consume, PeersCompareAnalysis, getCouponLabel, shopid): self.commentSummary = commentSummary self.businessStatistics = businessStatistics self.TrafficStats = TrafficStats self.customerAnalysis = customerAnalysis self.customerPerfer = customerPerfer self.effectiveOrders = effectiveOrders self.todaystatistics = todaystatistics self.activitylist = activitylist self.customer_reminderInfo = customer_reminderInfo self.customer_reminder = customer_reminder self.history_consume = history_consume self.PeersCompareAnalysis = PeersCompareAnalysis self.getCouponLabel = getCouponLabel self.shopid = shopid self.db = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'big_data', 'utf8mb4')) self.db2 = DBUtils(('192.168.1.200', 3306, 'njjs_zsz', 'njjs1234', 'zszdata', 'utf8mb4')) self.db3 = DBUtils(('116.62.70.68', 3306, 'bigdata', 'gisfood20171220@nj', 'wmds', 'utf8mb4')) self.date = datetime.datetime.now().strftime('%Y-%m-%d') self.yesterday = datetime.date.today() - datetime.timedelta(days=1) self.time = datetime.datetime.now().strftime('%H:%M:%S') self.platform = 2 #平台标签 self.shopname = self.get_shopname(self.shopid) self.WMDS_shopid = self.get_WMDS_shopid() def get_WMDS_shopid(self): if self.shopid == '160279990' or self.shopid == 'wmxhxq1392': WMDS_shopid = 10 return WMDS_shopid if self.shopid == '161289358' or self.shopid == 'wmxhxq168673': WMDS_shopid = 12 return WMDS_shopid if self.shopid == '161378073': WMDS_shopid = 13 return WMDS_shopid if self.shopid == '161313783': WMDS_shopid = 14 return WMDS_shopid if self.shopid == '161341608' or self.shopid == 'wmxhxq1791': WMDS_shopid = 15 return WMDS_shopid if self.shopid == '156753255': WMDS_shopid = 16 return WMDS_shopid if self.shopid == 'wmxhxq18423': WMDS_shopid = 18 return WMDS_shopid if self.shopid == 'wmxhxq19610': WMDS_shopid = 17 return WMDS_shopid if self.shopid == '161506911': WMDS_shopid = 19 return WMDS_shopid def get_shopname(self, shopid): ''' 获取店铺id对应的名称 :param shopid:店铺id :return: ''' if shopid == 'wmxhxq1392': shopname = '谢恒兴奇味鸡煲(义乌店)' return shopname if shopid == 'wmxhxq168673': shopname = '谢恒兴奇味鸡煲(河西万达店))' return shopname if shopid == 'wmxhxq1791': shopname = '谢恒兴奇味鸡煲(明发广场店)' return shopname if shopid == 'wmxhxq18423': shopname = '谢恒兴奇味鸡煲(元通店)' return shopname if shopid == 'wmxhxq19610': shopname = '谢恒兴奇味鸡煲(油坊桥店)' return shopname if shopid == 'wmxhxq20715': shopname = '谢恒兴奇味鸡煲(四方新村店)' return shopname def WMDS_meituan_data(self): ''' 处理美团外卖大师的数据 :return: ''' WMDS_meituan_data = [] m_bizScore = self.commentSummary['data']['bizScore'] m_exposureNum = self.TrafficStats['data']['flowGeneralInfoVo'][ 'exposureNum'] m_visitNum = self.TrafficStats['data']['flowGeneralInfoVo']['visitNum'] m_orderNum = self.TrafficStats['data']['flowGeneralInfoVo']['orderNum'] m_visitRate = self.TrafficStats['data']['flowGeneralInfoVo'][ 'visitRate'] m_orderRate = self.TrafficStats['data']['flowGeneralInfoVo'][ 'orderRate'] WMDS_meituan_data.extend([ self.WMDS_shopid, m_bizScore, m_exposureNum, m_visitNum, m_orderNum, m_visitRate, m_orderRate ]) print('美团外卖大师的数据:', WMDS_meituan_data) return WMDS_meituan_data ''' 处理web端的商家后台数据,每天晚上8-9点运行,四张表的数据 ''' def deal_commentSummary(self): ''' 处理顾客评价的信息:店铺ID、时间、平台、评分、评分日比 :return: ''' commentSummary_content = [] bizScore = self.commentSummary['data']['bizScore'] commentSummary_content.extend( [self.shopid, self.date, self.platform, bizScore]) print('店铺评分信息:', commentSummary_content) sql = "insert into rating_score VALUES " batch = BatchSql(sql) batch.addBatch(commentSummary_content) self.db.update(batch) def deal_TrafficStats(self): ''' 处理数据流量分析的信息:店铺id、时间、曝光人数,访问人数,下单人数,访问转化率,下单转化率 :return: ''' TrafficStats = [] exposureNum = self.TrafficStats['data']['flowGeneralInfoVo'][ 'exposureNum'] visitNum = self.TrafficStats['data']['flowGeneralInfoVo']['visitNum'] orderNum = self.TrafficStats['data']['flowGeneralInfoVo']['orderNum'] visitRate = self.TrafficStats['data']['flowGeneralInfoVo']['visitRate'] orderRate = self.TrafficStats['data']['flowGeneralInfoVo']['orderRate'] TrafficStats.extend([ self.shopid, self.date, exposureNum, visitNum, orderNum, visitRate, orderRate ]) print('流量分析信息:', TrafficStats) sql = "insert into meituan_backstage VALUES " batch = BatchSql(sql) batch.addBatch(TrafficStats) self.db.update(batch) def deal_Activity_type(self, type): ''' 处理活动信息的类型问题 :param type: :return: ''' if type == 17: type = '折' return type if type == 100: type = '返' return type if type == 103: type = '领' return type if type == 2: type = '减' return type if type == 1: type = '首' return type if type == 22: type = '新' return type else: type = '惠' return type def deal_Activitylist(self): ''' 处理店铺活动的信息:id,店铺id,活动id,时间,平台名称,活动类型,活动名称,活动内容 :return: ''' Activity_onGoingActs = self.activitylist['data']['onGoingActs'] for item in Activity_onGoingActs: Activitylist = [] actId = item['actId'] actName = item['actName'] poiPolicy = item['poiPolicy'] type = item['type'] Activity_type = self.deal_Activity_type(type) Activitylist.extend([ 0, self.shopid, actId, self.date, 2, Activity_type, actName, poiPolicy ]) print('店铺活动信息:', Activitylist) sql = "insert into daily_shop_activity_data VALUES" batch = BatchSql(sql) batch.addBatch(Activitylist) self.db3.update(batch) def deal_CustomerReminder(self): ''' 处理店铺催单信息:id,店铺id,日期,时间,平台名称,催单次数,催单时间 :return: ''' CustomerReminder_Info = self.customer_reminderInfo['wmOrderList'] CustomerReminder_Data = self.customer_reminder['data'] if CustomerReminder_Info != None: for item in CustomerReminder_Info: #获取另一个催单账号信息借口获取催单的ID CustomerReminder_ID = item['wm_order_id_view'] try: for item in CustomerReminder_Data[str( CustomerReminder_ID)]: #根据催单ID获取相应的催单的时间信息 times = len(CustomerReminder_Data[str( CustomerReminder_ID)]) #催单的次数 CustomerReminder = [] reminder_time_fmt = item['reminder_time_fmt'] response_time_fmt = item['response_time_fmt'] reminder_time_fmt = str(reminder_time_fmt).split( ':')[1] response_time_fmt = str(response_time_fmt).split( ':')[1] reminder_time = int(response_time_fmt) - int( reminder_time_fmt) #商家后台恢复催单信息的时长 CustomerReminder.extend([ 0, self.shopid, self.date, self.time, CustomerReminder_ID, 2, times, reminder_time ]) print('客户催单信息:', CustomerReminder) sql = "insert into daily_customer_reminder VALUES" batch = BatchSql(sql) batch.addBatch(CustomerReminder) self.db.update(batch) except Exception: pass # print('催单信息有问题!') ''' 运营日报的商家后台数据 ''' def deal_meituan_daily_operational_data(self): ''' 运营日报数据入库:id,店铺id,店铺名称,日期, 日有效订单量,订单收入, 曝光量,进店量,下单量,进店转化率,下单转化绿 新客人数,新客占比,旧客人数,旧客占比 推广资金,(推广点击次数,单词点击费用) :return: ''' meituan_daily_operational_data = [] effectiveOrders = self.effectiveOrders['data']['effectiveOrders'] settleAcc = self.effectiveOrders['data']['settleAcc'] exposureNum = self.TrafficStats['data']['flowGeneralInfoVo'][ 'exposureNum'] visitNum = self.TrafficStats['data']['flowGeneralInfoVo']['visitNum'] xiadanNum = self.TrafficStats['data']['flowGeneralInfoVo']['orderNum'] visitRate = self.TrafficStats['data']['flowGeneralInfoVo']['visitRate'] orderRate = self.TrafficStats['data']['flowGeneralInfoVo']['orderRate'] newOrderNum = self.customerAnalysis['data']['newOrderNum'] oldOrderNum = self.customerAnalysis['data']['oldOrderNum'] orderNum = self.customerAnalysis['data']['orderNum'] if orderNum == 0: newOrderNum_percent = 0 oldOrderNum_percent = 0 else: newOrderNum_percent = newOrderNum / orderNum oldOrderNum_percent = oldOrderNum / orderNum history_consume_list = self.history_consume['data']['flowVoList'] popularize_amount = 0 for item in history_consume_list: history_consume_list_date = str(item['date']).split(' ')[0] history_consume_list_reason = item['reason'] if history_consume_list_date == str( self.yesterday) and history_consume_list_reason == '推广消费': popularize_amount += abs(item['amount']) # print(popularize_amount) meituan_daily_operational_data.extend([ 0, self.shopid, self.shopname, str(self.yesterday), effectiveOrders, settleAcc, exposureNum, visitNum, xiadanNum, visitRate, orderRate, newOrderNum, newOrderNum_percent, oldOrderNum, oldOrderNum_percent, popularize_amount ]) print("美团运营日报数据:", meituan_daily_operational_data) sql = "insert into meituan_daily_operational_data VALUES" batch = BatchSql(sql) batch.addBatch(meituan_daily_operational_data) self.db2.update(batch) ''' 商家后台数据分析 ''' def deal_meituan_Operational_data_analysis(self): ''' 处理商家后台数据分析的数据:id,店铺id,店铺名称,日期, 有效订单,订单收入, 有效订单打败同行,订单收入打败同行, 下单人数,新客人数,老客人数, 曝光人数,访问人数,下单人数,访问转化率,下单转化率, 商家评分,口味评分,包装评分,配送评分, :return: ''' meituan_Operational_data_analysis = [] effectiveOrders = self.effectiveOrders['data']['effectiveOrders'] settleAcc = self.effectiveOrders['data']['settleAcc'] effectiveOrdersRankPercent = self.PeersCompareAnalysis['data'][ 'effectiveOrdersRankPercent'] settleAccRankPercent = self.PeersCompareAnalysis['data'][ 'settleAccRankPercent'] newOrderNum = self.customerAnalysis['data']['newOrderNum'] oldOrderNum = self.customerAnalysis['data']['oldOrderNum'] orderNum = self.customerAnalysis['data']['orderNum'] exposureNum = self.TrafficStats['data']['flowGeneralInfoVo'][ 'exposureNum'] visitNum = self.TrafficStats['data']['flowGeneralInfoVo']['visitNum'] xiadanNum = self.TrafficStats['data']['flowGeneralInfoVo']['orderNum'] visitRate = self.TrafficStats['data']['flowGeneralInfoVo']['visitRate'] orderRate = self.TrafficStats['data']['flowGeneralInfoVo']['orderRate'] bizScore = self.commentSummary['data']['bizScore'] tasteScore = self.commentSummary['data']['tasteScore'] packScore = self.commentSummary['data']['packScore'] deliveryScore = self.commentSummary['data']['deliveryScore'] meituan_Operational_data_analysis.extend([ 0, self.shopid, self.shopname, str(self.yesterday), effectiveOrders, settleAcc, effectiveOrdersRankPercent, settleAccRankPercent, orderNum, newOrderNum, oldOrderNum, exposureNum, visitNum, xiadanNum, visitRate, orderRate, bizScore, tasteScore, packScore, deliveryScore ]) print('商家后台数据分析:', meituan_Operational_data_analysis) sql = "insert into meituan_Operational_data_analysis VALUES" batch = BatchSql(sql) batch.addBatch(meituan_Operational_data_analysis) self.db2.update(batch) def deal_meituan_customer_prefer_analysis(self): ''' 处理同行顾客爱好:id,店铺id,店铺名称,日期 同行顾客爱好的菜品名,该菜品的同行百分比 :return: ''' customer_prefer_analysi = self.customerPerfer['data']['pubCusEat'] for item in customer_prefer_analysi: meituan_customer_prefer_analysi = [] customer_prefer_name = item[0] customer_prefer_percent = item[1] meituan_customer_prefer_analysi.extend([ 0, self.shopid, self.shopname, str(self.yesterday), customer_prefer_name, float(customer_prefer_percent) ]) print('同行顾客爱好:', meituan_customer_prefer_analysi) sql = "insert into meituan_customer_prefer_analysis VALUES" batch = BatchSql(sql) batch.addBatch(meituan_customer_prefer_analysi) self.db2.update(batch) def deal_customer_prefer_activity_type(self, pic): ''' 处理顾客喜欢的活动的类型 :param pic: :return: ''' if pic == 'http://p1.meituan.net/xianfu/9c997ecce6150671b8459738a26f8bd9767.png': type = '折' return type if pic == 'http://p0.meituan.net/xianfu/652eea4034250563fe11b02e3219ba8d981.png': type = '返' return type if pic == 'http://p0.meituan.net/coupon/1ae419cdb421cfdeb3575fb90e1cc340862.png@!style1': type = '卷' return type if pic == 'http://p0.meituan.net/xianfu/f8bc8dffdbc805878aa3801a33f563cd1001.png': type = '减' return type if pic == 'http://p1.meituan.net/xianfu/5ffe01c550a139db693d152cefd1b247869.png': type = '赠' return type else: type = '活动' return type def deal_meituan_customer_prefer_activity(self): ''' 处理同行顾客喜欢的活动:id,店铺id,店铺名称,日期, 同行顾客喜欢的活动,活动类型 :return: ''' customer_prefer_activity = self.customerPerfer['data']['pubCusAct'] for item in customer_prefer_activity: customer_prefer_activity = [] customer_prefer_activity_name = item[0] customer_prefer_activity_pic = item[1] customer_prefer_activity_type = self.deal_customer_prefer_activity_type( customer_prefer_activity_pic) customer_prefer_activity.extend([ 0, self.shopid, self.shopname, str(self.yesterday), customer_prefer_activity_name, customer_prefer_activity_type ]) print("顾客喜欢的活动:", customer_prefer_activity) sql = "insert into meituan_customer_prefer_activity VALUES" batch = BatchSql(sql) batch.addBatch(customer_prefer_activity) self.db2.update(batch) def deal_meituan_customer_precision_operation(self): ''' 处理精准营销的数据:id,店铺id,店铺名称,日期, 营销名称,营销活动人数 :return: ''' customer_precision_labelList = self.getCouponLabel['data']['labelList'] for item in customer_precision_labelList: customer_precision_operation = [] precision_operation_name = item['title'] precision_operation_numbers = item['nums'] customer_precision_operation.extend([ 0, self.shopid, self.shopname, str(self.yesterday), precision_operation_name, precision_operation_numbers ]) print('精准营销的活动信息:', customer_precision_operation) sql = "insert into meituan_customer_precision_operation VALUES" batch = BatchSql(sql) batch.addBatch(customer_precision_operation) self.db2.update(batch)
class sql_util: ''' SQL创建语句,用来生成月度数据表 ''' def __init__(self, city_short, date_short): ''' :param city_short: 城市简写 :param date_short: 保存日期简写 ''' self.city_short = city_short self.date_short = date_short self.db = DBUtils(('192.168.1.200', 3306, 'njjs_test', 'njjs1234', 'datatest', 'utf8mb4')) def create_menu_table(self): ''' 创建创建menu类的数据表 :return: ''' date_short = str(self.date_short) #合并城市简写与日期简写 例子:nj_1711 tag = [] tag.append(self.city_short) tag.append(date_short) tag = "_".join(tag) tag = str(tag) create_menu_sql = """ CREATE TABLE t_e_rest_menu_level2_city_""" + tag + """ SELECT * FROM t_e_rest_menu_level2_import WHERE 1=2; CREATE TABLE t_e_rest_menu_level2_unique_city_""" + tag + """ SELECT * FROM t_e_rest_menu_level2_unique_import WHERE 1=2; """ print(create_menu_sql) self.db.deal_sql(create_menu_sql) #执行单SQl长语句 def create_table(self): ''' 创建月度常规数据表 eg:_1711 :return: ''' date_short = str(self.date_short) #每个月创建11张新表用来存储常规数据 sql = """ CREATE TABLE t_e_rest_list_city_pre_""" + date_short + """ SELECT * FROM t_e_rest_list_import WHERE 1=2; CREATE TABLE t_e_hot_search_word_city_""" + date_short + """ SELECT * FROM t_e_hot_search_word_import WHERE 1=2; CREATE TABLE t_e_rest_active_city_""" + date_short + """ SELECT * FROM t_e_rest_active_import WHERE 1=2; CREATE TABLE t_e_rest_category_city_""" + date_short + """ SELECT * FROM t_e_rest_category_import WHERE 1=2; CREATE TABLE t_e_rest_menu_discount_city_""" + date_short + """ SELECT * FROM t_e_rest_menu_discount_import WHERE 1=2; CREATE TABLE t_e_rest_menu_level1_city_""" + date_short + """ SELECT * FROM t_e_rest_menu_level1_import WHERE 1=2; CREATE TABLE t_e_rest_money_off_avg_city_""" + date_short + """ SELECT * FROM t_e_rest_money_off_avg_import WHERE 1=2; CREATE TABLE t_e_rest_money_off_city_""" + date_short + """ SELECT * FROM t_e_rest_money_off_import WHERE 1=2; CREATE TABLE t_e_rest_open_time_city_""" + date_short + """ SELECT * FROM t_e_rest_open_time_import WHERE 1=2; CREATE TABLE t_e_rest_rating_tag_city_""" + date_short + """ SELECT * FROM t_e_rest_rating_tag_import WHERE 1=2; CREATE TABLE t_e_rest_score_city_""" + date_short + """ SELECT * FROM t_e_rest_score_import WHERE 1=2; CREATE TABLE t_e_delivery_mode_city_pre_""" + date_short + """ SELECT * FROM t_e_delivery_mode_import WHERE 1=2; """ print(sql) self.db.deal_sql(sql)
#!/usr/bin/env python # -*- coding:utf-8 -*- # Author: Ryan # @Time: 2018/1/26 下午4:32 ''' 营收算法1.0,按照权重来算满减金额,获取商家一个月的预计营收,效果不太好,可能时数据太少 ''' from util.DB.DAO import DBUtils, BatchSql db = DBUtils( ('192.168.1.200', 3306, 'njjs', 'njjs1234', 'exdata_2018', 'utf8mb4')) def get_shop_money_off(shopid): ''' 获取该商家的满减信息 :return: ''' sql = ''' SELECT t1.rest_id,t1.full_price price,t1.sub_price FROM t_e_rest_money_off_city_1801 t1 WHERE t1.rest_id = %s ORDER BY price DESC ''' % (shopid) shop_money_off = db.queryForList(sql, None) shop_money_off_info = dict() for item in shop_money_off: shop_money_off_info[item[1]] = item[2] return shop_money_off_info
def run(): db = DBUtils(('192.168.0.200', 3306, 'njjs', 'njjs1234', 'areadata', 'utf8mb4')) p = ParseRatingObject("C:\\Users\\Administrator\\Desktop\\test\\rating.pickle", '南京', '2017-11-10', '谢恒兴', db) p.parse()