some working learning总结学习

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执行测试用例并读取返回结果）

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))