Python读取mysql统计应用

一、背景说明

1、网站应用无法导出csv或者excel表格的格式。

2、python读取mysql数据，然后批量写入excel表格，最后发送到企业微信群。

#! /usr/bin/python3
# -*- coding: utf-8 -*-

import requests
import pymysql
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
import time

# 获取数据库中数据
def get_mysql():
    connet = pymysql.connect(host="数据库地址",
                             port=23306,
                             user="账号",
                             password="密码",
                             database="数据库实例名",
                             charset="utf8mb4")
    curses = connet.cursor()
    # 执行sql语句
    qury_sql = "select tsys_name, tsys_code,tsys_create_at, tsys_update_at from 数据库实例名.表名;"
    curses.execute(qury_sql)
    data = curses.fetchall()
    curses.close()
    connet.commit()
    connet.close()
    print("mysql数据处理完成...")
    return data

def write_excel(file_name):
    try:
        get_data = get_mysql()
        # 创建excel文件
        wb = Workbook()

        # 创建工作表格
        sheet = wb.active
        sheet.title = "2023统计应用系统"
        sheet.sheet_properties.tabColor = "FFF8DC"

        # 写入标题
        title_list = ("应用名称", "entityId代码", "创建时间", "更新时间")
        sheet.append(title_list)

        # 批量写入数据
        for item in get_data:
            sheet.append(item)

        # 统计多少行数据
        row = sheet.max_row

        # table表格数据
        tab = Table(displayName="table1", ref=f"A1:D{row}")
        style = TableStyleInfo(name="TableStyleMedium11", showFirstColumn=False, showLastColumn=False,
                               showRowStripes=True, showColumnStripes=True)
        tab.tableStyleInfo = style
        sheet.add_table(tab)

        # 保存数据
        wb.save(file_name)
        print("xxxx数据统计完成！！！")

    except ValueError:
        print("xxxx数据已经写入！！！")

    except PermissionError:
        print("请关闭打开的excel文档！！！")

# 发送文件
def send_file(key, file_path):
    data = {
        'file': open(file_path, 'rb')
    }
    url = f"https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key={key}&type=file"
    req = requests.post(url, files=data)
    if req.status_code == 200:
        json_req = req.json()
        media_id = json_req['media_id']
        data = {
            "msgtype": "file",
            "file": {
                "media_id": media_id
            }
        }
        wx_url = f"https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key={key}"
        result = requests.post(wx_url, json=data)
        return result

# 发送文字
def send_message(key,text):
    api_url = f"https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key={key}"
    headers = {
        "Content-Type": "application/json;charset=utf-8"
    }
    data = {
        "msgtype": "text",
        "text": {
            "content": text,
            "mentioned_list": ["@all"],
            # "mentioned_mobile_list": ["@all"]
        }
    }
    req = requests.post(url=api_url,json=data, headers=headers)
    return req

if __name__ == '__main__':
    # 定义变量
    file_name = "2023xxxx所有应用系统.xlsx"
    weixin_key = "企业微信keyID"
    today = time.strftime("%Y-%m-%d %H:%M:%S")

    msg = f"【当前时间】：{today}\n"\
          f"请查阅2023xxxx所有应用统计！"

    # 调用函数
    write_excel(file_name)
    send_message(weixin_key, msg)
    send_file(weixin_key, file_name)