【python读取mysql数据导出excel文档】

开发环境

python: 3.9
PyMySQL: 1.0.2
xlwt: 1.3.0

repositories

https://pypi.python.org/simple
https://mirrors.aliyun.com/pypi/simple/

开发文件

mysql-config.json

{
  "file_type": "excel",
  "mysql_config_dict": {
    "ip": "127.0.0.1",
    "port": 3306,
    "username": "root",
    "password": "root",
    "database_name": "eployment_info"
  }
}

sqlFile.sql

select * from employee_info;

tableTitle.txt

员工姓名
入职日期
工资
性别

excelUtil.py

from datetime import datetime
import xlwt
import sys
import json

from pymysql.cursors import DictCursor

import pymysql
import logging

import datetime  # 导入日期时间模块


def write_data_to_excel(name, result, titlelist):
    # 实例化一个Workbook()对象(即excel文件)
    wbk = xlwt.Workbook(encoding='utf-8')
    # 新建一个名为Sheet1的excel sheet。此处的cell_overwrite_ok =True是为了能对同一个单元格重复操作。
    sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)

    today = datetime.date.today()  # 获得今天的日期
    yesterday = today - datetime.timedelta(days=1)

    for i in range(len(result)):
        # 对result的每个子元素作遍历，
        if i == 0:
            for x in range(len(titlelist)):
                sheet.write(0, x, titlelist[x])
            continue
        for j in range(len(result[i])-1):
            # 将每一行的每个元素按行号i,列号j,写入到excel中。
            print(f'result[i]: {result[i]}')
            print(f'result[j]: {result[i][j]}')
            cell_entity = result[i][j+1]

            if isinstance(cell_entity, datetime.datetime):
                print(f'{cell_entity} is date')
                cell_entity = result[i][j+1].strftime('%Y-%m-%d %H:%M:%S')

            sheet.write(i, j, cell_entity)
    # 以传递的name+当前日期作为excel名称保存。
    wbk.save(name + str(yesterday) + '.xls')

def execute_sql_list(sql_str, kcursor_entity):
    logging.debug(f"start to execute_sql_list! sql: {sql_str}")
    # 执行sql语句
    kcursor_entity.execute(sql_str)
    # 使用 fetchall() 方法获取全部数据
    result_list = kcursor_entity.fetchall()
    return list(result_list)

# 读取json文件
def read_json(mysql_config_file_name):
    try:
        if not mysql_config_file_name:
            config_file = "mysql_config.json"
        else:
            config_file = mysql_config_file_name
        parse_json = open(f"{config_file}", "r", encoding="utf-8").read()
        data = json.loads(parse_json)
        mysqlConfigDict = {}
        mysqlConfigDict["ip"] = data["mysql_config_dict"]["ip"]
        mysqlConfigDict["port"] = data["mysql_config_dict"]["port"]
        mysqlConfigDict["username"] = data["mysql_config_dict"]["username"]
        mysqlConfigDict["password"] = data["mysql_config_dict"]["password"]
        mysqlConfigDict["database_name"] = data["mysql_config_dict"]["database_name"]
        return mysqlConfigDict
    except Exception as e:
        logging.error(e)

def getSqlStr():
    # sql文件名， .sql后缀的
    sql_file = 'sqlFile.sql'
    # 读取 sql 文件文本内容
    sql = open(sql_file, 'r', encoding='utf8')
    sqltxt = sql.readlines()
    # 此时 sqltxt 为 list 类型
    # 读取之后关闭文件
    sql.close()
    # list 转 str
    sqlStr = "".join(sqltxt)
    return sqlStr

def getTableTitle(table_title_file_name):
    if not table_title_file_name:
        table_title_file = 'tableTitle.txt'
    else:
        table_title_file = table_title_file_name
    title_list = []
    try:

        titleFile = open(table_title_file, 'r', encoding='utf8')
        title_list = titleFile.readlines()
        titleFile.close()
        for i in range(len(title_list)):
            title_entity = title_list[i].replace('\n','').replace('\r','')
            title_list[i] = title_entity
    except Exception as e:
        logging.error(e)
    return title_list

if __name__ == '__main__':

    logging.info("start to execute main method!")
    mysql_config_file_name = ''
    table_title_file_name = ''
    try:
        executeArgvList = sys.argv
        if len(executeArgvList) < 2:
            logging.info('executeArgvList is empty!')
        else:
            argv = executeArgvList[1]
            env = executeArgvList[2]
            logging.info(f'argv: {argv}; env: {env}')
        mysql_config_file_name = ''
        table_title_file_name = ''
    except Exception as e:
        logging.error(e)

    # 读取配置文件信息
    mysqlConfigDict = read_json(mysql_config_file_name)

    try:
        # 打开数据库连接
        employee_conn = pymysql.connect(host = mysqlConfigDict['ip'], user = mysqlConfigDict['username'], password = mysqlConfigDict['password'],
                                     database = mysqlConfigDict['database_name'], port = mysqlConfigDict['port'])
    except Exception as e:
        logging.error("连接数据库失败", e)
        logging.exception(sys.exc_info())
        exit(-1)

    # 使用cursor()方法获取操作游标
    kcursor_employee = employee_conn.cursor(DictCursor)

    sqlStr = getSqlStr()
    print(f'sqlStr-------------> {sqlStr}')

    result_list = execute_sql_list(sqlStr, kcursor_employee)
    if not len(result_list):
        logging.info('result_list is empty!')
        exit(-1)
    #获取结果集标题
    resultKeys = result_list[0]
    resultKeys = list(resultKeys)

    titleList = getTableTitle(table_title_file_name)
    if not titleList:
        titleList = resultKeys

    logging.info(f'resultKeys: {resultKeys}')

    list = [[] for i in range(len(resultList))]
    print(f'{list}')

    for i in range(len(resultList)):
        result_entity = resultList[i]
        list[i].append(result_entity)
        for key in resultKeys:
            list[i].append(result_entity[key])
    
    print(f'{list}')
    name = 'employmentInfo'
    write_data_to_excel(name, list, titleList)