背景:
项目每周一需要从tdsql数据库获取一周的最新运营数据工单及数据,考虑使用脚本做定时发送
功能介绍:
python连接mysql逐条执行sql语句将结果导入本地xlsx,同时通过企业微信机器人将xlsx发送到群里
1、 创建配置文件config.ini
vim config.ini
[mysql]
host = 127.0.0.1
database = mysql
user = root
port = 3306
password = 123456
[wechat]
wx_url = https://qyapi.weixin.qq.com
robot_key = 76faa519-d4af-xxxx-xxxx-xxxxxxxxxx
[user]
query = SELECT Host, User FROM mysql.user;
[all]
query = select * from sys.processlist;
2、代码内容 :
(密码设在代码里、之后pyinstaller -F做简单加密、安全要求高的同学可做额外处理)
import pandas as pd
import requests
from sqlalchemy import create_engine
import configparser
import os
from datetime import datetime
import urllib.parse
MYSQL_PASSWORD = "q.]^)@5jsKE#s=N."
def send_file(wx_url, robot_key, file):
"""
使用企业微信机器人发送文件消息
:param wx_url: 企业微信机器人基础 URL
:param robot_key: 企业微信机器人密钥
:param file: 待发送文件的路径
"""
oFileUploadUrl = f'{wx_url}/cgi-bin/webhook/upload_media?key={robot_key}&type=file'
oWX_ROBOT_URL = f'{wx_url}/cgi-bin/webhook/send?key={robot_key}'
# 上传文件到企业微信
with open(file, 'rb') as f:
files = {'media': f}
response = requests.post(oFileUploadUrl, files=files)
json_res = response.json()
print(f"文件上传响应: {json_res}")
# 检查文件上传的响应是否成功
if 'media_id' in json_res:
media_id = json_res['media_id']
data = {
"msgtype": "file",
"file": {
"media_id": media_id
}
}
headers = {'Content-Type': 'application/json'}
response = requests.post(url=oWX_ROBOT_URL, json=data, headers=headers, timeout=300)
print(f"发送文件响应: {response.json()}")
else:
print("文件上传失败,无法获取 media_id")
def query_mysql_to_excel(engine, queries, output_dir, wx_url, robot_key):
"""
执行查询并将结果导出为 Excel 文件,然后发送到企业微信
:param engine: SQLAlchemy 引擎对象
:param queries: 查询字典,键为查询名称,值为 SQL 查询
:param output_dir: 输出目录
:param wx_url: 企业微信基础 URL
:param robot_key: 企业微信机器人密钥
"""
try:
# 如果 output 目录不存在,创建它
os.makedirs(output_dir, exist_ok=True)
# 获取当前时间,格式为 "YYYYMMDD_HHMM"
current_time = datetime.now().strftime("%Y%m%d_%H%M")
# 遍历查询列表
for name, query in queries.items():
# 执行查询
df = pd.read_sql(query, engine)
# 将数据写入 Excel 文件,文件名包含查询名称和当前时间
excel_file = os.path.join(output_dir, f"{name}_{current_time}.xlsx")
df.to_excel(excel_file, index=False, engine='openpyxl')
print(f"数据成功导入到 {excel_file}")
# 发送文件到企业微信
send_file(wx_url, robot_key, excel_file)
except Exception as e:
print(f"发生错误: {e}")
def read_config(config_file):
"""
读取配置文件,获取数据库连接信息、查询语句以及企业微信信息
:param config_file: 配置文件路径
:return: SQLAlchemy 引擎对象、查询字典、企业微信信息
"""
config = configparser.ConfigParser()
config.read(config_file)
# 读取连接信息
host = config['mysql']['host']
port = config.get('mysql', 'port', fallback='3306') # 默认为 3306
database = config['mysql']['database']
user = config['mysql']['user']
password = urllib.parse.quote_plus(MYSQL_PASSWORD)
# 读取企业微信信息
wx_url = config['wechat']['wx_url']
robot_key = config['wechat']['robot_key']
# 创建 SQLAlchemy 引擎
engine = create_engine(f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}")
# 读取查询信息
queries = {section: config[section]['query'] for section in config.sections() if section not in ['mysql', 'wechat']}
return engine, queries, wx_url, robot_key
def main():
# 使用示例
config_file = 'config.ini'
output_dir = 'output' # 设置输出目录为当前目录下的 output 文件夹
engine, queries, wx_url, robot_key = read_config(config_file)
query_mysql_to_excel(engine, queries, output_dir, wx_url, robot_key)
if __name__ == '__main__':
main()