记第一次数据分析

[3763, ‘明‘, ‘预发-灰度环境部署成功‘, ‘1570599052000‘, ‘1570599052000‘, ‘成功\r‘, ‘2019-10-09 13:30:52‘, ‘grayPub‘]
[3764, ‘明‘, ‘代码合并成功  ‘, ‘1570598557000‘, ‘1570598557000‘, ‘成功\r‘, ‘2019-10-09 13:22:37‘, ‘codemergesuccess‘]
[3765, ‘明‘, ‘代码合并冲突  ‘, ‘1570598415000‘, ‘1570598415000‘, ‘失败\r‘, ‘2019-10-09 13:20:15‘, ‘codemergefail‘]
[3766, ‘明‘, ‘代码合并冲突  ‘, ‘1570598349000‘, ‘1570598349000‘, ‘失败\r‘, ‘2019-10-09 13:19:09‘, ‘codemergefail‘]
[3767, ‘明‘, ‘将变更 ‘, ‘1570598310000‘, ‘1570598310000‘, ‘成功\r‘, ‘2019-10-09 13:18:30‘, ‘beginChange‘]
[3768, ‘晨‘, ‘代码合并冲突  ‘, ‘1570598040000‘, ‘1570598040000‘, ‘失败\r‘, ‘2019-10-09 13:14:00‘, ‘codemergefail‘]

import datetime
import pymysql
begin_time = datetime.date(2019, 10, 10) # 设置日期起始时间
conn = pymysql.connect(host="127.0.0.1", user="root", password="root", database="test", charset="utf8")
cursor = conn.cursor()
cursor.execute("SELECT * FROM test3;")
row = cursor.fetchall()
cursor.close()
allList = list(row) #allList即为表一行一个元素组成的列表
collectList = [] # 定义一个列表最后把结果都放这里面
##### 按天分片 #####
t = 0
# 只取起始日期后31天的数据
while t <= 31:
    dayList = []
    delta = datetime.timedelta(days=t)
    n_days = begin_time + delta
    end_time = n_days.strftime(‘%Y-%m-%d‘)
    for k in allList:
        k = list(k)
        times = k[6].split(‘ ‘)[0]
        if end_time == times:   # 提取匹配当前日期的行加入dayList列表
            dayList.append(k)
    t = t + 1
    if len(dayList) == 0:   # 周末没有部署记录，则没有数据
        pass

    waitTimes = 0
    for l in dayList:

        tempList = []
        if l[7] == ‘grayPub‘:  # 选择灰度发布为发布成功标志
            # 将发布成功行加入tempList临时表
            tempList.append(l)
            waitTimes = waitTimes + 1 # waitTime为当日发布次数
            pubSucman = l[1] 
            pubSuctime = l[3]
            pubSucId = l[0]

            ntime = 0 # while中需要给select传参，循环增加的那种
            # 三个判断
            waitOrnot = ‘false‘
            jiangbiangeng = ‘false‘
            failornot = ‘false‘

            while 1:
                cursor = conn.cursor()
                cursor.execute("select * from test3 where id = %s", pubSucId + ntime)
                beforeLine = cursor.fetchall()
                transList = list(beforeLine)[0]

                if transList[7] == ‘codemergesuccess‘ and transList[1] == pubSucman:
                   # 合并成功行加入tempList临时表
                    tempList.append(transList)
                    waitOrnot = ‘true‘


                elif transList[7] == ‘codemergefail‘ and transList[1] == pubSucman:
                    # 合并失败行加入tempList临时表
                    tempList.append(transList)
                    failornot = ‘true‘


                elif transList[2] == ‘将变更 ‘:
                    # 任务起始行加入tempList临时表
                    tempList.append(transList)
                    jiangbiangeng = ‘true‘
                    break
                ntime = ntime + 1
            cursor.close()
        
            if tempList != [] and jiangbiangeng == ‘true‘:
            #提取一条任务记录进行分析（开始-合入代码成功、失败-发布）
                # print(tempList)
                oneSuccess = tempList[0]
                oneSuctime = oneSuccess[3]
                oneBegin = tempList[-1]
                oneBegintime = oneBegin[3]
                # 一条成功记录总时间
                oneRecordtime = int(oneSuctime) - int(oneBegintime)

                onemergeSuctime = 0  # 代码合并成功时间-codemergesuccess的时间
                onemergeFailtime = 0  # 代码合并冲突最早时间-最后一条codemergefail的时间
                latestmerge = ‘true‘
                if waitOrnot == ‘true‘:
                    for m in tempList:
                        if m[7] == ‘codemergesuccess‘ and latestmerge == ‘true‘:
                                onemergeSuctime = m[3]
                                latestmerge = ‘false‘
                        elif m[7] == ‘codemergefail‘ and failornot == ‘true‘:
                                onemergeFailtime = m[3]

                onemergetime = 0
                # 一条有代码冲突的任务，冲突占用时间
                if onemergeSuctime != 0 and onemergeFailtime != 0:
                    onemergetime = (int(onemergeSuctime) - int(onemergeFailtime)) * (waitTimes - 1)

                print(end_time, pubSucman, oneRecordtime, onemergetime, waitTimes)
                cursor = conn.cursor()
                input_sql = "INSERT INTO collecttime(date, creatorname, pubonetime, failcosttime, failtime) VALUES (%s,%s,%s,%s,%s)"
                timeValue = (end_time, pubSucman, oneRecordtime, onemergetime, waitTimes)
                try:
                    # 执行sql语句
                    cursor.execute(input_sql, timeValue)
                    print(‘to mysql success‘)
                    # 提交到数据库执行
                    conn.commit()
                except:
                    # 如果发生错误则回滚
                    conn.rollback()
                    print(‘to mysql fail‘)
        else:
            pass

cursor.close()
conn.close()