mysql 数据转excel文件

缘由

为售后拉取数据，用navicat太墨迹了，用python写一个main方法跑一下；
1.抽取共同方法，封装成传入mysql，直接下载成excel；
2.写入所有sql语句，传入参数；

代码

'''
@Author: Jeff.zheng
@Date : 2024/4/27
@Desc : mysql 转成excel文件
'''import json
import os
import time
from datetime import datetime
import pandas as pd
import pymysql.cursors
from future.backports.datetime import timedelta# 获取游标
def getCursor():# 创建连接Connection = pymysql.connect(host='127.0.0.1',  # 数据库主机地址user='root',  # 数据库用户名password='123456',  # 数据库密码db='cloud3',  # 数据库名cursorclass=pymysql.cursors.DictCursor  # 使用DictCursor以字典形式返回查询结果)cursorOfConnection = Connection.cursor()return cursorOfConnection# 获取结果
def getResultBySql(cursorOfConnection, sql):try:cursorOfConnection.execute(sql)# 获取查询结果Result = cursorOfConnection.fetchall()return Resultexcept Exception as e:print(e)cursorOfConnection.close()def exportExcelByList(listData, dateName, fileName):df = pd.DataFrame(listData)# 设置表头need_path = os.path.join(os.path.expanduser("~"), "Desktop") + '/测试文件夹-' + dateNameos.makedirs(need_path, exist_ok=True)fileNamePath = os.path.join(need_path, fileName + '.xlsx')df.to_excel(fileNamePath, index=None)# 这里可以格式化或者别的，太麻烦暂不处理；def sqlToExcel(myCursor, sql, dateName, xlsxName):print(xlsxName + "-开始-" + datetime.now().strftime('%Y-%m-%d %H:%M:%S'))sqlResultList = getResultBySql(myCursor, sql)exportExcelByList(sqlResultList, dateName, xlsxName)print(xlsxName + "-结束-" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "\n")if __name__ == '__main__':cursor = getCursor()date = "2024-04-28"dateAddOne = (datetime.strptime(date, '%Y-%m-%d') + timedelta(days=1)).strftime('%Y-%m-%d')sql0 = "SELECT col_device_id  as '所有出货的设备ID'  FROM cj_all_bed "sqlToExcel(cursor, sql0, date, "0-所有出货的设备ID")