python sqlserver数据库操作(附件使用于ubuntu14.04环境)

#coding=utf-8
import pymssql

class SQLServer:
    def __init__(self,server,user,password,database):
    # 类的构造函数，初始化DBC连接信息
        self.server = server
        self.user = user
        self.password = password
        self.database = database
        self.Lin_Body=""#线体
        self.Working_Procedure=""#工序
        self.Item_Name=""#产品名称
        self.Burn_In_BIOS_Product_Name=""#bios烧录工序参数
        self.CheckBios_Test_Product_Name=""#Check BIOS参数
        self.Diag_Test_Product_Name=""#Diag测试参数
        self.Bios_Count=0#bios颗粒数
        self.BIOS1_NAME=""#bios1文件名称
        self.BIOS2_NAME=""#bios2文件名称
        self.BProduct_Name=""#BIOS名称
        self.Bios_Config_File=""#BIOS配置
        self.Bios_PN=""#BIOS PN
        self.Bios_Version=""#Bios版本
        self.Bios_Date=""#Bios时间
        self.DiagPackage_Path=""#Diag测试包的路径
        self.DiagRunArgs=""#Diag测试程式运行参数
        self.SN=""#SN条码获取
        self.EMSMissTheStation_Info=""#EMS过站数据
    def __GetConnect(self):
    # 得到数据库连接信息，返回conn.cursor()
        if not self.database:
            raise(NameError,"没有设置数据库信息")
        self.conn = pymssql.connect(server=self.server,user=self.user,password=self.password,database=self.database,charset='utf8')
        cur = self.conn.cursor()
        if not cur:
            raise(NameError,"连接数据库失败")  # 将DBC信息赋值给cur
        else:
            return cur

    def ExecQuery(self,sql):
        '''
        执行查询语句
        返回一个包含tuple的list，list是元素的记录行，tuple记录每行的字段数值
        '''
        try:
            cur = self.__GetConnect()
            cur.execute(sql) # 执行查询语句
            result = cur.fetchall() # fetchall()获取查询结果
            # 查询完毕关闭数据库连接
            self.conn.close()
            return result
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return ""
    def Uploading_ChkBiosInfo(self,Product_Name,Args_1,Args_2,Args_3,Args_4,Args_5,Args_6):
        """
        上传Checkbios字符串
        Product_Name:产品名称
        Args_1:
        """
        try:
            #self.conn=pymssql.connect(server=self.server,user=self.user,password=self.password,database=self.database)
            cursor=self.__GetConnect()
            cursor.callproc('Usp_Insert_Diag_Check_BIOS_Args',(Product_Name,Args_1,Args_2,Args_3,Args_4,Args_5,Args_6,1))
            self.conn.commit()#提交修改
            self.conn.close()
            print("\033[32m"+Product_Name+" Test Data Uploading Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0

    def Uploading_DiagTestPlatform_Args(self,Lin_Body,Working_Procedure,Item_Name,Mac_Address):
        """
        上传DIAG测试平台参数
        Lin_Body:线体
        Working_Procedure:工序
        Item_Name:项目名
        """
        try:
            cursor=self.__GetConnect()
            cursor.callproc('usp_Insert_Diag_Test_platform_Config',(Lin_Body,Working_Procedure,Item_Name,Mac_Address,1))
            self.conn.commit()#提交修改
            self.conn.close()
            print("\033[32m"+Lin_Body+" Information Uploading Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0

    def Uploading_DiagTestPlatform_Config(self,MAC_Address,The_Machine_Numbe,Lin_Body):
        """
        上传DIAG测试平台信息
        MAC_Address:mac地址
        The_Machine_Numbe:平台编号
        Lin_Body:线体
        """
        try:
            cursor=self.__GetConnect()
            cursor.callproc('usp_Insert_Test_PlatFrom_Info_Diag',(MAC_Address,The_Machine_Numbe,Lin_Body,1))
            self.conn.commit()#提交修改
            self.conn.close()
            print("\033[32m"+MAC_Address+" Test Diag Platform Information Uploading Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0

    def Uploading_DiagTempTestData(self,Product_Name,Working_Procedure,Mac_Address,SN):
        """
        上传DIAG测试临时数据
        Product_Name:产品名称
        Working_Procedure:工序，diag\windows
        Mac_Address:mac address
        SN:SN条码
        """
        try:
            cursor=self.__GetConnect()
            cursor.callproc('usp_Insert_Diag_Temp_TestData',(Product_Name,Working_Procedure,Mac_Address,SN,1))
            self.conn.commit()#提交修改
            self.conn.close()
            print("\033[32m"+Product_Name+" Test Data Uploading Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0
    def Uploading_Graphics_Card_ID_TieYards(self,Asic_Id,Sn):
        """
        上传显卡id+sn绑码数据
        Asic_ID:显卡ID
        SN:显卡条码
        """
        try:
            cursor=self.__GetConnect()
            cursor.callproc('usp_Insert_Graphics_Card_ID',(Asic_Id,Sn,1))
            self.conn.commit()#提交修改
            self.conn.close()
            print("\033[32m"+Asic_Id+" And "+Sn+" TieYards Data Uploading Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0
    def Uploading_Querying_EMS_MissTheStation(self,SN):
        """
        上传显卡关键查询SN过站信息到数据查
        SN:显卡SN条码
        """
        try:
            cursor=self.__GetConnect()
            cursor.callproc("Usp_Insrt_EMS_MissTheStation",(SN,"1",1))
            self.conn.commit()#提交修改
            self.conn.close()
            print("\033[32m"+SN+" Querying EMS MissTheStation Data Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033"+e+"\033[0m")
            return 1
        return 0
    def Get_UbuntuTest_Information_Platform(self,Mac_Address):
        """
        通过Mac获取平台信息
        Mac_Address:测试主板网卡ID
        self.Lin_Body:获取的线体信息
        self.Working_Procedure:获取的工序信息
        self.Item_Name:获取的产品名称
        """
        try:
            cursor=self.__GetConnect()
            cursor.execute("select * from Diag_Test_platform_Config where Mac_Address="+"'"+Mac_Address+"'")
            row=cursor.fetchone()
            self.Lin_Body=str(row[1])
            self.Working_Procedure=str(row[2])
            self.Item_Name=str(row[3])
            self.conn.close()
            print("\033[32m"+Mac_Address+" Test Platform Information Get Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0

    def Get_UbuntuTest_Lin_Body(self,Lin_Body):
        """
            通过线体获取线体信息
            self.Burn_In_BIOS_Product_Name:烧录BIOS参数项目名称
            self.CheckBios_Test_Product_Name:Check BIOS参数项目名称
            self.CheckBios_Test_Product_Name:DIAG测试参数项目名称
            self.Bios_Count:BIOS数量
        """
        try:
            cursor=self.__GetConnect()
            cursor.execute("select * from Graphics_Card_Config where Lin_Body="+"'"+Lin_Body+"'")
            row=cursor.fetchone()
            self.Burn_In_BIOS_Product_Name=str(row[2])
            self.CheckBios_Test_Product_Name=str(row[3])
            self.Diag_Test_Product_Name=str(row[4])
            self.Bios_Count=int(row[5])
            self.conn.close()
            print("\033[32m"+Lin_Body+" Information Get Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0

    def Get_Ubuntu_Brun_In_Args(self,Product_Name):
        """
        通过产品名称获取BIOS烧录参数
        self.BIOS1_NAME:烧录BIOS1名称
        self.BIOS2_NAME:烧录BIOS2名称
        self.Bios_Count:BIOS颗粒数
        """
        try:
            cursor=self.__GetConnect()
            cursor.execute("select * from Brun_InBiosArgs where Product_Name="+"'"+Product_Name+"'")
            row=cursor.fetchone()
            self.BIOS1_NAME=str(row[2])
            self.BIOS2_NAME=str(row[3])
            self.Bios_Count=int(row[4])
            self.conn.close()
            print("\033[32m"+Product_Name+" Ubuntu Bios Brun_In Args Get Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0

    def Get_Diag_CheckBios_Args(self,Product_Name):
        """
        通过产品名称获取BIOS参数
        self.BProduct_Name:BIOS名称
        self.Bios_Config_File:BIOS配置
        self.Bios_PN:BIOS PN
        self.Bios_Version:BIOS版本
        self.Bios_Date:BIOS时间
        self.Bios_Count:BIOS颗粒数
        """
        try:
            cursor=self.__GetConnect()
            cursor.execute("select * from Diag_Check_BIOS_Args where Product_Name="+"'"+Product_Name+"'")
            row=cursor.fetchone()
            self.BProduct_Name=str(row[2])
            self.Bios_Config_File=str(row[3])
            self.Bios_PN=str(row[4])
            self.Bios_Version=str(row[5])
            self.Bios_Date=str(row[6])
            self.Bios_Count=int(row[7])
            self.conn.close()
            print("\033[32m"+Product_Name+" Ubuntu Check Bios Args Get Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0

    def Get_Diag_Test_Args(self,Product_Name):
        """
        通过产品名称获取DIAG测试参数
        self.DiagPackage_Path:Diag测试包路径
        self.DiagRunArgs:Diag测试程式运行参数
        """
        try:
            cursor=self.__GetConnect()
            cursor.execute("select * from Diag_Test_Args where Product_Name="+"'"+Product_Name+"'")
            row=cursor.fetchone()
            self.DiagPackage_Path=str(row[2])
            self.DiagRunArgs=str(row[3])
            self.conn.close()
            print("\033[32m"+Product_Name+" Ubuntu Diag Test Args Get Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0
    def Get_SnBarCode(self,Asic_Id):
        """
        通过Asic_Id获取sn条码
        Asic_Id:显卡ID
        self.SN:显卡SN条码
        """
        try:
            cursor=self.__GetConnect()
            cursor.execute("select * from Graphics_Card_ID_TieYards where Graphics_Card_Id="+"'"+Asic_Id+"'")
            row=cursor.fetchone()
            self.SN=str(row[2])
            self.conn.close()
            print("\033[32m"+Asic_Id+" Sn Bar Code Get Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0
    def Get_EMS_MissTheStation_Info(self,SN):
        """
        通过SN查询EMS开机测试过站信息
        SN:显卡SN条码
        self.EMSMissTheStation_Info:EMS过站信息
        """
        try:
            cursor=self.__GetConnect()
            cursor.execute("select * from EMS_MissTheStation where SN="+"'"+SN+"'")
            row=cursor.fetchone()
            self.EMSMissTheStation_Info=str(row[2])
            self.conn.close()
            print("\033[32m"+SN+" Ems Miss The Station Information Get Succeed!!\033[0m")
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1
        return 0
    def Compare_BiosInfo(self,BProduct_Name,Bios_Config_File,Bios_PN,Bios_Version,Bios_Date,Bios_Count):
        """
        比较BIOS信息并返回0或者1
        BProduct_Name:BIOS产品名称
        Bios_Config_File：BIOS Config 文件名
        Bios_PN:Bios PN文件
        Bios_Version:Bios版本
        Bios_Date:Bios时间
        Bios_Count:BIOS颗粒数
        """
        try:
            cursor=self.__GetConnect()
            Sql_str="select count(*) from Diag_Check_BIOS_Args where Bios_Info_1="+"'"+BProduct_Name+"'"+" and Bios_Info_2="+"'"+Bios_Config_File+"'"+" and Bios_Info_3="+"'"+Bios_PN+"'"+" and Bios_Info_4="+"'"+Bios_Version+"'"+" and Bios_Info_5="+"'"+Bios_Date+"'"+" and Bios_Serial_Number="+"'"+Bios_Count+"'"
            print(Sql_str)
            cursor.execute(Sql_str)
            for row in cursor:
                if(row[0]>=1):
                    print("\033[32m Check "+BProduct_Name+" Bios_"+Bios_Count+" Pass\033[0m")
                    self.conn.close()
                    return 0
                else:
                    print("\033[31m Check "+BProduct_Name+" Bios_"+Bios_Count+" Fail\033[0m")
                    self.conn.close()
                    return 1
        except ZeroDivisionError as e:
            print("\033[31m"+e+"\033[0m")
            return 1