I Data separation: reading data from Excel

In the previous use cases, the data is directly written in the code file, which is not conducive to modifying and constructing the data. Here, we use Excel to save the test data, realize the separation of code and data, and create a new EXCEL file test_user_data.xlsx contains two workbooks, TestUserLogin and TestUserReg, and copies them to the project root directory.

to update: excel In the table,Add one headers column,The content is json format, as follows

II Excel reading method

Python we use the third-party library xlrd to read Excel, and use pip install xlrd to install xlrd.

import xlrd

wb = xlrd.open_workbook("test_user_data.xlsx")  # Open excel
sh = wb.sheet_by_name("TestUserLogin")  # Locate sheet by workbook name
print(sh.nrows)  # Number of valid data rows
print(sh.ncols)  # Number of valid data columns
print(sh.cell(0, 0).value)  # Output first row first column values `case_name`
print(sh.row_values(0))  # Output all values of line 1 (list format)

# Assemble data and titles into dictionaries to make data clearer
print(dict(zip(sh.row_values(0), sh.row_values(1))))

# Traverse excel and print all data
for i in range(sh.nrows):
    print(sh.row_values(i))

Output results

3
5
case_name
['case_name', 'url', 'method', 'data', 'expect_res']
{'case_name': 'test_user_login_normal', 'url': 'http://115.28.108.130:5000/api/user/login/','method':'post','data':'{"name": "Zhang San", "password": "123456"}','expect_ Res':' <h1> login succeeded </h1>'}
['case_name', 'url', 'method', 'data', 'expect_res']
['test_user_login_normal', 'http://115.28.108.130:5000/api/user/login/','post', '{"name": "Zhang San", "password": "123456"}', '<h1> login succeeded </h1>']
['test_user_login_password_wrong', 'http://115.28.108.130:5000/api/user/login/','post', '{"name": "Zhang San", "password": "1234567"}', '<h1> failed, the user does not exist </h1>']

III Encapsulate excel read operation

1. create a new read_excel.py
Our purpose is to obtain the data of a use case. Three parameters are required. The Excel data file name is data_ File, workbook name sheet, case name_ name. If we only encapsulate one function, we need to open excel and traverse it once for each call (each use case), which is inefficient. We can split it into two functions, one function excel_to_list(data_file, sheet) to obtain all the data of one worksheet at a time, and the other function get_ test_ Data (data\u list, case\u name) finds the data of this use case from all the data.

import xlrd

def excel_to_list(data_file, sheet):
    data_list = []  # Create an empty list to multiply and load all data
    wb = xlrd.open_workbook(data_file)  # Open excel
    sh = wb.sheet_by_name(sheet)  # Get Workbook
    header = sh.row_values(0)  # Get header row data
    for i in range(1, sh.nrows):  # Skip the header row and fetch data from the second row
        d = dict(zip(header, sh.row_values(i)))  # Assemble headings and each row of data into a dictionary
        data_list.append(d)
    return data_list  # List nested dictionary format, each element is a dictionary

def get_test_data(data_list, case_name):
    for case_data in data_list:
        if case_name == case_data['case_name']:  # If case in dictionary data_ Name is consistent with the parameter
            return case_data
            # If the query fails, it will return None

if __name__ == '__main__':   # Test your code
    data_list = excel_to_list("test_user_data.xlsx", "TestUserLogin")  # Read all data of excel, TestUserLogin Workbook
    case_data = get_test_data(data_list, 'test_user_login_normal')  # Find case'test_user_login_normal'data
    print(case_data)

Output results

{'case_name': 'test_user_login_normal', 'url': 'http://115.28.108.130:5000/api/user/login/','method':'post','data':'{"name": "Zhang San", "password": "123456"}','expect_ Res':' <h1> login succeeded </h1>'}

2. methods used in use cases (test_user_login.py)

import unittest
import requests
from read_excel import *  # Import read_ Methods in Excel
import json  # Used to convert json strings in excel into Dictionaries

class TestUserLogin(unittest.TestCase):
    @classmethod
    def setUpClass(cls):   # The entire test class is executed only once
        cls.data_list = excel_to_list("test_user_data.xlsx", "TestUserLogin")  # Read all case data of the test class
        # cls.data_list is the same as self data_ List is a public attribute of this class

    def test_user_login_normal(self):
        case_data = get_test_data(self.data_list, 'test_user_login_normal')   # Find the case data from the data list
        if not case_data:   # Probably None
            print("Case data does not exist")
        url = case_data.get('url')   # Take data from the dictionary, and the title in excel must also be a lowercase url
        data = case_data.get('data')  # Note the string format. You need to use json Loads() to dictionary format
        expect_res = case_data.get('expect_res')  # Expected data

        res = requests.post(url=url, data=json.loads(data))  # Form request, data converted to dictionary format
        self.assertEqual(res.text, expect_res)  # Change to assertEqual assertion

if __name__ == '__main__':   # Not necessary to test our code
    unittest.main(verbosity=2)

3. methods used in use cases (test\u user\u reg.py)

import unittest
import requests
from db import *
from read_excel import *
import json

class TestUserReg(unittest.TestCase):

    @classmethod
    def setUpClass(cls):
        cls.data_list = excel_to_list("test_user_data.xlsx", "TestUserReg")  # Read all data from TestUserReg Workbook

    def test_user_reg_normal(self):
        case_data = get_test_data(self.data_list, 'test_user_reg_normal')
        if not case_data:
            print("Case data does not exist")
        url = case_data.get('url')
        data = json.loads(case_data.get('data'))  # To convert to a dictionary, you need to check the database with the name in it
        expect_res = json.loads(case_data.get('expect_res'))  # Convert to dictionary, and directly assert whether the two dictionaries are equal when asserting
        name = data.get("name")  # Fan Bingbing

        # Environmental inspection
        if check_user(name):
            del_user(name)
        # Send request
        res = requests.post(url=url, json=data)  # You can also use data=data to send a string
        # Response assertion (overall assertion)
        self.assertDictEqual(res.json(), expect_res)
        # Database assertion
        self.assertTrue(check_user(name))
        # Environment cleanup (because the registration interface writes user information to the database)
        del_user(name)

if __name__ == '__main__':    # Not necessary to test our code
    unittest.main(verbosity=2)  

IV Add log function

1. create a new config Py file

import logging

logging.basicConfig(level=logging.DEBUG,  # log level
                    format='[%(asctime)s] %(levelname)s [%(funcName)s: %(filename)s, %(lineno)d] %(message)s',  # log format
                    datefmt='%Y-%m-%d %H:%M:%S',  # Date format
                    filename='log.txt',  # Log output file
                    filemode='a')  # append mode 

if __name__ == '__main__':
    logging.info("hello")

Generate log in the current directory after running Txt, as follows

[2018-09-11 18:08:17] INFO [<module>: config.py, 38] hello

2. log level

CRITICAL: used to output CRITICAL error information
ERROR: used to output ERROR information
WARNING: used to output WARNING information
INFO: used to output some promotion information
DEBUG: used to output some debugging information

3. log priority

CRITICAL > ERROR > WARNING > INFO > DEBUG

Specify level = logging DEBUG all information with a level greater than or equal to DEBUG will be output; If you specify level = logging Error warning, info, DEBUG information less than the setting level will not be output.

4. log format description

%(levelno)s: print log level values

%(levelname)s: print log level name

%(pathname)s: print the path of the current executing program, which is actually sys argv[0]

%(filename)s: print the name of the current executing program

%(funcName)s: current function for printing logs

%(lineno)d: current line number of the print log

%(actime) s: time to print the log

%(thread)d: print thread ID

%(threadName)s: print thread name

%(process)d: print process ID

%(message)s: print log information

5. use of logs in the project

db.py

import pymysql
from config import *

# Encapsulating database query operations
def query_db(sql):
    conn = get_db_conn()
    cur = conn.cursor()  
    logging.debug(sql)    # Output executed sql
    cur.execute(sql)
    conn.commit()
    result = cur.fetchall() 
    logging.debug(result)  # Output query results
    cur.close() 
    conn.close() 
    return result 

# Encapsulate change database operations
def change_db(sql):
    conn = get_db_conn() 
    cur = conn.cursor()
    logging.debug(sql)  # Output executed sql
    try:
        cur.execute(sql) 
        conn.commit() 
    except Exception as e:
        conn.rollback() 
        logging.error(str(e))  # Output error message
    finally:
        cur.close() 
        conn.close()

Use case

import unittest
import requests
from read_excel import *  # Import read_ Methods in Excel
import json  # Used to convert json strings in excel into Dictionaries
from config import *

class TestUserLogin(unittest.TestCase):
    @classmethod
    def setUpClass(cls):   # The entire test class is executed only once
        cls.data_list = excel_to_list("test_user_data.xlsx", "TestUserLogin")  # Read all case data of the test class
        # cls.data_list is the same as self data_ List is a public attribute of this class

    def test_user_login_normal(self):
        case_data = get_test_data(self.data_list, 'test_user_login_normal')   # Find the case data from the data list
        if not case_data:   # Probably None
            logging.error("Case data does not exist")
        url = case_data.get('url')   # The title in excel must also be a lowercase url
        data = case_data.get('data')  # Note the string format. You need to use json Loads() to dictionary format
        expect_res = case_data.get('expect_res')  # Expected data

        res = requests.post(url=url, data=json.loads(data))  # Form request, data converted to dictionary format
        logging.info("Test case:{}".format('test_user_login_normal'))
        logging.info("url: {}".format(url))
        logging.info("Request parameters:{}".format(data))
        logging.info("Expected results:{}".format(expect_res))
        logging.info("Actual results:{}".format(res.text)
        self.assertEqual(res.text, expect_res)  # Assertion

if __name__ == '__main__':
    unittest.main(verbosity=2)

Output results

[2018-09-13 10:34:49] INFO [log_case_info: case_log.py, 8] Test case: test_user_login_normal
[2018-09-13 10:34:49] INFO [log_case_info: case_log.py, 9] url: http://115.28.108.130:5000/api/user/login/
[2018-09-13 10:34:49] INFO [log_case_info: case_log.py, 10] Request parameters:{"name": "Zhang San","password":"123456"}
[2018-09-13 10:34:49] INFO [log_case_info: case_log.py, 11] Expected results:<h1>Login successful</h1>
[2018-09-13 10:34:49] INFO [log_case_info: case_log.py, 12] Actual results:<h1>Login successful</h1>

Because each use case needs to output a lot of log information, we encapsulate a case_log function

from config import *
import json

def log_case_info(case_name, url, data, expect_res, res_text): 
    if isinstance(data,dict):
        data = json.dumps(data, ensure_ascii=False)  # If data is in dictionary format, convert it to string
    logging.info("Test case:{}".format(case_name))
    logging.info("url: {}".format(url))
    logging.info("Request parameters:{}".format(data))
    logging.info("Expected results:{}".format(expect_res))
    logging.info("Actual results:{}".format(res_text)

Simplified use case log output

import unittest
import requests
from read_excel import *  
import json
from config import *
from case_log import log_case_info  # Import method

class TestUserLogin(unittest.TestCase):
    @classmethod
    def setUpClass(cls):  
        cls.data_list = excel_to_list("test_user_data.xlsx", "TestUserLogin") 

    def test_user_login_normal(self):
        case_data = get_test_data(self.data_list, 'test_user_login_normal') 
        if not case_data: 
            logging.error("Case data does not exist")
        url = case_data.get('url')  
        data = case_data.get('data') 
        expect_res = case_data.get('expect_res')

        res = requests.post(url=url, data=json.loads(data))
        log_case_info('test_user_login_normal', url, data, expect_res, res_text)  # Output case log information
        self.assertEqual(res.text, expect_res)  

if __name__ == '__main__':
    unittest.main(verbosity=2)

If you are interested in python automated testing, web automation, interface automation, mobile terminal automation, interview experience exchange, etc, You can click here to get

V Send mail

After generating the report, we hope that the framework can automatically send the report to our mailbox. Like outlook, foxmail and other mail clients, sending mail in Python needs to be sent through the smtp service of Email.

1. first, confirm whether the mailbox used to send mail has enabled the smtp service;

2. write Email content (Email requires special MIME format);

3. assemble Email header (sender, recipient, subject);

4. connect to the smtp server and send mail;

import smtplib  # Used to establish smtp connection
from email.mime.text import MIMEText  # The message requires a special MIME format

# 1. write Email content (Email requires special MIME format)
msg = MIMEText('this is a test email', 'plain', 'utf-8')  # plain refers to the content of mail in normal text format

# 2. assemble Email header (sender, recipient, subject)
msg['From'] = 'test_results@sina.com'  # From
msg['To'] = '2375247815@qq.com'  # addressee
msg['Subject'] = 'Api Test Report'  # Message subject

# 3. connect to the smtp server and send mail
smtp = smtplib.SMTP_SSL('smtp.sina.com')  # smtp server address uses SSL mode
smtp.login('Own email address', 'Own email password')  # User name and password
smtp.sendmail("Receiving email address 1", "Receiving email address 2", msg.as_string())
smtp.quit()

5. Chinese email subject, HTML email content, and attachments

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart  # Mixed MIME format, supporting uploading attachments
from email.header import Header  # Used to use Chinese mail subject

# 1. write email content
with open('report.html', encoding='utf-8') as f:  # Open html report
    email_body = f.read()  # Read report content

msg = MIMEMultipart()  # Mixed MIME format
msg.attach(MIMEText(email_body, 'html', 'utf-8'))  # Add html message body (css format will be lost)

# 2. assemble Email header (sender, recipient, subject)
msg['From'] = 'test_results@sina.com'  # From
msg['To'] = '2375247815@qq.com'  # addressee
msg['Subject'] = Header('Interface test report', 'utf-8')  # Chinese email subject, specify utf-8 code

# 3. construct attachment 1 and transfer test Txt file
att1 = MIMEText(open('report.html', 'rb').read(), 'base64', 'utf-8')  # Binary format open
att1["Content-Type"] = 'application/octet-stream'
att1["Content-Disposition"] = 'attachment; filename="report.html"'  # filename is the name of the attachment in the message
msg.attach(att1)

# 4. connect to the smtp server and send mail
smtp = smtplib.SMTP_SSL('smtp.sina.com')  # smtp server address uses SSL mode
smtp.login('test_results@sina.com', 'hanzhichao123')  # User name and password
smtp.sendmail("test_results@sina.com", "2375247815@qq.com", msg.as_string())
smtp.sendmail("test_results@sina.com", "superhin@126.com", msg.as_string())  # Send to another mailbox
smtp.quit()

6. encapsulate the method of sending mail

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart  # Mixed MIME format, supporting uploading attachments
from email.header import Header  # Used to use Chinese mail subject
from config import *


def send_email(report_file):
    msg = MIMEMultipart()  # Mixed MIME format
    msg.attach(MIMEText(open(report_file, encoding='utf-8').read(), 'html', 'utf-8'))  # Add html message body (css format will be lost)

    msg['From'] = 'test_results@sina.com'  # From
    msg['To'] = '2375247815@qq.com'  # addressee
    msg['Subject'] = Header('Interface test report', 'utf-8')  # Chinese email subject, specify utf-8 code

    att1 = MIMEText(open(report_file, 'rb').read(), 'base64', 'utf-8')  # Binary format open
    att1["Content-Type"] = 'application/octet-stream'
    att1["Content-Disposition"] = 'attachment; filename="report.html"'  # filename is the name of the attachment in the message
    msg.attach(att1)

    try:
        smtp = smtplib.SMTP_SSL('smtp.sina.com')  # smtp server address uses SSL mode
        smtp.login('test_results@sina.com', 'hanzhichao123')  # User name and password
        smtp.sendmail("test_results@sina.com", "2375247815@qq.com", msg.as_string())
        smtp.sendmail("test_results@sina.com", "superhin@126.com", msg.as_string())  # Send to another mailbox
        logging.info("Mail sending completed!")
    except Exception as e:
        logging.error(str(e))
    finally:
        smtp.quit()

7.run_ all. Send mail after finishing in PY

import unittest
from HTMLTestReportCN import HTMLTestRunner
from config import *
from send_email import send_email

logging.info("====================== Test start =======================")
suite = unittest.defaultTestLoader.discover("./")

with open("report.html", 'wb') as f:  # Change to with open format
    HTMLTestRunner(stream=f, title="Api Test", description="Test description", tester="Kaka").run(suite)

send_email('report.html')  # Send mail
logging.info("======================= End of test =======================")

Vi Use profile

Like the log configuration of the project, we usually put the database server address and mail service address into the configuration file config Py.

import logging
import os

# Project path
prj_path = os.path.dirname(os.path.abspath(__file__))  # One level above the absolute path of the current file__ file__ Refers to the current file

data_path = prj_path  # Data directory, temporarily under the project directory
test_path = prj_path  # Use case directory, temporarily under the project directory

log_file = os.path.join(prj_path, 'log.txt')  # You can also generate new log files every day
report_file = os.path.join(prj_path, 'report.html')  # You can also generate new reports each time

# log configuration
logging.basicConfig(level=logging.DEBUG,  # log level
                    format='[%(asctime)s] %(levelname)s [%(funcName)s: %(filename)s, %(lineno)d] %(message)s',  # log format
                    datefmt='%Y-%m-%d %H:%M:%S',  # Date format
                    filename=log_file,  # Log output file
                    filemode='a')  # append mode 


# Database configuration
db_host = '127.0.0.1'   # Own server address
db_port = 3306
db_user = 'test'
db_passwd = '123456'
db = 'api_test'

# Mail configuration
smtp_server = 'smtp.sina.com'
smtp_user = 'test_results@sina.com'
smtp_password = 'hanzhichao123'

sender = smtp_user  # From
receiver = '2375247815@qq.com'  # addressee
subject = 'Interface test report'  # Message subject

Modify db py，send_email.py，run_all.py and other references to configuration files

db.py

import pymysql
from config import *

# Get connection method
def get_db_conn():
    conn = pymysql.connect(host=db_host,   # Read from configuration file
                           port=db_port,
                           user=db_user,
                           passwd=db_passwd,  # passwd is not password
                           db=db,
                           charset='utf8')  # If the query is in Chinese, you need to specify the test set code

send_email.py

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header 
from config import *


def send_email(report_file):
    msg = MIMEMultipart()
    msg.attach(MIMEText(open(report_file, encoding='utf-8').read(), 'html', 'utf-8'))

    msg['From'] = 'test_results@sina.com'
    msg['To'] = '2375247815@qq.com'
    msg['Subject'] = Header(subject, 'utf-8')  # Read from configuration file

    att1 = MIMEText(open(report_file, 'rb').read(), 'base64', 'utf-8')  # Read from configuration file
    att1["Content-Type"] = 'application/octet-stream'
    att1["Content-Disposition"] = 'attachment; filename="{}"'.format(report_file)  # Parameterize report_file
    msg.attach(att1)

    try:
        smtp = smtplib.SMTP_SSL(smtp_server)  # Read from configuration file
        smtp.login(smtp_user, smtp_password)  # Read from configuration file
        smtp.sendmail(sender, receiver, msg.as_string())
        logging.info("Mail sending completed!")
    except Exception as e:
        logging.error(str(e))
    finally:
        smtp.quit()

run_all.py

import unittest
from HTMLTestReportCN import HTMLTestRunner
from config import *
from send_email import send_email

logging.info("==================== Test start =======================")
suite = unittest.defaultTestLoader.discover(test_path)  # Read use case path from configuration file

with open(report_file, 'wb') as f:  # Read from configuration file
    HTMLTestRunner(stream=f, title="Api Test", description="Test description").run(suite)

send_email(report_file)  # Read from configuration file
logging.info("==================== End of test =======================")

VII Frame arrangement

Currently, all files (configuration files, public methods, test cases, data, reports, and log s) are in the project root directory. With the addition of use cases and the addition of functions, there will be more and more files, which is not convenient for maintenance and management. Therefore, we need to establish different folders to organize the files by classification.

1. create the following folder in the project

config: store project configuration file

Data: save case data file

lib: public method library

Log: store log files

Report: save report file

Test: store test cases

User: store user module use cases (there must be \u init\u.py under the module so that the use cases in it can be read)

2. code catalog sorting

Set the configuration file config Py to config directory

Test the data file_ user_ data. Move xlsx to the data directory

Set the public method db py，send_email.py，case_log.py，read_excel.py，HTMLTestReportCN.py to the lib directory

Test the test case_ user_ login. py，test_ user_ reg. Move py to the test/user directory and keep run_all.py under the project root directory, as shown in the figure

3. modify the configuration file (config/config.py)

import logging
import os

# Project path
prj_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))  # The upper level directory of the current file (add one level)

data_path = os.path.join(prj_path, 'data')  # Data directory
test_path = os.path.join(prj_path, 'test')   # Use case catalog

log_file = os.path.join(prj_path, 'log', 'log.txt')  # Change the path to the log directory
report_file = os.path.join(prj_path, 'report', 'report.html')  # Change the path to the report directory

4. modify the reference to the configuration file and public method. To avoid the error of the relative path, we uniformly promote the search path of the guided package (sys.path) to the project root directory, such as lib/db py

db.py

import pymysql
import sys
sys.path.append('..')  # Upgrade one level to the project directory
from config.config import *  # Import from project root

test_user_login.py

import unittest
import requests
import json
import os  # An os is added, which needs to be used to assemble paths
import sys
sys.path.append("../..")  # Promote level 2 to the project root directory
from config.config import *  # Import from project path
from lib.read_excel import *  # Import from project path
from lib.case_log import log_case_info  # Import from project path

class TestUserLogin(unittest.TestCase):
    @classmethod
    def setUpClass(cls):   # The entire test class is executed only once
        cls.data_list = excel_to_list(os.path.join(data_path, "test_user_data.xlsx"),"TestUserLogin")  # Add data path

run_all.py

import unittest
from lib.HTMLTestReportCN import HTMLTestRunner  # Modify import path
from config.config import *  # Modify import path
from lib.send_email import send_email  # Modify import path

logging.info("================================== Test start ==================================")
suite = unittest.defaultTestLoader.discover(test_path)  # Read from configuration file

with open(report_file, 'wb') as f:  # Read from configuration file
    HTMLTestRunner(stream=f, title="Api Test", description="Test description").run(suite)

send_email(report_file)  # Read from configuration file
logging.info("================================== End of test ==================================")

If methods in the same folder refer to each other (e.g lib/read_excel.py If you need to quote lib/db.py)，You also need to import from the project path; run_all.py Directly under the project path, no promotion is required sys.path，When we do not need to import our own packages, such as read_excel.py，No lifting required

5. run_all.py, debug the code according to the log and report until all use cases pass

If you are interested in python automated testing, web automation, interface automation, mobile terminal automation, interview experience exchange, etc, You can click here to get