python解析jmeter.jtl文件_通过ant-jmeter读取jtl文件拆分数据并insert DB

第一：需独立创建一个job

第二：需按照一定规范输出

因此，放弃解析csv方式，直接解析自动化生成的原始jtl文件并集成到ant-jmeter中，不在独立一个job而是跟项目在一起，直接在持续集成过程中insert DB。

解析jtl文件与前面介绍的javamail思路相同(若想了解 http://www.cnblogs.com/nzg-noway/p/6909821.html )

insert DB依然是2张表,详情表和统计表

Step1：具体insert操作就是jdbc链接和sql操作。如下：

注意：

1.把jdbc配置项拉取到配置文件，支持灵活可变

2.创建表结构(根据业务创建索引、唯一等)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134package org.programmerplanet.ant.taskdefs.jmeter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.Date;

public class InsertDB {

public static String currTime(){

//设置日期格式

SimpleDateFormat df = new SimpleDateFormat(“yyyy-MM-dd”);

// new Date()为获取当前系统时间

String now = df.format(new Date());

return now;

}

//详情数据

static public void insertDetailDB(String BuildNum,String Module,String InterfaceName,String CaseName,String Result,String databaseName,String userName, String password,String connUrl){

try {

Class.forName(“com.mysql.jdbc.Driver”);

// String databaseName = “AutoResult”;

// String userName = “root”;

// String password = “xxxxxx”;

// String connUrl = “jdbc:mysql://10.64.66.227:3306/”;

Connection conn = DriverManager.getConnection(connUrl + databaseName, userName, password);

PreparedStatement st = null;

String currTime = InsertDB.currTime();

Statement stmt = conn.createStatement();

// 创建数据库中的表，

String sql = “create table if NOT EXISTS Auto_Detail”

+ “(id int NOT NULL auto_increment primary key ,”

+ “BuildNum varchar(20) ,”

+ “Module varchar(100),”

+ “InterfaceName varchar(100),”

+ “CaseName varchar(100) ,”

+ “Result varchar(10),”

+ “CreateTime varchar(30) NOT NULL DEFAULT ‘”+currTime+”‘,”

+ ” UNIQUE INDEX(BuildNum,Module,InterfaceName,CaseName,CreateTime))”;

// System.out.println(“输出sql创建表语句：”+sql);

int result = stmt.executeUpdate(sql);

if (result != -1) {

sql = “insert into Auto_Detail(BuildNum,Module,InterfaceName,CaseName,Result) values(?,?,?,?,?) “

+ “ON DUPLICATE KEY UPDATE Result=?”;

st = conn.prepareStatement(sql);

st.setString(1, BuildNum);

//存入过程大写转小写

st.setString(2, Module.toLowerCase());

st.setString(3, InterfaceName.toLowerCase());

st.setString(4, CaseName.toLowerCase());

//存入执行结果true或者false转为小写

st.setString(5, Result.toLowerCase());

st.setString(6, Result.toLowerCase());

st.executeUpdate();

sql = “SELECT * FROM Auto_Detail”;

// System.out.println(stmt.executeQuery(sql));

ResultSet rs = stmt.executeQuery(sql);

// System.out.println(“id\tBuildNum\tModule\tInterfaceName\tCaseName\tResult\tCreatTime”);

while (rs.next()) {

// System.out.println(rs.getString(1) + “\t” + rs.getString(2) + “\t” + rs.getString(3) + “\t” + rs.getString(4) + “\t” + rs.getString(5) + “\t” + rs.getString(6) + “\t” + rs.getString(7));

}

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

//统计数据

static public void insertStatisticsDB(String BuildNum,String Module,int CaseNum,int FailNum,String Rate,String databaseName,String userName, String password,String connUrl){

try {

Class.forName(“com.mysql.jdbc.Driver”);

// String databaseName = “AutoResult”;

// String userName = “root”;

// String password = “xxxxxx”;

// String connUrl = “jdbc:mysql://10.64.66.227:3306/”;

Connection conn = DriverManager.getConnection(connUrl + databaseName, userName, password);

PreparedStatement st = null;

String currTime = InsertDB.currTime();

Statement stmt = conn.createStatement();

// 创建数据库中的表，

String sql = “create table if NOT EXISTS Auto_Statistics”

+ “(id int NOT NULL auto_increment primary key ,”

+ “BuildNum varchar(20) ,”

+ “Module varchar(100),”

+ “CaseNum int,”

+ “FailNum int ,”

+ “Rate varchar(20),”

+ “CreateTime varchar(30) NOT NULL DEFAULT ‘”+currTime+”‘,”

+ ” UNIQUE INDEX(BuildNum,Module,CreateTime))”;

// System.out.println(“输出sql创建表语句：”+sql);

int result = stmt.executeUpdate(sql);

if (result != -1) {

sql = “insert into Auto_Statistics(BuildNum,Module,CaseNum,FailNum,Rate) values(?,?,?,?,?) “

+ “ON DUPLICATE KEY UPDATE CaseNum=?,FailNum=?,Rate=?”;

// System.out.println(“查看统计sql：”+sql);

st = conn.prepareStatement(sql);

st.setString(1, BuildNum);

//存入过程大写转小写

st.setString(2, Module.toLowerCase());

st.setInt(3, CaseNum);

st.setInt(4, FailNum);

st.setString(5, Rate);

st.setInt(6, CaseNum);

st.setInt(7, FailNum);

st.setString(8, Rate);

st.executeUpdate();

sql = “SELECT * FROM Auto_Statistics”;

// System.out.println(stmt.executeQuery(sql));

ResultSet rs = stmt.executeQuery(sql);

// System.out.println(“id\tBuildNum\tModule\tCaseNum\tFailNum\tRate\tCreatTime”);

while (rs.next()) {

}

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

public static void main(String[] args){

insertDetailDB(“9.8.1001”, “test”, “aaa”, “bbb”, “true”,”AutoResult”,”root”,”xxxxxx”,”jdbc:mysql://10.64.66.227:3306/”);

}

step2：拆分和获取insert DB的参数

注意：

1.命名规范(满足module和interface，由于统计jdbc请求因此，module和table也可以)

2.参数拆分和获取依据

3.数据统计计算

4.由于业务方面buildNum需要，因此参数值获取时需要把buildNum丢掉

python解析jmeter.jtl文件_通过ant-jmeter读取jtl文件拆分数据并insert DB package org.programmerplanet.ant.taskdefs.jmeter;import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStreamReader;import javax.mail.MessagingException;import java.io.UnsupportedEncodingException;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import org.apache.tools.ant.BuildException;import org.apache.tools.ant.Task;public class DBTask extends Task{ private String resultLog=null;

File resultLogFile;//

private String databaseName=null; private String userName=null; private String password=null; private String connUrl=null; /* String databaseName = “AutoResult”;

String userName = “root”;

String password = “xxxxxx”;

String connUrl = “jdbc:mysql://10.64.66.227:3306/”;*/

//jtl文件

public void setResultLog(String resultLog) { this.resultLog = resultLog;

} public String getResultLog() { return resultLog;

}

public void setdatabaseName(String databaseName) { this.databaseName = databaseName;

} public String getdatabaseName() { return databaseName;

} public void setuserName(String userName) { this.userName = userName;

} public String getuserName() { return userName;

} public void setpassword(String password) { this.password = password;

} public String getpassword() { return password;

} public void setconnUrl(String connUrl) { this.connUrl = connUrl;

} public String getconnUrl() { return connUrl;

}

* @see org.apache.tools.ant.Task#execute()

* task执行的入口 */

public void execute() throws BuildException {

System.out.println(“开始执行插入数据库task”);

resultLogFile = new File(System.getProperty(“user.dir”)+resultLog); if (resultLogFile.exists()){

System.out.println(“开始解析数据插入DBresultLog”); try {

analyseResultLog();

} catch (MessagingException e) {

e.printStackTrace();

}

}else{System.out.println(“resultLog不存在，请检查！”);}

}

private class Entity { int successNum=0; int failNum=0;

}

private void analyseResultLog() throws BuildException, MessagingException {

String fullTitile= null;

String BuildNum= null;

String Module= null;

String InterfaceName= null;

String InterfaceName_1= null;

String CaseName= null;

String Result= null;

String rate= null; int CaseTotalNum=0; int FailNumber=0;

List ModuleArray = new ArrayList();

List InterfaceNameArray = new ArrayList();

List CaseNameArray = new ArrayList();

List ResultArray = new ArrayList();

HashMap data = new HashMap();

DecimalFormat df = new DecimalFormat(“0.00”);

try {

FileInputStream fis = new FileInputStream(System.getProperty(“user.dir”)+resultLog);

InputStreamReader isr = new InputStreamReader(fis, “UTF-8”);

BufferedReader br = new BufferedReader(isr);

String line = null; while ((line = br.readLine()) != null) { if (line.contains(“

fullTitile = line.split(“\””)[13];

Result = line.split(“\””)[11];

ResultArray.add(Result); //获取构建版本号

if(fullTitile.contains(“BuildNum”)==true){

BuildNum = fullTitile.substring(fullTitile.toLowerCase().indexOf(“module”)+6,fullTitile.indexOf(“BuildNum”)).trim();

} if(fullTitile.toLowerCase().contains(“module”)==true){

Module = fullTitile.substring(0,fullTitile.toLowerCase().indexOf(“module”)).trim();

ModuleArray.add(Module); if(fullTitile.toLowerCase().contains(“interface”)==true){

InterfaceName_1 = fullTitile.substring(fullTitile.toLowerCase().indexOf(“module”)+6,fullTitile.toLowerCase().indexOf(“interface”)).trim(); if(InterfaceName_1.contains(“BuildNum”)==true){

InterfaceName = InterfaceName_1.substring(InterfaceName_1.indexOf(“BuildNum”)+8);

}else {

InterfaceName = InterfaceName_1;

}// System.out.println(“输出接口名称：”+InterfaceName);

CaseName = fullTitile.substring(fullTitile.toLowerCase().indexOf(“interface”)+9).trim();// System.out.println(“输出用例名称：”+CaseName);

}else if(fullTitile.toLowerCase().contains(“table”)==true){

InterfaceName = fullTitile.substring(fullTitile.toLowerCase().indexOf(“module”)+6,fullTitile.indexOf(“table”)).trim();

CaseName = fullTitile.substring(fullTitile.toLowerCase().indexOf(“table”)+5).trim();

}

InterfaceNameArray.add(InterfaceName);

CaseNameArray.add(CaseName);

if(data.get(Module) == null){

Entity entity = new Entity(); if (line.indexOf(” s=\”true\””) !=-1) {

entity.successNum = 1;

}else{

entity.failNum = 1;

}

data.put(Module, entity);

}else{

Entity entity = data.get(Module); if (line.indexOf(” s=\”true\””) !=-1) {

entity.successNum += 1;

}else{

entity.failNum += 1;

}

data.put(Module, entity);

}

}// System.out.println(“ModuleArray名称为：”+ModuleArray+ModuleArray.size());// System.out.println(“InterfaceNameArray名称为：”+InterfaceNameArray+InterfaceNameArray.size());// System.out.println(“CaseNameArray名称为：”+CaseNameArray+CaseNameArray.size());// System.out.println(“ResultArray名称为：”+ResultArray+ResultArray.size());

System.out.println(“构建日期为：”+InsertDB.currTime());

System.out.println(“构建版本为：”+BuildNum); for(int i=0;i

Module=ModuleArray.get(i);

InterfaceName=InterfaceNameArray.get(i);

CaseName=CaseNameArray.get(i);

Result=ResultArray.get(i);

InsertDB.insertDetailDB(BuildNum, Module, InterfaceName, CaseName, Result,databaseName,userName,password,connUrl);

}

System.out.println(“场景详情数据插入DB成功”);

Iterator iterator = data.keySet().iterator(); while(iterator.hasNext()){

Module = iterator.next();

Entity entity = data.get(Module);

CaseTotalNum =entity.successNum+entity.failNum;

FailNumber = entity.failNum; if(CaseTotalNum == 0){

rate = “0”;

}else {

rate = df.format((float)entity.successNum/(float)CaseTotalNum*100);

}// System.out.println(“统计参数字段：版本”+BuildNum+”模块：”+Module+”用例数：”+CaseTotalNum+”失败数：”+FailNumber+”通过率：”+rate+”%”);

InsertDB.insertStatisticsDB(BuildNum, Module, CaseTotalNum,FailNumber, rate+”%”, databaseName,userName,password,connUrl);

}

System.out.println(“场景统计数据插入DB成功”);

br.close();

isr.close();

fis.close();

}catch (IOException e) { throw new BuildException(“Could not read jmeter resultLog: ” + e.getMessage());

}

} //测试

public static void main(String[] args) throws MessagingException, UnsupportedEncodingException {

DBTask mt = new DBTask();

mt.setResultLog(“/InterfaceAutoTestReport.jtl”);

mt.execute();

}