python_files/cppc_python脚本/服务器脚本应急/7qiaoPlus_update_address_list.py

# -*- codeing = utf-8 -*-
# @Time : 2022/7/21 16:32
# @Author : Clown
# @File : 7qiaoPlus_update_address_list.py
# @Software : PyCharm
import pymysql
import json
import requests
import csv
import os
import time
from datetime import datetime,timedelta
import sys
import traceback
import random
from all_key_table import update_key_value_pair_7qiaoPlus

def linkTomySql(host,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)
        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 loadDataInfile(file_path ,csv_name,db,table_name):
    '''v2新增 执行csv文件导入数据库，注意ENCLOSED BY '"' 防止误判'''
    cursor = db.cursor()
    sql0 = f"SET global local_infile = 1;"
    cursor.execute(sql0)
    db.commit()
    sql1 = f'''LOAD DATA LOCAL INFILE '{file_path+'/'+csv_name}' REPLACE INTO TABLE {table_name} FIELDS TERMINATED BY ','   ENCLOSED BY '"'   LINES TERMINATED BY '\r\n'   IGNORE 1 LINES ;'''
    cursor.execute(sql1)
    db.commit()
    sql2= f"SET global local_infile = 0;"
    cursor.execute(sql2)
    db.commit()
    cursor.close()

def update_scriptrunninglogs(log_date,log_time,running_Time,operation_results,table_name,db):
    '''将脚本执行情况发送至【脚本执行日志】表
    配套内容
    from datetime import datetime
    import sys
    import traceback
    time_begin = time.time()
    log_date = datetime.now().strftime("%Y-%m-%d")  # 日志记录日期
    log_time = datetime.now().strftime('%H:%M:%S')  # 日志记录时间
    df_name = '数据库表名'
    try:
        operation_results = '执行成功'
    except Exception as e:
        operation_results = traceback.format_exc()
    running_Time = time.time()  - time_begin  # 脚本运行时长
    '''
    try:
        sql = "INSERT INTO scriptrunninglogs VALUES (%s,%s,%s,%s,%s,%s,%s)"#date, time, running_Time, script_name, operation_results, table_name, id
        script_name = os.path.basename(sys.argv[0])
        value = (log_date,log_time,running_Time,script_name,operation_results,table_name,0)
        cursor = db.cursor()
        cursor.execute(sql, value)  # 执行sql语句
        db.commit()
        cursor.close()
    except:
        result = "数据库连接失败"
        print(result)

def update_address_list_7qiaoPlus(file_path, key_json, applicationId, formModelId, key):
    '''
    :param file_path: csv输出路径
    :param key_json: 密钥键值对
    :param applicationId: 应用id
    :param formModelId: 表单id
    :param key: 组件id
    :return: csv_name(xxx.csv)
    '''
    csv_name = 'address_list_7qiaoPlus.csv'

    title = ['key', 'search_name', 'address_list', 'level', 'update_date']

    update_date = datetime.now().strftime("%Y-%m-%d")

    #生成基础表单文件
    f = open(file_path + '/' + csv_name, mode='w', encoding='utf-8-sig', newline="")
    writer = csv.writer(f)
    writer.writerow(title)

    #解析密钥键值对
    Token = key_json['data']

    #定义请求头
    headers_api = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36',
        "Content-type": "application/json",
        "X-Auth0-Token": Token}

    #执行请求
    try:
        url_addr_level1 = f'https://qy.do1.com.cn/qiqiao2/runtime/api/v1/bpms-runtime/forms/{formModelId}/form_fields/address/{key}/options?applicationId={applicationId}&displayAll=false'
        resp_addr1 = requests.get(url_addr_level1, headers=headers_api).json()
        for addr_level1 in resp_addr1['data']:
            addr1 = {'id': addr_level1['id'],
                     'value': addr_level1['value']}
            l = {'code':200,'data':[]}
            l['data']=[addr1]
            row1 = [key, addr_level1['value'], json.dumps(l, ensure_ascii=False).replace('\\t',''), 1, update_date]
            writer.writerow(row1)

            parentId1 = addr_level1['id']
            url_addr_level2 = f'https://qy.do1.com.cn/qiqiao2/runtime/api/v1/bpms-runtime/forms/{formModelId}/form_fields/address/{key}/options?applicationId={applicationId}&parentId={parentId1}&displayAll=false'
            resp_addr2 = requests.get(url_addr_level2, headers=headers_api).json()
            for addr_level2 in resp_addr2['data']:
                addr2 = {'id': addr_level2['id'],
                         'value': addr_level2['value']}
                l['data']=[addr1,addr2]
                row2 = [key, addr_level2['value'], json.dumps(l, ensure_ascii=False).replace('\\t',''), 2, update_date]
                writer.writerow(row2)

                parentId2 = addr_level2['id']
                url_addr_level3 = f'https://qy.do1.com.cn/qiqiao2/runtime/api/v1/bpms-runtime/forms/{formModelId}/form_fields/address/{key}/options?applicationId={applicationId}&parentId={parentId2}&displayAll=false'
                resp_addr3 = requests.get(url_addr_level3, headers=headers_api).json()
                for addr_level3 in resp_addr3['data']:
                    addr3 = {'id': addr_level3['id'],
                             'value': addr_level3['value']}
                    l['data']=[addr1,addr2,addr3]
                    row3 = [key, addr_level3['value'], json.dumps(l, ensure_ascii=False).replace('\\t',''), 3, update_date]
                    writer.writerow(row3)

    except Exception as e:
        print(e)

    return csv_name

if __name__ == '__main__':

    # 日志参数
    time_begin = time.time()
    log_date = datetime.now().strftime("%Y-%m-%d")  # 日志记录日期
    log_time = datetime.now().strftime('%H:%M:%S')  # 日志记录时间


    host = 'localhost'
    passwd = '111???clown'
    db_name = 'hexingxing'
    db = linkTomySql(host, passwd, db_name)

    brand_name = '道一云'
    wm_plate = '7qiaoPlus'
    owner = 'all'
    key_name = ''

    headers_api = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.85 Safari/537.36',
        "Content-type": "application/json",
        "X-Auth0-Token": ""}

    file_path = 'C:/Users/ClownHe/Desktop/goods'  # csv保存路径
    applicationId = 'c10510196559404b99b0f8591e8baa5b'  # 门店服务中心
    formModelId = '62d8cd1eff61c87eae935a2f'  # 门店平台信息表
    key = 'key_1658380104096_247482'  # 控件编号

    table_name = 'address_list_7qiaoplus'

    operation_results = '执行成功'
    try:
        try:
            key_json = read_key_value_pair(db, brand_name, wm_plate, owner)
            Token = key_json['data']
            headers_api["X-Auth0-Token"] = Token

            csv_name = update_address_list_7qiaoPlus(file_path, key_json, applicationId, formModelId, key)

        except:
            print('重载关键表')

            update_key_value_pair_7qiaoPlus(db, brand_name, wm_plate, owner, key_name)

            key_json = read_key_value_pair(db, brand_name, wm_plate, owner)
            Token = key_json['data']
            headers_api["X-Auth0-Token"] = Token

            csv_name = update_address_list_7qiaoPlus(file_path, key_json, applicationId, formModelId, key)

            operation_results = operation_results + f',更新【{brand_name}】【{wm_plate}】【all_key_table】'

        loadDataInfile(file_path, csv_name, db, table_name)

        print(f'【{csv_name}】导入成功')

    except Exception as e:

        operation_results = traceback.format_exc()
        print(operation_results)

    print(f'本次脚本执行总用时{(time.time() - time_begin) / 60} min')
    running_Time = time.time() - time_begin  # 脚本运行时长
    db = linkTomySql(host, passwd, db_name)
    update_scriptrunninglogs(log_date, log_time, running_Time, operation_results, table_name, db)