python_files/cppc_python脚本/楼兰说每日数据/runall楼-1.py

# -*- codeing = utf-8 -*-
# @Time : 2022/6/16 13:21
# @Author : Clown
# @File : 批量执行每日数据获取脚本.py
# @Software : PyCharm

import os
import threading
import time
import json
import pymysql




def linkTomySql(host, passwd, db_name, port):
    '''连接至数据库返回【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 read_key_value_pair(db, brand_name, wm_plate, owner):
    '''按条件读取，数据库中all_key_table表里的key_value_pair字段中的值，以键值对的形式输出
       db:数据库，
       brand_name：品牌名，
       wm_plate:外卖平台MEITUAN或ELEME，
       owner:账号权限all或one
    '''
    cursor = db.cursor ()
    sql = f'SELECT key_value_pair FROM all_key_table WHERE brand_name = "{brand_name}" AND wm_plate = "{wm_plate}" AND owner = "{owner}";'
    cursor.execute (sql)
    pair = json.loads (cursor.fetchall ()[0][0])
    return pair


def get_shops_info_to_list(db, brand_name, wm_plate, key_name):
    '''获取门店信息表【shops_info_to_list】中的信息，
    并返回表单shops_info_df【shop_id，shop_name，update_datetime，info_for_script】
    db：数据库信息
    brand_name：品牌
    wm_plate：外卖平台
    key_name：关键信息字段名，如无填‘’，如有填对应键值对的key
    '''
    cursor = db.cursor ()
    if key_name == '':
        sql = f'SELECT shop_id,shop_name,update_datetime FROM shops_info_for_script WHERE brand_name = "{brand_name}" AND wm_plate = "{wm_plate}";'
        cursor.execute (sql)
        shops_info = cursor.fetchall ()
        shops_info_df = []
        for shop_info in shops_info:
            shop_info_dict = {'shop_id': shop_info[0],
                              'shop_name': shop_info[1]}
            shops_info_df.append (shop_info_dict)
        return shops_info_df
    else:
        sql = f'SELECT shop_id,shop_name,update_datetime,info_for_script -> "$.{key_name}" FROM shops_info_for_script WHERE brand_name = "{brand_name}" AND wm_plate = "{wm_plate}";'
        cursor.execute (sql)
        shops_info = cursor.fetchall ()
        shops_info_df = []
        for shop_info in shops_info:
            shop_info_dict = {'shop_id': shop_info[0],
                              'shop_name': shop_info[1],
                              'update_datetime': shop_info[2],
                              f'{key_name}': shop_info[3]}
            shops_info_df.append (shop_info_dict)
        return shops_info_df




if __name__ == '__main__':


    start_time = time.time()
    host = '124.222.188.59'
    passwd = '111...Clown'
    db_name = 'zuzu_data'
    port = 63306
    db = linkTomySql(host, passwd, db_name, port)
    brand_name = '楼兰说'
    owner = 'all'
    keys_dict = {'elm':'',
                 'elm_shops':'',
                 'mt':'',
                 'mt_shops':''}
    keys_dict['elm'] = read_key_value_pair(db, brand_name, 'ELEME', owner)
    keys_dict['elm_shops'] = get_shops_info_to_list(db,brand_name,'ELEME','')
    keys_dict['mt'] = read_key_value_pair (db, brand_name, 'MEITUAN', owner)
    keys_dict['mt_shops'] = get_shops_info_to_list (db, brand_name, 'MEITUAN', '')
    keys_dict = json.dumps(keys_dict,ensure_ascii=False)
    s = '美团(分时流量转化数据-楼兰).py'
    comd = 'python '+ s + ' ' + f'"{keys_dict}"'




    os.system ('"%s"'%(comd))