mysql数据驱动测试_自动化测试框架浅析+selenium 数据驱动之mysql的使用+python

测试逻辑：

1.打开网页，从mysql数据库中获取测试过程中需要的数据

2.在搜索框中输入查询关键词测试数据

3.单击搜索按钮

4.断言搜索结果中是否出现了数据库中提供的预期内容，包含则认为执行成功，否则认为执行失败。

实现代码

1.在文件夹下新建以下四个python文件，分别为sql.py，databaseinit.py，mysqlutil.py，datadrivenbymysql

sql.py:用于创建数据库和数据表的SQL语句，具体内容如下：

#encoding=utf-8

# 创建ketest数据库sql语句

create_database = 'CREATE DATABASE IF NOT EXISTS ketest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;'

# 创建testdata表

create_table = """

drop table if exists testdata;

create table testdata(

id int not null auto_increment comment '主键',

kename varchar(40) unique not null comment '课程名称',

teacher varchar(30) not null comment '老师',

test_result varchar(30) default null,

primary key(id)

)engine=innodb character set utf8 comment '测试数据表';

"""

databaseinit.py文件用于编写初始化数据库的脚本

#encoding=utf-8

import MySQLdb

from Sql import *

class DataBaseInit(object):

# 本类用于完成初始化数据操作

# 创建数据库，创建数据表，向表中插入测试数据

def __init__(self, host, port, dbName, username,charset):

self.host = host

self.port = port

self.db = dbName

self.user = username

self.charset = charset

def create(self):

try:

# 连接mysql数据库

conn = MySQLdb.connect(

host = self.host,

port = self.port,

user = self.user,

charset = self.charset

)

# 获取数据库游标

cur = conn.cursor()

# 创建数据库

cur.execute(create_database)

# 选择创建好的ketest数据库

conn.select_db("ketest")

# 创建测试表

cur.execute(create_table)

except MySQLdb.Error, e:

raise e

else:

# 关闭游标

cur.close()

# 提交操作

conn.commit()

# 关闭连接

conn.close()

print u"创建数据库及表成功"

def insertDatas(self):

try:

# 连接mysql数据库中具体某个库

conn = MySQLdb.connect(

host = self.host,

port = self.port,

db = self.db,

user = self.user,

charset = self.charset

)

cur = conn.cursor()

# 向测试表中插入测试数据

sql = "insert into testdata(kename, teacher) values(%s, %s);"

res = cur.executemany(sql, [('一个暑假提升你的四六级听力', '剑昆'),

('忘词王是怎样炼成的', '曲根'),

('四六级', '欧阳萍'),

('杨老师', 'so cool')])

except MySQLdb.Error, e:

raise e

else:

conn.commit()

print u"初始数据插入成功"

# 确认插入数据成功

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="ketest",

username="root",

charset="utf8"

)

db.create()

db.insertDatas()

print u"数据库初始化结束"

mysqlutil.py文件用于从数据库中获取测试数据：

#encoding=utf-8

import MySQLdb

from DatabaseInit import DataBaseInit

class MyMySQL(object):

def __init__(self, host, port, dbName, username, charset):

# 进行数据库初始化

dbInit = DataBaseInit(host, port, dbName, username, charset)

dbInit.create()

dbInit.insertDatas()

self.conn = MySQLdb.connect(

host = host,

port = port,

db = dbName,

user = username,

charset = charset

)

self.cur = self.conn.cursor()

def getDataFromDataBases(self):

# 从testdata表中获取需要的测试数据

# bookname作为搜索关键词，author作为预期关键词

self.cur.execute("select kename, teacher 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="ketest",

username="root",

charset="utf8"

)

print db.getDataFromDataBases()

datadrivenbymysql用于编写执行数据驱动的测试脚本

#encoding=utf-8

from selenium import webdriver

import time

import datetime

from openpyxl import *

import MySQLdb

from selenium.webdriver.common.keys import Keys

def get_test_data():

conn = MySQLdb.connect(

host = "127.0.0.1",

port = 3306,

user = "root",

db = "ketest",

charset = "utf8"

)

# 使用cursor()方法获取数据库的操作游标

cursor = conn.cursor()

cursor.execute("select * from testdata;")

resSet = cursor.fetchall()

print u"共%s条数据。" %len(resSet)

print resSet

# 关闭游标

cursor.close()

# 提交事务

conn.commit()

# 关闭数据库连接

conn.close()

return resSet

def update_test_result(data,result):

conn = MySQLdb.connect(

host = "127.0.0.1",

port = 3306,

user = "root",

db = "ketest",

charset = "utf8"

)

# 使用cursor()方法获取数据库的操作游标

cursor = conn.cursor()

print 'update testdata set test_result="'+result+'" where kename="'+data+'";'

update=cursor.execute('update testdata set test_result="'+result+'" where kename="'+data+'";')

print u"修改语句受影响的行数：", update

# 关闭游标

cursor.close()

# 提交事务

conn.commit()

# 关闭数据库连接

conn.close()

driver=webdriver.Chrome(executable_path="D:\\python\\Scripts\\chromedriver.exe")

test_result=[]

for data in get_test_data():

try:

driver.get("https://ke.youdao.com/")

search = driver.find_element_by_class_name("_3l-Kp")

search.send_keys(data[1])

search.send_keys(Keys.ENTER)

time.sleep(3)

assert data[2] in driver.page_source

update_test_result(data[1],u"成功")

except AssertionError,e:

print data[2] +u"断言失败"

update_test_result(data[1],u"断言失败")

except Exception,e:

print e

print data[1] +u"测试执行出现异常"

update_test_result(data[1],u"执行出现异常")

driver.quit()