python的tkinter做界面连接MySQL（下）

…接上篇

三、数据增删改查及导出至Excel的代码

from tkinter import *
from tkinter import ttk
from tkinter import messagebox
from openpyxl import Workbook
from openpyxl.styles import Font, colors, Alignment
from tkinter import filedialog

import pymysql

class Mo_Rs:
    def __init__(self):
        self.conn = pymysql.connect(
            host = '00.000.00.000',
            user = '***',
            password = '******',
            database = '数据库名',
            charset = 'utf8')
        self.cursor=self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()
    # 显示所有数据
    def show_all_info(self, tree_view, tb_name):
        # 清空表格中显示的内容
        x = tree_view.get_children()
        for item in x:
            tree_view.delete(item)
        try:
            sql = "SELECT `Date`,`Mean`,`Min`,`Max`,`Std_Dev(%%)`,Target,LCL,UCL FROM %s ORDER BY `Date` DESC;" % tb_name
            self.cursor.execute(sql)
            rs_tuple = self.cursor.fetchall()
            for info in rs_tuple:
                tree_view.insert('', 'end', values=info)
        except:
            messagebox.showerror('show failed', '打开失败，请检查网络状态\n或找数据库管理员查询原因')
        self.close()
    # 添加数据
    def add_info(self, tree_view, Date, Mean, Min, Max, Std_Dev, Target, LCL, UCL, tb_name):
        Date = Date.get()
        Mean = Mean.get()
        Min = Min.get()
        Max = Max.get()
        Std_Dev = Std_Dev.get()
        Target = Target.get()
        LCL = LCL.get()
        UCL = UCL.get()     
        if Min == '':
            Min = 'NULL'
        if Max == '':
            Max = 'NULL'
        try:
            if not(Date.strip() and Mean.strip() and Std_Dev.strip()):
                raise ValueError
            if Target.strip() != '' and Target.strip() != '默认值':
                sql1 = "ALTER TABLE %s MODIFY COLUMN Target INT(11) DEFAULT %s;" %\
                       (tb_name, Target.strip())
                self.cursor.execute(sql1)
                self.conn.commit()
            if LCL.strip() != '' and LCL.strip() != '默认值':
                sql2 = "ALTER TABLE %s MODIFY COLUMN LCL INT(11) DEFAULT %s;" %\
                       (tb_name, LCL.strip())
                self.cursor.execute(sql2)
                self.conn.commit()
            if UCL.strip() != '' and UCL.strip() != '默认值':
                sql3 = "ALTER TABLE %s MODIFY COLUMN UCL INT(11) DEFAULT %s;" %\
                       (tb_name, UCL.strip())
                self.cursor.execute(sql3)
                self.conn.commit()
            sql = "INSERT INTO %s(`Date`,`Mean`,`Min`,`Max`,`Std_Dev(%%)`) VALUES('%s',%s,%s,%s,%s);" %\
                  (tb_name, Date, Mean, Min, Max, Std_Dev)
            self.cursor.execute(sql)
            self.conn.commit()
            messagebox.showinfo(title='insert success~', message='数据插入成功')
            
            x = tree_view.get_children()
            for item in x:
                tree_view.delete(item)
            sql = "SELECT `Date`,`Mean`,`Min`,`Max`,`Std_Dev(%%)`,Target,LCL,UCL FROM %s ORDER BY `Date` DESC;" % tb_name
            self.cursor.execute(sql)
            rs_tuple = self.cursor.fetchall()
            for info in rs_tuple:
                tree_view.insert('', 'end', values=info)
        except ValueError:
            messagebox.showerror('insert failed', '插入失败，请检查信息填写是否正确\n（注：前面带“*”号的为必填项）')
        self.close()
    # 查找数据：这里是按日期去查找
    def search_info(self, tree_view, Date, tb_name):
        x = tree_view.get_children()
        for item in x:
            tree_view.delete(item)
        try:
            Date = Date.get()
            if Date.strip() == '':
                messagebox.showinfo('search info', '请填入要查找的日期Date！')
            sql = "SELECT * FROM %s WHERE `Date`='%s';" % (tb_name, Date)
            if self.cursor.execute(sql):
                self.conn.commit
                rs_tuple = self.cursor.fetchall()
                for info in rs_tuple:
                    tree_view.insert('', 'end', values=info)
            else:
                messagebox.showinfo('search info', '未查询到该日期的点检数据')
                sql = "SELECT `Date`,`Mean`,`Min`,`Max`,`Std_Dev(%%)`,Target,LCL,UCL FROM %s ORDER BY `Date` DESC;" % tb_name
                self.cursor.execute(sql)
                rs_tuple = self.cursor.fetchall()
                for info in rs_tuple:
                    tree_view.insert('', 'end', values=info)
        except:
            messagebox.showerror('search failed', '查找失败，请检查日期是否正确')
        self.close()   
    # 修改数据
    def modify_info(self, tree_view, Date, Mean, Min, Max, Std_Dev, tb_name):
        Date = Date.get()
        Mean = Mean.get()
        Min = Min.get()
        Max = Max.get()
        Std_Dev = Std_Dev.get()
        if Min == '':
            Min = 'NULL'
        if Max == '':
            Max = 'NULL'
        if not(Date.strip() and Mean.strip() and Std_Dev.strip()):
            messagebox.showinfo('modify info', '前面带“*”号的不能为空！')
        try:
            sql = "UPDATE %s SET `Mean`=%s, `Min`=%s, `Max`=%s, `Std_Dev(%%)`=%s WHERE `Date`='%s';" % \
                  (tb_name, Mean, Min, Max, Std_Dev, Date)
            self.cursor.execute(sql)
            self.conn.commit()
            messagebox.showinfo(title='modify success~', message='数据修改成功！')
            
            x = tree_view.get_children()
            for item in x:
                tree_view.delete(item)
            sql = "SELECT `Date`,`Mean`,`Min`,`Max`,`Std_Dev(%%)`,Target,LCL,UCL FROM %s ORDER BY `Date` DESC;" % tb_name
            self.cursor.execute(sql)
            rs_tuple = self.cursor.fetchall()
            for info in rs_tuple:
                tree_view.insert('', 'end', values=info)
        except:
            messagebox.showerror('modify failed', '修改失败，日期不能修改！')
        self.close()
    # 删除数据
    def delete_info(self, tree_view, Date, Mean, Std_Dev, tb_name): 
        Date = Date.get()
        Mean = Mean.get()
        Std_Dev = Std_Dev.get()
        if not(Date.strip() and Mean.strip() and Std_Dev.strip()):
            messagebox.showerror('delete failed', '请填入要删除数据\nDate、Mean和Std_Dev(%)必须填写！')
        try:
            sql = "DELETE FROM %s WHERE `Date`='%s' AND `Mean`='%s' AND `Std_Dev(%%)`='%s';" % (tb_name, Date, Mean, Std_Dev)
            if self.cursor.execute(sql):
                if messagebox.askokcancel('提示','确定删除这行数据？'):
                    self.conn.commit()
                    
                    x = tree_view.get_children()
                    for item in x:
                        tree_view.delete(item)                                                              
                    sql = "SELECT `Date`,`Mean`,`Min`,`Max`,`Std_Dev(%%)`,Target,LCL,UCL FROM %s ORDER BY `Date` DESC;" % tb_name
                    self.cursor.execute(sql)
                    rs_tuple = self.cursor.fetchall()
                    for info in rs_tuple:
                        tree_view.insert('', 'end', values=info)
                    messagebox.showinfo('delete success~', '数据删除成功')
            else:
                messagebox.showerror('delete info', '删除失败，未查询到该日期的点检数据')
        except:
            messagebox.showerror('delete failed', '删除失败，请检查网络状态\n或找数据库管理员查询原因')
        self.close()
    # 导出至Excel
    def export_excel(self, tb_name):
        try:
            sql = "SELECT * FROM %s;" % tb_name
            self.cursor.execute(sql)
            rs_tuple = self.cursor.fetchall()
            wb = Workbook()    # 实例化工作簿
            ws = wb.create_sheet('sheet1',0)  # 创建表 插入最开始
            ws.append(["Date","Mean","Min","Max","Std_Dev(%)","Target","LCL","UCL"])
            for i in range(len(rs_tuple)):
                ws.append(rs_tuple[i])
            for row in ws[1:2]:     # 设置第一行title居中，字体
                for cell in row:
                    cell.alignment=Alignment(horizontal='center', vertical='center')  
                    cell.font= Font(name='Times New Roman', size=14)
            for row in ws[2:(ws.max_row+1)]:     # 批量设置居中，字体
                for cell in row:
                    cell.alignment=Alignment(horizontal='center', vertical='center')  
                    cell.font= Font(name='Times New Roman', size=11)
            filename = filedialog.asksaveasfilename(filetypes=[('xlsx','*xlsx')],initialdir=r'C:\Users\Administrator\Desktop\\')
            if filename:
                filename = filename + '.xlsx'            
                wb.save(filename)
                messagebox.showinfo('export success~', '成功导出至Excel文件')
        except:
            messagebox.showerror('export failed', '导出至Excel失败，请检查网络状态\n或找数据库管理员查询原因')        
        self.close()

四、画图监测数据的代码

from tkinter import *
from tkinter import ttk
from tkinter import messagebox
import numpy as np
import matplotlib
import matplotlib.pyplot as plt
from matplotlib.pylab import mpl
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg, NavigationToolbar2Tk
from matplotlib.backend_bases import key_press_handler
import pymysql

class Draw_Info:
    def __init__(self, master):
        self.conn = pymysql.connect(
            host = '00.000.00.000',
            user = '***',
            password = '******',
            database = '数据库名',
            charset = 'utf8')
        self.cursor=self.conn.cursor()

        plt.rcParams['font.sans-serif'] = ['SimHei']    #解决中文乱码问题
        # 设置画布
        self.f = plt.figure(figsize=(16,6), dpi=100)       
        # 把绘制的图形显示在tkinter窗口
        self.canvas = FigureCanvasTkAgg(self.f, master)
        self.canvas.draw()
        self.canvas.get_tk_widget().pack(side=TOP, fill=BOTH, expand=1)        
        # matplotlib工具条
        toolbar = NavigationToolbar2Tk(self.canvas, master)
        toolbar.update()
        self.canvas._tkcanvas.pack(side=TOP, fill=BOTH, expand=1)

    def close(self):
        self.cursor.close()
        self.conn.close()

    def mo_Rs_plot(self, tb_name):
        mean_fig = plt.subplot(211)  # 2行1列第1个
        sql = "SELECT Date, Mean, Target, LCL, UCL from %s" % tb_name
        self.cursor.execute(sql)
        rs_mean_tuple = self.cursor.fetchall()
        rs_x = [x[0] for x in rs_mean_tuple]
        y1 = [x[1] for x in rs_mean_tuple]
        y2 = [x[2] for x in rs_mean_tuple]
        y3 = [x[3] for x in rs_mean_tuple]
        y4 = [x[4] for x in rs_mean_tuple]
        #将日期格式转为字符串
        for i in range(len(rs_x)):
            rs_x[i] = str(rs_x[i])
            
        line1 = mean_fig.plot(rs_x, y1, color='DodgerBlue', linewidth=1, label='Mean', linestyle='-', \
                              marker='.', markersize=10, markerfacecolor='limegreen', markeredgecolor='DodgerBlue')
        line2 = mean_fig.plot(rs_x, y2, color='Navy', linewidth=1, label='Target', linestyle='-.')
        line3 = mean_fig.plot(rs_x, y3, color='red', linewidth=1, label='LCL', linestyle='--')
        line4 = mean_fig.plot(rs_x, y4, color='red', linewidth=1, label='UCL', linestyle='--')
        plt.xticks(range(0,len(rs_x),2),rotation=45, fontsize=10)
        plt.title('Rs Mean', loc='center', pad=35,\
                           fontdict={'family':'DejaVu Sans', 'size':22})
        plt.legend(labels=['Mean','Target','LCL','UCL'], ncol=4, bbox_to_anchor=(0.71, 1.2),\
                        prop={'family':'DejaVu Sans', 'size':12}, frameon=True, framealpha=0.6)
        plt.ylabel('Rs', fontsize=16)
        # 设置坐标轴边界
        plt.xlim(min(rs_x),max(rs_x))
        plt.ylim(min(min(y3),min(y1))*0.96, max(max(y4),max(y1))*1.02)     
        # 隐藏top和right边框
        mean_fig.spines['right'].set_color('none')
        mean_fig.spines['top'].set_color('none')
     
        # 第二幅图
        std_fig = plt.subplot(212)
        sql = "SELECT Date, `Std_Dev(%%)` from %s" % tb_name
        self.cursor.execute(sql)
        rs_std_tuple = self.cursor.fetchall()
        self.close()
        std_x = [x[0] for x in rs_std_tuple]
        z1 = [x[1] for x in rs_std_tuple]
        z2 = [1]*len(std_x)
        #将日期格式转为字符串
        for i in range(len(std_x)):
            std_x[i] = str(std_x[i])
        line1 = std_fig.plot(std_x, z1, color='DodgerBlue', linewidth=1, label='Std Dev(%)', linestyle='-', \
                              marker='.', markersize=10, markerfacecolor='limegreen', markeredgecolor='DodgerBlue')
        line2 = std_fig.plot(std_x, z2, color='red', linewidth=1, label='UCL(%)', linestyle='--')
        plt.xticks(range(0,len(std_x),2),rotation=45, fontsize=10)
        plt.title('Std Dev%', loc='center', pad=25,\
                           fontdict={'family':'DejaVu Sans', 'size':22})
        plt.legend(labels=['Std Dev(%)','UCL(%)'], ncol=2, bbox_to_anchor=(0.64, 1.1),\
                        prop={'family':'DejaVu Sans', 'size':12}, frameon=True, framealpha=0.6)
        plt.ylabel('U%', fontsize=16)
        # 设置坐标轴边界
        plt.xlim(min(std_x),max(std_x))
        plt.ylim(0, max(max(z1),1)+0.2)     
        # 隐藏top和right边框
        std_fig.spines['right'].set_color('none')
        std_fig.spines['top'].set_color('none')
        # 子图间距
        plt.tight_layout(pad=2, h_pad=1.8)     

五、界面展示

增删改查界面

导出至Excel界面

画图监测数据界面

六、打包成exe

最后一步，打包成exe
这样就可以在任何电脑上运行这个软件而无需安装下载python和各种程序包
打包exe最常见的方法就是通过Pyinstaller来实现

安装Pyinstaller

pip install pyinstaller

Pyinstaller打包

1. 用cmd切换到存放代码文件的文件夹目录下。之前所写的所有代码块分别是这个包（也即文件夹）里的文件
   
2. 准备exe的图标ico
   选取图片，通过图标转换网站将图片转换成图标格式，并放入该目录下
3. 命令语句

pyinstaller -makespec -D Databases.py

生成Databases.spec文件

pyinstaller -D -w -i 图标名.ico Databases.spec

-D：我是多文件代码，所以用-D，一般用-F
-w：不带控制台
-i ：指定exe图标
运行成功后生成两个文件夹

exe程序就在dist文件夹中