1. Python通过pypyodbc访问Access数据库
https://blog.csdn.net/jisuanjiguoba/article/details/73163721
2. java大数据学习路线图
https://blog.csdn.net/weixin_44386638/article/details/86664006
3. 使用Python对Access读写操作
https://www.cnblogs.com/mayi0312/p/6646957.html
4. python 字符串组成MySql 命令时,字符串含有单引号或者双引号导致出错解决办法
https://www.cnblogs.com/Presley-lpc/p/9152020.html
5. 纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示例
https://blog.csdn.net/weixin_36185028/article/details/78685844
6.实现三个功能
1. 读取excel脚本上的测试用例,使用Automation desk执行py脚本,运行程序,将测试结果通过socket的方式(因为pyqt来实时读取测试结果,操控机器,com段通信涉及到进程、线程占用的问题,所以采用了socket)发送到pyqt做的控制器(监控网站测试需求,操作automation执行测试用例并读取返回结果)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
from openpyxl import load_workbook from time import sleep from audxilapiconveniencelib import Write,Read from openpyxl.comments import Comment from openpyxl.styles import colors, Font import socket import json import time #记录测试用例所耗时间 start_time = time.time() #测试进度推送 server_socket = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) address = ("localhost", 8108) #测试用例导入并筛选 TC=load_workbook ("C:\\03_TestCase\\BenShaw\\ATC\\VCU_TC_LvPwrOff.xlsx") TCsheet=TC.active TCrows=[] Cases=[] for row in TCsheet.iter_rows(): TCrows.append(row) #for i in range(2,14): for i in range(2,14): Cases.append(TCrows[i]) #测试执行 item=0 sum_num = 0 for Case in Cases: Result=[] #PreCondition init_list = [] for i in range(2,5): init_list.append(Case[i].value) check_pre_list = [] tag = 0 count = 0 while count<1: #分4种条件,先判断是否Powcomplete,chargfed,stready,状态, # 1、全0; # 2、4,0,0; # 3、4,0,1; 高压上电状态 # 4、4,2,0; 充电状态 if init_list[0] == 0 and init_list[1] == 0 and init_list[2] == 0: print("用例%s进入初始条件一的判断"%sum_num) Write(_AD_.MAPort, _AD_.TestVar.IN_ChrgOffButton_Value, Case[5].value) Write(_AD_.MAPort, _AD_.TestVar.IN_PowON_Value, Case[8].value) Write(_AD_.MAPort, _AD_.TestVar.IN_12Voltage_Value, Case[9].value) tag = 1 count = 4 elif init_list[0] == 4 and init_list[1] == 0 and init_list[2] == 0: print("用例%s进入初始条件二的判断"%sum_num) Write(_AD_.MAPort, _AD_.TestVar.IN_ChrgOffButton_Value, Case[5].value) Write(_AD_.MAPort, _AD_.TestVar.IN_PowON_Value, Case[8].value) Write(_AD_.MAPort, _AD_.TestVar.IN_12Voltage_Value, Case[9].value) time.sleep(1) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, Case[7].value) time.sleep(3) count2 = 0 while count2<3: if Read(_AD_.MAPort, _AD_.TestVar.RX_BMS_Tx_stPowComplete) == init_list[0]: print("用例%s初始条件二再循环判断正确"%sum_num) tag = 1 break else: Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, 0) time.sleep(1) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, 1) time.sleep(3) count2 +=1 count = 4 elif init_list[0] == 4 and init_list[1] == 0 and init_list[2] == 1: print("用例%s进入初始条件三的判断"%sum_num) Write(_AD_.MAPort, _AD_.TestVar.IN_ChrgOffButton_Value, Case[5].value) Write(_AD_.MAPort, _AD_.TestVar.IN_PowON_Value, Case[8].value) Write(_AD_.MAPort, _AD_.TestVar.IN_12Voltage_Value, Case[9].value) time.sleep(1) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, Case[7].value) time.sleep(3) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyStart_Value, Case[6].value) time.sleep(1) count3 = 0 while count3<4: if Read(_AD_.MAPort, _AD_.TestVar.RX_BMS_Tx_stPowComplete) == init_list[0]: count4 = 0 while count4 < 3: if Read(_AD_.MAPort, _AD_.TestVar.RX_VCU_Tx_stReady) == 1: print("用例%s初始条件三再循环判断正确"%sum_num) #退出count3的循环 count3 = 5 tag = 1 break else: Write(_AD_.MAPort, _AD_.TestVar.IN_KeyStart_Value, 0) time.sleep(3) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyStart_Value, 1) count4 +=1 count3 +=1 else: Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, 0) time.sleep(1) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, 1) time.sleep(3) count3 +=1 count = 4 elif init_list[0] == 4 and init_list[1] == 2 and init_list[2] == 0: print("用例%s进入初始条件四的判断"%sum_num) Write(_AD_.MAPort, _AD_.TestVar.IN_ChrgOffButton_Value, Case[5].value) Write(_AD_.MAPort, _AD_.TestVar.IN_PowON_Value, Case[8].value) Write(_AD_.MAPort, _AD_.TestVar.IN_12Voltage_Value, Case[9].value) time.sleep(1) Write(_AD_.MAPort, _AD_.TestVar.RC2Value_Value, Case[11].value) time.sleep(4) count5 = 0 while count5<3: if Read(_AD_.MAPort, _AD_.TestVar.RX_BMS_Tx_stPowComplete) == init_list[0] and Read(_AD_.MAPort, _AD_.TestVar.RX_VCU_Tx_stChrgFed) == init_list[1]: print("用例%s初始条件四再循环判断正确"%sum_num) #退出count3的循环 tag = 1 break else: Write(_AD_.MAPort, _AD_.TestVar.RC2Value_Value, 0) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, 1) time.sleep(1) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, 0) Write(_AD_.MAPort, _AD_.TestVar.RC2Value_Value, Case[11].value) time.sleep(3) count5 +=1 count = 4 print("退出条件4循环") else: print("用例%s属于目前未加初始条件,待扩展判断区"%sum_num) count +=1 check_pre_list.append(Read(_AD_.MAPort, _AD_.TestVar.RX_BMS_Tx_stPowComplete)) check_pre_list.append(Read(_AD_.MAPort, _AD_.TestVar.RX_VCU_Tx_stChrgFed)) check_pre_list.append(Read(_AD_.MAPort, _AD_.TestVar.RX_VCU_Tx_stReady)) #进行写入操作前的判断,节省资源 # if tag: #Action print("用例%s进入测试步骤正确"%sum_num) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyStart_Value, Case[12].value) #写入speed Write(_AD_.MAPort, _AD_.TestVar.Active_Value, 1) Write(_AD_.MAPort, _AD_.TestVar.Control_Value,Case[13].value) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, Case[14].value) Write(_AD_.MAPort, _AD_.TestVar.RC2Value_Value, Case[15].value) Write(_AD_.MAPort, _AD_.TestVar.IN_ChrgOffButton_Value, Case[16].value) Write(_AD_.MAPort, _AD_.TestVar.IN_PowON_Value, Case[17].value) time.sleep(Case[18].value) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, Case[19].value) time.sleep(Case[20].value) #Result Result.append(Read(_AD_.MAPort, _AD_.TestVar.RX_VCU_Tx_stDCDCEn)) Result.append(Read(_AD_.MAPort, _AD_.TestVar.RX_VCU_Tx_powMaxPerm)) Result.append(Read(_AD_.MAPort, _AD_.TestVar.RX_VCU_Tx_stChrgFed)) Result.append(Read(_AD_.MAPort, _AD_.TestVar.O_S_BMSPwrOn_0_1__Out)) Result.append(Read(_AD_.MAPort, _AD_.TestVar.O_S_MCUPwrOn_0_1__Out)) Result.append(Read(_AD_.MAPort, _AD_.TestVar.O_S_DCPwrOn_0_1__Out)) Result.append(Read(_AD_.MAPort, _AD_.TestVar.O_S_VCUst_0_1__Out)) Result.append(Read(_AD_.MAPort, _AD_.TestVar.O_S_OBCPwrOn_0_1__Out)) time.sleep(1) Result.append(Read(_AD_.MAPort, _AD_.TestVar.O_S_ACPDURly_ON_0_1__Out)) #Result Judge item+=1 j=0 for i in range(21,30): if Result[i-21]==Case[i].value: pass else: j=j+1 Case[i].comment=Comment('Actual Result:'+str(Result[i-21]), 'Fail') Case[i].font=Font(color=colors.RED) if j==0: Case[30].value='Pass' data=json.dumps([str(item),'Pass']) server_socket.sendto(data,address) else: Case[30].value='Fail' data=json.dumps([str(item),'Fail']) server_socket.sendto(data,address) sleep(1) else: Case[30].value='进入初始条件Fail' print("初始条件进入Fail,case %s"%sum_num) data=json.dumps([str(item),'Fail']) for j in range(2,5): if check_pre_list[j-2] ==Case[j].value: pass else: Case[j].comment=Comment('Actual Result:'+str(check_pre_list[j-2]), 'Fail') Case[j].font=Font(color=colors.RED) #TestCleanUp Write(_AD_.MAPort, _AD_.TestVar.IN_KeyON_Value, 0) Write(_AD_.MAPort, _AD_.TestVar.IN_PowON_Value, 0) Write(_AD_.MAPort, _AD_.TestVar.IN_12Voltage_Value, 12) Write(_AD_.MAPort, _AD_.TestVar.RCValue_Value, 0) Write(_AD_.MAPort, _AD_.TestVar.RC2Value_Value, 0) Write(_AD_.MAPort, _AD_.TestVar.IN_KeyStart_Value, 0) time.sleep(2) sum_num +=1 #测试报告保存 ExcuteTime=time.strftime("%Y_%m_%d_%H_%M_%S", time.localtime()) TC.save("C:\\03_TestCase\\BenShaw\\ATC\\VCU_TC_LvPwrOff_"+ExcuteTime+".xlsx") stop_time = time.time() ctime = stop_time-start_time if ctime<60: min = 0 sec = int(ctime % 60) else: min = int(ctime / 60) sec = int(ctime % 60) print("此功能用例耗费时间 %s分钟%s秒"%(min,sec))