Retry数据管理系统

目标：通过pyhon 进行Derby数据库中读取数据，统计分析

1.python读取Derby数据库

介绍：Apache Derby 是100% Java 编写的内存数据库，属于 Apache 的一个开源项目。并且是一个容易管理的关系数据库管理系统，可以和一些商业产品的特性进行交付。
Apache Derby 是一个与平台无关的数据库引擎，它以 Java 类库的形式对外提供服务。与其他难以部署的数据库不同， Derby 数据库体积小、安装非常简单，只需要将其 *.jar 文件复制到系统中并为用户的项目添加该 *.jar 文件即可。

python调用方法：通过cmd命令，将要执行的ij命令写入",txt"格式的文档中，通过python的cmd命令执行方式“ij 文件地址”来进行数据库访问

2.将学到的内容：异常捕获，tkinter显示图像，tkinter设置字体，多线程的使用，

  异常捕获十分方便程序开发时，当未每一个函数添加抛出异常时，可以方便判定错误出现的函数

 图像显示：Canvas实现

多线程使用：（代码还未实现多线程）参考文档 https://blog.csdn.net/qq_42233538/article/details/86659085

3.生成exe格式可执行文件

  pyinstaller -F xxx.py 参考文档 https://blog.csdn.net/qq_35203425/article/details/78568141

4.python-Tkinter图形界面开发

  1.按钮Button   2.文本框 Text  3.容器 LabelFrame 4.复选框 Checkbutton 5.画布Canvas 6.设置字体

界面：

代码：（涉及到三个文件之间的调用）

模块1：从ftp上进行文件下载，涉及的只是包括：ftp文件的操作，删除非空文件夹

from ftplib import FTP
import time
import os
import shutil
import zipfile
class FtpDownFiles():
    def __init__(self):
        self.host="10.81.254.242"
        self.username="boedtzdhab"
        self.password="boedtzdhab49"
        self.from_path='./60000679/Retry/'
        self.to_path='H:/retryViewer\SSDB'
        # self.stk_sum=['AFST01','AFST02','AFST03','AFST04','AFST05','AFST06','AFST07','AFST08','AFST09',
        #          'TFST01', 'TFST02', 'TFST03', 'TFST04','CFST01','CFST02','CFST03','CFST04',]
        # self.stk_sum =ftplist
    def __myzipfile__(self,path):
        #解压zip格式文件
        file = 'sss_db.zip'
        zip_file = zipfile.ZipFile(os.path.join(path, file))
        for names in zip_file.namelist():
            zip_file.extract(names, path)
        zip_file.close()

    def ftpconnect(self):
        #连接服务器
        self.ftp = FTP()
        # ftp.set_debuglevel(2)
        self.ftp.connect(self.host, 21)
        self.ftp.login(self.username, self.password)

    def __ftpDownloadFile__(self, ftpfile, localfile):
        #单个文件下载文件
        try:
            file_list=self.ftp.nlst(ftpfile)
        except Exception as error:
            # print("%s下载错误"%ftpfile)
            raise Exception(error)
            # print(error)
        else:
            ftpfile = os.path.join(ftpfile, 'sss_db.zip')
            localfile1 = os.path.join(localfile, 'sss_db.zip')
            bufsize = 1024
            with open(localfile1, 'wb') as fd:
                self.ftp.retrbinary('RETR {}'.format(ftpfile), fd.write, bufsize)
            self.__myzipfile__(localfile)

    def ftpDownloadFiles(self,stklist,curpath):

        #多个文件下载文件
        time_str = time.strftime("%Y%m%d", time.localtime())
        # path=os.path.join(self.to_path,time_str)
        # path = os.path.join(curpath, time_str)
        # if os.path.exists(path):
        #     os.removedirs(path)   #递归删除文件夹,只能是空文件夹
            # shutil.rmtree(path)
        # os.mkdir(path)            #创建该文件夹
        # print('路径生成完成')
        cur_stk=[]
        for stk in stklist:
            ftpfile=os.path.join(self.from_path,stk)
            localfile=os.path.join(curpath,stk)
            os.mkdir(localfile)
            try:
                self.__ftpDownloadFile__(ftpfile,localfile)
            except:
                print('%s下载错误'%stk)
            else:
                cur_stk.append(stk)
        self.ftp.close()
        return cur_stk

if __name__ == '__main__':
    ftp=FtpDownFiles()
    ftp.ftpconnect()
    # ftp.ftpDownloadFiles()

 模块2：读取derby数据库，并将内容保存到excel文件中

"""
Retry管理
"""
import numpy as np
import os
import pandas as pd
import shutil
from ftplib import FTP
import time
import os
import zipfile

class RetryManage():
    def __init__(self,path1,path2,path3,stklist,start,end):
        # self.path=os.getcwd()
        self.path = path1   #保存路径
        self.datapath=path2  #数据库路径
        self.savepath = path3  # 数据保存路径
        self.start =start
        self.end =end
        # self.start='2020-02-27'
        # self.end='2020-03-03'
        # self.host="10.81.254.242"
        # self.username="boedtzdhab"
        # self.password="boedtzdhab49"
        # self.from_path='./60000679/Retry/'
        # self.to_path = 'H:/retryViewer\SSDB'
        self.stk_sum =stklist    #获取下载好的文件STK列表
        # self.stk_sum = ['AFST01', 'AFST02', 'AFST03', 'AFST04', 'AFST05', 'AFST06', 'AFST07', 'AFST08', 'AFST09',
        #                 'TFST01', 'TFST02', 'TFST03', 'TFST04', 'CFST01', 'CFST02', 'CFST03', 'CFST04', ]
        # print(self.path)
        # self.ftpconnect()
        # self.ftpDownloadFiles()
        # self.readretry()

    def readretry(self):
        curstk=[]
        self.creatcmd(self.start, self.end)
        # cmdpath = os.path.join(self.path + "\cmdfile")
        cmdpath=self.path
        if os.path.exists(cmdpath):
            pathlist=os.listdir(cmdpath)
            for file in pathlist:
                cmdpath1 = os.path.join(cmdpath,file)
                if os.path.isfile(cmdpath1):
                    filename=os.path.basename(cmdpath1)
                    # filepath1=os.path.join(self.path,'retry_data')
                    # if not os.path.exists(filepath1):
                    #     os.mkdir(filepath1)
                    # filepath=os.path.join(self.path,'retry_data',filename[:-4]+'.csv')
                    filepath = os.path.join(self.savepath,  filename[:-4] + '.csv')
                    cmd='ij'+' '+cmdpath1
                    # print(cmd)
                    try:
                        text=self.execmd(cmd)
                        self.writefile(filepath,text)
                    except:
                        print(filename[:-4],'数据获取失败')
                    else:
                        curstk.append(filename[:-4])
        return curstk

    def creatcmd(self,start,end):
        # 生成ij需要执行的命令文件
        #在self.path文件夹下创建cmdfile，用于保存生成的cmd文件
        try:
            # cmdpath=os.path.join(self.path+"\cmdfile")
            # print(cmdpath)
            # if os.path.exists(cmdpath):
            #     shutil.rmtree(cmdpath)
            # os.mkdir(cmdpath)
            cmdpath=self.path
            for stk in self.stk_sum:
                # path = os.path.join(self.path, self.time_str, stk, 'sss_db')
                path = os.path.join(self.datapath, stk, 'sss_db')
                if os.path.exists(path):
                    filepath = os.path.join(cmdpath, stk + '.txt')
                    string1='CONNECT '+"'"+'jdbc:derby:'+path+";"+"'"+";"
                    statime=start+' 00:00:00.000'
                    endtime=end+' 00:00:00.000'
                    string2='select * from retrydata  where DTRETRYTIME between'+" '"+statime+"'" +' and '+ "'"+endtime+"'"+';'
                    # with open(filepath,'w',encoding='utf-8') as f:
                    with open(filepath, 'w', encoding='gbk') as f:
                        f.write(string1+'\n'+string2)
        except:
            raise Exception("ij文件生成失败")

    def execmd(self,cmd):
        # 执行'ij '命令进行数据刷取
        text=''
        try:
            r = os.popen(cmd)
            text = r.read()
        except:
            raise Exception("数据库读取失败")
        finally:
            r.close()
            return text
        # return text
    def writefile(self,filename, data):
        # 将'ij'生成的数据写入到filename的路径中
        try:
            strlist = data.split('\n')
            data1 = []
            for i, item in enumerate(strlist):
                curlist = item.split('|')
                if len(curlist) == 8 and i != 3:
                    curlist=[s.strip() for s in curlist]
                    data1.append(curlist)
            if len(data1)>0:
                data2 = np.array(data1).T
                col = {'dtRetryTime': data2[0], 'strCraneID': data2[1], 'strFrom': data2[2], 'strTo': data2[3],
                   'iLocationType': data2[4], 'strLocation': data2[5], 'iActiveType': data2[6], 'iRetrySec': data2[7]}
                text = pd.DataFrame(col)
                text.to_csv(filename, index=False)
            else:
                data3={'dtRetryTime':[],'strCraneID':[],'strFrom':[],'strTo':[],'iLocationType':[],
                'strLocation':[],'iActiveType':[],'iRetrySec':[]}
                text1=pd.DataFrame(data3)
                text1.to_csv(filename, index=False)
        except:
            raise Exception("数据转换失败")
            # text.to_csv('H:\软件安装\python\programe\\ab.csv', index=False)

if __name__ == '__main__':
    # cmd1 = 'ij H:\软件安装\python\programe\ijcmd.txt'
    # cmd='ij H:\软件安装\python\programe\cmdfile\AFST02.txt'
    # cmd1='ij H:\软件安装\python\programe\cmdfile\AFST01.txt'
    # path = 'H:\软件安装\python\programe\\ab.csv'
    retry=RetryManage()
    retrymanage=retrymange.Retry_Manage()
    # retry.creatcmd('AFST01','H:\\retryViewer\SSDB\\20200302\AFST08\sss_db','2020-02-27','2020-03-03')
    # text=retry.execmd(cmd)
    # print(text)
    # retry.writefile(path,text)

 模块3：读取excel文件，进行数据的统计分析

import pandas as pd
import os
import time
import matplotlib.pyplot as plt
class Retry_Manage():
    def __init__(self,path):
        self.my_data = []
        self.sum_data=[]
        self.datapath=path
        self.path_creat()

        if len(self.path)>0:
            for p in self.path:
                try:
                    self.retry_make(p)
                except:
                    print("文件%s打开错误"%os.path.basename(p))
                else:
                    print("文件%s打开完成" % os.path.basename(p))

        self.sum_retry()
        try:
            self.retry_save()
            self.figsave()
        except IOError as error:
            print("文件写入失败",error)
        else:
            print("文件写入完成")

    def path_creat(self):

        # path_name=os.listdir('./retry_data')   #返回文件名称
        # abs_path=os.path.abspath('./retry_data')
        path_name=os.listdir(self.datapath)   #返回文件名称
        abs_path=os.path.abspath(self.datapath)
        self.path=[os.path.join(abs_path,p) for p in path_name  if p[1:4]=='FST']


        # print(self.path)
    def retry_make(self,path):
        # print(path)
        STK=os.path.basename(path)[:6]
        data = pd.read_csv(path)

        iretrysec = list(data['iRetrySec'])
        strLocation = list(data['strLocation'])
        strCraneID = list(data['strCraneID'])

        location = []   #retry位置
        craneid = []    #retry车
        retrysec = []   #retry时间
        for i, loc in enumerate(strLocation):
            if loc not in location:
                location.append(loc)
                craneid.append(strCraneID[i])
                retrysec.append(iretrysec[i])
        loc_dict = {}  # retry次数统计
        crane_dict = {}  # retry台车
        sec_dict = {}  # retry时间
        for j, loc in enumerate(location):
            str_count = strLocation.count(loc)
            loc_dict[loc] = str_count
            crane_dict[loc] = craneid[j]
            sec_dict[loc] = retrysec[j]

        for loc in location:
            data = []
            data.append(STK)
            data.append(loc)
            data.append(crane_dict[loc])
            data.append(loc_dict[loc])
            data.append(sec_dict[loc])
            self.my_data.append(data)  # append与expend的区别
    def sum_retry(self):
        STK_SUM={'AFST01':0.0,'AFST02':0.0,'AFST03':0.0,'AFST04':0.0,'AFST05':0.0,'AFST06':0.0,'AFST07':0.0,'AFST08':0.0,'AFST09':0.0,
                 'TFST01': 0.0, 'TFST02': 0.0, 'TFST03': 0.0, 'TFST04': 0.0,'CFST01':0.0,'CFST02':0.0,'CFST03':0.0,'CFST04':0.0,}
        # stk_id=self.my_data[:,0]
        for i in range(len(self.my_data)):
            cur_data=self.my_data[i]
            cur_time=cur_data[3]*cur_data[4]
            STK_SUM[cur_data[0]]+=cur_time
        # print(STK_SUM)
        for x in STK_SUM:
            data=[]
            data.append(x)
            data.append(STK_SUM[x]/60)
            self.sum_data.append(data)
        self.stksum=STK_SUM


    def retry_save(self):
        time_str=time.strftime("%Y%m%d", time.localtime())
        data = pd.DataFrame(data=self.my_data, columns=['STK', "strLocation", 'strCraneID', 'RetryCount', 'RetrySec'])
        # data.to_csv('H:/retryViewer\SSDB/20191231/result.csv')
        path1=self.datapath+'/result_'+time_str+'.csv'
        path2 = self.datapath + '/sum_result_' + time_str + '.csv'
        data.to_csv(path1,index=False)
        data_sum = pd.DataFrame(data=self.sum_data, columns=['STK', "sum_time"])
        # data.to_csv('H:/retryViewer\SSDB/20191231/result.csv')
        data_sum.to_csv(path2,index=False)
    def figsave(self):
        sortstk = dict(sorted(self.stksum.items(), key=lambda x: x[1], reverse=True))
        names = list(sortstk.keys())
        names=[name[0]+name[-1:] for name in names]
        values = list(sortstk.values())
        values=[v/60 for v in values ]
        plt.figure()
        plt.bar(names,values)
        path=os.path.join(self.datapath,'sortstk.png')
        plt.savefig(path)


if __name__ == '__main__':
    data=Retry_Manage()

模块4：主模块，进行界面开发

from tkinter import *
import shutil   #删除文件包
import os
import time
import numpy as np
from PIL import Image,ImageTk
import tkinter.font as tkfont
# 私人包
import ftpdown
import getretrydata
import getdata
class RetryFcae():
    def __init__(self):
        self.window=Tk()
        self.stk_sum = ['AFST01', 'AFST02', 'AFST03', 'AFST04', 'AFST05', 'AFST06', 'AFST07', 'AFST08', 'AFST09',
                        'TFST01', 'TFST02', 'TFST03', 'TFST04', 'CFST01', 'CFST02', 'CFST03', 'CFST04', ]
        self.var_l = [StringVar() for _ in range(len(self.stk_sum))]
        self.var_lp = [StringVar() for _ in range(len(self.stk_sum))]
        self.var_r = [StringVar() for _ in range(len(self.stk_sum))]
        self.var_rp = [StringVar() for _ in range(len(self.stk_sum))]
        self.path = os.getcwd()   #获取当前工作目录
        self.time_str = time.strftime("%Y%m%d%H%M", time.localtime())   #生成文件日期后缀
        self.__makedire__()                               #生成工作路径
    def __makedire__(self):
        path=os.path.join(self.path,self.time_str+'_retry')
        if os.path.exists(path):
            shutil.rmtree(path)
        time.sleep(0.1)
        os.mkdir(path)
        self.ftppath=os.path.join(path,'ssdb')
        self.cmdpath = os.path.join(path, 'cmd')
        self.datapath = os.path.join(path, 'data')
        os.mkdir(self.ftppath)
        os.mkdir(self.cmdpath)
        os.mkdir(self.datapath)

    def show_image(self, path):
        # 图像显示
        self.canvas.delete('tag1')
        img_show = Image.open(path)
        img_shape = np.array(img_show)


        img_h, img_w, _ = img_shape.shape  # 返回为图像高度，宽度
        if img_w / 490 >= img_h / 350:
            # img_show = img_show.resize((int(img_h * 380 / img_w), 380))  # resize(h,w)
            img_show = img_show.resize((490, int(img_h * 490 / img_w)))
        else:
            img_show = img_show.resize((int(img_w * 350 / img_h), 350))
        img_show = ImageTk.PhotoImage(img_show)
        self.canvas.create_image(245, 175, image=img_show, tag="tag1")
        self.canvas.image = img_show
    def query(self):
        start = self.text1.get(1.0, 'end')[:-1]
        # start.replace('\n','')
        # print(start,len(start))
        end = self.text2.get(1.0, 'end')[:-1]
        # end.replace('\n','')
        # print(end,len(end))
        if len(start) == 8 and len(end) == 8:
            # １．据集获取需要加载的数据集
            stklist=[s.get() for s in self.var_l]
            curstk=[]        #需要ftp下载的stk
            for i in range(len(self.stk_sum)):
                if stklist[i]=='true':
                    curstk.append(self.stk_sum[i])
            ftp=ftpdown.FtpDownFiles()
            ftp.ftpconnect()
            truestk=ftp.ftpDownloadFiles(curstk,self.ftppath)
            # print(truestk)
            for stk in truestk:
                i=self.stk_sum.index(stk)
                self.var_lp[i].set('true')
            # 2. 生成ij命令
            try:
                # start=self.text1.get(1.0,'end')[:8]
                # end=self.text2.get(1.0,'end')[:8]
                # if len(start)==8 and len(end)==8:
                start1=start[0:4]+'-'+start[4:6]+'-'+start[6:8]
                end1 = end[0:4] + '-' + end[4:6] + '-' + end[6:8]
                    # print(start1,end1)
                dataget=getretrydata.RetryManage(self.cmdpath,self.ftppath,self.datapath,truestk,start1,end1)
                truestk1=dataget.readretry()
                    # print(truestk1)
                for stk in truestk1:
                    i = self.stk_sum.index(stk)
                    self.var_rp[i].set('true')
            except Exception as err:
                print('数据库读取失败',err)
            else:
                # 3．获取需要解析的数据集与已加载数据集合的公共子集
                getdata.Retry_Manage(self.datapath)
                path = os.path.join(self.datapath, 'sortstk.png')
                if os.path.exists(path):
                    self.show_image(path)
        else:
            print('输入时间格式错误')

    def interface(self):
        self.window.title('DFK Retry管理')
        self.window.geometry('800x550+100+100')
        self.window.resizable(width=False,height=False)
        # 指定字体名称、大小、样式
        # ft=tkfont.Font(family='Fixdsys', size=15, weight=tkfont.BOLD,slant=tkfont.ITALIC)
        ft = tkfont.Font(family='Fixdsys', size=15, slant=tkfont.ITALIC)
        # 下载界面
        label_l = LabelFrame(master=self.window,text='加载', width=330, height=280)
        label_l.grid(row=0, column=0, rowspan=17, columnspan=3, sticky='EW', pady=5, padx=5)
        for i,stk in enumerate(self.stk_sum):
            Checkbutton(label_l,text=stk,variable=self.var_l[i], onvalue='true', offvalue='false').grid(row=i, column=0, sticky='EW', pady=1, padx=5)
            Checkbutton(label_l, variable=self.var_lp[i], onvalue='true', offvalue='false').grid(row=i,column=1,sticky='EW',pady=1,padx=5)
            self.var_lp[i].set('false')
            self.var_l[i].set('true')

        # 数据生成界面
        label_r = LabelFrame(master=self.window,text='数据生成',width=330, height=280)
        label_r.grid(row=0, column=4, rowspan=17, columnspan=3, sticky='EW', pady=5, padx=5)
        for i,stk in enumerate(self.stk_sum):
            Checkbutton(label_r,text=stk,variable=self.var_r[i], onvalue='true', offvalue='false').grid(row=i, column=0, sticky='EW', pady=1, padx=5)
            Checkbutton(label_r, variable=self.var_rp[i], onvalue='true', offvalue='false').grid(row=i,column=1,sticky='EW',pady=1,padx=5)
            self.var_rp[i].set('false')
        #时间管理界面
        label_d = LabelFrame(master=self.window,text='查询时间' ,width=330, height=280)
        label_d.grid(row=0, column=8, rowspan=1, columnspan=5, sticky='N', pady=5, padx=5)
        Label(label_d, text="开始时间").grid(row=0, column=0, sticky='EW', pady=5, padx=5)
        self.text1 = Text(label_d, width=10, height=1,font=ft)
        self.text1.grid(row=0, column=1, sticky='EW', pady=5, padx=5)
        Label(label_d, text="结束时间").grid(row=0, column=2, sticky='EW', pady=5, padx=5)
        self.text2 = Text(label_d, width=10, height=1,font=ft)
        # self.text2.tag_config('tag1',font='Fixdsys')
        self.text2.grid(row=0, column=3, sticky='EW', pady=5, padx=5)
        button4 = Button(label_d, text='开始查询', command=self.query, width=16, height=1).grid(row=0, column=4,sticky='EW', pady=5,padx=5)
        #结果显示
        label_u = LabelFrame(self.window,text='图像显示', width=380, height=380)
        label_u.grid(row=2, column=8, sticky='EW', pady=5, padx=5)
        self.canvas = Canvas(label_u, background='white', width=490, height=350)
        self.canvas.grid(sticky='EW', pady=5, padx=10)
        
        self.window.mainloop()


if __name__ == '__main__':

    face=RetryFcae()
    face.interface()