Python--Linux (ubuntu) Shell 环境 显卡测试 Python操作SQLSERVER测试数据交互

#coding=utf-8
import pymssql#加载SQLSERVER连接
import os#加载系统操作
import uuid#加载读取本机Mac Address
import sys#获取命令行参数

#定义全局变量
Temp="" #操作全局变量使用global A
Isn=""
IMac=""
BiosInfo=['','','','','']
BiosInfoValues=['','','','','']
BiosInfoArgs=['Product Name','Bios Config File','Bios P/N','Bios Version','Bios Date']
OrderName=""
BiosParticle_Number=0

def createsConnection(server,uid,pwd,database_str):#设置数据库连接
    conn=pymssql.connect(server=server,user=uid,password=pwd,database=database_str)
    print("\033[0m")
    return conn

def closeConnection(conn):#关闭数据库连接
    conn.close()

def Read_Diag_Test_Program_Name(conn,Stored_Str,n):#读取数据库对应键值字符串
    cursor=conn.cursor()
    cursor.execute(Stored_Str)
    row=cursor.fetchone()
    return str(row[n])

def InsertTestData(conn,SN,Mac_Address):#插入DIAG测试数据
    curs=conn.cursor()
    curs.callproc('usp_Insert_Graogucs_Card_Diag_Data',(SN,Mac_Address,1))
    conn.commit()
    curs.execute("select count(*) from Graogucs_Card_Diag where SN=%s",SN)
    for row in curs:
        if(row[0]==0):
            print("\033[31mInsert Diag Test Data Fail!!")#字体颜色设置为红色
            print("\033[0m")
            closeConnection(conn)
            return 1
        else:
            print("\033[32mInsert Diag Test Data Succeed!!")
            print("\033[0m")
            closeConnection(conn)
            return 0

def Insert_Upadte_BiosInfo(conn,DiagPackageName):#插入或更新线体BIOS临时信息
    My_Mac_Address=Get_MacAddress()
    Querying_Str="select * from ThePositionInformation where Mac_Address="+"'"+My_Mac_Address+"'"#查询该Mac_Address所在线体
    LineBody=Read_Diag_Test_Program_Name(conn, Querying_Str,1)
    curs=conn.cursor()
    curs.callproc('usp_InsertOrUpdate_NB_DiagTest_Args',(LineBody,BiosInfoValues[0],BiosInfoValues[1],BiosInfoValues[2],
                  BiosInfoValues[3],BiosInfoValues[4],DiagPackageName,1))
    conn.commit()
    curs.execute("select count(*) from NB_Diag_Test_Args where Code_Name=%s",LineBody)
    for row in curs:
        if(row[0]==0):
            print("\033[31mInsert Or Update Bios Info Temp Information Fail!!")
            print("\033[0m")
            closeConnection(conn)
            return 1
        else:
            print("\033[32mInsert Or Update Bios Info Temp Information Succeed!!")
            print("\033[0m")
            closeConnection(conn)
            return 0

def Querying_DataBase_MacAddress_IfThereIsa(conn):#查找数据库该MAC_Address是否存在
    My_Mac_Address=Get_MacAddress()
    curs=conn.cursor()
    curs.execute("select count(*) from ThePositionInformation where Mac_Address=%s", My_Mac_Address)
    for row in curs:
        if(row[0]==0):
            #print("\033[31m Querying Database Read Mac Information Fail!!")
            #print("\033[0m")
            closeConnection(conn)
            return 1
        else:
            #print("\033[32m Querying Database Read Mac Information Succeed!!")
            #print("\033[0m")
            closeConnection(conn)
            return 0

def Query_Section_Database_Exists(Section):#查询区间数据库是否存在
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    curs=conn.cursor()
    curs.execute("select * from JobNumber_Prefix")
    for row in curs:
        if row[1]==Section[:len(row[1])]:
            closeConnection(conn)
            return 0
    closeConnection(conn)
    return 1

def Insert_Employee_Information(JobNumber,LineBody,Mac_Address):#插入员工信息
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    curs=conn.cursor()
    sql_str="insert into ThePositionInformation values("+"'"+LineBody+"'"+",'L05-Diag',"+"'"+JobNumber+"'"+","+"'"+Mac_Address+"'"+",'020')"
    curs.execute(sql_str)
    conn.commit()
    curs.execute("select count(*) from ThePositionInformation where Mac_Address=%s",Mac_Address)
    for row in curs:
        if(row[0]==0):
            print("\033[31mInsert Employee Information Fail")
            print("\033[0m")
            closeConnection(conn)
            return 1
        else:
            print("\033[32mInsert Employee Information Succeed!!")
            print("\033[0m")
            closeConnection(conn)
            return 0

def Insert_Upadte_BiosInfo_2(conn,DiagPackageName,OrderName):#插入或更新当前线机程式Bios_1
    My_Mac_Address=Get_MacAddress()
    Querying_Str="select * from ThePositionInformation where Mac_Address="+"'"+My_Mac_Address+"'"#查询该Mac_Address所在线体
    #LineBody=Read_Diag_Test_Program_Name(conn, Querying_Str,1)
    curs=conn.cursor()
    curs.callproc('usp_InsertOrUpdate_NB_DiagTest_Args',(OrderName,BiosInfoValues[0],BiosInfoValues[1],BiosInfoValues[2],
                  BiosInfoValues[3],BiosInfoValues[4],DiagPackageName,1))
    conn.commit()
    curs.execute("select count(*) from NB_Diag_Test_Args where Code_Name=%s",OrderName)
    for row in curs:
        if(row[0]==0):
            print("\033[31mInsert Or Update Bios_1 Info Temp Information Fail!!")
            print("\033[0m")
            closeConnection(conn)
            return 1
        else:
            print("\033[32mInsert Or Update Bios_1 Info Temp Information Succeed!!")
            print("\033[0m")
            closeConnection(conn)
            return 0

def Insert_Upadte_BiosInfo_Bios2(conn,DiagPackageName,OrderName):#插入或更新当前线机程式Bios_2
    My_Mac_Address=Get_MacAddress()
    Querying_Str="select * from ThePositionInformation where Mac_Address="+"'"+My_Mac_Address+"'"#查询该Mac_Address所在线体
    #LineBody=Read_Diag_Test_Program_Name(conn, Querying_Str,1)
    curs=conn.cursor()
    curs.callproc('usp_Insert_Update_NB_Diag_Test_Args_Bios2',(OrderName,BiosInfoValues[0],BiosInfoValues[1],BiosInfoValues[2],
                  BiosInfoValues[3],BiosInfoValues[4],DiagPackageName,1))
    conn.commit()
    curs.execute("select count(*) from NB_Diag_Test_Args_Bios2 where Code_Name=%s",OrderName)
    for row in curs:
        if(row[0]==0):
            print("\033[31mInsert Or Update Bios_2 Info Temp Information Fail!!")
            print("\033[0m")
            closeConnection(conn)
            return 1
        else:
            print("\033[32mInsert Or Update Bios_2 Info Temp Information Succeed!!")
            print("\033[0m")
            closeConnection(conn)
            return 0

def Title():#显示标题
    os.system("clear")
    print("\033[37mProgram Read_Diag_Args Version.1.00")#将字体设置为灰色
    print("\033[37mProerty in Copyright Ferex ShenBo.")
    print("\033[37m------------------------------------------------------------------------------------")

def Help():#帮助信息
    os.system("clear")
    print("\033[37mProgram Version.1.00")
    print("\033[37mProperty in Copyright Ferex ShenBo.")
    print("\033[37m====================================================================================")
    print("\033[37m/Uploading               Uploading Test Data In DataBase!!")
    print("\033[37m/RdDiagPackageName       Read Diag Test Program Package Name!!")
    print("\033[37m/ChkBiosTest             Check Graphics Card Bios_1 Test!!")
    print("\033[37m/ChkBiosTest2            Check Graphics Card Bios_2 Test!!")
    print("\033[37m/InUpDiagArgs            Inset Or Update Graphics Card Diag Test Information!!")
    print("\033[37m/NowInUpDiagArgs         Now Inset Or Update Graphics Card Diag Bios_1 Test Information!!")
    print("\033[37m/NowInUpDiagArgs_Bios2   Now Inset Or Update Graphics Card Diag Bios_2 Test Information!!")
    print("\033[37m/InUpBiosParticleNumber  Insert Or Update Graphics Card Bios Particle Number!!")
    print("\033[37m/RedBiosParticleNumber   Read Graphics Card Bios Particle Number!!")
    print("\033[37m/RWVersionInfo           Read Local And Server Version Information Comparison!!")
    print("\033[37m/RWEmployeeInfo          Connection DataBase Read And Write Employee Information!!")
    print("\033[36mExample_1:*.exe /Uploading sn.dat")
    print("\033[36mExample_2:*.exe /RdDiagPackageName")
    print("\033[36mExample_3:*.exe /ChkBiosTest  BiosLog.dat")
    print("\033[36mExample_3:*.exe /ChkBiosTest2 BiosLog.dat")
    print("\033[36mExample_4:*.exe /InUpDiagArgs BiosLog.dat DiagPackage_Name")
    print("\033[36mExample_4:*.exe /NowInUpDiagArgs BiosLog.dat 'DiagPackage_Name' 'OrderName'")
    print("\033[36mExample_4:*.exe /NowInUpDiagArgs_Bios2 BiosLog.dat 'DiagPackage_Name' 'OrderName'")
    print("\033[36mExample_5:*.exe /InUpBiosParticleNumber 'Code_Name' 'Bios_Particle_Number'")
    print("\033[36mExample_6:*.exe /RedBiosParticleNumber")
    print("\033[36mExample_7:*.exe /RWVersionInfo 'ServerTextFileName' 'LocalTextFileName'")
    print("\033[36mExample_7:*.exe /RWEmployeeInfo")
    print("\033[0m")#恢复当前的颜色

def Write_BathFile(FileName,Str):#将字符串写入文件中
    with open(FileName,'w',encoding='utf-8') as fw:
        fw.write(Str)
    fw.closed

def Read_CfgFile(FileName):#读取配置文件
    with open(FileName,'r',encoding='utf-8') as fr:
        Temp=str(fr.read())
    fr.closed
    return Temp

def Get_MacAddress():#读取本机Mac_Address
    Mac=uuid.UUID(int=uuid.getnode()).hex[-12:].upper()
    return "".join([Mac[e:e+2] for e in range(0,11,2)])

def Get_Bios_Info(Str):#读取BIOS信息
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    My_Mac_Address=Get_MacAddress()
    Querying_Str="select * from ThePositionInformation where Mac_Address="+"'"+My_Mac_Address+"'"#查询该Mac_Address所在线体
    LineBody=Read_Diag_Test_Program_Name(conn, Querying_Str,1)
    if LineBody.strip():#判断返回的Querying_Str字符串是否为空
        Querying_Str="select * from Line_Body_Program where Line_BOdy="+"'"+LineBody+"'"#查询该线体产品订单号
        Code_Name_Str=Read_Diag_Test_Program_Name(conn,Querying_Str,1)
        if Code_Name_Str.strip():#判断返回的Code_Name_Str字符串是否为空
            Querying_Str=Str+"'"+Code_Name_Str+"'"#查询调用Diag测试包名称
            cursor=conn.cursor()
            cursor.execute(Querying_Str)
            row=cursor.fetchone()
            i=0
            global BiosInfo
            while(i<5):
                BiosInfo[i]=str(row[i+2])
                if not str(BiosInfo[i]).strip():
                    print("\033[31mRead Order Number ",Code_Name_Str," DataBase BiosInfo ",i," Is Null",)
                    return 1
                i+=1
            print("\033[32mRead Order Number ",Code_Name_Str," DataBase BiosInfo Succeed!!")
            return 0
        else:
            print("\033[31mQuerying DataBase Read Code_Name is Null")
            closeConnection(conn)
            return 2
    else:
        print("\033[31mQuerying DataBase Read LineBody is Null")
        closeConnection(conn)
        return 3

def ReadTextStore(FileName):#读取字符串并存储于集合中返回
    with open(FileName,"r",encoding='utf-8') as rf:
        list=rf.readlines()
    rf.closed
    return list

def ReadTestLog(FileName):#读取BIOS INFO LOG File Info
    Title()
    global BiosInfoValues
    t=0
    with open(FileName,'r',encoding='utf-8') as rf:
        list=rf.readlines()
        for l in list:
            n=0
            while(n<len(BiosInfoArgs)):
                if(t==len(BiosInfoArgs)):
                    print("\033[32mRead ",FileName," Succeed!!")
                    print("\033[0m")
                    return 0
                else:
                    if BiosInfoArgs[t] in l:
                        l=l[l.find(":")+1:l.find("\n")]
                        s=0
                        while(l[s]==" "):
                            s+=1
                        l=l[s:]
                        e=len(l)-1
                        while(l[e]==" "):
                            e-=1
                        l=l[:e+1]
                        BiosInfoValues[t]=l
                        t+=1
                        break
                n+=1
    rf.closed
    if(t<len(BiosInfoArgs)):
        print("\033[31m Read ",FileName," Fail!!")
        print("\033[0m")
        return 1

def ReadDiagPackageName():#读取Diag包名称
    Title()
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    My_Mac_Address=Get_MacAddress()
    Querying_Str="select * from ThePositionInformation where Mac_Address="+"'"+My_Mac_Address+"'"#查询该Mac_Address所在线体
    LineBody=Read_Diag_Test_Program_Name(conn, Querying_Str,1)
    if LineBody.strip():#判断返回的Querying_Str字符串是否为空
        Querying_Str="select * from Line_Body_Program where Line_BOdy="+"'"+LineBody+"'"#查询该线体产品订单号
        Code_Name_Str=Read_Diag_Test_Program_Name(conn,Querying_Str,1)
        if Code_Name_Str.strip():#判断返回的Code_Name_Str字符串是否为空
            Querying_Str="select * from NB_Diag_Test_Args where Code_Name="+"'"+Code_Name_Str+"'"#查询调用Diag测试包名称
            Call_Diag_Name_Str=Read_Diag_Test_Program_Name(conn,Querying_Str,7)
            if Call_Diag_Name_Str.strip():#判断返回的Call_Diag_Name_St字符串是否为空
                closeConnection(conn)
                Write_BathFile("fpath",Call_Diag_Name_Str)
                print("\033[32mRead DataBase Call_Diag_Name Succeed!!")
                closeConnection(conn)
                return 0
            else:
                print("\033[31mQuerying DataBase Read Call_Diag_Name is Null")
                closeConnection(conn)
                return 1
        else:
            print("\033[31mQuerying DataBase Read Code_Name is Null")
            closeConnection(conn)
            return 2
    else:
        print("\033[31mQuerying DataBase Read LineBody is Null")
        closeConnection(conn)
        return 3

def UploadingTestData_In_DataBase(Isn_FileName):#上传测试数据至数据库
    Title()
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    My_Mac_Address=Get_MacAddress()
    Isn=Read_CfgFile(Isn_FileName)
    if(InsertTestData(conn,Isn,My_Mac_Address)==1):
        print("\033[31mUpload Test Data In DataBase Fail!!")
        print("\033[0m")
        return 1
    else:
        print("\033[32mUpload Test Data In DataBase Succeed!1")
        print("\033[0m")
        return 0

def CheckBiosInfo(LogFileName,x):#核对BIOS信息
    n=0
    if (x==1):
        if(Get_Bios_Info("select * from NB_Diag_Test_Args where Code_Name=")==0):
            if(ReadTestLog(LogFileName)==0):
                while(n<len(BiosInfo)):
                    if(BiosInfo[n]!=BiosInfoValues[n]):
                        print("\033[31m Check Bios Info ",BiosInfoArgs[n],":",BiosInfo[n]," Fail!!")
                        print("\033[0m")
                        return 1
                    n+=1
                print("\033[32n Check Bios Info All Items Pass!!")
                print("\033[0m")
                return 0
            else:
                return 1
        else:
            return 1
    if (x==2):
        if(Get_Bios_Info("select * from NB_Diag_Test_Args_Bios2 where Code_Name=")==0):
            if(ReadTestLog(LogFileName)==0):
                while(n<len(BiosInfo)):
                    if(BiosInfo[n]!=BiosInfoValues[n]):
                        print("\033[31m Check Bios Info ",BiosInfoArgs[n],":",BiosInfo[n]," Fail!!")
                        print("\033[0m")
                        return 1
                    n+=1
                print("\033[32n Check Bios Info All Items Pass!!")
                print("\033[0m")
                return 0
            else:
                return 1
        else:
            return 1

def Inset_Update_NB_DiagTest_Args(LogFileName,DiagPackageName,n):#插入或更新蓝宝Diag测试参数
    Title()
    if(ReadTestLog(LogFileName)==0):
        conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
        if n==1:
            if(Insert_Upadte_BiosInfo(conn,DiagPackageName)==0):
                return 0
            else:
                return 1
        elif n==2:
            if(Insert_Upadte_BiosInfo_2(conn,DiagPackageName,OrderName)==0):
                return 0
            else:
                return 1
        elif n==3:
            if(Insert_Upadte_BiosInfo_Bios2(conn,DiagPackageName,OrderName)==0):
                return 0
            else:
                return 1
    else:
        return 1

def Insert_Update_BiosParticle_Number_Data(Code_Name,Bios_Particle_Number):#插入或更新对应订单号BIOS颗粒数
    Title()
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    curs=conn.cursor()
    curs.callproc("usp_Insert_Update_Bios_Particle_Number_Data_Info",(Code_Name,Bios_Particle_Number,1))
    conn.commit()
    curs.execute("select count(*) from BIOS_Particle_Number_Data where Code_Name=%s",Code_Name)
    for row in curs:
        if(row[0]==0):
            print("\033[31mInsert Or Update Bios Particle Number Data Information Fail!!")#字体颜色设置为红色
            print("\033[0m")
            closeConnection(conn)
            return 1
        else:
            print("\033[32mInset Or Update Bios Particle Number Data Information Succeed!!")
            print("\033[0m")
            closeConnection(conn)
            return 0

def ReadBiosParticle_Number():#读取BIOS颗粒数
    Title()
    My_Mac_Address=Get_MacAddress()
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    curs=conn.cursor()
    conn.commit()
    Querying_Str="select * from ThePositionInformation where Mac_Address="+"'"+My_Mac_Address+"'"#查询该Mac_Address所在线体
    LineBody=Read_Diag_Test_Program_Name(conn, Querying_Str,1)
    if LineBody.strip():#判断返回的Querying_Str字符串是否为空
        Querying_Str="select * from Line_Body_Program where Line_BOdy="+"'"+LineBody+"'"#查询该线体产品订单号
        Code_Name_Str=Read_Diag_Test_Program_Name(conn,Querying_Str,1)
        if Code_Name_Str.strip():#判断返回的Code_Name_Str字符串是否为空
            curs.execute("select count(*) from BIOS_Particle_Number_Data where Code_Name=%s",Code_Name_Str)
            global BiosParticle_Number
            for row in curs:
                if(row[0]<=0):
                    BiosParticle_Number=0
                    closeConnection(conn)
                    return 0
                else:
                    curs.execute("select * from BIOS_Particle_Number_Data where Code_Name=%s",Code_Name_Str)
                    row=curs.fetchone()
                    BiosParticle_Number=row[2]
                    closeConnection(conn)
                    return 0
        else:
            print("\033[31mQuerying DataBase Read Code_Name is Null")
            closeConnection(conn)
            return 2
    else:
        print("\033[31mQuerying DataBase Read LineBody is Null")
        closeConnection(conn)
        return 3
    #curs.execute("select count(*) from BIOS_Particle_Number_Data where Code_Name=%s",OrderName)
    #for row in curs:

def Version_File_Comparison(ServerFileName,ThisLocalityFileName):#本地版本文件与服务器版本文件比较
    ServerFileStr=ReadTextStore(ServerFileName)
    ThisLocalityStr=ReadTextStore(ThisLocalityFileName)
    if len(ServerFileStr)>0 and len(ThisLocalityStr)>0:
        if(len(ServerFileStr)==len(ThisLocalityStr)):
            i=0
            return 0
        else:
            print("\033[31mThe Current Version Of The File Is Not Up To Date And The Latest Version Needs To Be Updated")
            print("\033[0m")
            return 1
    else:
        print("\033[31mThe Contents Of The Local Version File Are Empty!!")
        print("\033[0m")
        return 1

def Connection_DataBase_RWEmployee():#读写员工信息
    Title()
    conn=createsConnection("192.167.10.12\server2","sa","adminsystem","E_Graphics_Card")
    if(Querying_DataBase_MacAddress_IfThereIsa(conn)==1):
        Job_Number=input("\033[33mPlease Enter Your Job Number:")
        while(len(Job_Number)<6 or (Query_Section_Database_Exists(Job_Number))==1):
             Title()
             Job_Number=input("\033[33mPlease Enter Your Job Number:")
        My_Mac_Address=Get_MacAddress()
        LineBody=Read_CfgFile("Line_Body.dat")
        if(Insert_Employee_Information(Job_Number,LineBody,My_Mac_Address)==0):
            return 0
        else:
            return 1
    else:
        return 0


#def Bios_Particle_Number_Data_WU()
if(len(sys.argv))>=2:
    if sys.argv[1] in "/Uploading" and len(sys.argv)==3:
        if(UploadingTestData_In_DataBase(sys.argv[2])==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/RdDiagPackageName":
        if(ReadDiagPackageName()==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/RedBiosParticleNumber":
        if(ReadBiosParticle_Number()==0):
            Write_BathFile("BsPe_Number",str(BiosParticle_Number))
            print("\033[32m Read And Insert Bios Particle Number Succeed!!")
            print("\033[0m")
            exit(0)
        else:
            print("\033[31m Read And Insert Bios Particle Number Fail!!")
            print("\033[0m")
            exit(1)
    elif sys.argv[1] in "/ChkBiosTest" and len(sys.argv)==3:
        if(CheckBiosInfo(sys.argv[2],1)==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/ChkBiosTest2" and len(sys.argv)==3:
        if(CheckBiosInfo(sys.argv[2],2)==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/InUpDiagArgs" and len(sys.argv)==4:
        if(Inset_Update_NB_DiagTest_Args(sys.argv[2],sys.argv[3],1)==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/NowInUpDiagArgs" and len(sys.argv)==5:
        OrderName=sys.argv[4]
        if(Inset_Update_NB_DiagTest_Args(sys.argv[2],sys.argv[3],2)==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/NowInUpDiagArgs_Bios2" and len(sys.argv)==5:
        OrderName=sys.argv[4]
        if(Inset_Update_NB_DiagTest_Args(sys.argv[2],sys.argv[3],3)==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/InUpBiosParticleNumber" and len(sys.argv)==4:
        if(Insert_Update_BiosParticle_Number_Data(sys.argv[2],sys.argv[3])==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/RWVersionInfo" and len(sys.argv)==4:
        if(Version_File_Comparison(sys.argv[2],sys.argv[3])==0):
            exit(0)
        else:
            exit(1)
    elif sys.argv[1] in "/RWEmployeeInfo":
        if(Connection_DataBase_RWEmployee()==0):
            exit(0)
        else:
            exit(1)
    else:
        Help()
        exit(1)
else:
    Help()
    exit(1)