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