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) + "条用例执行完毕======")