使用pytest分别从xlsx,json,csv及数据库中读取数据，以实现数据驱动

使用pytest分别从xlsx,json,csv中读取数据，以实现数据驱动

从csv中读取数据：

tom,kate,rose
zhangsan, lisi, wangwu

#-*- coding = utf-8 -*-
#@Time: 2021/4/15 17:22
#@Author : Wang
#@File : test_csv.py
#@Software : PyCharm

import pytest
import csv

def get_data():
    with open('test.csv') as f:
        lst = csv.reader(f)#lst应该是一个列表的列表，其中每个元素是一个列表，其中每个元素为同一行的元素
        my_data = []
        for row in lst:
            my_data.extend(row)
        return my_data
@pytest.mark.parametrize('name',get_data())
def test01(name):
    print(name)

if __name__ == '__main__':
    # print(get_data())
    pytest.main(['-sv' ,'test_csv.py'])

从json中读取数据：

{
  "keys": ["tom","kate","rose"]
}

#-*- coding = utf-8 -*-
#@Time: 2021/4/15 21:02
#@Author : Wang
#@File : test_json.py
#@Software : PyCharm

import pytest
import json

def get_data():
    with open('test.json') as f:
        lst = []
        data = json.load(f)
        lst.extend(data['keys'])
        return lst

@pytest.mark.parametrize('name',get_data())
def test01(name):
    print(name)

if __name__ == '__main__':
    # print(get_data())
    pytest.main(['-sv','test_json.py'])

从xlsx中读取数据

#-*- coding = utf-8 -*-
#@Time: 2021/4/15 21:11
#@Author : Wang
#@File : test_excel.py
#@Software : PyCharm
import pytest
import xlrd

def get_data():
    filename = 'data.xlsx'#在pycharm外面建立一个xlsx文件复制进来才可以的
    wb = xlrd.open_workbook(filename)
    sheet = wb.sheet_by_index(0)
    rows = sheet.nrows
    cols = sheet.ncols
    lst = []

    for row in range(rows):
        for col in range(cols):
            cell_data = sheet.cell_value(row, col)
            lst.append(cell_data)
    return lst

@pytest.mark.parametrize('name', get_data())
def test1(name):
    print(name)

if __name__ == '__main__':
    pytest.main(['-sv', 'test_excel.py'])

从数据库中读取数据

mysql> create database  testing_db;#创建数据库
mysql> use testing_db;#切换到数据库
mysql> create table user_tbl(   #创建数据表
    -> id int primary key auto_increment,
    -> username varchar(20),
    -> pwd varchar(20))
    -> ;
mysql> select * from user_tbl;
+----+----------+------+
| id | username | pwd  |
+----+----------+------+
|  1 | tom      | 123  |
|  2 | kate     | 456  |
|  3 | rose     | 789  |
+----+----------+------+
3 rows in set (0.00 sec)

#-*- coding = utf-8 -*-
#@Time: 2021/4/15 22:41
#@Author : Wang
#@File : test_db.py
#@Software : PyCharm

import MySQLdb
import pytest

conn = MySQLdb.connect(
    user = 'root',
    passwd = 'PASSWD',
    host = 'localhost',
    port = 3306,
    db = 'testing_db'
)

def get_data():
    query_sql = 'select id,username,pwd from user_tbl'
    lst = []
    try:
        cursor = conn.cursor()
        cursor.execute(query_sql)
        r = cursor.fetchall()
        for x in r:
            u = (x[0],x[1],x[2])
            lst.append(u)
        return lst
    finally:
        cursor.close()
        conn.close()
@pytest.mark.parametrize('id,name,pwd',get_data())
def test1(id, name, pwd):
    print(id, name, pwd)


if __name__ == '__main__':
    pytest.main(['-sv'])