一、更新日志

1.1、计划

web，每15分钟动态展示Bugs统计数据（计划）。

1.2、优化

Python环境安装。
添加执行sh执行代码。
优化代码，可以每15分钟执行一次。
优化代码，可以指定时间执行。
优化代码，能查询当天新提交，已关闭，未关闭状态的Bugs数量（The_BUG_today_report）。
优化代码，The_BUG_today_report添加The_query_time字段，可以查询python运行时间。

1.3、BUG

解决当日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 国际许可协议进行许可。

一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

Python收集Redmine bug数据

一、更新日志

1.1、计划

1.2、优化

1.3、BUG

二、环境部署

1.1、环境安装

1.2、MySQL

1.2.1 、Bugs数据表

1.2.2、The_BUG_month_report数据表

1.2.3、The_BUG_days_report数据表

1.2.3、The_BUG_weeks_report数据表

1.2.4、The_BUG_year_report数据表

1.2.5、The_BUG_today_report数据表

2、代码

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

2.2 指定时间运行

2.3、原始代码

解决运行Python报缺少libmysqlclient.so.20文件

Linux如何添加sh脚本

暂无评论

旅游

导航

测试

Python收集Redmine bug数据

打赏作者

一、更新日志

1.1、计划

1.2、优化

1.3、BUG

二、环境部署

1.1、环境安装

1.2、MySQL

1.2.1 、Bugs数据表

1.2.2、The_BUG_month_report数据表

1.2.3、The_BUG_days_report数据表

1.2.3、The_BUG_weeks_report数据表

1.2.4、The_BUG_year_report数据表

1.2.5、The_BUG_today_report数据表

2、代码

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

2.2 指定时间运行

2.3、原始代码

解决运行Python报缺少libmysqlclient.so.20文件

Linux如何添加sh脚本

相关推荐

暂无评论

搜索

Python收集Redmine bug数据

Python收集Redmine bug数据

打赏作者