def run_cmd(cmd_str, echo_print=1):
"""
执行cmd命令，不显示执行过程中弹出的黑框
备注：subprocess.run()函数会将本来打印到cmd上的内容打印到python执行界面上，所以避免了出现cmd弹出框的问题
:param cmd_str: 执行的cmd命令
:return:
"""
from subprocess import run
if echo_print == 1:
print('\n执行cmd指令="{}"'.format(cmd_str))
run(cmd_str, shell=True)
import concurrent.futures
from selenium.webdriver.firefox.options import Options
import sys
from tqdm import tqdm
import time
import openpyxl
from openpyxl.styles import Font # 导入字体模块
from openpyxl.styles import PatternFill # 导入填充模块
from openpyxl.styles import Border, Side # 导入修改边框线及颜色模块
from openpyxl.styles import Alignment # 导入居中方式模块
import tkinter
from tkinter import *
try:
import xlrd
except:
run_cmd("pip install xlrd")
import xlrd
# try:
# from sikuli import *
# except:
# run_cmd("pip install sikuli")
# from sikuli import *
import xlwt
try:
import pandas as pd
except:
run_cmd("pip install pandas")
import pandas as pd
try:
import pymysql
except:
run_cmd("pip install pymysql")
import pymysql
try:
import xlutils.copy
except:
run_cmd("pip install xlutils")
import xlutils.copy
from USSyunwei.basicdata import *
import datetime
import re
from time import sleep
import os
try:
from selenium import webdriver
except:
run_cmd("pip install selenium")
from selenium import webdriver
try:
from openpyxl import load_workbook
except:
run_cmd("pip install openpyxl")
from openpyxl import load_workbook
try:
import xlsxwriter
except:
run_cmd("pip install xlsxwriter")
import xlsxwriter
try:
import threading
except:
run_cmd("pip install threading")
import threading
try:
from multiprocessing import Process
except:
run_cmd("pip install Process")
from multiprocessing import Process
import pytesseract
import cv2
import numpy as np
import copy
import requests
import json
import json
# from USSyunwei.bin import *
import requests
from USSapi.bascidata import *
# ym=ym
# token=token
# import tesserocr
from PIL import Image,ImageFilter,ImageEnhance
#JS按文本匹配元素
def shadow_click(tagname,clickname,text,deveces,num=""):
tag_num = 0
JS_len=10000
if num == "" or num == "1" or num == 1 or num == 1.0 or num == "1.0":
for i in range(0,JS_len):
try:
if deveces.execute_script(f'return document.querySelector("{tagname}").shadowRoot.querySelectorAll("{clickname}")[{i}].textContent')==text:
JStext=deveces.execute_script(f'return document.querySelector("{tagname}").shadowRoot.querySelectorAll("{clickname}")[{i}]')
# print(deveces.execute_script(f'return document.querySelector("{tagname}").shadowRoot.querySelectorAll("{clickname}")[{i}]'))
# print(type(')))
return deveces.execute_script(f'return document.querySelector("{tagname}").shadowRoot.querySelectorAll("{clickname}")[{i}]')
else:
pass
if i>=JS_len:
return "JS下表设定长度不够，加大JS_len的值"
except:
return "元素不可下标"
else:
for i in range(0, JS_len):
try:
if deveces.execute_script(f'return document.querySelector("{tagname}").shadowRoot.querySelectorAll("{clickname}")[{i}].textContent')==text:
JStext=deveces.execute_script(f'return document.querySelector("{tagname}").shadowRoot.querySelectorAll("{clickname}")[{i}]')
tag_num += 1
if tag_num==int(num):
return JStext
else:
pass
else:
if i==JS_len:
return "JS下表设定长度已达到最大值都没有查到，请加大JS_len的值/该值不存在"
else:
pass
except:
return "元素不可下标"
#下拉滚动框点击
def drop_down(business_name,deveces,tag="div",tye=2):
try:
if tye==1:
print(f"{business_name}")
s1 = deveces.find_element(by="xpath", value=f"//{tag}[contains(text(),'{business_name}')]")
else:
print(f"{business_name}")
s1 = deveces.find_element(by="xpath", value=f"//{tag}[text()='{business_name}']")
except:
return False,"下框元素没有找到"
try:
deveces.execute_script("arguments[0].scrollIntoView();", s1)
except:
return False,"下拉框JS滚动到指定位置失败"
try:
if tye==1:
deveces.find_element(by="xpath", value=f"//{tag}[contains(text(),'{business_name}'])").click()
return False,"下拉框点击成功"
else:
deveces.find_element(by="xpath", value=f"//{tag}[text()='{business_name}']").click()
return False, "下拉框点击成功"
except Exception as e:
return False,e
#下拉不滚动
def drop_down_NOGUN(business_name,deveces,tag="div"):
try:
s1 = deveces.find_element(by="xpath", value=f"//{tag}[text()='{business_name}']")
except:
return False,"下框元素没有找到"
try:
deveces.find_element(by="xpath", value=f"//{tag}[text()='{business_name}']").click()
return False,"下拉框点击成功"
except:
return False,"下拉框点击元素失败"
# 读取excel表头
def read_rows_cols(path):
if_hava_remove=False
# print(path)
try:
data = xlrd.open_workbook(rf'{path[0]}')
dict1={}
table = data.sheets()[0]
# 获取行数
rows = table.nrows
# 获取列数
cols = table.ncols
except Exception as e:
return False,f"{path}文件格式错误无法打开，请打开“.xls”类型的文件{e}"
try:
for col in range(0, cols):
value = table.cell_value(0, col)
dict1[f"{value}"]=""
if len(dict1)==0:
return False,"读取文件成功但文件内没有内容"
else:
# print("读取文件成功")
return True,dict1
except:
return False,"读取文件失败，未知错误请找谢伟文排查“bin”文件“读取excel”的“for”循环"
# 仅读取excel数据
def read_excel_data(path):
print("文件正在读取中....")
if_hava_remove=False
try:
data = xlrd.open_workbook(rf'{path}')
list1=[]
table = data.sheets()[0]
# 获取行数
rows = table.nrows
# 获取列数
cols = table.ncols
except Exception as e:
return False,f"文件格式错误无法打开，请打开“.xls”类型的文件{e}"
try:
for row in range(rows):
for col in range(0, cols):
value = table.cell_value(row, col)
list1.append(value)
if len(list1)==0:
return False,"读取文件成功但文件内没有内容"
else:
print("读取文件成功")
return True,list1
except:
return False,"读取文件失败，未知错误请找谢伟文排查“bin”文件“读取excel”的“for”循环"
#读取表头和数据
def read_excel_data_header(path):
print("文件正在读取中....")
if_hava_remove=False
try:
data = xlrd.open_workbook(rf'{path}')
list1=[]
table = data.sheets()[0]
# 获取行数
rows = table.nrows
# 获取列数
cols = table.ncols
except Exception as e:
return False,f"文件格式错误无法打开，请打开“.xls”类型的文件{e}"
try:
for row in range(rows):
dict_key_value={}
num=0
for col in range(0, cols):
key=table.cell_value(0, col)
value = table.cell_value(row, col)
if value=="":
num+=1
dict_key_value[key]=value
if row!=0 and num!=cols:
list1.append(dict_key_value)
if len(list1)==0:
return False,"读取文件成功但文件内没有内容"
else:
print("读取文件成功")
return True,list1
except:
return False,"读取文件失败，未知错误请找谢伟文排查“bin”文件“读取excel”的“for”循环"
#追加写入excel
def excel_with(rows,cols,path_file,value,sheet_code=0):
# print("正在写入")
try:
sheet_code=int(sheet_code)
except:
return False,"excel，sheet编号格式错误"
try:
data=xlrd.open_workbook(path_file)
ws=xlutils.copy.copy(data)
table=ws.get_sheet(sheet_code)
table.write(rows,cols,value)
ws.save(path_file)
# print("数据写入成功")
return True, "数据写入成功"
except Exception as e:
# print(f"数据写入失败{e}")
return False,f"数据写入失败{e}"
#封装数据库查询方法
class DB():
def __init__(self,sql,DB = {'ip': "10.74.149.166", 'port': 3306, 'usr': "rw_uss_02", 'pwd': "rw_uss_02#123"},database="uss_basicdata"):
self.DB = DB
self.database =database
self.sql=sql
try:
self.db = pymysql.connect(host=self.DB["ip"], port=self.DB["port"], user=self.DB["usr"], password=self.DB["pwd"], database=database, charset='utf8',allowMultiQueries=True)
self.cursor = self.db.cursor()
except Exception as e:
return False,f"请检查当前地址的数据库的库名是否存在{e}"
def select(self):
try:
# print("正在查询请稍后...")
ip = self.DB['ip']
port = self.DB['port']
usr = self.DB['usr']
pwd = self.DB['pwd']
except:
return False,"账号或密码错误，请检查数据"
try:
# print(self.sql)
self.cursor.execute(self.sql)
res = self.cursor.fetchall()
if len(res)==0:
return False,"sql查询出来是空值"
self.db.close()
return True,res
except Exception as e:
return False,f"数据查询失败：{e}"
#封装修改，删除，插入方法，
def execute(self):
try:
print("正在修改请稍后...")
ip = DB['ip']
port = DB['port']
usr = DB['usr']
pwd = DB['pwd']
except:
return False,"账号或密码错误请检查数据"
try:
db = pymysql.connect(host=ip, port=port, user=usr, password=pwd, database=self.database, charset='utf8')
cursor = db.cursor()
except:
return False, "请检查当前地址的数据库的库名是否存在"
try:
cursor.execute(self.sql)
db.commit()
db.close()
return True,"数据修改成功"
except Exception as e:
db.rollback()
db.close()
return False,f"sql错误请检查，数据已回滚：{e}"
#获取目标路径下的文件名称从这开始吧
def dir_ride_file(path):
file_list={}
# print(path)
file1=os.walk(path)
num = 1
for root, dirs, files in file1:
for i in files:
if str(i).endswith(".xls") :
file_list[f"{num}"]=i
file_list[f"{i}"] = root
num+=1
else:
pass
# print(file_list)
return file_list
#自动获取文件名称路径
def get_file_name(path,file_name):
try:
file_list=dir_ride_file(path)
except:
return False, "该文件不存在请检查文件夹"
filr_list_path=[]
for k in file_list:
if file_name in k:
# print(k)
filr_list_path.append(file_list[k] + rf"\{k}")
else:
pass
if len(filr_list_path)==0:
return False ,"没有找到该文件"
else:
return True, filr_list_path
#手动获取文件名称路径
def get_file_name_path(path,isSelect=False):
file_list=dir_ride_file(path)
file_list_name_path = []
print("-----------------------------已为您搜索到如下文件请选择文件对应的编号:-------------------------------------")
for i in file_list:
try:
if type(int(i[0]))==int:
print(f"{i}:{file_list[i]}")
except:
pass
if isSelect:
file_list[f"{i+1}"]=f"取消{path}文件及功能的执行"
print(f"{i+1}:取消{path}文件及功能的执行")
num=input(fr"---------------------请选择你要执行的文件:")
if num in file_list:
file_name=file_list[f"{num}"]
if "取消" in file_name and "执行" in file_name:
return True,"该功能被取消执行"
file_list_name_path.append(file_list[file_name]+rf"\{file_name}")
return True,file_list_name_path
else:
return False,f"选择错误"
#登录处理
def environment_click(login):
try:
implicitly_wait_time = 10
options = Options()
options.binary_location = binary_location_path
deveces = webdriver.Firefox( options=options)
except Exception as e:
a=run_cmd(cmd_str="where python", echo_print=1)
print(a)
print(e)
return False,e,"请检查火狐驱动的安装"
stuta,msg=run_environment()
if stuta:
deveces.get(f"{msg['path']}")
deveces.maximize_window()
deveces.implicitly_wait(implicitly_wait_time)
login(f"{msg['user']}",f"{msg['pwd']}",deveces)
current_time = time.time()
while 1:
current_time1 = time.time()
if current_time1 - current_time<= implicitly_wait_time:
if deveces.title in '首页 - USS2.0':
# print(current_time1 - current_time )
break
else:
continue
else:
# print()
print(f"本次登录用时%.2f超时{implicitly_wait_time}内没有没有登录成功，，正在关闭浏览器重新登录请耐心等待...."%(current_time1 - current_time))
deveces.close()
options = Options()
options.binary_location = binary_location_path
deveces = webdriver.Firefox(options=options)
deveces.get(f"{msg['path']}")
deveces.maximize_window()
implicitly_wait_time += 5
deveces.implicitly_wait(implicitly_wait_time)
login(f"{msg['user']}", f"{msg['pwd']}", deveces)
current_time = time.time()
if deveces.title in '首页 - USS2.0':
break
elif deveces.title in 'callback - USS2.0':
while 1:
sleep(5)
if deveces.title in '首页 - USS2.0':
break
else:
continue
else:
implicitly_wait_time +=5
print("登录成功")
else:
return False,msg,"登录选择失败"
#执行故障现象
return True,deveces,msg
#对象正则匹配动态元素
def re_get_list(check_headle,check_tail,list_num,re_stast,deveces):
"""该方法用于页面元素的值是动态的，但是数据结构是固定的时取到变化的值
check_headle：取以什么的开头数据进行正则
check_tail：取以什么的结尾数据进行正则
list_num：取符合条件的第几个数据
re_stast：取到的数居因为会包含check_headle，所以需要重新正则一次，需要给到具体的数据是以什么开头的
deveces：对象
"""
chick = check_headle
chick1 = check_tail
text = deveces.page_source
li = re.findall(f"{chick}.*?{chick1}", text)
num = li[list_num]
elem = re.findall(f'{re_stast}.*?"', num).pop(-1)
elem = re.sub('"', "", elem)
return elem
#正则匹配
def re_get_list_str(check_headle,check_tail,text):
"""该方法用于页面元素的值是动态的，但是数据结构是固定的时取到变化的值
check_headle：取以什么的开头数据进行正则
check_tail：取以什么的结尾数据进行正则
list_num：取符合条件的第几个数据
re_stast：取到的数居因为会包含check_headle，所以需要重新正则一次，需要给到具体的数据是以什么开头的
deveces：对象
"""
chick = check_headle
chick1 = check_tail
li = re.findall(f"{chick}.*?{chick1}", text,re.I | re.DOTALL)
return li
#执行CMD命令
def run_cmd( cmd_str, echo_print=1):
"""
执行cmd命令，不显示执行过程中弹出的黑框
备注：subprocess.run()函数会将本来打印到cmd上的内容打印到python执行界面上，所以避免了出现cmd弹出框的问题
:param cmd_str: 执行的cmd命令
:return:
"""
from subprocess import run
if echo_print == 1:
print('\n执行cmd指令="{}"'.format(cmd_str))
a=run(cmd_str, shell=True)
print(a)
def pand_delete_rows(data,path,):
"""inplace: True表示在原表格上删除
axis：0表示行，1表示列"""
sheet = pd.read_excel(path, sheet_name=0)
print(sheet)
# data = pd.read_csv("person.csv", index_col="Name")
sheet.drop(data, axis=0, inplace=True)
print("------------------------------------")
sheet.to_excel(path)
print(sheet)
def pand_delete_cows(heade_Name,data,path,):
"""inplace: True表示在原表格上删除
axis：0表示行，1表示列"""
sheet = pd.read_excel(path, sheet_name=0,index_col=heade_Name)
print(sheet)
# 删除多列，默认 inplace 参数位 False，即会返回结果
print(sheet.drop(labels=data, axis=1))
# data = pd.read_csv("person.csv", index_col="Name")
# sheet.drop(data, axis=1, inplace=True)
print("------------------------------------")
sheet.to_excel(path)
print(sheet)
def pand_add(path):
sheet = pd.read_excel(path, sheet_name=0)
sheet.to_excel(path)
#写入数据并且绘画图标
def excel_data_figure(path,data,figure_region_LIST,isrow=True,figure_type="pie",Sheet="Sheet1",type=""):
"""figure_region_dict:绘画区域列表
area 区域样式表
bar 条形样式表
column 柱状样式表
line 线条样式表
pie 饼图样式表
doughnut 圆环样式表
scatter 散点样式表
stock 库存样式表
rader 雷达样式表
"""
workbook = xlsxwriter.Workbook(path)
worksheet = workbook.add_worksheet()
rows_cols = [l for l in range(len(data))]
for rows in rows_cols:
worksheet.write_row(rows, 0, data[rows])
print(rows, " ", data[rows])
# Create a new Chart object.
if type=="line":
chart1 = workbook.add_chart({'type': f'{type}'})
chart2 = workbook.add_chart({'type': f'{type}'})
chart3 = workbook.add_chart({'type': f'{type}'})
# Write some data to add to plot on the chart.
chart1.add_series({
'values': f'={Sheet}!${figure_region_LIST[0][0]}${figure_region_LIST[0][1][0]}:${figure_region_LIST[0][0]}${figure_region_LIST[0][1][1]}',
"line": {"color": 'red'},})
chart1.add_series({
'values': f'={Sheet}!${figure_region_LIST[1][0]}${figure_region_LIST[0][1][0]}:${figure_region_LIST[1][0]}${figure_region_LIST[0][1][1]}',
"line": {"color": 'yellow'},
})
# chart.add_series({
# 'values': f'={Sheet}!${figure_region_LIST[2][0]}${figure_region_LIST[0][1][0]}:${figure_region_LIST[2][0]}${figure_region_LIST[0][1][1]}'})
chart2.add_series({
'values': f'={Sheet}!${figure_region_LIST[2][0]}${figure_region_LIST[0][1][0]}:${figure_region_LIST[2][0]}${figure_region_LIST[0][1][1]}',
"line": {"color": 'red'}, })
chart2.add_series({
'values': f'={Sheet}!${figure_region_LIST[3][0]}${figure_region_LIST[0][1][0]}:${figure_region_LIST[3][0]}${figure_region_LIST[0][1][1]}',
"line": {"color": 'yellow'},
})
chart3.add_series({
'values': f'={Sheet}!${figure_region_LIST[4][0]}${figure_region_LIST[0][1][0]}:${figure_region_LIST[4][0]}${figure_region_LIST[0][1][1]}',
"line": {"color": 'red'},
})
chart3.add_series({
'values': f'={Sheet}!${figure_region_LIST[5][0]}${figure_region_LIST[0][1][0]}:${figure_region_LIST[5][0]}${figure_region_LIST[0][1][1]}',
"line": {"color": 'yellow'},
})
# Insert the chart into the worksheet.
worksheet.insert_chart('A7', chart1)
worksheet.insert_chart('J7', chart2)
worksheet.insert_chart('R7', chart3)
elif type=="column":
chart1 = workbook.add_chart({'type': f'{type}'})
chart1.add_series({'values':F'=Sheet1!$N${len(data)-2}:$N${len(data)}','name':'是','color': 'red',"categories":f"=Sheet1!$M${len(data)-2}:$M${len(data)}"})
chart1.add_series({'values':F'=Sheet1!$O${len(data)-2}:$O${len(data)}','name':'否','color': 'red',"categories":f"=Sheet1!$M${len(data)-2}:$M${len(data)}"})
worksheet.insert_chart('A7', chart1)
else:
pass
workbook.close()
# 键值对覆盖写入
def excel_key_value_with(path_file,data_key_value,sheet_code=0):
print("正在写入")
try:
sheet_code=int(sheet_code)
except:
return False,"excel，sheet编号格式错误"
try:
data=xlrd.open_workbook(path_file)
ws=xlutils.copy.copy(data)
table=ws.get_sheet(sheet_code)
row_com=data.sheets()[0]
# 获取行数
rows = row_com.nrows
# 获取列数
cols = row_com.ncols
for row in range(rows):
for col in range(cols):
table.write(row,col,"")
col = 0
for key_header in data_key_value[0]:
table.write(0, col, key_header)
col+=1
row = 1
for value_dict in data_key_value:
col = 0
for key_header in value_dict:
table.write(row, col, value_dict[key_header])
col+=1
print(value_dict)
row+=1
ws.save(path_file)
# print("数据写入成功")
return True, "数据写入成功"
except Exception as e:
print(f"数据写入失败{e}")
return False,"写入失败"
#根据RGB三色的值解析组合成HEX
def hex_conversion_color(RGB):
try:
if isinstance(RGB,tuple):
color_hex=""
decimal_table_sixteen=["0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F"]
for i in RGB:
if isinstance(i,int):
if 0<=i<=255:
color_hex=color_hex+decimal_table_sixteen[i//16]+decimal_table_sixteen[i%16]
else:
raise ValueError(f"RGB三个值都应该为0到255之间，元组{RGB}中{i}不符合要求")
else:
raise TypeError("元组需要int类型")
else:
raise ValueError("请输入RGB对照的值组成元组")
# print(color_hex)
return True,color_hex
except Exception as e:
return False,e
#excel设置字体、颜色、大小、对其方式、背景颜色等等
class Textcolor:
def __init__(self,**kwargs):
"""file_name:文件路径
title：工作表格sheel
text：要写入的文字
frame_color：边框颜色,默认为黑框
frame_range：边框范围
align_row：需要进行居中的行如A11
align_cos:需要居中的列如E12
font：字体
font_cos：字体所在的列
font_row：字体所在行
font_size：字体大小，默认12号字
font_color：字体颜色，默认黑色
is_bold:是否加粗，默认否
Background_RGB:单元格底色RGB值
"""
if "file_name" in kwargs:
self.file_name=kwargs["file_name"]
if "title" in kwargs:
self.title=kwargs["title"]
if "text" in kwargs:
self.text=kwargs["text"]
if "frame_color" in kwargs:
self.frame_color=kwargs["frame_color"]
else:
self.frame_color="000000"
if "frame_range" in kwargs:
self.frame_range=kwargs["frame_range"]
if "align_row" in kwargs:
self.align_range_row=kwargs["align_row"]
if "align_cos" in kwargs:
self.align_range_cos=kwargs["align_cos"]
if "font" in kwargs:
self.font=kwargs["font"]
else:
self.font = "宋体"
# 字体颜色支持两种输入方式通过RGB值或者HEX
if "font_color" in kwargs and "font_RGB" in kwargs:
raise "Velus_Eerror:font_RGB和font_color只能存在一个"
elif "font_RGB" in kwargs:
statu, msg = hex_conversion_color(kwargs["font_RGB"])
if statu:
self.font_color = msg
else:
raise ValueError(f"{msg}")
elif "font_color" in kwargs:
self.font_color = kwargs["font_color"]
else:
self.font_color = "000000"
if "font_size" in kwargs:
self.font_size=kwargs["font_size"]
else:
self.font_size = 12
if "font_row" in kwargs:
self.font_row=kwargs["font_row"]
if "font_cos" in kwargs:
self.font_cos=kwargs["font_cos"]
#支持两种输入方式通过RGB值或者HEX
if "Background_color" in kwargs and "Background_RGB" in kwargs:
raise "Velus_Eerror:Background_RGB和Background_color只能存在一个"
elif "Background_RGB" in kwargs:
statu,msg=hex_conversion_color(kwargs["Background_RGB"])
if statu:
self.Background_color =msg
else:
raise ValueError(f"{msg}")
elif "Background_color" in kwargs:
self.Background_color = kwargs["Background_color"]
else:
self.Background_color = "FFFFFF"
#是否加粗
if "is_bold" in kwargs:
self.is_bold = kwargs["is_bold"]
else:
self.is_bold=False
self.wk = openpyxl.load_workbook(self.file_name) # 加载已经存在的excel
self.sheet1 = self.wk[self.title] # 打开excel中标签名称为title的表格
def set_color(self):
# 设置单元格字体颜色和背景色
fille = PatternFill('solid', fgColor=f'{self.Background_color}') # 设置填充颜色为橙色,solid代表表格为纯色填充，也可以是网格
font = Font(f'{self.font}', size=self.font_size, bold=self.is_bold, italic=False, strike=False, color=f'{self.font_color}') # 设置字体样式
self.sheet1.cell(row=self.font_row, column= self.font_cos, value="").fill = fille # 指定位置填充单元格背景色
self.sheet1.cell(row=self.font_row, column= self.font_cos, value=f"{self.text}").font = font # 填充单元格内容和字体样式
self.wk.save(self.file_name) # 保存excel
def set_border_style(self):
# 设置单元格边框属性
thin = Side(border_style='thick', color=f"{self.frame_color}") # 设置边框颜色
border = Border(left=thin, right=thin, top=thin, bottom=thin) # 设置边框属性为细线
for row in self.sheet1[f'{self.frame_range}']:
for cell in row: cell.border = border
self.wk.save(self.file_name) # 保存excel
# sheet1.cell(row=2, column=8).border = border #单一单元格设置边框
def set_Alignment_mode(self):
# 设置单元格居中方式
align = Alignment(horizontal='center', vertical='center', wrapText=True) # 设置居中方式为水平、垂直居中
# print(self.row,type(self.row))
for i in range(1,self.align_range_row+1):
for m in range(1,self.align_range_cos+1):
self.sheet1.cell(row=i, column=m).alignment = align
self.wk.save(self.file_name) # 保存excel
#开启多进程
def main(process):
#开启4个进程，传入爬取的页码范围
process_list = []
p1 = Process(target=process)
p1.start()
p2 = Process(target=process)
p2.start()
p3 = Process(target=process)
p3.start()
p4 = Process(target=process)
p4.start()
process_list.append(p1)
process_list.append(p2)
process_list.append(p3)
process_list.append(p4)
for t in process_list:
t.join()
# -*- coding: utf-8 -*-
# python version 3.6.4
def RGB2HSI(rgb_img):
"""
这是将RGB彩色图像转化为HSI图像的函数
:param rgm_img: RGB彩色图像
:return: HSI图像
"""
# 保存原始图像的行列数
row = rgb_img.shape[0]
col = rgb_img.shape[1]
# 对原始图像进行复制
hsi_img = rgb_img.copy()
# 对图像进行通道拆分
B, G, R = cv2.split(rgb_img)
# 把通道归一化到[0,1]
[B, G, R] = [i / 255.0 for i in ([B, G, R])]
H = np.zeros((row, col)) # 定义H通道
I = (R + G + B) / 3.0 # 计算I通道
S = np.zeros((row, col)) # 定义S通道
for i in range(row):
den = np.sqrt((R[i] - G[i]) ** 2 + (R[i] - B[i]) * (G[i] - B[i]))
thetha = np.arccos(0.5 * (R[i] - B[i] + R[i] - G[i]) / den) # 计算夹角
h = np.zeros(col) # 定义临时数组
# den>0且G>=B的元素h赋值为thetha
h[B[i] <= G[i]] = thetha[B[i] <= G[i]]
# den>0且G<=B的元素h赋值为thetha
h[G[i] < B[i]] = 2 * np.pi - thetha[G[i] < B[i]]
# den<0的元素h赋值为0
h[den == 0] = 0
H[i] = h / (2 * np.pi) # 弧度化后赋值给H通道
# 计算S通道
for i in range(row):
min = []
# 找出每组RGB值的最小值
for j in range(col):
arr = [B[i][j], G[i][j], R[i][j]]
min.append(np.min(arr))
min = np.array(min)
# 计算S通道
S[i] = 1 - min * 3 / (R[i] + B[i] + G[i])
# I为0的值直接赋值0
S[i][R[i] + B[i] + G[i] == 0] = 0
# 扩充到255以方便显示，一般H分量在[0,2pi]之间，S和I在[0,1]之间
hsi_img[:, :, 0] = H * 255
hsi_img[:, :, 1] = S * 255
hsi_img[:, :, 2] = I * 255
return hsi_img
def HSI2RGB(hsi_img):
"""
这是将HSI图像转化为RGB图像的函数
:param hsi_img: HSI彩色图像
:return: RGB图像
"""
# 保存原始图像的行列数
row = np.shape(hsi_img)[0]
col = np.shape(hsi_img)[1]
# 对原始图像进行复制
rgb_img = hsi_img.copy()
# 对图像进行通道拆分
H, S, I = cv2.split(hsi_img)
# 把通道归一化到[0,1]
[H, S, I] = [i / 255.0 for i in ([H, S, I])]
R, G, B = H, S, I
for i in range(row):
h = H[i] * 2 * np.pi
# H大于等于0小于120度时
a1 = h >= 0
a2 = h < 2 * np.pi / 3
a = a1 & a2 # 第一种情况的花式索引
tmp = np.cos(np.pi / 3 - h)
b = I[i] * (1 - S[i])
r = I[i] * (1 + S[i] * np.cos(h) / tmp)
g = 3 * I[i] - r - b
B[i][a] = b[a]
R[i][a] = r[a]
G[i][a] = g[a]
# H大于等于120度小于240度
a1 = h >= 2 * np.pi / 3
a2 = h < 4 * np.pi / 3
a = a1 & a2 # 第二种情况的花式索引
tmp = np.cos(np.pi - h)
r = I[i] * (1 - S[i])
g = I[i] * (1 + S[i] * np.cos(h - 2 * np.pi / 3) / tmp)
b = 3 * I[i] - r - g
R[i][a] = r[a]
G[i][a] = g[a]
B[i][a] = b[a]
# H大于等于240度小于360度
a1 = h >= 4 * np.pi / 3
a2 = h < 2 * np.pi
a = a1 & a2 # 第三种情况的花式索引
tmp = np.cos(5 * np.pi / 3 - h)
g = I[i] * (1 - S[i])
b = I[i] * (1 + S[i] * np.cos(h - 4 * np.pi / 3) / tmp)
r = 3 * I[i] - g - b
B[i][a] = b[a]
G[i][a] = g[a]
R[i][a] = r[a]
rgb_img[:, :, 0] = B * 255
rgb_img[:, :, 1] = G * 255
rgb_img[:, :, 2] = R * 255
return rgb_img
def classify(ll, distance):
ll.sort()
new = list()
j = 0
i = 1
while i < len(ll):
if ll[i] - ll[j] <= distance:
i = i + 1
if i == len(ll):
new.append(ll[j:])
else:
new.append(ll[j:i])
j = copy.deepcopy(i)
i += 1
return new
def eraseline(rgb_img, near_pixel=3):
# 转换成HSI模式后，H维度的数字表示人眼所见的颜色，对此维度做聚类
hsi_img = RGB2HSI(rgb_img)
d = dict()
s = set()
for x in range(hsi_img.shape[0]):
for y in range(hsi_img.shape[1]):
s.add(hsi_img[x, y, 0]) # 得到所有颜色的分类
for each in s:
d[each] = list()
for x in range(hsi_img.shape[0]):
for y in range(hsi_img.shape[1]):
d[hsi_img[x, y, 0]].append((x, y)) # 得到同一颜色所在的所有的坐标
fenlei_list = classify(list(d.keys()), near_pixel)
fenlei_cord_dict = dict()
fenlei_len_list = list()
# 同一类颜色，坐标点数 填进列表
for i, colorlist in enumerate(fenlei_list):
fenlei_cord_dict[i] = list()
for color in colorlist:
fenlei_cord_dict[i] += d[color]
fenlei_len_list.append((i, len(fenlei_cord_dict[i])))
fenlei_len_list = sorted(fenlei_len_list, key=lambda x: x[1])
newimg = np.full(rgb_img.shape, 255, dtype='uint8')
for cctuple in fenlei_len_list[-5:-1]:
for cccord in fenlei_cord_dict[cctuple[0]]:
newimg[cccord[0], cccord[1], 0] = rgb_img[cccord[0], cccord[1], 0]
newimg[cccord[0], cccord[1], 1] = rgb_img[cccord[0], cccord[1], 1]
newimg[cccord[0], cccord[1], 2] = rgb_img[cccord[0], cccord[1], 2]
return newimg
# 按颜色占比进行ocr识别
def QR_code_ocr(jpg_get_path,QR_code_count,jpg_push_path,isloct=True):
# os.chdir(fr"C:\Users\isoftstone\Desktop")
if isloct:
#打开图片
im = Image.open(fr"{jpg_get_path}")
else:
#
im=jpg_get_path
# 获取图片的像素
rows, cols = im.size
# change_pos = []
#记录图片的所有颜色
bil_list=[]
#记录每种颜色
count1=[]
#记录颜色和次数
num_dict={}
# 记录次数和颜色
num_dict1={}
#记录每种颜色出现的次数
num_list=[]
#获取每种颜色的rgba值
for i in range(0,rows):
for j in range(0,cols):
pixel_set = []
bil_list.append(im.getpixel((i, j)))
for ll in bil_list:
if ll not in count1:
num=bil_list.count(ll)
num_dict[ll]=num
num_dict1[num] = ll
count1.append(ll)
num_list.append(num)
num_list1=sorted(num_list,reverse=True)
#获取出现次数最多的5种颜色
num_list2=num_list1[:QR_code_count:]
num_dict2={}
for oo in num_list2:
num_dict2[num_dict1[oo]]=oo
for i in range(0,rows):
for j in range(0,cols):
if im.getpixel((i,j)) in num_dict2:
pass
else:
im.putpixel((i, j), (255, 255, 255))
im.save(fr"{jpg_push_path}")
# print(num_dict)
im.show(fr"{jpg_push_path}")
return pytesseract.image_to_string(fr"{jpg_push_path}")
#ocr识别颜色占比最多的几种颜色
def ocr_text(jpg_get_path,jpg_push_path,QR_code_count,heibai=0,isloct=True):
# 打开图片
if isloct:
im = Image.open(fr"{jpg_get_path}")
else:
im=jpg_get_path
# im.show(fr"{jpg_get_path}")
if heibai:
im = im.convert('L')
threshold1 = 200
table1 = []
for i in range(256):
if i>=threshold1:
table1.append(1)
else:
table1.append(0)
im = im.point(table1, "1")
im.save(fr"{jpg_push_path}")
else:
pass
# 获取图片的像素
rows, cols = im.size
# change_pos = []
# 记录图片的所有颜色
bil_list = []
# 记录每种颜色
count1 = []
# 记录颜色和次数
num_dict = {}
# 记录次数和颜色
num_list1 = []
# 记录每种颜色出现的次数
num_list = []
# 获取每种颜色的rgba值
for i in range(0, rows):
for j in range(0, cols):
pixel_set = []
bil_list.append(im.getpixel((i, j)))
# if i==17 and j==81:
# # print(im.getpixel((i, j)))
for ll in bil_list:
if ll not in count1:
num = bil_list.count(ll)
num_dict[ll] = num
num_list1.append((ll,num))
count1.append(ll)
num_list.append(num)
num_list_sorted = sorted(num_list)
# 获取出现次数最多的QR_code_count
num_list2 = num_list_sorted[-1:-QR_code_count-1:-1]
print(num_list1)
num_dict3 = {}
#记录颜色出现次数最多得集中颜色
for i in num_list1:
if i[1] in num_list2:
num_dict3[i[0]] = i[1]
# print(len(num_dict3))
# print(num_dict3)
#去除不是想要得颜色
for i in range(0, rows):
for j in range(0, cols):
rgb=im.getpixel((i, j))
# for di in num_dict3:
# if rgb[0]-di[0]<=10 and rgb[1]-di[1]<=10 and rgb[2]-di[2]<=10:
#把和白色接近的颜色变成纯白色，其余颜色变成黑色
if 255-rgb[0]<=80 and 255-rgb[1]<=80 and 255-rgb[2]<=80:
# print(rgb)
im.putpixel((i, j), (255, 255, 255))
# break
else:
# print(rgb)
im.putpixel((i, j), (0, 0, 0))
# break
# else:
# im.putpixel((i, j), (255, 255, 255))
#像素增加三倍
# im = im.resize((rows*50, cols*50), Image.ANTIALIAS)
super_res = cv2.dnn_superres.DnnSuperResImpl_create()
super_res.readModel('FSRCNN_x2.pb')
super_res.setModel('fsrcnn', 2)
img_super_res = super_res.upsample(im)
# im.save(file_out)
im.save(fr"{jpg_push_path}")
# print(num_dict)
im.show(fr"{jpg_push_path}")
return pytesseract.image_to_string(fr"{jpg_push_path}",lang="chi_sim+eng")
# js脚本获取光标位置的文本返回排序值列表
def get_js_orderBy_num(title,js_str,deveces,cuoshi=None,cuoshicode=None):
js = """
window.hovered_element = null
function track_mouse(event){
var x =1000, y = 540
var element = document.elementFromPoint(x, y)
if (!element) {
window.hovered_element = null
return // 当前位置没有元素
}
window.hovered_element = element
}
window.onmousemove = track_mouse
"""
deveces.execute_script(js)
try:
#必须要执行js后再进行定位输入框才可以获取到光标位置
deveces.execute_script(js_str).click()
except Exception as e:
print(f"action文件的js_str输入错误无法定位到输入框{e}")
return e
while 1:
element = deveces.execute_script('return window.hovered_element')
if element:
# print(f'当前鼠标所在的标签为：{element.tag_name}, 其中的文本内容为：{element.text}')
break
else:
print(element)
print("当前位置没有元素")
input("1")
element_list=element.text.split("\n")
print(element_list)
num=0
start=0
try:
for start_elem in element_list:
if title in start_elem and start_elem not in ("","None","null") and num==0:
# element_list.pop("品牌名称")
# element_list.remove(start_elem)
num+=1
# print(element_list)
start = element_list.index(title)
for end_elem in element_list:
if "共 " in end_elem:
end=element_list.index(end_elem)
else:
pass
brand_list=element_list[start+1:end:1]
# print(brand_list.index(brand_name)+1)
print(brand_list)
print("------------")
if title=='品牌名称':
hmd=("三星", "惠达", "LG", "自建屏维修", "慧达", "耶鲁")
elif title=='品类':
hmd=('测试品类（勿用）')
# elif title == '部门名称':
# hmd = ('华南大区', '华东大区', '西南大区', '华北大区', '西北大区', '东北大区','连锁总部')
elif title == '坐席技能':
hmd = ('电话断线回拨','方言回拨','外溢回拨')
if title == '措施编码':
if cuoshi == None:
return "维修措施不能为空"
elif cuoshicode==None:
return "维修措施编码不能为空"
else:
hmd = (f'{cuoshi} {cuoshicode}')
brand_index = []
for brand_name in brand_list:
if brand_name in hmd and brand_name in ('测试品类（勿用）') :#别动
brand_index.append(brand_list.index(brand_name)+1)
elif brand_name in hmd and brand_name in ("三星", "惠达", "LG", "自建屏维修", "慧达", "耶鲁","电话断线回拨",'方言回拨') :
brand_index.append(brand_list.index(brand_name)-4 )
elif brand_name in hmd and brand_name in ('外溢回拨') :
brand_index.append(brand_list.index(brand_name)-3 )
elif title == '措施编码' and brand_name in hmd:
brand_index.append(brand_list.index(brand_name)+2)
elif brand_name in hmd :
brand_index.append(brand_list.index(brand_name) )
# else:
# print(f"数据无法匹配请确认原始数据{brand_name}--->{hmd}")
# return "数据无法匹配请确认原始数据"
print(brand_index)
return brand_index
# return brand_list.index(brand_name)+1
except Exception as e:
print(e)
return e
#DBPLAS_生产数据库查询
def DB_sql(sql,database,example=""):
"""生产数据库查询"""
url = "http://dbplus.tclo2o.cn/authenticate/"
data = {"username": "ex_yong.zeng",
"password": "zy012499*"
}
header = {
"Accept": "application/json, text/javascript, */*; q=0.01",
"Accept-Encoding": "gzip, deflate",
"Accept-Language": "zh-CN,zh;q=0.9",
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36",
"X-Requested-With": "XMLHttpRequest",
"Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
"X-CSRFToken": "ZDiYCprZj54DAEHFc0pJrvJrcYr2nJf7rHmyoGp5aUcyL6gWMNx247RqsYDmsFHJ",
"Cookie": "csrftoken=ZDiYCprZj54DAEHFc0pJrvJrcYr2nJf7rHmyoGp5aUcyL6gWMNx247RqsYDmsFHJ",
}
res = requests.post(url=url, data=data, headers=header)
# print(res.headers)
# print(res["msg"].encode("utf-8").decode("utf-8"))
res_headers = str(res.headers).replace("'", '"')
res_json = json.loads(res_headers, strict=False)
# print(res_json)
cookie = res_json["Set-Cookie"].split(";")[0]
try:
sessionid = res_json["Set-Cookie"].split(";")[4]
except:
sessionid = res_json["Set-Cookie"].split(";")[0]
try:
session = sessionid.split("=")[2]
except:
session = sessionid.split("=")[1]
X_CSRFToken = cookie.split("=")[1]
url = "http://dbplus.tclo2o.cn/query/"
example_list={"1":"生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)",
"2":"生产_营销_十分到家_USS2.0_platform_MYSQL_从(10.68.70.29)",
"3":"生产_营销_十分到家_USS2.0_parts_MYSQL_从(10.68.70.35)",
"4":"生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.37)",
"5":"生产_营销_十分到家_USS2.0_platform_MYSQL_从(10.68.70.30)",
"6":"生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.33)",
"7":"生产_营销_十分到家_USS2.0_parts_MYSQL_从(10.68.70.36)",
"8":"生产_营销_十分到家_USS2.0_basicdata_MYSQL_从(10.68.70.39)"
}
header = {"Accept": "application/json, text/javascript, */*; q=0.01",
"Accept-Encoding": "gzip, deflate",
"Accept-Language": "zh-CN,zh;q=0.9",
"Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
"Cookie": f"{cookie}; sessionid={session}",
"Host": "dbplus.tclo2o.cn",
"Proxy-Connection": "keep-alive",
"Referer": "http://dbplus.tclo2o.cn/sqlquery/",
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.0.0 Safari/537.36",
"X-Csrftoken": X_CSRFToken,
"X-Requested-With": "XMLHttpRequest"}
if example=="":
for key,val in example_list.items():
print(f"{key}:{val}")
example=example_list[input("请选择实例")]
#选择库和实例
try:
# print("正在获取数据...")
data = {
"instance_name": example,
"db_name": database,
"schema_name": "",
"tb_name": "",
"sql_content": sql,
# "allowMultiQueries":True,
"limit_num": 200000}
response = requests.post(url=url, data=data, headers=header)
# print()
response_text = json.loads(rf'{response.text.encode("utf-8").decode("utf-8")}')
try:
if response_text["status"]==1:
return False,F"{response_text}\n{sql}"
if "null" in response_text["data"]["rows"]:
response_text=response.text.replace("null",'""')
if "None" in response_text["data"]["rows"]:
response_text = response.text.replace("None", '""')
# print(response_text)
if response_text["status"]==0:
if len(response_text["data"]["rows"])!=0:
# for order in response_text["data"]["rows"]:
# print(response_text)
return response_text["data"]["column_list"],response_text["data"]["rows"]
else:
return False,f"sql查询出来是空值:{sql}"
else:
return False,f"sql有误请检查{response_text}\n{sql}"
except:
return False,response_text
except Exception as e:
print(e)
print("登录过期，请更换“header”参数中的“X-Csrftoken”和“Cookie”")
return False,e
def send_amin(recipient_email,email_title,email_body):
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
# 你的 QQ 邮箱地址和授权码
sender_email = qq_email
sender_password = qq_password
# 收件人地址
# recipient_email = recipient_email
# 构建邮件主体
subject = email_title
body = email_body
msg = MIMEMultipart()
msg.attach(MIMEText(body, 'plain'))
msg['Subject'] = subject
msg['From'] = sender_email
# 收件人地址
msg['To'] = recipient_email
# 使用 SSL 连接到 QQ 邮箱的 SMTP 服务器
smtp_server = "smtp.qq.com"
smtp_port = 465
try:
server = smtplib.SMTP_SSL(smtp_server, smtp_port)
server.login(sender_email, sender_password)
server.sendmail(sender_email, recipient_email, msg.as_string())
print("邮件发送成功")
except Exception as e:
print(f"邮件发送失败: {e}")
finally:
server.quit()
# 覆盖式写入
def excel_list_data(path,data,num,header_list):
"""path:写入的路径
num：指定在哪行写入最小为2，因为第一行默认写入头部
header_list:一个文本列表，用于写入头部
"""
workbook = xlsxwriter.Workbook(path)
worksheet = workbook.add_worksheet()
rows_cols = [l for l in range(len(data))]
# worksheet.write_row(f"A1",["case","工单","服务方式","分类","品牌","品类","小类","产品","向公司收费" ,"向用户收费","完工时间"] )
# 写入头部
worksheet.write_row(f"A1",header_list)
# 写入数据
for rows in rows_cols:
worksheet.write_row(f"A{num}", data[rows])
print(num, " ", data[rows])
num+=1
workbook.close()
from openpyxl import load_workbook
def excel_zhui_data(path,data_list,num):
# 打开已存在的 Excel 文件
workbook = load_workbook(path)
# 选择要追加写入的工作表
sheet = workbook.active
# num=1
# 追加写入数据
# data_to_append = ['New', 'Data', 'To', 'Append']
for i in data_list:
sheet.append(i)
print(f"{num} {i}")
num+=1
# 保存修改后的 Excel 文件
workbook.save(path)
import pandas as pd
# #追加写入
# def append_data_to_excel(data_to_append, file_path,header,num,isexcelheder=False):
# """data_to_append:数据列表，一行数据为列表的一个元素，由所有行组成的一个列表
# file_path：读取的文件路径，如果文件不存在则创建一个新的飚哥
# ,header：文件中的表头
# ,num"""
# try:
# global isfistdata
# isfistdata = True
# global df_existing
# # 尝试读取已存在的 Excel 文件
# df_existing = pd.read_excel(file_path,header=1)
# except FileNotFoundError:
# # global df_existing
# # 如果文件不存在，则创建一个空的 DataFrame
# df_existing = pd.DataFrame()
#
# # 如果 DataFrame 是空的，手动添加入参作为列名
#
# df_existing = pd.DataFrame(columns=header)
#
# # data_dict = {header[j]: i[j] for j in range(len(header))}
# # print(data_dict)
# for i in data_to_append:
# df_existing= df_existing.append(i, ignore_index=True)
#
# df_existing.to_excel(file_path, index=False)
# def append_data_to_excel(data_to_append, file_path,header,num):
# """data_to_append:数据列表，一行数据为列表的一个元素，由所有行组成的一个列表
# file_path：读取的文件路径，如果文件不存在则创建一个新的飚哥
# ,header：文件中的表头
# ,num"""
# try:
# # global num
# # 尝试读取已存在的 Excel 文件
# df_existing = pd.read_excel(file_path)
# except FileNotFoundError:
# # 如果文件不存在，则创建一个空的 DataFrame
# df_existing = pd.DataFrame()
#
# # 如果 DataFrame 是空的，手动添加入参作为列名
# if df_existing.empty:
# df_existing = pd.DataFrame(columns=header)
# #
# # def submit_task(i,df_existing=df_existing):
# # # 将入参作为新的一行数据添加到 DataFrame 中
# # # for i in data_to_append:
# # # print(f"{num} {i} ")
# # # nonlocal df_existing
# # df_existing = df_existing.append(i, ignore_index=True)
# #
# # # print(datetime.datetime.now())
# # return i
# #
# # executor_Thread(max_workers=1, taskfctionname=submit_task, datalist=data_to_append, num=num)
# for i in data_to_append:
# print(f"{num} {i} ")
# # nonlocal df_existing
# df_existing = df_existing.append(i, ignore_index=True)
# # 将数据写入 Excel 文件
# df_existing.to_excel(file_path, index=False)
#追加写入
def append_data_to_excel(file_path, data, header=None):
"""data:数据列表，一行数据为列表的一个元素，由所有行组成的一个列表
file_path：读取的文件路径，如果文件不存在则创建一个新的表格
,header：文件中的表头
"""
if not os.path.exists(file_path):
# 如果文件不存在，先创建一个空的 Excel 表格并写入表头
df = pd.DataFrame(columns=header)
df.to_excel(file_path, index=False)
df = pd.read_excel(file_path)
# df = pd.read_html(file_path)
new_data = pd.DataFrame(data, columns=header)
df = df.append(new_data, ignore_index=True)
df.to_excel(file_path, index=False)
import pyautogui
#根据坐标获取坐标的文本
def get_text(driver,element_x,element_y):
# element_x = 100
# element_y = 200
# 移动鼠标到坐标位置并点击
pyautogui.moveTo(element_x, element_y)
pyautogui.click()
# 获取当前焦点的元素（可能是您点击的元素）
element = driver.switch_to.active_element
# 获取元素的文本
element_text = element.text
return element_text
#接口参数转换类型
def Api_bodytoparam(data):
param="?"
for key,value in data.items():
param=param+f"{key}"+"="+f"{value}"+"&"
param=param[0:len(param)-1]
return param
#接口服务
# 接口服务
def Api_server(port_name, Goal_hierarchy="content", assert_data="", isprinterror=True,**kwargs):
"""ym:域名，
header：请求头，
data：json数据，如果传了data接口请求数据从data来，不从模板取数据，
url_path：请求路径，
assert_data：断言，
requests_type：请求方式，不传默认get,
Goal_hierarchy:目标数据层级，请求后得到返回值，目标数据再哪个key里面，默认在content里面,如果目标数据仅在第一层 可传空字符串，但是不能不传，
token:令牌，
port_name：接口名称，
**kwargs：接口参数，可以指定参数名传，也可以传data字典，
"""
if "data" in kwargs.keys():
data = url_list(port_name, **kwargs["data"], issenddata=True)
# data = kwargs
else:
data = url_list(port_name, **kwargs, issenddata=False)
# 判断返回的data是否是正常返回
try:
if type(data[1][1]) != dict:
return False, data[1][1]
except:
return False, f"{data}下标1里面没有下标1"
# url
url = data[0][0]
# 请求方式
requests_type = data[0][1]
# 断言
if assert_data=="":
retext = data[0][2]
else:
retext=assert_data
# 请求数据
if data[1][0]:
data = data[1][1]
else:
return False, data[1][1]
header = {
"Authorization": f"{token}",
"Content-Type": "application/json;charset=UTF-8",
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36",
"Cookie":"sensorsdata2015jssdkcross=%7B%22distinct_id%22%3A%22ex_yong.zeng%22%2C%22first_id%22%3A%2218cce911e5d1c8d-020ee3b22e7e8f6-4c657b58-1327104-18cce911e5e1dd2%22%2C%22props%22%3A%7B%22%24latest_traffic_source_type%22%3A%22%E7%9B%B4%E6%8E%A5%E6%B5%81%E9%87%8F%22%2C%22%24latest_search_keyword%22%3A%22%E6%9C%AA%E5%8F%96%E5%88%B0%E5%80%BC_%E7%9B%B4%E6%8E%A5%E6%89%93%E5%BC%80%22%2C%22%24latest_referrer%22%3A%22%22%7D%2C%22identities%22%3A%22eyIkaWRlbnRpdHlfY29va2llX2lkIjoiMThjY2U5MTFlNWQxYzhkLTAyMGVlM2IyMmU3ZThmNi00YzY1N2I1OC0xMzI3MTA0LTE4Y2NlOTExZTVlMWRkMiIsIiRpZGVudGl0eV9sb2dpbl9pZCI6ImV4X3lvbmcuemVuZyJ9%22%2C%22history_login_id%22%3A%7B%22name%22%3A%22%24identity_login_id%22%2C%22value%22%3A%22ex_yong.zeng%22%7D%2C%22%24device_id%22%3A%2218cce911e5d1c8d-020ee3b22e7e8f6-4c657b58-1327104-18cce911e5e1dd2%22%7D"
}
# 1、如果请求data有/？&等符号时优先用parm格式请求
match=["/","？","&","?"]
#如果data里面的数据有关键字并且data里面的数据不是字典的话
if any(c in str(data.values()) for c in match) and type(data[f"{port_name}"])!=dict:
if requests_type == "get":
re_check = requests.get(url=url + data[f"{port_name}"], headers=header)
elif requests_type == "post":
re_check = requests.post(url=url + data[f"{port_name}"], headers=header)
elif requests_type == "put":
re_check = requests.put(url=url + data[f"{port_name}"], headers=header)
else:
return False, "请求方式不存在"
re_check_json = json.loads(re_check.text)
if Goal_hierarchy != "":
#进行断言
if len(re_check_json[f"{Goal_hierarchy}"]) == 1 and retext in str(re_check_json):
return True, re_check_json
elif len(re_check_json[f"{Goal_hierarchy}"]) == 1 and retext not in str(re_check_json):
print("断言失败请确认数据是否正确")
return False, re_check_json
else:
print(f"########################警告！-{port_name}数据不唯一如确认无误请忽略#################")
return True, re_check_json
else:
if retext in str(re_check_json):
return True, re_check_json
else :
print("########################断言失败请确认数据是否正确########################")
return False, re_check_json
# 如果是json格式或者数据是字典的话
else:
try:
# 2、以正常的方式请求data和url分开请求
if requests_type == "get":
re_check1 = requests.get(url=url, data=json.dumps(data[f"{port_name}"]), headers=header)
elif requests_type == "post":
re_check1 = requests.post(url=url, data=json.dumps(data[f"{port_name}"]), headers=header)
elif requests_type == "put":
re_check1 = requests.put(url=url, json=data[f"{port_name}"], headers=header)
else:
return False, "请求方式不存在"
re_check1 = json.loads(re_check1.text)
#判断token是否有效
if "过期" in str(re_check1):
return False, re_check1
if retext in str(re_check1):
if Goal_hierarchy== "":
# re_check1changge=1
return True,re_check1
else:
re_check1changge = len(re_check1[f"{Goal_hierarchy}"])
if re_check1changge==1:
return True, re_check1
else:
if Goal_hierarchy == "":
# re_check1changge = 1
print(f"########################{port_name}断言失败,接口数据已返回########################")
return False, re_check1
else:
re_check1changge = len(re_check1[f"{Goal_hierarchy}"])
if re_check1changge == 1:
print(f"########################{port_name}断言失败,接口数据已返回########################")
return False, re_check1
# re_check1changge = len(re_check1[f"{data_path}"])
except:
re_check1 = {}
re_check1changge = 999
# 把json字典里面的接口名称的data数据的接口名称组装成param的格式
try:
url1 = f"{url}{Api_bodytoparam(data[f'{port_name}'])}"
# print(url)
if requests_type == "get":
re_check2 = requests.get(url=url1, headers=header)
elif requests_type == "post":
re_check2 = requests.post(url=url1, headers=header)
elif requests_type == "put":
re_check2 = requests.put(url=url1, headers=header)
else:
return False, "请求方式不存在"
re_check2 = json.loads(re_check2.text)
if "过期" in str(re_check2):
return False, re_check2
if retext in str(re_check2):
if Goal_hierarchy == "":
# re_check1changge=1
return True, re_check2
else:
re_check2changge = len(re_check2[f"{Goal_hierarchy}"])
if re_check2changge == 1:
return True, re_check2
else:
if Goal_hierarchy == "":
# re_check1changge = 1
print(f"########################{port_name}断言失败,接口数据已返回########################")
return False, re_check2
else:
re_check2changge = len(re_check2[f"{Goal_hierarchy}"])
if re_check2changge == 1:
print(f"########################{port_name}断言失败,接口数据已返回########################")
return False, re_check2
except:
re_check2 = {}
re_check2changge = 999
# 把data里面的数据组装成parm格式
try:
url3 = f"{url}{Api_bodytoparam(data)}"
if requests_type == "get":
re_check3 = requests.get(url=url3, headers=header)
elif requests_type == "post":
re_check3 = requests.post(url=url3, headers=header)
elif requests_type == "put":
re_check3 = requests.put(url=url3, headers=header)
else:
return False, "请求方式不存在"
re_check3 = json.loads(re_check3.text)
if "过期" in str(re_check3):
return False, re_check3
if retext in str(re_check3):
if Goal_hierarchy == "":
# re_check1changge=1
return True, re_check3
else:
re_check3changge = len(re_check3[f"{Goal_hierarchy}"])
if re_check3changge == 1:
return True, re_check3
else:
if Goal_hierarchy == "":
# re_check1changge = 1
print(f"########################{port_name}断言失败,接口数据已返回########################")
return False, re_check3
else:
re_check3changge = len(re_check3[f"{Goal_hierarchy}"])
if re_check3changge == 1:
print(f"########################{port_name}断言失败,接口数据已返回########################")
return False, re_check3
except:
re_check3 = {}
re_check3changge = 999
changge = [re_check3changge, re_check2changge, re_check1changge]
changgedict = {re_check3changge: re_check3, re_check2changge: re_check2, re_check1changge: re_check1}
# 有成功得并且成功返回得数量只有一条
if 1 in changge:
re_check_json = changgedict[1]
return False, re_check_json
# 有成功得但是返回得数量有多条
elif (re_check3changge not in (999, 0) or re_check2changge not in (999, 0) or re_check1changge not in (999, 0)) and retext in str(re_check3changge) :
# if retext in str(re_check3changge):
for re_check_jsonnum in changge:
if re_check_jsonnum != 999 and re_check_jsonnum != 0:
print(f"########################警告-{port_name}数据不唯一如确认无误请忽略########################")
re_check_json = changgedict[re_check_jsonnum]
return True, re_check_json
# 有0的则取为0的
elif 0 in changge:
re_check_json = changgedict[0]
if isprinterror:
print(f"########################警告-{port_name}接口返回为空########################")
return False, re_check_json
#没有返回成功并且返回得数量有多条
elif (re_check3changge not in (999, 0) or re_check2changge not in (999, 0) or re_check1changge not in (999, 0)) and retext not in str(re_check3changge) :
for re_check_jsonnum in changge:
if re_check_jsonnum != 999 and re_check_jsonnum != 0:
print(f"########################警告-{port_name}断言失败，并且返回了多条数据########################")
re_check_json = changgedict[re_check_jsonnum]
return False, re_check_json
# 最后返回全部失败的返回第一条
else:
re_check_json = changgedict[re_check3changge]
print("########################接口异常，请联系管理员########################")
return False, re_check_json
import hashlib
import urllib.parse
#md5加密
def Md5_encode(text):
#b10a8db164e0754105b7a99be72e3fe5
hash_object = hashlib.md5(text.encode())
md5_hash = hash_object.hexdigest()
return md5_hash
#前端加密urllib
#%E8%B0%A2%20%E4%BC%9F%E6%96%87
def urllib_encode(text):
encode_text=urllib.parse.quote(text)
return encode_text
#运行工作台的内容以json格式打印
def print_json(data):
try:
print(json.dumps(data, sort_keys=True, indent=4, separators=(', ', ': '), ensure_ascii=False))
except:
print(data)
#以任务池的方式，多线程执行任务
def executor_Thread(max_workers,taskfctionname,datalist,num=1,isprint=True):
"""max_workers:任务池最大线程
taskfction：具体执行哪个任务的函数名
datalist：执行任务函数的参数列表，所有参数"""
with concurrent.futures.ThreadPoolExecutor(max_workers=max_workers) as executor:
# 提交10000个任务给任务池
results=[]
for order_deation in datalist:
results.append(executor.submit(taskfctionname, order_deation))
# results = executor.submit(task_case, order_deation)
# 获取任务执行结果
for future in concurrent.futures.as_completed(results):
result = future.result()
if isprint:
print(f"{num} {result}")
num+=1
