一、摘要

本博文将介绍Python和Selenium做自动化测试的时候，基于unittest框架，借助ddt模块使用mysql数据库为数据源作为测试输入

二、SQL脚本

# encoding = utf-8create_database = 'CREATE DATABASE IF NOT EXISTS davieyang DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'drop_table = 'DROP TABLE testdata;'create_table = """    CREATE TABLE testdata(        ID int primary key not null auto_increment comment '主键',        BOOKNAME varchar(40) unique not null comment '书名',        AUTHOR varchar(30) not null comment '作者'    )engine = innodb character set utf8 comment '测试数据表';"""

三、解析Mysql

# encoding = utf-8"""__title__ = ''__author__ = 'davieyang'__mtime__ = '2018/4/21'"""import pymysqlfrom TestData.SqlScripts import create_tablefrom TestData.SqlScripts import create_databasefrom TestData.SqlScripts import drop_tableclass MySQL(object):    def __init__(self, host, port, dbName, username, password, charset):        self.conn = pymysql.connect(            host=host,            port=port,            db=dbName,            user=username,            password=password,            charset=charset        )        self.cur = self.conn.cursor()    def create(self):        try:            self.cur.execute(create_database)            self.conn.select_db("davieyang")            self.cur.execute(drop_table)            self.cur.execute(create_table)            '''            cur.execute("drop database if exists davieyang")   #如果davieyang数据库存在则删除              cur.execute("create database davieyang")   #新创建一个数据库davieyang              cur.execute("use davieyang")         #选择davieyang这个数据库              # sql 中的内容为创建一个名为testdata的表              sql = """create table testdata(id BIGINT,name VARCHAR(20),age INT DEFAULT 1)"""  #()中的参数可以自行设置              conn.execute("drop table if exists testdata") # 如果表存在则删除              conn.execute(sql)# 创建表                # 删除              # conn.execute("drop table testdata")              conn.close()# 关闭游标连接              connect.close()# 关闭数据库服务器连接 释放内存              '''        except pymysql.Error as e:            raise e        else:            self.cur.close()            self.conn.commit()            self.conn.close()            print(u"创建数据库和表成功")    def insertDatas(self):        try:            sql = "insert into testdata(bookname, author) values(%s, %s);"            self.cur.executemany(sql, [('selenium xml DataDriven', 'davieyang'),                                    ('selenium excel DataDriven', 'davieyang'),                                    ('selenium ddt data list', 'davieyang')])        except pymysql.Error as e:            raise e        else:            self.conn.commit()            print(u"初始数据插入成功")            self.cur.execute("select * from testData;")            for i in self.cur.fetchall():                print(i[1], i[2])            self.cur.close()            self.conn.close()    def getDataFromDataBase(self):        # 从数据库中获取数据        # bookname作为搜索关键词，author作为期望结果        self.cur.execute("select bookname, author from testdata;")        # 从查询区域取回所有查询结果        dataTuple = self.cur.fetchall()        return dataTuple    def closeDataBase(self):        # 数据库清理        self.cur.close()        self.conn.commit()        self.conn.close()if __name__ == "__main__":    db = MySQL(        host="localhost",        port=3306,        dbName="davieyang",        username="root",        password="root",        charset="utf8"    )    print(db.getDataFromDataBase())    db.closeDataBase()

四、测试脚本

# encoding = utf-8"""__title__ = ''__author__ = 'davieyang'__mtime__ = '2018/4/21'"""from selenium import webdriverimport unittestimport timeimport loggingimport tracebackimport ddtfrom Util.MysqlDBUtil import MySQLfrom selenium.common.exceptions import NoSuchElementException# 初始化日志对象logging.basicConfig(    # 日志级别    level=logging.INFO,    # 时间、代码所在文件名、代码行号、日志级别名字、日志信息    format='%(asctime)s %(filename)s[line: %(lineno)d] %(levelname)s %(message)s',    # 打印日志的时间    datefmt='%a, %d %b %Y %H:%M:%S',    # 日志文件存放的目录及日志文件名    filename='F:\\DataDriven\\TestResults\TestResults.TestResults',    # 打开日志的方式    filemode='w')def getTestDatas():    db = MySQL(        host="localhost",        port=3306,        dbName="davieyang",        username="root",        password="root",        charset="utf8"    )    # 从数据库中获取测试数据    testData = db.getDataFromDataBase()    db.closeDataBase()    return testData@ddt.ddtclass DataDrivenByMySQL(unittest.TestCase):    def setUp(self):        self.driver = webdriver.Chrome(executable_path=r"F:\automation\webdriver\chromedriver.exe")    @ddt.data(* getTestDatas())    def test_dataDrivenByMySQL(self, data):        # 对获得的数据进行解包        testData, expectData =data        url = "http://www.baidu.com"        self.driver.get(url)        self.driver.maximize_window()        print(testData, expectData)        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 as e:            logging.error(u"查找的页面元素不存在，异常堆栈信息为：" + str(traceback.format_exc()))        except AssertionError as e:            logging.info(u"搜索 ‘%s’,期望 ‘%s’ ,失败" % (testData, expectData))        except Exception as e:            logging.error(u"未知错误，错误信息：" + str(traceback.format_exc()))        else:            logging.info(u"搜索 ‘%s’,期望 ‘%s’ ,通过" % (testData, expectData))    def tearDown(self):        self.driver.quit()if __name__ == "__main__":    unittest.main()