基于jira的缺陷自动化报表分析 （三）获取并保存jira缺陷数据到mysql数据库

PS：因公司运营管理、审计安全要求，无法直接连接 jira 数据库，所以多此一举，相当于在此重新备份了一个数据库。如果情况允许，还是直接使用jira库最简单准确

 

一、创建数据库

1、MYSQL数据库安装：https://blog.csdn.net/kk_gods/article/details/110651996

2、建表SQL：

CREATE TABLE `jira_issues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aggregateprogress` varchar(20) DEFAULT NULL,
  `aggregatetimeestimate` varchar(20) DEFAULT NULL,
  `aggregatetimeoriginalestimate` varchar(20) DEFAULT NULL,
  `aggregatetimespent` varchar(20) DEFAULT NULL,
  `assignee` varchar(20) DEFAULT NULL,
  `components` varchar(20) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `creator` varchar(20) DEFAULT NULL,
  `customfield_10000` text,
  `customfield_10100` varchar(20) DEFAULT NULL,
  `customfield_10104` varchar(20) DEFAULT NULL,
  `customfield_10105` varchar(20) DEFAULT NULL,
  `frequency` varchar(20) DEFAULT NULL COMMENT '出现频率customFieldId10200',
  `issue_type` varchar(20) DEFAULT NULL COMMENT '缺陷类型customFieldId10413',
  `severity_level` varchar(20) DEFAULT NULL COMMENT '严重等级 customFieldId10207',
  `repair_date` varchar(20) DEFAULT NULL COMMENT '修复日期customFieldId10300',
  `verify_date` varchar(20) DEFAULT NULL COMMENT '验证日期customFieldId10301',
  `refused_date` varchar(20) DEFAULT NULL COMMENT '拒绝日期customFieldId10400',
  `repeat_date` varchar(20) DEFAULT NULL COMMENT '重复日期customFieldId10401',
  `reopen_date` varchar(20) DEFAULT NULL COMMENT '打回日期customFieldId10402',
  `reopen_count` int(11) DEFAULT NULL COMMENT 'Reopen Count customFieldId10403',
  `re_open_count` int(11) DEFAULT NULL COMMENT 'Re Open Count customFieldId10404',
  `re_open_date` varchar(20) DEFAULT NULL COMMENT '重新引入日期customFieldId10405',
  `test_version` varchar(20) DEFAULT NULL COMMENT '测试版本customFieldId10406',
  `review_date` varchar(20) DEFAULT NULL COMMENT '审查日期customFieldId10407',
  `discovery_phase` varchar(20) DEFAULT NULL COMMENT 'customfield_10411发现阶段',
  `source` varchar(20) DEFAULT NULL COMMENT 'customfield_10410缺陷来源',
  `caused` varchar(20) DEFAULT NULL COMMENT '缺陷原因customFieldId10412',
  `description` text,
  `duedate` datetime DEFAULT NULL,
  `environment` varchar(100) DEFAULT NULL,
  `fixVersions` varchar(100) DEFAULT NULL,
  `issuelinks` varchar(100) DEFAULT NULL,
  `issuetype` varchar(20) DEFAULT NULL,
  `labels` varchar(20) DEFAULT NULL,
  `lastViewed` varchar(20) DEFAULT NULL,
  `priority` varchar(20) DEFAULT NULL,
  `progress` varchar(100) DEFAULT NULL,
  `project` varchar(20) DEFAULT NULL,
  `reporter` varchar(20) DEFAULT NULL,
  `resolution` varchar(20) DEFAULT NULL,
  `resolutiondate` varchar(50) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `subtasks` varchar(20) DEFAULT NULL,
  `summary` text,
  `timeestimate` varchar(20) DEFAULT NULL,
  `timeoriginalestimate` varchar(20) DEFAULT NULL,
  `timespent` varchar(20) DEFAULT NULL,
  `updated` varchar(20) DEFAULT NULL,
  `versions` varchar(20) DEFAULT NULL,
  `votes` varchar(20) DEFAULT NULL,
  `watches` varchar(20) DEFAULT NULL,
  `workratio` varchar(20) DEFAULT NULL,
  `loading_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `assignee_index` (`assignee`) USING BTREE,
  KEY `creator_index` (`creator`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=34312 DEFAULT CHARSET=utf8mb4;

二、获取、保存数据

1、创建jira连接

test_jira = JIRA('http://jira.test.cn', basic_auth=("username", "password"))

2、遍历所有的项目列表

my_projects = test_jira.projects()
for project in my_projects:
    project_name = project.name

3、按照项目获取上一天的所有缺陷

（1）获取当天日期和上一天日期

class NowDatetime:
    # 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
    def get_date(self, day):
        today = datetime.datetime.now()
        # 计算偏移量
        offset = datetime.timedelta(days=-day)
        # 获取想要的日期的时间
        re_date = (today + offset).strftime("%Y-%m-%d")
        return re_date

yesterday = NowDatetime().get_date(1)
today = NowDatetime().get_date(0)

（2）获取上一天的缺陷数据，保存到数据库

数据库连接：https://blog.csdn.net/kk_gods/article/details/109053646

def load_issues(project_name):
    for issue in test_jira.search_issues(
            "project = {} AND issuetype = 故障 AND created >= {} AND created <= {}".format(project_name, yesterday, today),
            maxResults=1000):
        # print(issue.fields.labels)
        sql = '''INSERT INTO `jira_issues` SET `id`=id,
                     `aggregateprogress`='{}',
                     `aggregatetimeestimate`={},
                     `aggregatetimeoriginalestimate`={},
                     `aggregatetimespent`={},
                     `assignee`='{}',
                     `components`='{}',
                     `created`='{}',
                     `creator`='{}',
                     `customfield_10000`='{}',
                     `customfield_10100`='{}',
                     `customfield_10104`='{}',
                     `customfield_10105`='{}',
                     `frequency`='{}',
                     `issue_type`='{}',
                     `severity_level`='{}',
                     `repair_date`='{}',
                     `verify_date`='{}',
                     `refused_date`='{}',
                     `repeat_date`='{}',
                     `reopen_date`='{}',
                     `reopen_count`='{}',
                     `re_open_count`='{}',
                     `re_open_date`='{}',
                     `test_version`='{}',
                     `review_date`='{}',
                     `discovery_phase`='{}',
                     `source`='{}',
                     `caused`='{}',
                     `description`='{}',
                     `duedate`='{}',
                     `environment`='{}',
                     `fixVersions`='{}',
                     `issuelinks`='{}',
                     `issuetype`='{}',
                     `labels`='{}',
                     `lastViewed`='{}',
                     `priority`='{}',
                     `progress`='{}',
                     `project`='{}',
                     `reporter`='{}',
                     `resolution`='{}',
                     `resolutiondate`='{}',
                     `status`='{}',
                     `subtasks`='{}',
                     `summary`='{}',
                     `timeestimate`='{}',
                     `timeoriginalestimate`='{}',
                     `timespent`='{}',
                     `updated`='{}',
                     `versions`='{}',
                     `votes`='{}',
                     `watches`='{}',
                     `workratio`='{}',
                     `loading_time`='{}'
                '''.format(issue.fields.aggregateprogress.progress,
                           issue.fields.aggregatetimeestimate if issue.fields.aggregatetimeestimate else 'NULL',
                           issue.fields.aggregatetimeoriginalestimate if issue.fields.aggregatetimeoriginalestimate else 'NULL',
                           issue.fields.aggregatetimespent if issue.fields.aggregatetimespent else 'NULL',
                           issue.fields.assignee,
                           issue.fields.components[0].name,
                           datetime.strptime(format_date_tz(issue.fields.created),
                                             '%Y-%m-%d %H:%M:%S'),
                           issue.fields.creator,
                           issue.fields.customfield_10000.title if issue.fields.customfield_10000 else 0,  #
                           issue.fields.customfield_10100 if issue.fields.customfield_10100 else 'NULL',  #
                           issue.fields.customfield_10104 if issue.fields.customfield_10104 else 'NULL',  #
                           issue.fields.customfield_10105 if issue.fields.customfield_10105 else 'NULL',  #
                           issue.fields.customfield_10200 if issue.fields.customfield_10200 else 'NULL',  # 出现频率
                           issue.fields.customfield_10413 if issue.fields.customfield_10413 else 'NULL',  # 缺陷类型
                           issue.fields.customfield_10207 if issue.fields.customfield_10207 else 'NULL',  # 严重等级
                           issue.fields.customfield_10300 if issue.fields.customfield_10300 else 'NULL',  # 修复日期
                           issue.fields.customfield_10301 if issue.fields.customfield_10301 else 'NULL',  # 验证日期
                           issue.fields.customfield_10400 if issue.fields.customfield_10400 else 'NULL',  # 拒绝日期
                           issue.fields.customfield_10401 if issue.fields.customfield_10401 else 'NULL',  # 重复日期
                           issue.fields.customfield_10402 if issue.fields.customfield_10402 else 'NULL',  # 打回日期
                           issue.fields.customfield_10403 if issue.fields.customfield_10403 else 0,  # Reopen Count
                           issue.fields.customfield_10404 if issue.fields.customfield_10404 else 0,  # Re Open Count
                           issue.fields.customfield_10405 if issue.fields.customfield_10405 else 'NULL',  # 重新引入日期
                           issue.fields.customfield_10406 if issue.fields.customfield_10406 else 'NULL',  # 测试版本
                           issue.fields.customfield_10407 if issue.fields.customfield_10407 else 'NULL',  # 审查日期
                           issue.fields.customfield_10411 if issue.fields.customfield_10411 else 'NULL',  # 发现阶段
                           issue.fields.customfield_10410 if issue.fields.customfield_10410 else 'NULL',  # 缺陷来源
                           issue.fields.customfield_10412 if issue.fields.customfield_10412 else 'NULL',  # 缺陷原因
                           pymysql.escape_string(issue.fields.description) if issue.fields.description else 'NULL',
                           issue.fields.duedate,
                           pymysql.escape_string(issue.fields.environment) if issue.fields.environment else 'NULL',
                           pymysql.escape_string(issue.fields.fixVersions[0].name) if issue.fields.fixVersions else 'NULL',
                           pymysql.escape_string(issue.fields.issuelinks[0].id) if issue.fields.issuelinks else 'NULL',
                           issue.fields.issuetype,
                           pymysql.escape_string(issue.fields.labels[0]) if issue.fields.labels else 'NULL',
                           datetime.strptime(format_date_tz(issue.fields.lastViewed),
                                             '%Y-%m-%d %H:%M:%S') if issue.fields.lastViewed else 'NULL',
                           issue.fields.priority,
                           issue.fields.progress.progress,
                           # issue.fields.project,
                           project_name,
                           issue.fields.reporter,
                           issue.fields.resolution,
                           datetime.strptime(format_date_tz(issue.fields.resolutiondate),
                                             '%Y-%m-%d %H:%M:%S') if issue.fields.resolutiondate else 'NULL',
                           issue.fields.status,
                           issue.fields.subtasks,
                           issue.fields.summary,
                           issue.fields.timeestimate if issue.fields.timeestimate else 'NULL',
                           issue.fields.timeoriginalestimate if issue.fields.timeoriginalestimate else 'NULL',
                           issue.fields.timespent if issue.fields.timespent else 'NULL',
                           datetime.strptime(format_date_tz(issue.fields.updated),
                                             '%Y-%m-%d %H:%M:%S'),
                           pymysql.escape_string(issue.fields.versions[0].name) if issue.fields.versions else 'NULL',
                           issue.fields.votes,
                           issue.fields.watches.watchCount,
                           issue.fields.workratio,
                           datetime.now()
                           )
        MysqlUntil().mysql_insert(sql)

三、结果

执行后，查看数据库保存成功

my_projects = test_jira.projects()
for project in my_projects:
    project_name = project.name
    load_issues(project_name)