# -*- 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) 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() # def test(): # r = requests.get('http://www.baidu.com').text # print(r) # print('这是test函数') # # def test_demo(a): # test() # print(a) if __name__ == '__main__': db = linkTomySql('localhost','111???clown','hexingxing') # 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字段,联网部分