基于Python pands和paramiko的自动化测试

背景

最近测试工作中使用到Linux环境，并且测试用例需要重复执行一些命令或脚本。于是想到如果能有一个自动化工具来替我执行就好好了。然后有了一个初步的解决方案。

方案构思

1. 创建connect_linux类，用于创建ssh连接，登录Linux环境，执行cmd，接收回显。使用paramiko库。
2. 创建readExcel类，用于读取测试用例，例如hostip，port，username，password，commands 等。使用pandas库。
3. 创建testcase类，用于实现执行测试用例的过程。
4. 创建testlog类，用于记录执行用例过程日志。使用logging库。
5. 创建report类，用于记录用例执行结果。暂时使用open函数。

实现过程

Python工程结构如下

1.connect_linux类

首先安装paramiko库

> pip install paramiko

然后实现类

import time

import paramiko

from log import testlog
testlog = testlog.testlog().logger
class connect_linux:
    def __init__(self, ip, port, username, password):
        self.channel = None
        self.ssh = None
        self.hostIp = ip
        self.hostport = port
        self.username = username
        self.password = password

    def connect(self):
        # 创建ssh对象
        self.ssh = paramiko.SSHClient()
        # 自动添加策略，保存服务器的主机名和密钥信息，如果不添加，那么不再本地know_hosts文件中记录的主机将无法连接
        self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        # 建立连接
        try:
            self.ssh.connect(hostname=self.hostIp, port=self.hostport, username=self.username, password=self.password)
        except:
            testlog.info("连接{}失败".format(self.hostIp))
            print("连接{}失败".format(self.hostIp))
        else:
            # 创建channel对象
            self.channel = self.ssh.invoke_shell()

    # 发送命令
    def send_command(self, command):
        command += '\n'
        self.channel.send(command)

    # 接收回显
    def recv_msg(self):
        result = ''
        while self.channel.recv_ready():
            re = self.channel.recv(65535).decode('UTF-8')
            result += re
            time.sleep(0.2)
        return result

    # 关闭通道和连接
    def close_ssh(self):
        self.channel.close()
        self.ssh.close()

2.实现readExcel类

首先安装pandas库

> pip install pandas

pandas会默认依赖xlrd

pip install xlrd==1.1.0

然后实现类

import pandas


class readExcel:

    def __init__(self, filename):
        self.filename = filename

    # 根据sheetname和行号，返回每一行的值
    def read_row_values(self, sheetname, row):
        df = pandas.DataFrame(pandas.read_excel(self.filename, sheet_name=sheetname, header=0))
        return df.values[row]

    # 读取Excel中sheet的名字，并通过列表返回
    def read_sheetnames(self):
        sheet = pandas.read_excel(self.filename, sheet_name=None)
        return list(sheet)

    # 读取sheet页中的总行数
    def read_rows(self, sheetname):
        df = pandas.DataFrame(pandas.read_excel(self.filename, sheet_name=sheetname, header=0))
        return df.shape[0]

3.实现testcase类

import logging
import time

from readExcel import readExcel
from sshclient import connect_linux

from log import testlog
from report import report

testlog = testlog.testlog().logger
report = report.report()


class testcase:

    def exec_testcase(self, filename):
        read_excel = readExcel.readExcel(filename)
        sheetnames = read_excel.read_sheetnames()
        row = read_excel.read_rows(sheetnames[0])
        # 依次读取每一数据，并执行命令
        for i in range(row):
            row_values = read_excel.read_row_values(sheetnames[0], i)
            case_num = row_values[0]
            case_name = row_values[1]
            case_step = row_values[2]
            step_description = row_values[3]
            ip = row_values[4]
            port = row_values[5]
            username = row_values[6]
            password = str(row_values[7])
            commands = row_values[8]
            expect_values = row_values[9].split('|')
            flush_time = row_values[10]
            max_time = row_values[11]
            log = ''
            if i == 0:
                log = "*****************************************\n"
                log += "start exec testcase: " + case_num + " " + case_name + "\n"
            else:
                pre_case_name = read_excel.read_row_values(sheetnames[0], i - 1)[1]
                if case_name != pre_case_name:
                    log = "*****************************************\n"
                    log += "start exec testcase: " + case_num + " " + case_name + "\n"
            testlog.info(log)
            report.open_report().write(log)

            # 实例化ssh，建立连接，发送命令，接收回显
            log = 'start connect {}'.format(ip)
            testlog.info(log)
            ssh = connect_linux.connect_linux(ip, port, username, password)
            ssh.connect()
            ssh.send_command(commands)
            flag_success = False
            duration = 0
            # 循环验证结果，回显判断成功，或超时即退出循环
            while True:
                res = ssh.recv_msg()
                testlog.info(res)
                for expect_value in expect_values:
                    if expect_value in res:
                        log = case_num + " " + case_name + " " + case_step + " " + step_description + " " + 'run success'
                        testlog.info(log)
                        report.open_report().write(log)
                        print(case_num, case_name, case_step, step_description, 'run success')
                        flag_success = True
                        break
                # 回显判断成功，即退出循环
                if flag_success:
                    break
                if duration > max_time:
                    log = case_num + " " + case_name + " " + case_step + " " + step_description + " " + 'run failed , timeout'
                    testlog.info(log)
                    report.open_report().write(log)
                    print(case_num, case_name, case_step, step_description, 'run failed , timeout')
                    break
                duration += flush_time
                time.sleep(flush_time)
            ssh.close_ssh()

        log = "finish!!!!!"
        logging.info(log)
        report.open_report().write(log)
        report.close_file()


if __name__ == '__main__':
    test001 = testcase()
    test001.exec_testcase(r'C:\Users\刘弘艳\Desktop\测试用例.xlsx')

4.实现testlog类

import logging
import os.path
from time import strftime


class testlog:
    # 日志输出路径
    PATH = os.path.abspath('..') + "/logs/"
    # 日志格式
    FMT = "%(asctime)s %(filename)s [line:%(lineno)d ] %(levelname)s: %(message)s"
    DATEFMT = "%Y-%m-%d %H:%M:%S"
    DATEFMT1 = "%Y%m%d%H%M%S"

    # 初始化logger
    def __init__(self):
        self.logger = logging.getLogger()
        self.formatter = logging.Formatter(fmt=self.FMT, datefmt=self.DATEFMT)
        self.filename = '{0}{1}.log'.format(self.PATH, strftime(self.DATEFMT1))

        self.logger.setLevel(logging.INFO)
        self.logger.addHandler(self.get_file_handler(self.filename))

    # 创建filehandler对象并实例化
    def get_file_handler(self, filename):
        filehandler = logging.FileHandler(filename, encoding='UTF-8')
        filehandler.setFormatter(self.formatter)
        return filehandler

5.实现report类

import os.path
from time import strftime

PATH = os.path.abspath('..') + '/reports/'
DATEFMT = "%Y%m%d%H%M%S"


class report:

    def __init__(self):
        self.report = None
        self.reportname = '{0}{1}.txt'.format(PATH, strftime(DATEFMT))

    def open_report(self):
        self.report = open(self.reportname, 'a')
        return self.report

    def close_file(self):
        self.report.close()

总结

1、使用paramiko库创建ssh连接时，首先要创建ssh对象，即

# 创建ssh对象
        self.ssh = paramiko.SSHClient()

然后要创建channel对象，即进入命令行环境

# 创建channel对象
            self.channel = self.ssh.invoke_shell()

2、使用pandas读取Excel时，pandas.DataFrame和pandas.read_excel结合使用更加灵活方便。
3、日志记录，目前的实现是会重复打印日志，暂时没有找到原因
4、用例执行完成后，应该关闭open函数。每执行完一个步骤，关闭ssh连接。

附用例模板