接着昨天写完了数据库数据录入与核对之后,今天写了个数据库存储过程单元测试的小工具,与大家分享一下。
github:https://github.com/xunmeng2002/python/tree/master/dbtest
本模块依赖于昨天完成的check_db模块:csv解析与db数据检查
本模块写了两个文件:test_struct.py 包含测试组件结果、测试用例结构及相关函数,run_utest.py 获取测试用例、配置环境、及执行,使用了一个配置文件 utest.ini
test_struct.py:
# encoding: utf-8
import os
import sys
import traceback
import datetime
sys.path.append("..\source_py")
from common_utils import common_utils
from common_utils import db_struct
from check_db import db_operate
from check_db import csv_parse
class TestSuite:
def __init__(self, name, dir, cases):
self.name = name
self.cases = cases
self.dir = dir
self.fail_num = 0
self.pass_num = 0
def exec_suite(self, cursor):
print "exec suite[%s]:" % self.name
for case in self.cases:
start = datetime.datetime.now()
if case.exec_case(cursor):
self.pass_num += 1
else:
self.fail_num += 1
end = datetime.datetime.now()
case.elapse = (end - start).seconds
class TestCase:
def __init__(self, name, dir, admin_db, history_db, init_db, sync_db):
self.name = name
self.dir = dir
self.admin_db = admin_db
self.history_db = history_db
self.init_db = init_db
self.sync_db = sync_db
self.command = ""
self.result = True
self.msg = ''
self.elapse = 0
self.read_command()
def read_command(self):
command_path = os.path.join(self.dir, "command.txt")
for line in open(command_path):
line = common_utils.trim_return(line)
if line.find("#") > 0:
line = line[:line.find("#")]
self.command += line
def replace_db_name(self, sql):
sql = sql.replace('admin.', self.admin_db + '.')
sql = sql.replace('history.', self.history_db + '.')
sql = sql.replace('init.', self.init_db + '.')
sql = sql.replace('sync.', self.sync_db + '.')
sql = sql.replace('<admin>', self.admin_db)
sql = sql.replace('<history>', self.history_db)
sql = sql.replace('<init>', self.init_db)
sql = sql.replace('<sync>', self.sync_db)
return sql
def check_expect(self, cursor, sql):
if sql.startswith('EXPECT_NOT_EQUAL'):
values = sql.replace('EXPECT_NOT_EQUAL', '').replace('(', '').replace(')', '').replace(';', '').split(',')
return self.expect_not_equal(cursor, values[0], values[1])
else:
values = sql.replace('EXPECT', '').replace('(', '').replace(')', '').replace(';', '').split(',')
return self.expect_equal(cursor, values[0], values[1])
def expect_equal(self, cursor, value1, value2):
sql1 = "select " + value1
cursor.execute(sql1)
v1 = cursor.fetchone()[0]
sql2 = "select " + value2
cursor.execute(sql2)
v2 = cursor.fetchone()[0]
if v1 != v2:
self.result = False
self.msg = "EXPECT(%s,%s):[%s]不等于[%s]" % (value1, value2, str(v1), str(v2))
return False
return True
def expect_not_equal(self, cursor, value1, value2):
sql1 = "select " + value1
cursor.execute(sql1)
v1 = cursor.fetchone()[0]
sql2 = "select " + value2
cursor.execute(sql2)
v2 = cursor.fetchone()[0]
if v1 == v2:
self.result = False
self.msg = "EXPECT_NOT_EQUAL(%s,%s):[%s]等于[%s]" % (value1, value2, str(v1), str(v2))
return False
return True
def exec_case(self, cursor):
print "\texec case[%s]." % self.name
try:
init_dir = os.path.join(self.dir, "init")
expect_dir = os.path.join(self.dir, "expect")
db_operate.load_csv_into_db(cursor, init_dir, self.admin_db, self.history_db, self.init_db, self.sync_db)
sqls = common_utils.trim_return(self.command).split(';')
for sql in sqls:
sql = self.replace_db_name(sql)
if sql == '':
continue
elif sql.startswith("EXPECT"):
if self.result is False:
return False
else:
try:
cursor.execute(sql)
except Exception, e:
print sql
raise e
db_info = db_struct.DbInfo(self.admin_db, self.history_db, self.init_db, self.sync_db)
csv_parse.parse_csvs(cursor, expect_dir, db_info)
self.result, self.msg = db_operate.check_all_result(cursor, db_info)
return self.result
except Exception, e:
self.msg = traceback.format_exc()
self.result = False
return self.result
run_utest.py:
# encoding:utf-8
import os
import ConfigParser
import datetime
import sys
import re
import webbrowser as web
import test_struct
sys.path.append("..\source_py")
from check_db import db_operate
reload(sys)
sys.setdefaultencoding("utf-8")
# 参数,全局使用
arg_names = ['help', 'filter', 'suite-filter', 'only-load-data']
args = {}
def get_suites(root_dir, admin_db, history_db, init_db, sync_db):
test_suites = []
for name in os.listdir(root_dir):
if name == 'report':
continue
suite_dir = os.path.join(root_dir, name)
if not os.path.isdir(suite_dir):
continue
if "suite-filter" in args and re.search(args["suite-filter"], name) is None:
continue
suite = test_struct.TestSuite(name, suite_dir, get_cases(suite_dir, admin_db, history_db, init_db, sync_db))
if len(suite.cases) > 0:
test_suites.append(suite)
return test_suites
def get_cases(suite_dir, admin_db, history_db, init_db, sync_db):
cases = []
for name in os.listdir(suite_dir):
case_dir = os.path.join(suite_dir, name)
if not os.path.isdir(case_dir):
continue
if "filter" in args and re.search(args["filter"], name) is None:
continue
cases.append(test_struct.TestCase(name, case_dir, admin_db, history_db, init_db, sync_db))
return cases
def print_summary(suites):
print 'test result:'
for suite in suites:
for case in suite.cases:
if case.result:
print 'pass :[%s].%s' % (suite.name, case.name)
else:
print ' fail:[%s].%s' % (suite.name, case.name)
def parse_args():
for i in range(1, len(sys.argv)):
kv = sys.argv[i].split("=")
if not kv[0].startswith('--') or kv[0][2:] not in arg_names:
print 'error:%s is invalid argument' % kv[0]
exit(-1)
if len(kv) < 2:
args[kv[0][2:]] = None
else:
args[kv[0][2:]] = kv[1]
def report(suites, start_time, end_time):
html_name = os.path.join("./report", "report%s.html" % start_time.strftime("%Y%m%d_%H%M%S"))
html = open(html_name, "wb")
html.write("<!DOCTYPE html> \n")
html.write("<html> \n")
html.write(" \n")
html.write("<head> \n")
html.write(" <meta charset=\"utf-8\"> \n")
html.write(" <title>测试结果</title> \n")
html.write(" <style> \n")
html.write(" span{display:block;} \n")
html.write(" table {text-align: center;} \n")
html.write(" thead{ font-weight: bold;} \n")
html.write(" table, th, td {border: 1px solid gray;} \n")
html.write(" th, td{padding: 7px 12px;white-space:pre-wrap;} \n")
html.write(" .pass { color: green;} \n")
html.write(" .error { color: red;} \n")
html.write(" .result_col { text-align: left;} \n")
html.write(" </style> \n")
html.write("</head> \n")
html.write("<body style=\"padding: 30px 80px;\"> \n")
html.write(" <span>开始时间:%s</span>\n" % start_time.strftime("%Y-%m-%d %H:%M:%S"))
html.write(" <span>结束时间:%s</span>\n" % end_time.strftime("%Y-%m-%d %H:%M:%S"))
html.write(" <span>执行时间:%d 秒</span>\n" % (end_time - start_time).seconds)
case_pass_num = 0
case_fail_num = 0
suite_pass_num = 0
suite_fail_num = 0
for suite in suites:
case_pass_num += suite.pass_num
case_fail_num += suite.fail_num
if suite.fail_num > 0:
suite_fail_num += 1
else:
suite_pass_num += 1
html.write(" <span>总共执行测试套件数量:%d,通过数量:%d,失败数量:%d</span>\n" % (
suite_pass_num + suite_fail_num, suite_pass_num, suite_fail_num))
html.write(" <span>总共执行测试用例数量:%d,通过数量:%d,失败数量:%d</span>\n" % (
case_pass_num + case_fail_num, case_pass_num, case_fail_num))
html.write(" <table style=\"margin-top: 30px;\"> \n")
html.write(" <thead> \n")
html.write(" <tr> \n")
html.write(" <th>测试套件</th> \n")
html.write(" <th>测试用例</th> \n")
html.write(" <th>结果</th> \n")
html.write(" <th style=\"width:90px;\">耗时</th> \n")
html.write(" </tr> \n")
html.write(" </thead> \n")
html.write(" <tbody> \n")
for suite in suites:
first = True
for case in suite.cases:
html.write(" <tr>\n")
if first:
first = False
html.write(" <td rowspan=\"%d\">%s(%d,%d)</td>\n" %
(len(suite.cases), suite.name, suite.pass_num + suite.fail_num, suite.fail_num))
html.write(" <td>%s</td>\n" % case.name)
if case.result:
html.write(" <td class=\"result_col pass\">pass</td>\n")
else:
html.write(" <td class=\"result_col error\">failed:\n%s</td>\n" % case.msg)
html.write(" <td >%d秒</td>\n" % case.elapse)
html.write(" </tr>\n")
html.write(" </tbody> \n")
html.write(" </table> \n")
html.write(" </body> \n")
html.write(" </html> \n")
html.close()
web.open_new_tab(html_name)
def show_help():
print 'args:'
print ' --filter=xxxxx [optional] filt test case'
print ' --suite-filter=xxxxx [optional] filt test suite'
print ' --only-load-data [optional] only load init data'
print ' --help [optional] show help info'
def main():
parse_args()
if 'help' in args:
show_help()
exit(0)
cfg = ConfigParser.ConfigParser()
cfg.read("utest.ini")
db_user = cfg.get('db', 'user')
db_password = cfg.get('db', 'password')
db_host = cfg.get('db', 'host')
db_port = cfg.get('db', 'port')
db_database = cfg.get('db', 'curr_db')
conn = db_operate.connect_db(db_user, db_password, db_host, db_port, db_database)
cursor = conn.cursor()
admin_db = cfg.get("db", "admin_db")
history_db = cfg.get("db", "history_db")
init_db = cfg.get("db", "init_db")
sync_db = cfg.get("db", "sync_db")
start_time = datetime.datetime.now()
root_dir = cfg.get('path', 'root_dir')
suites = get_suites(root_dir, admin_db, history_db, init_db, sync_db)
for suite in suites:
suite.exec_suite(cursor)
end_time = datetime.datetime.now()
report(suites, start_time, end_time)
print_summary(suites)
if __name__ == '__main__':
main()
utest.ini:
[db]
user=test
password=Test@1234
host=192.168.6.125
port=3306
admin_db=test_admin
history_db=test_history
init_db=test_init
sync_db=test_sync
curr_db=test_history
[path]
root_dir=./settlement_test_suite