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

# -*- codeing = utf-8 -*-
# @Time : 2022/6/20 13:02
# @Author : Clown
# @File : all_key_table.py
# @Software : PyCharm

'''
关键表
All data is from this table
表名：all_key_table
更新方式：每日自检，如信息失效则更新
关联脚本：
字段：
字段名	值类型	备注
brand_name	varchar	品牌名（浆小白/粥小鲜/楼兰说）
wm_plate	varchar	外卖平台（MEITUAN/ELEME)
acct_kinds	varchar	账号类别（chain连锁/single单店）
owner	varchar	平台登录账号所有人（all管理员总账号/one子账号）
user	varchar	平台登录用户
key	varchar	钥匙
data_pipe	json	数据通道接通验证（当前来源食亨）
key_value_pair	json	键值对参数（用于启动其他脚本执行）
'''

import requests
import pymysql
import json
import time
import random

def linkTomySql(host,passwd,db_name):
    '''连接数据库，返回值为：type:参数 【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=63306)
        print('\nconnect to mysql server 成功')
        print('---------------------------------------')

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

def wkong_login(username,pw):
    '''登录食亨悟空控件，返回值为type:str 【sh_token】'''
    try:
        cookie = ''
        url = 'https://bi.shihengtech.com/api/userlogin/login'
        headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Safari/537.36',
                    'Cookie': cookie}

        params = {'username': username,
                  'password': pw}

        resp = requests.post(url, headers = headers ,json = params)
        sh_token = resp.json()['result']
    except:
        sh_token = ''
    return sh_token

def take_token(wm_plate, user, sh_token):

    '''
    wm_plate:平台名称【MEITUAN】/【ELEME】
    user：外卖平台登录账号
    sh_token：通信证明
    '''
    try:
        url_wukong = 'https://bi.shihengtech.com/token-owner/acquire_token'
        headers_wukong = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Safari/537.36',
            'extension': '2.1.0'}
        params_wukong = {"platform": wm_plate,
                         "username": user,
                         "userToken": sh_token}
        resp = requests.post(url_wukong, headers=headers_wukong, json=params_wukong)
        data = resp.text
    except:
        data = {}
        '''
        美团参数
        data = {"device_uuid":"!ce3baac4-0ef4-4c51-8e06-d50515d383db",
                "uuid_update":"true",
                "pushToken":"0VlrP_me3r43iR4zJqAPcilheMwouJO32OXkvzjsrXxs*",
                "acctId":"113245982",
                "token":"0fiJecyopKkIyVG7mb-qItH88bFRjfRkzCeugUySJTco*",
                "brandId":"-1",
                "city_id":"0",
                "isChain":"1",
                "existBrandPoi":"true",
                "ignore_set_router_proxy":"true",
                "region_id":"",
                "region_version":"0",
                "newCategory":"false",
                "bsid":"5-D2l3L0gOXLAhsFiizjlcgSqXQFLctqNKCMyhxp65hgGKgu8FGZskNq5hSJnGTg02QDgHTgXc0bx1Gm_4sqzw",
                "city_location_id":"0",
                "location_id":"0",
                "cityId":"310100",
                "provinceId":"310000",
                "wmPoiId":"-1",
                "_lxsdk_cuid":"17e235602f4c8-05aa5f2b22c2a3-978153c-1fa400-17e235602f44f",
                "_lxsdk":"17e235602f4c8-05aa5f2b22c2a3-978153c-1fa400-17e235602f44f",
                "uuid":"d85a7b5354c578c949b1.1654585692.1.0.0",
                "set_info":"%7B%22wmPoiId%22%3A14471551%2C%22ignoreSetRouterProxy%22%3Atrue%7D",
                "logan_session_token":"3s5se0y0p102jodkb4c3",
                "_lxsdk_s":"1815ab1658e-b68-cdb-52e%7C%7CNaN"}
        饿了么参数(仅部分数据，如有需要可打印你出来查看)
        data = {'ksid': "NZUWN2MTA1Mjg5MjA0OTM5NjAxT1lGbGRPZTlQ",
                'loginShopId': "99731295",
                'shopId': "99731295"}
        '''
    return data

def update_key_value_pair(db,brand_name,wm_plate,owner,key_value_pair):
    '''
    更新关键表，all_key_table字段,
    db:数据库，
    brand_name：品牌名，
    wm_plate:外卖平台MEITUAN或ELEME，
    owner:账号权限all或one
    key_value_pair:为空值 ‘’ 时联网自动更新，不为空值时需手动更新
    '''
    cursor = db.cursor()
    if key_value_pair == '':
        sql = f'''SELECT * FROM all_key_table WHERE owner = '{owner}' AND brand_name = '{brand_name}' AND wm_plate = '{wm_plate}' ;'''
        cursor.execute(sql)
        df = cursor.fetchall()
        row = df[0]
        u_p = json.loads(row[6])
        username = u_p['username']
        pw = u_p['pw']
        wm_plate = row[1]
        user = row[4]
        try:
            cookie = ''
            url = 'https://bi.shihengtech.com/api/userlogin/login'
            headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Safari/537.36',
                        'Cookie': cookie}

            params = {'username': username,
                      'password': pw}

            resp = requests.post(url, headers = headers ,json = params)
            sh_token = resp.json()['result']
            # print(sh_token)
        except Exception as e:
            print(e)
            sh_token = ''
        try:
            url_wukong = 'https://bi.shihengtech.com/token-owner/acquire_token'
            headers_wukong = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Safari/537.36',
                'extension': '2.1.0'}
            params_wukong = {"platform": wm_plate,
                             "username": user,
                             "userToken": sh_token}
            resp = requests.post(url_wukong, headers=headers_wukong, json=params_wukong)
            data = resp.text
            # print(data)
        except Exception as e:
            print(e)
            data = {}
            '''
            美团参数
            data = {"device_uuid":"!ce3baac4-0ef4-4c51-8e06-d50515d383db",
                    "uuid_update":"true",
                    "pushToken":"0VlrP_me3r43iR4zJqAPcilheMwouJO32OXkvzjsrXxs*",
                    "acctId":"113245982",
                    "token":"0fiJecyopKkIyVG7mb-qItH88bFRjfRkzCeugUySJTco*",
                    "brandId":"-1",
                    "city_id":"0",
                    "isChain":"1",
                    "existBrandPoi":"true",
                    "ignore_set_router_proxy":"true",
                    "region_id":"",
                    "region_version":"0",
                    "newCategory":"false",
                    "bsid":"5-D2l3L0gOXLAhsFiizjlcgSqXQFLctqNKCMyhxp65hgGKgu8FGZskNq5hSJnGTg02QDgHTgXc0bx1Gm_4sqzw",
                    "city_location_id":"0",
                    "location_id":"0",
                    "cityId":"310100",
                    "provinceId":"310000",
                    "wmPoiId":"-1",
                    "_lxsdk_cuid":"17e235602f4c8-05aa5f2b22c2a3-978153c-1fa400-17e235602f44f",
                    "_lxsdk":"17e235602f4c8-05aa5f2b22c2a3-978153c-1fa400-17e235602f44f",
                    "uuid":"d85a7b5354c578c949b1.1654585692.1.0.0",
                    "set_info":"%7B%22wmPoiId%22%3A14471551%2C%22ignoreSetRouterProxy%22%3Atrue%7D",
                    "logan_session_token":"3s5se0y0p102jodkb4c3",
                    "_lxsdk_s":"1815ab1658e-b68-cdb-52e%7C%7CNaN"}
            饿了么参数(仅部分数据，如有需要可打印你出来查看)
            data = {'ksid': "NZUWN2MTA1Mjg5MjA0OTM5NjAxT1lGbGRPZTlQ",
                    'loginShopId': "99731295",
                    'shopId': "99731295"}
            '''
        try:
            sql_up = f"UPDATE all_key_table SET key_value_pair = (%s) WHERE all_key_table.user = '{user}' ;"
            value = (data)
            cursor.execute(sql_up, value)
            db.commit()
            print(user, '已更新')
        except Exception as e:
            print(e)

    else:
        try:
            sql_up = f"UPDATE all_key_table SET key_value_pair = (%s) WHERE owner = '{owner}' AND brand_name = '{brand_name}' AND wm_plate = '{wm_plate}' ;"
            value = (key_value_pair)
            cursor.execute(sql_up, value)
            db.commit()
            print(f'{brand_name} {wm_plate} 键值对信息已更新')
        except Exception as e:
            print(e)
        db.close()

def read_key_value_pair(db,brand_name,wm_plate,owner):
    '''按条件读取，数据库中all_key_table表里的key_value_pair字段中的值，返回值type:dict【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 update_key_value_pair_7qiaoPlus(db,brand_name,wm_plate,owner,key_value_pair):
    '''
        更新关键表，all_key_table字段,
        \ndb:数据库，
        \nbrand_name：道一云
        \nwm_plate:7qiaoPlus
        \nacct_kinds:chain
        \nowner: all
        \nuser:root
        \nkey:rootkey
        \ndata_pipe:{"corpId":"wp4nJkEAAAXE4BbTWEYSdyrX_-fl8vqA","secret":"fc092ce3365b4da39c682298f6b8684e","account":"wo4nJkEAAARtG8M8Ig6n6r5eyQ1Vj1ow"}
        \nkey_value_pair:为空值 ‘’ 时联网自动更新，不为空值时需手动更新
    '''
    cursor = db.cursor()
    if key_value_pair == '':
        sql = f'''SELECT * FROM all_key_table WHERE owner = '{owner}' AND brand_name = '{brand_name}' AND wm_plate = '{wm_plate}' ;'''
        cursor.execute(sql)
        df = cursor.fetchall()
        row = df[0]
        u_p = json.loads(row[6])
        corpId = u_p['corpId']
        secret = u_p['secret']
        account = u_p['account']
        timestamp = str(int(time.time()))
        randomint = str(random.randint(1, 10000))
        user = 'root'
        try:
            headers = {
                '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'}
            url_AccessKey = f'https://qiqiao.do1.com.cn/plus/cgi-bin/securities/access_key?timestamp={timestamp}&random={randomint}&corpId={corpId}&secret={secret}&account={account}'
            resp_AccessKey = requests.get(url_AccessKey, headers=headers).json()
            accessKey = resp_AccessKey['data']
            url_Token = f'https://qiqiao.do1.com.cn/plus/cgi-bin/securities/qiqiao_token?timestamp={timestamp}&random={randomint}&corpId={corpId}&secret={secret}&account={account}&accessKey={accessKey}'
            resp = requests.get(url_Token, headers=headers)
            data = resp.text
        except Exception as e:
            print(e)
            data = {}
        try:
            sql_up = f"UPDATE all_key_table SET key_value_pair = (%s) WHERE all_key_table.user = '{user}' ;"
            value = (data)
            cursor.execute(sql_up, value)
            db.commit()
            print(user, '已更新')
        except Exception as e:
            print(e)

    else:
        try:
            sql_up = f"UPDATE all_key_table SET key_value_pair = (%s) WHERE owner = '{owner}' AND brand_name = '{brand_name}' AND wm_plate = '{wm_plate}' ;"
            value = (key_value_pair)
            cursor.execute(sql_up, value)
            db.commit()
            print(f'{brand_name} {wm_plate} 键值对信息已更新')
        except Exception as e:
            print(e)
        db.close()

if __name__ == '__main__':

    db = linkTomySql('124.222.188.59', '111...Clown', 'zuzu_data')
    # db = linkTomySql('localhost','12345678','zuzu_data')

    update_key_value_pair(db,'浆小白','ELEME','all','')

    # update_key_value_pair_7qiaoPlus(db,'道一云','7qiaoPlus','all','')

    #以下为初始化all_key_table表中的所有key_value_pair字段,联网部分
    # cursor = db.cursor()
    # sql = '''SELECT * FROM all_key_table WHERE owner = 'all';'''   #选择所有满足条件的账户
    # cursor.execute(sql)
    # df = cursor.fetchall()
    # for row in df:
    #     #遍历所有账户，并更新key_value_pair字段
    #     u_p = json.loads(row[6])
    #     username = u_p['username']
    #     pw = u_p['pw']
    #     sh_token = wkong_login(username,pw)
    #
    #     wm_plate = row[1]
    #     user = row[4]
    #     data = take_token(wm_plate,user,sh_token)
    #     try:
    #         sql_up = f"UPDATE all_key_table SET key_value_pair = (%s) WHERE all_key_table.user = '{user}' ;"
    #         value = (data)
    #         cursor.execute(sql_up,data)
    #         db.commit()
    #         print(user,'已更新')
    #     except Exception as e:
    #         print(e)
    #
    # db.close()
    #以上为初始化all_key_table表中的所有key_value_pair字段,联网部分