python_files/cppc_python脚本/服务器脚本/demo_tool_for_goods.py

# -*- codeing = utf-8 -*-
# @Time : 2023/12/5 16:18
# @Author : Clown
# @File : demo_tool_for_goods.py
# @Software : PyCharm
import pymysql
from datetime import timedelta, datetime
from dateutil.parser import parse
import time
import os
import pandas as pd
import json
import hashlib
import traceback

#连接数据库
def linkTomySql(host,port,passwd,db_name):
    '''连接至数据库返回【db】,v2新增local_infile=1 打开文件导入权限'''
    try:
        #本地连接为：localhost  服务器连接为：124.222.188.59
        db = pymysql.connect(
                        host=host, user="root",
                        passwd=passwd,
                        db=db_name,
                        charset='utf8mb4',
                        local_infile=1,
                        port=port)
        print('\nconnect to mysql server 成功')
        print('------------------------------------')

    except:
        print("\ncould not connect to mysql server")
        db = "连接失败"
    return db

def selectDataByYearMonth(db,ym_list,out_excel_path):
    df_out = pd.DataFrame(columns = ['年月','品牌','省份','城市','菜单','销量'])
    for ym in ym_list:
        s = time.time()
        cursor = db.cursor()
        sql = f'''SELECT DATE_FORMAT(STR_TO_DATE(`日期`, '%Y%m%d'), '%Y%m') AS `年月`,`品牌`,`省`,`城市`,`类别` AS `菜单`,SUM(销量) AS `销量` FROM daily_sales_data_2rd WHERE 日期 LIKE '{ym}%' GROUP BY DATE_FORMAT(STR_TO_DATE(`日期`, '%Y%m%d'), '%Y%m'),`品牌`,`省`,`城市`,`类别` ORDER BY `销量` DESC;'''
        cursor.execute(sql)
        results = cursor.fetchall()
        value_out_list = pd.DataFrame(results,columns = ['年月','品牌','省份','城市','菜单','销量'])
        cursor.close()
        df_out = pd.concat([df_out, value_out_list])
        print(f'{ym} done t:{int(time.time()-s)}s')
    df_out.to_excel(out_excel_path, index = False)

def selectDataByKeyWords(db,kw_list,out_excel_path):
    df_out = pd.DataFrame(columns = ['日期', '门店id', '关键词', '销量', '省', '城市', '门店名称'])
    for kw in kw_list:
        s = time.time()
        cursor = db.cursor()
        sql = f'''SELECT t2.*,t1.省,t1.城市,t1.门店名称 FROM
                    (SELECT * FROM temp_daily_shops_goods_sales_map_kw_{kw} WHERE dateNo >= '2023-11-01' AND dateNo <= '2023-11-30')t2
                    LEFT JOIN
                    (select  平台, 门店名称, 客户id, 门店id+0 AS 门店id, 省, 城市 from shop_daily_operation_data_app_all where `有效单量` > 0  and `日期` >= '2023-01-01' 
                        AND `日期` <= '2023-11-30' group by  客户id, 门店id, 省, 城市, 平台, 门店名称)t1 
                    ON t2.shopId = t1.门店id;'''
        cursor.execute(sql)
        results = cursor.fetchall()
        value_out_list = pd.DataFrame(results, columns = ['日期', '门店id', '关键词', '销量', '省', '城市', '门店名称'])
        cursor.close()
        df_out = pd.concat([df_out, value_out_list])
        print(f'{kw} done t:{int(time.time() - s)}s')
    df_out.to_excel(out_excel_path, index = False)


if __name__ == '__main__':
    s = time.time()

    if 1 == 0:
        host = 'localhost'
        port = 3306
        passwd = '111???clown'
        db_name = 'hexingxing'
        db = linkTomySql(host, port, passwd, db_name)
        ym_list = [202301]
        ym_list = [202101,202102,202103,202104,202105,202106,202107,202108,202109,202110,202111,202112,
                   202201,202202,202203,202204,202205,202206,202207,202208,202209,202210,202211,202212,
                   202301,202302,202303,202304,202305,202306,202307,202308,202309,202310,202311]
        out_excel_path = '省市年月菜单销量.xlsx'
        selectDataByYearMonth(db, ym_list, out_excel_path)
    if 1==1:
        host = '192.168.110.96'
        port = 3306
        passwd = '111???clown'
        db_name = 'jxb-h'
        db = linkTomySql(host, port, passwd, db_name)
        kw_list = ['大圆碗','肠粉粉','肠粉酱','蛋饼调味酱','豆花调味汁','豆浆碗','热干面调味酱']
        out_excel_path = '省市门店11月菜单对应供应品销量.xlsx'
        selectDataByKeyWords(db, kw_list, out_excel_path)
    db.close()
    print(f'total t:{int((time.time()-s)/60)}min')