【Python制作数据查询小工具】

需求背景

1.从MySQL数据库查询某个时间段的数据

2.查询SQL固定，数据的时间区间可自定义选择

3.查询的数据结果可导出为excel

4.查询结果可发送到飞书消息

5.可在其它windows电脑上运行

实现步骤

1.连接数据库，执行sql语句

cfg_sql_template.yaml

# 数据库查询SQL配置--日期为变量
  # '${start_date}'--起始日期（包含）
  # '${end_date}'--截止日期(不包含)

sql: "select id,updated_at,username,real_name,mobile from test_member where updated_at >='${start_date}' and updated_at<'${end_date}' order by 1 desc;"

cfg_mysql.yaml

# 数据库配置

host: "192.168.1.1"
port: 3306
user: "test"
passwd: "test_passwd"
db: "test_db"
charset: "utf8"

代码如下：

import traceback
from pprint import pprint
from string import Template
import pymysql
import yaml


def generate_sql(sql_tpl, start_date, end_date):
    """
    替换sql模板中的时间变量
    :return: 数据库查询sql
    """
    is_start = sql_tpl.find("${start_date}")
    is_end = sql_tpl.find("${end_date}")
    if is_start != -1 and is_end != -1:  # sql模板中包含${start_date}和${end_date}参数时
        sql = Template(sql_tpl).safe_substitute({"start_date": start_date, "end_date": end_date})  # 替换字符串中的变量
        return sql
    print("请检查sql模板！")


def execute_sql(dbinfo, sql):
    """
    连接数据库，执行sql查询语句，返回查询结果
    :param dbinfo: 数据库配置信息
    :param sql: 数据库查询语句
    :return:
    """
    try:
        conn = pymysql.connect(**dbinfo)  # 连接数据库
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 数据库游标
        cursor.execute(sql)  # 执行sql语句
        desc = cursor.description  # 查询结果描述（相当于表格表头）
        results = cursor.fetchall()  # 查询结果内容（相当于表格内容）
        conn.close()  # 关闭数据库连接
        return desc, results
    except Exception as err:
        info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
        return info


def yaml_read(filename):
    """
    读取yaml文件
    """
    with open(file=filename, mode="r", encoding="utf-8") as f:
        data = yaml.safe_load(f.read())
    return data

运行结果：

if __name__ == "__main__":
    # 读取sql模板
    sql_template = yaml_read("cfg_sql_template.yaml")['sql']
    # 读取数据库配置信息
    mysql_info = yaml_read("cfg_mysql.yaml")
    # 生成sql语句
    mysql_sql = generate_sql(sql_template, "2023-06-01", "2023-08-20")
    # 连接数据库，执行sql语句
    mysql_desc, mysql_result = execute_sql(mysql_info, mysql_sql)
    pprint(mysql_desc)
    pprint(mysql_result)

2.查询结果写入excel文件

import datetime
import time
import traceback
import xlwt


def write_excel(fields, results, name):
    """
    写入excel
    :param fields:  excel表头
    :param results: excel表格内容
    :param name: excel保存路径+文件名
    :return:
    """
    try:
        book = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
        sheet = book.add_sheet('new_sheet', cell_overwrite_ok=True)  # 新建sheet

        # 写入表头
        for col, field in enumerate(fields):
            sheet.write(0, col, field[0])
        print(f"写入表头成功！{[field[0] for field in fields]}")

        # 写入表格内容
        row = 1
        for data in results:
            for col, field in enumerate(data):
                val = data[field]
                sheet.write(row, col, val)
            row += 1

        book.save(name + ".xlsx")  # 保存excel
        print(f"excel保存成功！【{name}.xlsx】")
    except Exception as err:
        info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
        print(info)

运行结果：

if __name__ == "__main__":
    # 读取sql模板
    sql_template = yaml_read("cfg_sql_template.yaml")['sql']
    # 读取数据库配置信息
    mysql_info = yaml_read("cfg_mysql.yaml")
    # 生成sql语句
    mysql_sql = generate_sql(sql_template, "2023-06-01", "2023-08-20")
    # 连接数据库，执行sql语句
    mysql_desc, mysql_result = execute_sql(mysql_info, mysql_sql)
    # 结果写入excel文件
    sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
    write_excel(mysql_desc, mysql_result, sj)

查询结果成功保存到了excel中，可是时间格式的数据并不正常，我们来优化一手。

添加判断数据是否为时间格式方法is_date()，并在write_excel()方法中设置时间格式数据的样式。

import datetime
import time
import traceback
import xlwt


def write_excel(fields, results, name):
    """
    写入excel
    :param fields:  excel表头
    :param results: excel表格内容
    :param name: excel保存路径+文件名
    :return:
    """
    try:
        book = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
        sheet = book.add_sheet('new_sheet', cell_overwrite_ok=True)  # 新建sheet
        
        # excel表格内容样式设置
        style = xlwt.XFStyle()
        style.num_format_str = "yyyy/MM/dd HH:MM:SS"

        # 写入表头
        for col, field in enumerate(fields):
            sheet.write(0, col, field[0])
        print(f"写入表头成功！{[field[0] for field in fields]}")

        # 写入表格内容
        row = 1
        for data in results:
            for col, field in enumerate(data):
                val = str(data[field])
                if is_date(val):
                    new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S.%f")
                    sheet.write(row, col, new_val, style)
                else:
                    sheet.write(row, col, val)
            row += 1

        book.save(name + ".xlsx")  # 保存excel
        print(f"excel保存成功！【{name}.xlsx】")
    except Exception as err:
        info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
        print(info)


def is_date(date_str):
    """
    判断date_str是否为日期格式
    :param date_str:
    :return:
    """
    date_format = "%Y-%m-%d"
    try:
        if isinstance(date_str, str):
            new_str = date_str[:10]
            valid_date = time.strptime(new_str, date_format)
            return True
        else:
            return False
    except ValueError or TypeError as e:
        return False

运行结果正常：

3.结果发送飞书消息

import datetime
import json
import requests


def send_feishu(webhook, msg):
    """
    发送消息到飞书群
    :param webhook:
    :param msg: 消息内容
    :return:
    """
    headers = {'Content-Type': 'application/json; charset=utf-8'}
    data = {"msg_type": "text", "at": {}, "content": {"text": msg}}
    post_data = json.dumps(data)
    res = requests.post(webhook, headers=headers, data=post_data)
    print(res)


def format_msg(fields, results):
    """
    格式化SQL执行结果
    :param fields:  SQL执行结果-查询的字段
    :param results: SQL执行结果-查询的结果
    :return: msg--消息内容--字符串
    """
    head = ''
    content = ''
    for index, val in enumerate(fields):
        head += f"{val[0]:<12}"
    for val in results:
        for index, key in enumerate(val):
            str_val = str(val[key])
            if len(str_val) > 12:
                content += f"{str_val[:10]:<12}"
            else:
                content += f"{str_val:<12}"
        content += "\n"
    msg = head + "\n" + content
    return msg

运行结果：

if __name__ == "__main__":
    # 读取sql模板
    sql_template = yaml_read("cfg_sql_template.yaml")['sql']
    # 读取数据库配置信息
    mysql_info = yaml_read("cfg_mysql.yaml")
    # 生成sql语句
    mysql_sql = generate_sql(sql_template, "2023-06-01", "2023-08-20")
    # 连接数据库，执行sql语句
    mysql_desc, mysql_result = execute_sql(mysql_info, mysql_sql)
    # 结果写入excel文件
    sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
    write_excel(mysql_desc, mysql_result, sj)
    # 发送飞书消息
    feishu_msg = format_msg(mysql_desc, mysql_result)
    feishu_webhook = "飞书群机器人webhook地址"
    send_feishu(feishu_webhook, feishu_msg)

4. 制作GUI界面

import datetime
import ttkbootstrap as ttk


class GUI:

    def __init__(self, master):
        """GUI"""
        self.de2 = None
        self.de1 = None
        self.mf = None
        self.root = master
        self.create_page()

    def create_page(self):
        """创建页面元素"""

        # 主控件
        self.mf = ttk.Frame(self.root)
        self.mf.pack()

        # 标题标签
        fontstyle = ttk.font.Font(size=22, weight="bold")
        ttk.Label(self.mf, text="请选择数据日期", font=fontstyle, bootstyle="primary").pack(padx=20, pady=2)

        # 日期选择
        start_date = (datetime.timedelta(days=-7) + datetime.datetime.now())
        self.de1 = ttk.DateEntry(self.mf, startdate=start_date, width=10, dateformat="%Y-%m-%d")
        self.de1.pack(padx=0, pady=10)
        label1 = ttk.Label(self.mf, text="|", bootstyle='INFO')
        label1.pack(padx=0, pady=0)
        self.de2 = ttk.DateEntry(self.mf, width=10, dateformat="%Y-%m-%d")
        self.de2.pack(padx=0, pady=10)

        # 提交按钮
        ttk.Button(self.mf, text="导出excel", bootstyle="success").pack(padx=30, pady=10, side='left')
        ttk.Button(self.mf, text="发送飞书", bootstyle="success").pack(padx=30, pady=10, side='right')

 运行结果：

if __name__ == "__main__":

    root = ttk.Window(
        title="GUI",  # 窗口标题
        themename="cyborg"  # 主题
    )
    root.place_window_center()  # 窗口居中
    GUI(root)  # 展示GUI界面
    root.mainloop()  # 进入消息循环（必需）

 

5. 打包为exe文件（参考文章）

完整代码

import datetime
import json
import time
import traceback
from string import Template
import pymysql
import requests
import xlwt
import yaml
import ttkbootstrap as ttk
from ttkbootstrap.dialogs import Messagebox


class GUI:

    def __init__(self, master):
        """GUI"""
        self.de2 = None
        self.de1 = None
        self.mf = None
        self.root = master
        self.create_page()

    def create_page(self):
        """创建页面元素"""

        # 主控件
        self.mf = ttk.Frame(self.root)
        self.mf.pack()

        # 标题标签
        fontstyle = ttk.font.Font(size=22, weight="bold")
        ttk.Label(self.mf, text="请选择数据日期", font=fontstyle, bootstyle="primary").pack(padx=20, pady=2)

        # 日期选择
        start_date = (datetime.timedelta(days=-7) + datetime.datetime.now())
        self.de1 = ttk.DateEntry(self.mf, startdate=start_date, width=10, dateformat="%Y-%m-%d")
        self.de1.pack(padx=0, pady=10)
        label1 = ttk.Label(self.mf, text="|", bootstyle='INFO')
        label1.pack(padx=0, pady=0)
        self.de2 = ttk.DateEntry(self.mf, width=10, dateformat="%Y-%m-%d")
        self.de2.pack(padx=0, pady=10)

        # 提交按钮
        ttk.Button(self.mf, text="导出excel", bootstyle="success", command=self.to_excel).pack(padx=30, pady=10,
                                                                                             side='left')
        ttk.Button(self.mf, text="发送飞书", bootstyle="success", command=self.to_feishu).pack(padx=30, pady=10,
                                                                                           side='right')

    def get_date_value(self):
        date_format = "%Y-%m-%d"
        start_date_dateformat = datetime.datetime.strptime(self.de1.entry.get(), date_format)  # 将字符串转换为日期
        end_date_dateformat = datetime.datetime.strptime(self.de2.entry.get(), date_format)  # 将字符串转换为日期
        return start_date_dateformat, end_date_dateformat

    def to_excel(self):
        start_date, end_date = self.get_date_value()
        if end_date > start_date:
            # 读取sql模板
            sql_template = self.yaml_read("cfg_sql_template.yaml")['sql']
            # 读取数据库配置信息
            mysql_info = self.yaml_read("cfg_mysql.yaml")
            # 生成sql语句
            mysql_sql = self.generate_sql(sql_template, start_date, end_date)
            # 连接数据库，执行sql语句
            mysql_desc, mysql_result = self.execute_sql(mysql_info, mysql_sql)
            # 结果写入excel文件
            sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
            self.write_excel(mysql_desc, mysql_result, sj)
            Messagebox.show_info(title="恭喜发财！", message=f"已导出excel！")
        else:
            Messagebox.show_info(title="哦豁，出错了！", message=f"开始日期必须小于截止日期！")

    def to_feishu(self):
        start_date, end_date = self.get_date_value()
        if end_date > start_date:
            # 读取sql模板
            sql_template = self.yaml_read("cfg_sql_template.yaml")['sql']
            # 读取数据库配置信息
            mysql_info = self.yaml_read("cfg_mysql.yaml")
            # 生成sql语句
            mysql_sql = self.generate_sql(sql_template, start_date, end_date)
            # 连接数据库，执行sql语句
            mysql_desc, mysql_result = self.execute_sql(mysql_info, mysql_sql)
            # 发送飞书消息
            feishu_msg = self.format_msg(mysql_desc, mysql_result)
            feishu_webhook = "飞书群机器人webhook地址"
            self.send_feishu(feishu_webhook, feishu_msg)
            Messagebox.show_info(title="恭喜发财！", message=f"已发送飞书消息！")
        else:
            Messagebox.show_info(title="哦豁，出错了！", message=f"开始日期必须小于截止日期！")

    @staticmethod
    def generate_sql(sql_tpl, start_date, end_date):
        """
        替换sql模板中的时间变量
        :return: 数据库查询sql
        """
        is_start = sql_tpl.find("${start_date}")
        is_end = sql_tpl.find("${end_date}")
        if is_start != -1 and is_end != -1:  # sql模板中包含${start_date}和${end_date}参数时
            sql = Template(sql_tpl).safe_substitute(
                {"start_date": start_date, "end_date": end_date})  # 替换字符串中的变量
            return sql
        print("请检查sql模板！")

    @staticmethod
    def execute_sql(dbinfo, sql):
        """
        连接数据库，执行sql查询语句，返回查询结果
        :param dbinfo: 数据库配置信息
        :param sql: 数据库查询语句
        :return:
        """
        try:
            conn = pymysql.connect(**dbinfo)  # 连接数据库
            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 数据库游标
            cursor.execute(sql)  # 执行sql语句
            desc = cursor.description  # 查询结果描述（相当于表格表头）
            results = cursor.fetchall()  # 查询结果内容（相当于表格内容）
            conn.close()  # 关闭数据库连接
            return desc, results
        except Exception as err:
            info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
            return info

    @staticmethod
    def yaml_read(filename):
        """
        读取yaml文件
        """
        with open(file=filename, mode="r", encoding="utf-8") as f:
            data = yaml.safe_load(f.read())
        return data

    def write_excel(self, fields, results, name):
        """
        写入excel
        :param fields:  excel表头
        :param results: excel表格内容
        :param name: excel保存路径+文件名
        :return:
        """
        try:
            book = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
            sheet = book.add_sheet('new_sheet', cell_overwrite_ok=True)  # 新建sheet

            style = xlwt.XFStyle()
            style.num_format_str = "yyyy/MM/dd HH:MM:SS"

            # 写入表头
            for col, field in enumerate(fields):
                sheet.write(0, col, field[0])
            print(f"写入表头成功！{[field[0] for field in fields]}")

            # 写入表格内容
            row = 1
            for data in results:
                for col, field in enumerate(data):
                    val = str(data[field])
                    if self.is_date(val):
                        new_val = datetime.datetime.strptime(val, "%Y-%m-%d %H:%M:%S.%f")
                        sheet.write(row, col, new_val, style)
                    else:
                        sheet.write(row, col, val)
                row += 1

            book.save(name + ".xlsx")  # 保存excel
            print(f"excel保存成功！【{name}.xlsx】")
        except Exception as err:
            info = f"出了点小问题！\n{repr(err)}\n{traceback.format_exc()}"
            print(info)

    @staticmethod
    def is_date(date_str):
        """
        判断date_str是否为日期格式
        :param date_str:
        :return:
        """
        date_format = "%Y-%m-%d"
        try:
            if isinstance(date_str, str):
                new_str = date_str[:10]
                valid_date = time.strptime(new_str, date_format)
                return True
            else:
                return False
        except ValueError or TypeError as e:
            return False

    @staticmethod
    def send_feishu(webhook, msg):
        """
        发送消息到飞书群
        :param webhook:
        :param msg: 消息内容
        :return:
        """
        headers = {'Content-Type': 'application/json; charset=utf-8'}
        data = {"msg_type": "text", "at": {}, "content": {"text": msg}}
        post_data = json.dumps(data)
        res = requests.post(webhook, headers=headers, data=post_data)
        print(res)

    @staticmethod
    def format_msg(fields, results):
        """
        格式化SQL执行结果
        :param fields:  SQL执行结果-查询的字段
        :param results: SQL执行结果-查询的结果
        :return: msg--消息内容--字符串
        """
        head = ''
        content = ''
        for index, val in enumerate(fields):
            head += f"{val[0]:<12}"
        for val in results:
            for index, key in enumerate(val):
                str_val = str(val[key])
                if len(str_val) > 12:
                    content += f"{str_val[:10]:<12}"
                else:
                    content += f"{str_val:<12}"
            content += "\n"
        msg = head + "\n" + content
        return msg


if __name__ == "__main__":
    root = ttk.Window(
        title="GUI",  # 窗口标题
        themename="cyborg"  # 主题
    )
    root.place_window_center()  # 窗口居中（问题：窗口左上角坐标居中？）
    GUI(root)  # 展示GUI界面
    root.mainloop()  # 进入消息循环（必需）