Python收集Redmine bug数据

一、更新日志

1.1、计划

  1. web,每15分钟动态展示Bugs统计数据(计划)。

1.2、优化

  1. Python环境安装。
  2. 添加执行sh执行代码。
  3. 优化代码,可以每15分钟执行一次。
  4. 优化代码,可以指定时间执行。
  5. 优化代码,能查询当天新提交,已关闭,未关闭状态的Bugs数量(The_BUG_today_report)。
  6. 优化代码,The_BUG_today_report添加The_query_time字段,可以查询python运行时间。

1.3、BUG

  1. 解决当日Bug未关闭Bugs数量统计有问题。

二、环境部署

1.1、环境安装

  • 安装python
  • 安装python相关的包
sudo apt-get install libmysqld-dev 2pip install mysql-python 3pip install xlwt 4pip install python-redmine 5pip install progressbar 
  • 在Ubuntu上运行
nohup python -u bugs.py > bugs.log 2>&1 &

1.2、MySQL

1.2.1 、Bugs数据表

 CREATE TABLE `Bugs` (
   `id` int(11) NOT NULL,
   `num` int(11) DEFAULT NULL,
   `project` varchar(255) DEFAULT NULL,
   `status` varchar(255) DEFAULT NULL,
   `priority` varchar(255) DEFAULT NULL,
   `tracker` varchar(255) DEFAULT NULL,
   `author` varchar(255) DEFAULT NULL,
   `subject` varchar(255) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,
  `done_ratio` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2.2、The_BUG_month_report数据表

CREATE TABLE `The_BUG_month_report` (
  `month` varchar(11) NOT NULL,
  `times` int(11) DEFAULT NULL,
  `closed` varchar(255) DEFAULT NULL,
  `open` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2.3、The_BUG_days_report数据表

CREATE TABLE `The_BUG_days_report` (
  `days` date NOT NULL,
  `times` int(11) DEFAULT NULL,
  `closed` int(11) DEFAULT NULL,
  `open` int(11) DEFAULT NULL,
  PRIMARY KEY (`days`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2.3、The_BUG_weeks_report数据表

CREATE TABLE `The_BUG_weeks_report` (
  `week` varchar(255) NOT NULL,
  `times` int(11) DEFAULT NULL,
  `closed` varchar(255) DEFAULT NULL,
  `open` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`week`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2.4、The_BUG_year_report数据表

CREATE TABLE `The_BUG_year_report` (
  `years` varchar(255) NOT NULL,
  `times` varchar(255) DEFAULT NULL,
  `closed` varchar(255) DEFAULT NULL,
  `open` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`years`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2.5、The_BUG_today_report数据表

CREATE TABLE `The_BUG_today_report` (
  `days` date NOT NULL,
  `new` int(10) DEFAULT NULL,
  `closed` int(10) DEFAULT NULL,
  `open` int(10) DEFAULT NULL,
  `The_query_time` datetime DEFAULT NULL,
  PRIMARY KEY (`days`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、代码

2.1、每15分钟运行一次代码

#__author__ = 'chenghoufeng'
# -*- coding: utf-8 -*-

import MySQLdb
import xlwt
import time
import sys
from redminelib import Redmine

# 连接Redmine
def set_Redmine():
    REDMINE_URL = 'http://redmine.******.cn' #redmine 的地址
    REDMINE_KEY = '*****'#这个是自己redmine的账号
    redmine = Redmine(REDMINE_URL,key=REDMINE_KEY)
    issues = redmine.issue.all(project_id = 'ptq',status_id='*',tracker_id=None)
    return issues

# 清空全部数据表
def Empty_data():
     # 打开数据库连接
    db = MySQLdb.connect("localhost", "bug", "******", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    sql  = "truncate table Bugs"
    sql1 = "truncate table The_BUG_days_report"
    sql2 = "truncate table The_BUG_weeks_report"
    sql3 = "truncate table The_BUG_month_report"
    sql4 = "truncate table The_BUG_year_report"
    print_ts(u'清空全部数据表')
    try:
    # 执行sql语句
        #需要清除bug表的数据
        cursor.execute(sql)
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.execute(sql3)
        cursor.execute(sql4)
    #提交数据
        db.commit()
    except:
    #发生错误时回滚
        print u'回滚'
        db.rollback()
    # 关闭数据库连接
    db.close()
    
def bugs():
    # 先清空数据库的全部数据表
    Empty_data()
    print_ts(u'收集bugs数据。')
    # 打开数据库连接
    db = MySQLdb.connect("localhost", "bug", "******", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    for i in set_Redmine():
        # SQL插入语句
        sql = "INSERT INTO Bugs
            (id,project,status,priority,tracker,author,subject,start_date,done_ratio,num,updated_on)
            SELECT %d,'%s','%s','%s','%s','%s','%s','%s','%s','1','%s' FROM DUAL WHERE NOT EXISTS (SELECT id FROM Bugs WHERE id = %d and project = '%s')" %
            (i.id,i.project,i.status,i.priority,i.tracker,i.author,i.subject,i.start_date,i.done_ratio,i.updated_on,i.id,i.project)
        #print sql
        try:
        # 执行sql语句
            cursor.execute(sql)
            #提交数据
            db.commit()
        except:
            #发生错误时回滚
            print u'回滚'
            db.rollback()
    # 关闭数据库连接   
    db.close()
    bug_statistical_calculation()

# 按照日,周,月,年统计bugs数量
def bug_statistical_calculation():

    # 打开数据库连接
    db = MySQLdb.connect("localhost", "bug", "******", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    sql1 = "REPLACE  INTO The_BUG_days_report(days,times) SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS DAY,SUM(`num`) AS count FROM Bugs GROUP BY DAY"
    sql2 = "REPLACE  INTO The_BUG_weeks_report (WEEK, times) SELECT WEEK (DATE_ADD(start_date,INTERVAL 7 DAY)) AS WEEK,SUM(`num`) AS count FROM Bugs GROUP BY WEEK (DATE_ADD(start_date,INTERVAL 7 DAY))"
    sql3 = "REPLACE  INTO The_BUG_month_report(month,times) SELECT MONTH(start_date) as month,SUM(`num`) AS count FROM Bugs GROUP BY MONTH"
    sql4 = "REPLACE  INTO The_BUG_year_report(years,times) SELECT YEAR(start_date) as years,SUM(`num`) AS count FROM Bugs GROUP BY YEAR(start_date)"
    sql5 = "DELETE FROM Bugs WHERE tracker = '%s'"%
           ('管理')
    sql6= "REPLACE INTO The_BUG_today_report(days,closed,open,new) VALUES(
            DATE_FORMAT(NOW(),'%Y-%m-%d'),
            IFNULL((SELECT SUM(`num`) AS count FROM Bugs WHERE DATE_FORMAT(updated_on,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') and `status` = '已关闭'),0),
            IFNULL((SELECT SUM(`num`) AS count FROM Bugs WHERE  `status` = '新建'  or `status` = '进行中'  or `status` = '已解决' or `status` = '已提交' or `status` = '反馈' or `status` = '已拒绝'or `status` = '重复单' or `status` = '延迟修改'or `status` = '开发完成'),0),
            IFNULL((SELECT SUM(`num`) AS count FROM Bugs WHERE DATE_FORMAT(start_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')),0)IFNULL((SELECT SUM(`num`) AS count FROM Bugs WHERE DATE_FORMAT(start_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')),0)
            ,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'))"
    try:
        print_ts(u'统计,日,周,月,年bugs数量。')
        # 执行sql语句
        cursor.execute(sql5)
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.execute(sql3)
        cursor.execute(sql4)
        cursor.execute(sql6)
        db.commit()
    except:
        #发生错误时回滚
        print u'回滚'
        #print sql
        db.rollback()
    # 关闭数据库连接
    print_ts("运行结束。")
    print_ts("-"*61)
    db.close()
    
def print_ts(message):
    print "[%s] %s"%(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), message)
    
def run(interval):
    print_ts("-"*61)
    print_ts(u"每 %s 秒执行一次."%interval)
    print_ts("-"*61)
    while True:
        try:
            # sleep for the remaining seconds of interval
            time_remaining = interval-time.time()%interval
            print_ts(u"开始执行时间: %s (%s 秒)..."%((time.ctime(time.time()+time_remaining)), time_remaining))
            time.sleep(time_remaining)
            print_ts("开始运行。")
            print_ts("-"*61)
            bugs()
        except Exception, e:
            print e

if __name__ == '__main__':
    # 收集,统计bug数据
    reload(sys)
    sys.setdefaultencoding('utf-8')
    # 每15分钟(1800秒)收集一次Bugs数据,并进行统计。
    interval = 900
    run(interval)

2.2 指定时间运行

#__author__ = 'chenghoufeng'
# -*- coding: utf-8 -*-

import MySQLdb
import xlwt
import time
import datetime
import threading
from redminelib import Redmine
from progressbar import ProgressBar

# 连接Redmine
def set_Redmine():
    REDMINE_URL = 'Redmine网址' #redmine 的地址
    REDMINE_KEY = 'key'#这个是自己redmine的账号
    redmine = Redmine(REDMINE_URL,key=REDMINE_KEY)
    issues = redmine.issue.all(project_id = 'ptq',status_id='*',tracker_id=None)
    return issues

# 清空全部数据表
def Empty_data():
     # 打开数据库连接
    db = MySQLdb.connect("localhost", "test", "123456", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    sql  = "truncate table bugs"
    sql1 = "truncate table The_bug_days_report"
    sql2 = "truncate table The_bug_weeks_report"
    sql3 = "truncate table The_bug_month_report"
    sql4 = "truncate table The_bug_year_report"
    print u'一、清空全部数据表'
    try:
    # 执行sql语句
        #需要清除bug表的数据
        print u'1:清除收集的Redmine的bug数据'
        cursor.execute(sql)
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.execute(sql3)
        cursor.execute(sql4)
    #提交数据
        db.commit()
    except:
    #发生错误时回滚
        print u'回滚'
        db.rollback()
    # 关闭数据库连接
    db.close()
    
def bugs():
    # 先清空数据库的全部数据表
    Empty_data()
    print u'二、收集bugs数据'
    # 打开数据库连接
    db = MySQLdb.connect("localhost", "test", "123456", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    pbar = ProgressBar()    
    for i in pbar(set_Redmine()):
        # SQL插入语句
        sql = "INSERT INTO bugs
            (id,project,status,priority,tracker,author,subject,start_date,done_ratio,num,updated_on)
            SELECT %d,'%s','%s','%s','%s','%s','%s','%s','%s','1','%s' FROM DUAL WHERE NOT EXISTS (SELECT id FROM bugs WHERE id = %d and project = '%s')" %
            (i.id,i.project,i.status,i.priority,i.tracker,i.author,i.subject,i.start_date,i.done_ratio,i.updated_on,i.id,i.project)
        #print sql
        try:
        # 执行sql语句
            cursor.execute(sql)
        #提交数据
            db.commit()
        except:
        #发生错误时回滚
            print u'回滚'
            print i.id
            print sql
            db.rollback()
    # 关闭数据库连接   
    db.close()
    bug_statistical_calculation()
    #如果需要循环调用,就要添加以下方法
    timer = threading.Timer(86400, bugs)
    timer.start()

# 按照日,周,月,年统计bugs数量
def bug_statistical_calculation():

    # 打开数据库连接
    db = MySQLdb.connect("localhost", "test", "123456", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    # 删除紧急bug和优化立项的bug(不统计在内)
    #sql  = "DELETE FROM bugs where project = '%s'"%('紧急bug和市场优化立项')
    sql1 = "INSERT IGNORE INTO The_bug_days_report(days,times) SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS DAY,SUM(`num`) AS count FROM bugs GROUP BY DAY"
    sql2 = "INSERT IGNORE INTO The_bug_weeks_report (WEEK, times) SELECT WEEK (DATE_ADD(start_date,INTERVAL 7 DAY)) AS WEEK,SUM(`num`) AS count FROM bugs GROUP BY WEEK (DATE_ADD(start_date,INTERVAL 7 DAY))"
    sql3 = "INSERT IGNORE INTO The_bug_month_report(month,times) SELECT MONTH(start_date) as month,SUM(`num`) AS count FROM bugs GROUP BY MONTH"
    sql4 = "INSERT IGNORE INTO The_bug_year_report(years,times) SELECT YEAR(start_date) as years,SUM(`num`) AS count FROM bugs GROUP BY YEAR(start_date)"
    sql5 = "DELETE FROM bugs WHERE tracker = '%s'"%
           ('管理')
    try:
        print u'三、统计,日,周,月,年bugs数量'
        # 执行sql语句
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.execute(sql3)
        cursor.execute(sql4)
        cursor.execute(sql5)
        db.commit()
    except:
        #发生错误时回滚
        print u'回滚'
        db.rollback()
    # 关闭数据库连接
    db.close()


if __name__ == '__main__':
    # 收集,统计bug数据
    #bugs()
    # 获取现在时间
    now_time = datetime.datetime.now()
    # 获取明天时间
    next_time = now_time + datetime.timedelta(days=+1)
    next_year = next_time.date().year
    next_month = next_time.date().month
    next_day = next_time.date().day
    # 获取明天3点时间
    next_time = datetime.datetime.strptime(str(next_year)+"-"+str(next_month)+"-"+str(next_day)+" 17:20:00", "%Y-%m-%d %H:%M:%S")
    # # 获取昨天时间
    # last_time = now_time + datetime.timedelta(days=-1)

    # 获取距离明天3点时间,单位为秒
    timer_start_time = (next_time - now_time).total_seconds()
    print u'开始执行时间:'
    print (next_time)
    print u'程序启动时间:'
    print(now_time)
    # print(timer_start_time)
    # 54186.75975

    #定时器,参数为(多少时间后执行,单位为秒,执行的方法)
    timer = threading.Timer(timer_start_time, bugs)
    timer.start()

2.3、原始代码

#__author__ = 'chenghoufeng'
# -*- coding: utf-8 -*-
# 原始代码
 
import MySQLdb
import xlwt
import time
from redminelib import Redmine
from progressbar import ProgressBar

# 连接Redmine
def set_Redmine():
    REDMINE_URL = '***********************' #redmine 的地址
    REDMINE_KEY = '***********************'#这个是自己redmine的账号
    redmine = Redmine(REDMINE_URL,key=REDMINE_KEY)
    issues = redmine.issue.all(project_id = 'ptq',status_id='*',tracker_id=None)
    return issues

# 清空全部数据表
def Empty_data():
     # 打开数据库连接
    db = MySQLdb.connect("localhost", "test", "123456", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    sql  = "truncate table bugs"
    sql1 = "truncate table The_bug_days_report"
    sql2 = "truncate table The_bug_weeks_report"
    sql3 = "truncate table The_bug_month_report"
    sql4 = "truncate table The_bug_year_report"
    print u'一、清空全部数据表'
    try:
    # 执行sql语句
        #需要清除bug表的数据
        print u'1:清除收集的Redmine的bug数据'
        cursor.execute(sql)
        print u'2:清除日数据'
        cursor.execute(sql1)
        print u'3:清除周数据'
        cursor.execute(sql2)
        print u'4:清除月数据'
        cursor.execute(sql3)
        print u'5:清除年数据'
        cursor.execute(sql4)
    #提交数据
        db.commit()
    except:
    #发生错误时回滚
        print u'回滚'
        db.rollback()
    # 关闭数据库连接
    db.close()
    
def bugs():
    # 先清空数据库的全部数据表
    Empty_data()
    print u'二、收集bugs数据'
    # 打开数据库连接
    db = MySQLdb.connect("localhost", "test", "123456", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    pbar = ProgressBar()    
    for i in pbar(set_Redmine()):
        # SQL插入语句
        sql = "INSERT INTO bugs
            (id,project,status,priority,tracker,author,subject,start_date,done_ratio,num,updated_on)
            SELECT %d,'%s','%s','%s','%s','%s','%s','%s','%s','1','%s' FROM DUAL WHERE NOT EXISTS (SELECT id FROM bugs WHERE id = %d and project = '%s')" %
            (i.id,i.project,i.status,i.priority,i.tracker,i.author,i.subject,i.start_date,i.done_ratio,i.updated_on,i.id,i.project)
        #print sql
        try:
        # 执行sql语句
            cursor.execute(sql)
        #提交数据
            db.commit()
        except:
        #发生错误时回滚
            print u'回滚'
            print i.id
            print sql
            db.rollback()
    # 关闭数据库连接   
    db.close()
    bug_statistical_calculation()

# 按照日,周,月,年统计bugs数量
def bug_statistical_calculation():

    # 打开数据库连接
    db = MySQLdb.connect("localhost", "test", "123456", "bug", charset='utf8' )
    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    # 删除紧急bug和优化立项的bug(不统计在内)
    #sql  = "DELETE FROM bugs where project = '%s'"%('紧急bug和市场优化立项')
    sql1 = "INSERT IGNORE INTO The_bug_days_report(days,times) SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS DAY,SUM(`num`) AS count FROM bugs GROUP BY DAY"
    sql2 = "INSERT IGNORE INTO The_bug_weeks_report (WEEK, times) SELECT WEEK (DATE_ADD(start_date,INTERVAL 7 DAY)) AS WEEK,SUM(`num`) AS count FROM bugs GROUP BY WEEK (DATE_ADD(start_date,INTERVAL 7 DAY))"
    sql3 = "INSERT IGNORE INTO The_bug_month_report(month,times) SELECT MONTH(start_date) as month,SUM(`num`) AS count FROM bugs GROUP BY MONTH"
    sql4 = "INSERT IGNORE INTO The_bug_year_report(years,times) SELECT YEAR(start_date) as years,SUM(`num`) AS count FROM bugs GROUP BY YEAR(start_date)"
    sql5 = "DELETE FROM bugs WHERE tracker = '%s'"%
           ('管理')
    try:
        print u'三、统计,日,周,月,年bugs数量'
        # 执行sql语句
        print u'1:统计每天的bugs数量'
        cursor.execute(sql1)
        print u'2:统计没周的bugs数量'
        cursor.execute(sql2)
        print u'3:统计每月的bugs数量'
        cursor.execute(sql3)
        print u'4:统计每年的bugs数量'
        cursor.execute(sql4)
        print u'5:删除非BUG问题单。'
        cursor.execute(sql5)
        db.commit()
    except:
        #发生错误时回滚
        print u'回滚'
        db.rollback()
    # 关闭数据库连接
    db.close()


if __name__ == '__main__':
    # 收集,统计bug数据
    bugs()



知识共享许可协议本作品采用知识共享署名-相同方式共享 4.0 国际许可协议进行许可。

相关推荐

杭州车辆第一次年检笔记

这个月刷抖音,发现有车辆年检团购。下个月25号就是车辆6年免检到期;所有就在抖音上下单,269元; 周六到达车 ...

二十届三中全会学习心得

党的二十届三中全会提出了建设美丽中国和推动经济社会全面绿色转型的重要任务。这一理念不仅对国家发展具有深远意义, ...

暂无评论

目录展开