def get(self):
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
sql = "select id,data1,data2,data3,data4,created_time from table_1 where created_time + interval 10 second > now()"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
if len(result['data']) == 0:
raise Exception('No data in recent 10 seconds.')
data_list = []
for item in result['data']:
tmp = dict()
tmp['Id'] = item['id']
tmp['Data1'] = item['data1']
tmp['Data2'] = item['data2']
tmp['Data3'] = item['data3']
tmp['Data4'] = item['data4']
tmp['CreatedTime'] = item['created_time']
data_list.append(tmp)
return {'DataList': data_list}
def put(self):
# param check
parser = reqparse.RequestParser()
parser.add_argument('DataIds', required=True)
args = parser.parse_args()
data_ids = args.get('DataIds').strip()
data_id_list = data_ids.split(',')
if len(data_id_list) == 0:
raise Exception('Invalid DataIds')
for data_id in data_id_list:
if not data_id.isdigit():
raise Exception('Invalid DataIds')
# update the flag to 1, which means important
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
data_ids = args.get('DataIds')
sql = "update table_1 set flag = 1 where id in ({}) and flag = 0".format(
data_ids)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
return {'DataIds': data_ids}
def setUp(self):
global lg, data, token, groupId, csm, planId, phoneId, auto_test, sip, sip_id, group_number
data = GetValue()
#登录
lg = Login(data.getvalue('product_address'))
res = lg.login(data.getvalue('account'),
data.getvalue('product_password'))
token = res['data']['token']
self.assertEqual(res['data']['userName'], data.getvalue('account'))
self.assertEqual(res['data']['accountType'], 1)
#创建客户组手动上传号码
csm = CustomerManage(data.getvalue('product_address'))
res2 = csm.addPhoneNumber(token, data.getvalue('userid'),
['17200001999'], 1, 'autoTest')
self.assertEqual(res2['status'], 1000)
self.assertEqual(res2['msg'], '操作成功')
#查询mongodb获取groupId
product = Mongodb('outbound_product', 'phone_number', '172.20.10.20',
27017)
table = product.connect_mongodb()
res3 = product.mongodb_find(table, {
'userId': 21,
'groupName': 'autoTest'
})
for item in res3:
groupId = item['groupId']
#添加SIP
sip = sipManage(data.getvalue('product_address'))
res = sip.add_sip(token)
self.assertEqual(res['status'], 1000)
self.assertEqual(res['msg'], '操作成功')
#查询mysql获取线路id、group_number
product_m = Mysql('172.20.10.14', 3306, 'root', 'kalamodo',
'outbound_product')
con = product_m.connect_mysql()
res = product_m.mysql_select(
con[0],
'SELECT id,group_number FROM ko_sipmanager where privately=21')
for row in res:
sip_id = row[0]
group_number = row[1]
#创建外呼计划
auto_test = OutPlan(data.getvalue('product_address'))
res = auto_test.creat_outplan(token, data.getvalue('userid'), '3706',
'autoTest', '尚德销售纵线白名单', sip_id, groupId)
planId = res['data']['planId']
self.assertEqual(res['status'], 1000)
self.assertEqual(res['msg'], '操作成功')
#获取phoneid
res = auto_test.get_CallDetail(token, planId)
phoneId = res['data']['list'][0]['id']
def get(self):
parser = reqparse.RequestParser()
parser.add_argument('PageSize', type=int)
parser.add_argument('PageNo', type=int)
args = parser.parse_args()
page_size = args.get('PageSize')
page_no = args.get('PageNo')
if page_size is None:
page_size = 100
elif page_size <= 0:
raise Exception('Invalid PageSize')
if page_no is None:
page_no = 1
elif page_no <= 0:
raise Exception('Invalid PageNo')
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
sql = "select count(*) from table_2"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
total_records = result['data'][0]['count(*)']
offset = (page_no - 1) * page_size
sql = "select data1,data2,data3,data4,original_created_time,original_id from table_2 order by id desc " \
"limit {0},{1}".format(offset, page_size)
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
if len(result['data']) == 0:
raise Exception('No data in recent 10 seconds.')
data_list = []
for item in result['data']:
tmp = dict()
tmp['Data1'] = item['data1']
tmp['Data2'] = item['data2']
tmp['Data3'] = item['data3']
tmp['Data4'] = item['data4']
tmp['Id'] = item['original_id']
tmp['CreatedTime'] = item['original_created_time']
data_list.append(tmp)
rsp = dict()
rsp['DataList'] = data_list
rsp['PageSize'] = page_size
rsp['PageNo'] = page_no
rsp['TotalRecords'] = total_records
return rsp
def setUp(self):
global lg, token, groupId, csm, planId, phoneId, auto_test, sip, sip_id, group_number
#登录
lg = Login(product_address)
res = lg.login(account, product_password)
token = res['data']['token']
self.assertEqual(res['data']['userName'], account)
self.assertEqual(res['data']['accountType'], 1)
#创建客户组手动上传号码
csm = CustomerManage(product_address)
res2 = csm.addPhoneNumber(token, userid, phonum_list, 1, auto_name)
self.assertEqual(res2['status'], code_1000)
self.assertEqual(res2['msg'], import_suc)
#查询mongodb获取groupId
product = Mongodb(dbname, table_phonum, db_ip, db_port)
table = product.connect_mongodb()
res3 = product.mongodb_find(table, {
'userId': privately,
'groupName': auto_name
})
for item in res3:
groupId = item['groupId']
#添加SIP
sip = sipManage(product_address)
res = sip.add_sip(token, username, password, ip, port, comment,
lineType, groupSize)
self.assertEqual(res['status'], code_1000)
self.assertEqual(res['msg'], success)
#查询mysql获取线路id、group_number
product_m = Mysql(myq_ip, myq_port, myq_user, myq_pswd, dbname)
con = product_m.connect_mysql()
res = product_m.mysql_select(
con[0],
'SELECT id,group_number FROM ko_sipmanager where privately=21')
for row in res:
sip_id = row[0]
group_number = row[1]
#创建外呼计划
auto_test = OutPlan(product_address)
res = auto_test.creat_outplan(token, userid, '3706', auto_name,
'尚德销售纵线白名单', sip_id, groupId)
planId = res['data']['planId']
self.assertEqual(res['status'], code_1000)
self.assertEqual(res['msg'], success)
#获取phoneid
res = auto_test.get_CallDetail(token, planId)
phoneId = res['data']['list'][0]['id']
def website_msg(req):
"""
@summary: 网站订阅消息反馈页面
"""
if req.method == "GET":
msgs = Mysql.queryWebsiteMsg(req.user.id)
if msgs != None:
msgs = [models.WebsiteMsg(msg) for msg in msgs]
return render(req, "Subpage/website_msg.html", {"msgs": msgs})
else:
Mysql.clearMsg(req.user.id)
return HttpResponse(True)
def sub_manager(req):
"""
@summary: 订阅管理页面
"""
if req.method == "GET":
websites = Mysql.queryWebsite(req.user.id)
if websites:
websites = [models.Submanager(data) for data in websites]
return render(req, "Subpage/sub_manager.html", {"websites": websites})
if req.method == "POST":
website_ids = json.loads(req.POST.get('website_ids'))
Mysql.cancleWebsiteSub(req.user.id, website_ids)
return HttpResponse("0")
def all_do(self, i):
#读取excel数据
log().info("开始读取excel数据")
self.alists = self.xl.get_rows_values(i)
self.url = self.alists[0]
self.mothond = self.alists[1]
print(self.alists[2])
self.headers_value = eval(self.alists[2])
print(type(self.headers_value))
if self.headers_value == None:
self.headers = None
else:
self.headers = self.headers_value
self.data = json.dumps(eval(self.alists[3]))
self.beforesql = self.alists[4]
self.beforesql = self.beforesql.split(";")
self.aftersql = self.alists[5]
self.aftersql = self.aftersql.split(";")
self.exp = json.loads(self.alists[6], strict=False)
log().info("读取excel数据完毕")
#sql准备
log().info("开始准备sql数据")
print(getsqlparam())
self.sql = Mysql(host=getsqlparam()[0],
password=getsqlparam()[2],
db=getsqlparam()[3],
user=getsqlparam()[1])
for i in range(0, len(self.beforesql) - 1):
self.sql.executedo(self.beforesql[i])
self.sql.close()
log().info("sql数据准备完毕")
#执行用例
log().info("开始执行测试")
self.headers = {"Content-Type": "application/json"}
self.http = Http(self.url, self.mothond, self.headers, self.data)
self.req = self.http.dohttp()
log().info("测试结束")
# sql删除
log().info("开始删除sql数据")
print(getsqlparam())
self.sql = Mysql(host=getsqlparam()[0],
password=getsqlparam()[2],
db=getsqlparam()[3],
user=getsqlparam()[1])
for i in range(0, len(self.aftersql) - 1):
self.sql.executedo(self.aftersql[i])
self.sql.close()
log().info("sql数据删除完毕")
return self.req
def copy_important_data():
# connect
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
return
# detect data to copy
sql = "select id from table_1 where flag = 1 order by id desc limit 1"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
return
if len(result['data']) == 0:
return
max_important_id_1 = result['data'][0]['id']
sql = "select original_id from table_2 order by original_id desc limit 1"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
return
if len(result['data']) == 0:
max_important_id_2 = 0
else:
max_important_id_2 = result['data'][0]['original_id']
if max_important_id_1 == max_important_id_2:
return
elif max_important_id_1 < max_important_id_2:
Log.error('table 1 data is abnormal.')
return
else:
# start to copy
sql = "select * from table_1 where id > {id} and flag = 1".format(
id=max_important_id_2)
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
return
# insert data
value_list = []
for item in result['data']:
value = "({id},{data1},{data2},{data3},{data4},{flag},'{created_time}',now())".format(
id=item['id'],
data1=item['data1'],
data2=item['data2'],
data3=item['data3'],
data4=item['data4'],
flag=item['flag'],
created_time=item['created_time'])
value_list.append(value)
# if the data is large, segmentation is required
sql = "insert into table_2(original_id,data1,data2,data3,data4,flag,original_created_time,created_time) values" + ','.join(
value_list)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])
def assertmysql(self, sql, dictv):
'''
与mysql数据库指定值作比较
sql:要查询的sql语句
dictv:断言条件，形式:{n1:'value1',n2:'value2'} 例子：{2:'Jack',3:23}，（说明要比较查询结果的某一行是否同时存在第二列为Jack及第三列为23）
'''
# 实现关联
dictv = self.__get__relations(dictv)
if sql.upper().startswith('SELECT'):
#回写excel的数据
writeexcel = ''
#返回查询结果，mysqlcheck是一个元组，里面又包含了每一行的查询结果（每一行一个元组），形如((1,"Jack",23),(2,"Tom",16))
mysqlcheck = Mysql().mysqlexec(sql)
if 'error!!!' in mysqlcheck:
self.__write_excel(False, mysqlcheck)
logger.error('sql查询出错')
return False
#字符串转字典
#dictv = eval(f'{{{dictv}}}')
dictv = eval(dictv)
logger.info(mysqlcheck)
try:
# 遍历每一行
for i in mysqlcheck:
flag = True
# 遍历行中的每一列
for j in range(len(i)):
value = i[j]
#先判断dict的键存不存在
if j + 1 not in dictv:
continue
#如果某一值不符合断言就pass掉
if dictv[j + 1] != value:
flag = False
break
if flag:
#print(i)
writeexcel += str(i)
#break
if writeexcel:
self.__write_excel(True, writeexcel)
return True
else:
self.__write_excel(False, writeexcel)
return False
except Exception as e:
logger.error(f'比较sql时发生了错误：{str(e)}')
self.__write_excel(False, traceback.format_exc())
return False
else:
logger.error('不是以SELECT开头的sql语句，无法进行比较')
self.__write_excel(False, '不是以SELECT开头的sql语句，无法进行比较')
return False
def readNewList(user_id, latest_id, type, limit=10):
"""
@summary: 获取read_count条新闻
:param user_id: 用户id（未登录的用户ID为-1，会返回所有新闻中的最新新闻）
:param type: 读取类型（读取未读新闻的数量、获取未读新闻，获取旧新闻）
:param latest_id: 客户端最新新闻id
:param limit: 每次读取的新闻条数
:return: NewListItem列表
"""
res = Mysql.queryNewList(user_id, latest_id, type, limit)
if type == "getcount":
return res[0][0] > 10 and res[0][0] or 0
else:
return [NewListItem(item) for item in res]
def get_new_data():
# get data
try:
rsp = requests.get('http://localhost:5000/producer/data')
except Exception as e:
Log.error("ask api for new data error[{}]".format(str(e)))
return
if rsp.status_code != 200:
Log.error("get data error[{}]".format(rsp.text))
return
result = rsp.json()
if result['Code'] != 200:
Log.error("get data error[{}]".format(result['Message']))
return
# insert data
value_list = []
for item in result['Data']['DataList']:
value = "({Data1},{Data2},{Data3},{Data4},now())".format(
Data1=item['Data1'],
Data2=item['Data2'],
Data3=item['Data3'],
Data4=item['Data4'])
value_list.append(value)
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
return
# if the data is large, segmentation is required
sql = "insert into table_1(data1,data2,data3,data4,created_time) values" + ','.join(
value_list)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])
elif len(params) == 1:
func(line[4])
elif len(params) == 2:
func(line[4], line[5])
elif len(params) == 3:
func(line[4], line[5], line[6])
else:
print('暂时不支持超过3个参数的关键字')
# 读配置文件
config.get_config('./conf/conf.properties')
logger.info(config.config)
# 初始化数据库
mysql = Mysql()
mysql.init_mysql('./conf/userinfo.sql')
# 逐行读取excel
reader = Reader()
casename = 'Web'
reader.open_excel('./lib/%s.xls' % casename)
writer = Writer()
writer.copy_open('./lib/%s.xls' % casename, './lib/result-%s.xls' % casename)
sheetname = reader.get_sheets()
writer.set_sheet(sheetname[0])
starttime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
writer.write(1, 3, starttime)
reader.readline()
casetype = reader.readline()[1]
print('warning：目前只支持3个参数的关键字')
# 接口自动化运行
reader = Reader()
casename = "Web"
reader.open_excel('./lib/' + casename + '.xls')
sheetname = reader.get_sheets()
# 读取配置文件
config.get_config('./lib/conf.properties')
logger.info(config.config)
# 数据库初始化
mysql = Mysql()
mysql.init_mysql('./lib/userinfo.sql')
writer = Writer()
writer.copy_open('./lib/' + casename + '.xls', './lib/result-' + casename + '.xls')
writer.set_sheet(sheetname[0])
writer.write(1, 3, str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
reader.readline()
http = None
casetype = reader.readline()[1]
if casetype == 'HTTP':
http = HTTP(writer)
if casetype == 'SOAP':
http = SOAP(writer)
casepath = ''
# 为空，则使用默认的
if casepath == '':
casepath = path + '/lib/XLSWEB.xls'
resultpath = path + '/lib/结果-XLSWEB.xls'
else:
# 如果是绝对路径，就使用绝对路径
if casepath.find(':') >= 0:
# 获取用例文件名
resultpath = path + '/lib/结果-' + casepath[casepath.rfind('\\') +
1:]
else:
logger.error('非法用例路径')
config.get_config(path + '/lib/conf.properties')
# logger.info(config.config)
mysql = Mysql()
mysql.init_mysql(path + '/lib/userinfo.sql')
datadriven.getparams(casepath, resultpath)
# unittest.main()
# suite = unittest.TestSuite()
# suite.addTests(unittest.defaultTestLoader.loadTestsFromTestCase(baidu))
suite = unittest.defaultTestLoader.discover("./myunittest/",
pattern="WebTest.py",
top_level_dir=None)
# 生成执行用例的对象
runner = bf(suite)
runner.report(filename='./test.html', description='这个描述参数是必填的')
reader.set_sheet(sheet)
writer.set_sheet(sheet)
for i in range(reader.rows):
writer.row = i
writer.clo = 7
line = reader.readline()
runCases(line, runType)
end_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
writer.set_sheet(sheetname[0])
writer.write(1, 4, str(end_time))
writer.save_close()
r = res.get_res(result + ctime + '.xls')
print(r)
config.get_config('./lib/conf/conf.properties')
mysql = Mysql()
mysql.init_mysql(
'C:\\Users\\leez\\Documents\\Navicat\\MySQL\\servers\\112\\test_project\\userinfo.sql'
)
mail = Mail()
mail.mail_info['mail_subject'] = r['title'] + '_' + ctime
mail.mail_info['filepaths'] = [result + ctime + '.xls']
mail.mail_info['filenames'] = ['result-' + name + '用例' + ctime + '.xls']
config.config['mail_html'] = config.config['mail_html'].replace(
'title', r['title'])
if r['status'] == 'Fail':
config.config['mail_html'] = config.config['mail_html'].replace(
'color: #00d800;">status', 'color: #FF0000;">status')
config.config['mail_html'] = config.config['mail_html'].replace(
'status', r['status'])
# 为空，则使用默认的
if casepath == '':
casepath = path + '/lib/cases/HTTP接口用例.xls'
resultpath = path + '/lib/results/result-HTTP接口用例.xls'
else:
# 如果是绝对路径，就使用绝对路径
if casepath.find(':') >= 0:
# 获取用例文件名
resultpath = path + '/lib/cases/result-' + casepath[casepath.rfind('\\') + 1:]
else:
logger.error('非法用例路径')
# print(path)
config.get_config(path + '/conf/conf.properties')
# logger.info(config.config)
mysql = Mysql()
mysql.init_mysql(path + '/conf/userinfo.sql')
datadriven.getparams(casepath,resultpath)
# unittest.main()
# suite = unittest.TestSuite()
# suite.addTests(unittest.defaultTestLoader.loadTestsFromTestCase(baidu))
suite = unittest.defaultTestLoader.discover("./utest/", pattern="WebTest.py", top_level_dir=None)
# 生成执行用例的对象
runner = bf(suite)
runner.report(filename='./test.html', description='自动化测试报告')
sheetname= datadriven.writer.get_sheets()
datadriven.writer.set_sheets(sheetname[0])
datadriven.writer.write(1, 4, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
datadriven.writer.save_close()
class Test(unittest.TestCase):
xl = Xl("sheetok")
def all_do(self, i):
#读取excel数据
log().info("开始读取excel数据")
self.alists = self.xl.get_rows_values(i)
self.url = self.alists[0]
self.mothond = self.alists[1]
print(self.alists[2])
self.headers_value = eval(self.alists[2])
print(type(self.headers_value))
if self.headers_value == None:
self.headers = None
else:
self.headers = self.headers_value
self.data = json.dumps(eval(self.alists[3]))
self.beforesql = self.alists[4]
self.beforesql = self.beforesql.split(";")
self.aftersql = self.alists[5]
self.aftersql = self.aftersql.split(";")
self.exp = json.loads(self.alists[6], strict=False)
log().info("读取excel数据完毕")
#sql准备
log().info("开始准备sql数据")
print(getsqlparam())
self.sql = Mysql(host=getsqlparam()[0],
password=getsqlparam()[2],
db=getsqlparam()[3],
user=getsqlparam()[1])
for i in range(0, len(self.beforesql) - 1):
self.sql.executedo(self.beforesql[i])
self.sql.close()
log().info("sql数据准备完毕")
#执行用例
log().info("开始执行测试")
self.headers = {"Content-Type": "application/json"}
self.http = Http(self.url, self.mothond, self.headers, self.data)
self.req = self.http.dohttp()
log().info("测试结束")
# sql删除
log().info("开始删除sql数据")
print(getsqlparam())
self.sql = Mysql(host=getsqlparam()[0],
password=getsqlparam()[2],
db=getsqlparam()[3],
user=getsqlparam()[1])
for i in range(0, len(self.aftersql) - 1):
self.sql.executedo(self.aftersql[i])
self.sql.close()
log().info("sql数据删除完毕")
return self.req
def test(self):
for i in range(1, self.xl.get_rows()):
log().info("======第" + str(i) + "条用例开始执行======")
self.rq = self.all_do(i)
print(self.rq.headers)
print("这里是返回码啊")
print(self.rq.status_code)
print(self.req.text)
self.assertEqual(self.rq.status_code, 200, "返回码不是200")
self.assertEqual(self.rq.json(), self.exp, "返回内容有问题")
log().info("======第" + str(i) + "条用例执行完毕======")
