selenium+mysql数据库进行数据驱动测试
目录结构
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()
