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()
