Selenium3+MySQL数据库进行数据驱动测试

最新推荐文章于 2021-01-19 13:59:43 发布

weixin_30415113

最新推荐文章于 2021-01-19 13:59:43 发布

阅读量144

点赞数

文章标签：数据库测试 python

原文链接：http://www.cnblogs.com/test-postman/p/10131467.html

版权

目录结构

1、准备SQL语句 — Sql.py

#创建gloryroad数据库SQL语句
create_database = 'create database if not exists gloryroad default charset utf8 collate utf8_general_ci;'
#创建testdata表
create_table = """
    create table if not exists testdata(
        id int not null auto_increment comment '主键',
        bookname varchar(40) unique not null comment '书名',
        author varchar(30) not null comment '作者',
        primary key(id)
    )engine = innodb character set utf8 comment '测试数据表';
"""

2、初始化数据库脚本 — Databaselnit.py

#初始化数据库脚本
import pymysql
from Sql import *

class DataBaseInit():
    #本类用于完成初始化数据库操作
    #创建数据库，创建数据表，向表中插入测试数据
    def __init__(self, host, port, dbName, username, password, charset):
        self.host = host
        self.port = port
        self.db = dbName
        self.user = username
        self.passwd = password
        self.charset = charset

    def create(self):
        try:
            #连接MySQL数据库
            conn = pymysql.connect(
                host = self.host,
                port = self.port,
                user = self.user,
                passwd = self.passwd,
                charset = self.charset
            )
            #获取数据库游标
            cur = conn.cursor()
            #创建数据库
            cur.execute(create_database)
            #选择创建好的gloryroad数据库
            conn.select_db("gloryroad")
            #创建测试表
            cur.execute(create_table)
        except pymysql.Error as e:
            raise e
        else:
            #关闭游标
            cur.close()
            #提交操作
            conn.commit()
            #关闭连接
            conn.close()
            print("创建数据库及表成功")

    def insertDatas(self):
        try:
            #连接数据库中具体某个库
            conn = pymysql.connect(
                host=self.host,
                port=self.port,
                db = self.db,
                user=self.user,
                passwd=self.passwd,
                charset=self.charset
            )
            cur = conn.cursor()
            #向测试表中插入测试数据
            sql = "insert into testdata(bookname, author) values(%s, %s);"
            res = cur.executemany(sql, [('Selenium Data Driven', 'Flutter'),
                                        ('Python 基础教程', 'Magnus Lie Hetland'),
                                        ('算法设计与分析', '张德富'),
                                        ('计算机网络', '谢希仁')])
        except pymysql.Error as e:
            raise e
        else:
            conn.commit()
            print("初始数据插入成功")
            #通过打印，确认数据是否插入成功
            cur.execute("select * from testdata;")
            for i in cur.fetchall():
                print(i[1], i[2])
            cur.close()
            conn.close()

if __name__ == '__main__':
    db = DataBaseInit(
        host="localhost",
        port=3306,
        dbName="gloryroad",
        username="root",
        password="root",
        charset="utf8"
    )
    db.create()
    db.insertDatas()
    print("数据库初始化结束")

3、从数据库中获取测试数据 — MysqlUtil.py

#从数据库中获取测试数据
import pymysql
from DatabaseInit import DataBaseInit

class MyMySQL():
    def __init__(self, host, port, dbName, username, password, charset):
        #进行数据库初始化
        dbInit = DataBaseInit(host, port, dbName, username, password, charset)
        dbInit.create()
        dbInit.insertDatas()
        self.conn = pymysql.connect(
            host = host,
            port = port,
            db = dbName,
            user = username,
            passwd = password,
            charset = charset
        )
        self.cur = self.conn.cursor()

    def getDataFromDataBases(self):
        #从testdata表中获取需要的测试数据
        #bookname作为搜索关键词，author作为预期关键词
        self.cur.execute("select bookname, author from testdata")
        #从查询区域取回所有的查询结果
        datasTuple = self.cur.fetchall()
        return datasTuple

    def closeDatabase(self):
        #数据库后期清理工作
        self.cur.close()
        self.conn.commit()
        self.conn.close()

if __name__ == '__main__':
    #实例化对象
    db = MyMySQL(
        host="localhost",
        port=3306,
        dbName="gloryroad",
        username="root",
        password="root",
        charset="utf8"
    )
    #函数调用
    print(db.getDataFromDataBases())
    db.closeDatabase()

4、执行数据驱动测试脚本 — DataDrivenByMySQL.py

#执行数据驱动测试脚本
from selenium import webdriver
import unittest, time
import logging, traceback
import ddt
from MysqlUtil import MyMySQL
from selenium.common.exceptions import NoSuchElementException
from BSTestRunner import BSTestRunner

#初始化日志对象
logging.basicConfig(
    #日志级别
    level=logging.INFO,
    #日志格式
    #时间、代码所在的文件名、代码行号、日志级别名字、日志信息
    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
    #打印日志的时间
    datefmt='%a, %Y-%m-%d %H:%M:%S',
    #日志文件存放的目录(目录必须存在)及日志文件名
    filename='./dataDrivenReport.log',
    #打开日志文件的方式
    filemode='w'
)
def getTestDatas():
    #对象实例化
    db = MyMySQL(
        host="localhost",
        port=3306,
        dbName="gloryroad",
        username="root",
        password="root",
        charset="utf8"
    )
    #从数据库测试表中获取测试数据
    testData = db.getDataFromDataBases()
    #关闭数据库连接
    db.closeDatabase()
    #返回数据
    return testData

@ddt.ddt
class TestDemo(unittest.TestCase):
    def setUp(self):
        self.driver = webdriver.Chrome()

    @ddt.data(*getTestDatas())
    def test_dataDrivenByDatabase(self, data):
        #对获得的数据进行解包
        testData, expectData = data
        url = "http://www.baidu.com"
        #访问百度首页
        self.driver.get(url)
        #将浏览器窗口最大化
        self.driver.maximize_window()
        print(testData, expectData)
        #设置隐式等待时间为10秒
        self.driver.implicitly_wait(10)
        try:
            # 找到搜索输入框,并输入测试数据
            self.driver.find_element_by_id("kw").send_keys(testData)
            # 找到搜索按钮，并单击
            self.driver.find_element_by_id("su").click()
            time.sleep(3)
            #断言期望结果是否出现在页面源代码中
            self.assertTrue(expectData in self.driver.page_source)
        except NoSuchElementException:
            logging.error("查找的页面元素不存在,异常堆栈信息:" + str(traceback.format_exc()))
        except AssertionError:
            logging.info("搜索%s,期望%s,失败" % (testData, expectData))
        except Exception:
            logging.error("未知错误,错误信息:" + str(traceback.format_exc()))
        else:
            logging.info("搜索%s,期望%s,通过" % (testData, expectData))

    def tearDown(self):
        self.driver.quit()

if __name__ == '__main__':
    # unittest.main()
    testCase = unittest.TestLoader().loadTestsFromTestCase(TestDemo)
    # 将多个测试类加载到测试套件中
    suite = unittest.TestSuite(testCase)
    filename = "./test.html"
    fp = open(filename, 'wb')
    runner = BSTestRunner(stream=fp,
                          title='数据库驱动测试',
                          description='测试用例执行情况：')
    runner.run(suite)
    fp.close()