数据驱动实战

目录

Json

Yaml 

 Excel

 CSV

MySQL 

---

自动化测试中我们要把测试数据分离出去，分离并存储到文件中

Json

测试案例存储到json文件

{
  "request":
  [
    {"username":"xia","password":"admin","sex":"女", "age":"18"},
    {"password":"admin","sex":"女","age":18},
    {"username":"xia","sex":"女", "age":18},
    {"username":"xia","password":"admin","sex":"asdf", "age":18},
    {"username":"xia","password":"admin","sex":"女", "age":"rrest"}
  ]
}

结合pytest测试框架读取测试案例并发送请求

import json
import pytest
import requests


def readJson():
    return json.load(open("login.json", encoding='utf-8'))


@pytest.mark.parametrize('data', readJson()["request"])
def test_login_json(data):
    r = requests.post(url='http://127.0.0.1:5000/login',
                      json=data)
    print(r.json())

执行结果：

python -m pytest -s -v read_json.py

Yaml 

Pythona操作yaml文件需要单独安装第三方库：pip install pyyaml

同样的案例

案例存储在Yaml文件中

---
#用户名请求为空
"url": "http://localhost:5000/login"
"body": '{
           "password":"admin",
           "sex":"女",
           "age":18
        }'
"expect": '{
              "message": {
                  "username": "账号用户名不能为空"
              }
          }'

---
#密码为空
"url": "http://localhost:5000/login"
"body": '{
           "username":"admin",
           "sex":"女",
           "age":18
        }'
"expect": '{
              "message": {
                  "password": "账户密码不能为空"
              }
          }'
---
#性别参数验证
"url": "http://localhost:5000/login"
"body": '{
           "username":"xia",
           "password":"admin",
           "sex":"asdf",
           "age":18
         }'
expect: '{
             "message": {
                 "sex": "性别只能是男或者女"
             }
         }'
---
# 校验年龄是否是正整数
"url": "http://localhost:5000/login"
"body": '{
           "username":"xia",
           "password":"admin",
           "sex":"女",
           "age":"rrest"
         }'
"expect": '{
               "message": {
                   "age": "年龄必须是正整数"
               }
           }'
---
#登录成功
"url": "http://localhost:5000/login"
"body": '{
           "username":"xia",
           "password":"admin",
           "sex":"男",
           "age":"18"
         }'
"expect": '{
               "age": 18,
               "password": "admin",
               "sex": "男",
               "username": "wuya"
           }'

本质上还是我们把对象放到一个列表里边循环里边的数据信息，读取yaml文件代码如下：

import yaml
import requests
import pytest
import json


def readYaml():
    with open("login.yaml", encoding='utf-8') as f:
        return list(yaml.safe_load_all(f))


@pytest.mark.parametrize('data', readYaml())
def test_login_yaml(data):
    r = requests.post(url=data['url'],
                      json=json.loads(data['body']))
    assert r.json() == json.loads(data['expect'])

执行结果：

 Excel

在python中操作excel用到第三方库xlrd：pip install xlrd

我们把测试数据分离到excel文件中，excel文件内容如下：

 结合pytest框架读取数据并发送请求

import requests, json, xlrd
import pytest


def readExcel():
    data = []
    book = xlrd.open_workbook('login.xls')
    sheet = book.sheet_by_index(0)
    for item in range(1, sheet.nrows):
        data.append(sheet.row_values(item))
    return data


@pytest.mark.parametrize('data', readExcel())
def test_login_excel(data):
    r = requests.post(url=data[0],
                      json=json.loads(data[1]))
    assert r.json() == json.loads(data[2])

执行结果：

 CSV

我们把测试数据分离到csv文件中，csv文件内容如下：

 结合pytest框架读取数据并发送请求

import csv
import requests
import json
import pytest


def readCsv():
    data = []
    with open('login.csv') as f:
        reader = csv.reader(f)
        next(reader)
        for item in reader:
            data.append(item)
        return data


@pytest.mark.parametrize('data', readCsv())
def test_login_csv(data):
    r = requests.post(url=data[0],
                      json=json.loads(data[1]))
    assert r.json() == json.loads(data[2])

执行结果：

MySQL 

在python中操作mysql用到第三方库pymysql：pip install pymysql

import pymysql


# 打开数据库连接
def conn():
    try:
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="root", db="test", charset='utf8')
        cur = conn.cursor()
        sql = """CREATE TABLE login (

                 FIRST_NAME  CHAR(20) NOT NULL,

                 LAST_NAME  CHAR(20),

                 AGE INT,

                 SEX CHAR(1),

                 INCOME FLOAT )"""
        # 执行sql语句
        cur.execute(sql)
        # 提交到数据库执行
        conn.commit()
        print(cur.fetchone())
    except BaseException as e:
        # 如果发生错误则回滚
        conn.rollback()
        print(e.args[0])
    finally:
        cur.close()
        conn.close()

更多详细操作：https://www.cnblogs.com/tinghai8/p/9459282.html