案例3-数据驱动测试-从sql中导入数据

创建数据库语句sql.py

#encoding =utf-8
# -*- coding:utf-8 -*-
create_database='CREATE DATABASE IF NOT EXISTS gloryroad DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'
create_table='''
    drop table if exists testdata;
    create table 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 '测试数据表'
'''

创建一张数据库表databaseinit.py

#encoding =utf-8
# -*- coding:utf-8 -*-
import MySQLdb
from sql import *
class databaseinit(object):
    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:
            # 连接数据库
            conn=MySQLdb.connect(
                host=self.host[0],
                port=self.port[0],
                user=self.user[0],
                passwd=self.passwd[0],
                charset=self.charset
            )
            cur=conn.cursor()
            cur.execute(create_database)
            conn.select_db("gloryroad")
            cur.execute(create_table)
        except MySQLdb.Error,e:
            raise e
        else:
            cur.close()
            conn.commit()
            conn.close()
    def insertdatas(self):
        try:
            conn=MySQLdb.connect(
                host=self.host[0],
                port=self.port[0],
                user=self.user[0],
                passwd=self.passwd[0],
                charset=self.charset
            )
            cur=conn.cursor()
            conn.select_db("gloryroad")
            insert="insert into testdata(bookname,author)values (%s,%s);"
            res=cur.executemany(insert,[('lol','英雄联盟'),('wow','魔兽世界'),('qaq','QAQ'),('123','hao123')])
        except MySQLdb.Error,e:
            raise e
        else:
            cur.close()
            conn.commit()
            conn.close()
if __name__=='__main__':
    db=databaseinit(
        host="localhost",
        port=3306,
        dbName="gloryroad",
        username="root",
        password="root",
        charset="utf8"
    )
    db.create()
    db.insertdatas()
    print u"数据库初始化完成"

创建一个获取数据库数据的接口getdatafromsql.py

#encoding =utf-8
# -*- coding:utf-8 -*-
import MySQLdb
from databaseinit import databaseinit
class MyMySQL(object):
    def __init__(self,host,port,dbName,username,password,charset):
        dbinit=databaseinit(host,port,dbName,username,password,charset)
        dbinit.create()
        dbinit.insertdatas()
        self.conn=MySQLdb.connect(
            host=host,
            port=port,
            db=dbName,
            user=username,
            passwd=password,
            charset=charset
        )
        self.cur=self.conn.cursor()
    def getdatafromsql(self):
        self.cur.execute("select bookname,author from testdata;")
        datas=self.cur.fetchall()
        return datas
    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.getdatafromsql()
    db.closedatabase()

从sql中导入数据并自动化的脚本.py

#encoding =utf-8
# -*- coding:utf-8 -*-
from selenium import webdriver
import unittest,time
import logging,traceback
import ddt
from getdatafromsql import *
from reportHtml import htmlTemplate
from selenium.common.exceptions import NoSuchElementException

logger = logging.getLogger('mylogger1')
logger.setLevel(logging.DEBUG)
# 创建一个handler，用于写入日志文件
fh = logging.FileHandler('C:/test.log')
fh.setLevel(logging.DEBUG)
# 再创建一个handler，用于输出到控制台
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
# 定义handler的输出格式
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
fh.setFormatter(formatter)
ch.setFormatter(formatter)
# 给logger添加handler
logger.addHandler(fh)
logger.addHandler(ch)

def gettestdatas():
    db = MyMySQL(
        host="localhost",
        port=3306,
        dbName="gloryroad",
        username="root",
        password="root",
        charset="utf8"
    )
    testdata=db.getdatafromsql()
    db.closedatabase()
    return testdata

@ddt.ddt
class Testdemo(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        Testdemo.trStr=""
    def setUp(self):
        self.driver=webdriver.Firefox(executable_path="C:\\webdriver\geckodriver")
        status=None
        flag=0
    @ddt.data(*gettestdatas())
    def test_dataDriverByFile(self,value):
        flagDict={0:'red',1:'#00AC4E'}
        url="http://www.baidu.com"
        self.driver.get(url)
        self.driver.maximize_window()
        testdata,expectdata=value
        self.driver.implicitly_wait(10)
        try:
            start=time.time()
            startTime=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
            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,e:
            logger.error(u"找不到页面元素")
            status='fail'
            flag=0
        except AssertionError,e:
            logger.info(u"搜索失败")
            status='fail'
            flag=0
        except Exception,e:
            logger.info(u"未知错误")
            status='fail'
            flag=0
        else:
            logger.info(u"搜索成功")
            status='pass'
            flag=1
        wasteTime=time.time()-start-3
        Testdemo.trStr += u'''
        <tr>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%.2f</td>
            <td style="color:%s">%s</td>
        </tr><br/>'''%(testdata,expectdata,startTime,wasteTime,flagDict[flag],status)
    def tearDown(self):
        self.driver.quit()
    @classmethod
    def tearDownClass(cls):
        htmlTemplate(Testdemo.trStr)

if __name__=='__main__':
    unittest.main()