從數(shù)據(jù)庫(kù)查詢數(shù)據(jù)并導(dǎo)出到excel

import pymysql
import datetime
import xlwt
import os
import pandas as pd

def export_excel(output_dir):
    current_datetime = datetime.datetime.now()
    # 將日期時(shí)間格式化為字符串，例如：2023-10-23_14-30-15
    formatted_datetime = current_datetime.strftime('%Y-%m')
    formatted_datetime1 = current_datetime.strftime('%Y-%m-%d')
    # 構(gòu)造文件名，包含表名和格式化后的日期時(shí)間
    file_name = f'{formatted_datetime}月.xls'
    # 使用f-string來格式化工作表名稱
    sheet_name1 = f'{formatted_datetime1}'
    # 構(gòu)造完整的文件路徑
    file_path = os.path.join(output_dir, file_name)
    # 連接MySQL數(shù)據(jù)庫(kù)
    conn = pymysql.connect(host='***', user='***', password='***', db='***')
    # 創(chuàng)建一個(gè)Cursor對(duì)象，用于執(zhí)行SQL命令
    cur = conn.cursor()
    today = datetime.date.today()
    weekday = today.weekday()
    startday = today - datetime.timedelta(days=weekday)
    endday = startday + datetime.timedelta(days=(6 - weekday) if weekday != 6 else 0)
    startday_str = startday.strftime('%Y-%m-%d')
    endday_str = endday.strftime('%Y-%m-%d')

    print(f"本周起始日期: {startday_str}")
    print(f"本周結(jié)束日期: {endday_str}")
    person = 5
    sql_query =f"""  
    SELECT          
        CASE dept          
            WHEN 119 THEN '規(guī)劃南方大區(qū)'          
            WHEN 148 THEN '調(diào)度主網(wǎng)'          
            WHEN 149 THEN '調(diào)度配網(wǎng)'          
            WHEN 150 THEN '調(diào)度發(fā)電'          
            WHEN 151 THEN '調(diào)度校核'          
            WHEN 152 THEN '調(diào)度數(shù)字化'          
            WHEN 32 THEN '規(guī)劃產(chǎn)品研發(fā)'          
            WHEN 92 THEN '大數(shù)據(jù)產(chǎn)品研發(fā)'          
            WHEN 102 THEN '新電-平臺(tái)開發(fā)部'          
            WHEN 76 THEN '新電-產(chǎn)品交付部'          
            WHEN 62 THEN '新電-交易'          
            WHEN 75 THEN '數(shù)字設(shè)備研發(fā)二部'          
            WHEN 26 THEN '數(shù)字設(shè)備研發(fā)一部'          
            WHEN 19 THEN '研發(fā)中心平臺(tái)巴'         
        END AS 部門,        
        dh.計(jì)劃消耗總工時(shí),      
        dh.總?cè)藬?shù),      
        '{person}' * dh.總?cè)藬?shù) * 8 AS 標(biāo)準(zhǔn)工時(shí),
        round((dh.計(jì)劃消耗總工時(shí) / ('{person}' * dh.總?cè)藬?shù) * 8)) * 100, 2) AS 研發(fā)計(jì)劃負(fù)載率, 
        LEAST(round((dh.計(jì)劃消耗總工時(shí) / ('{person}' * dh.總?cè)藬?shù) * 8)) * 100, 2), 100) AS 研發(fā)計(jì)算負(fù)載率,  -- 確保負(fù)載率不超過100    
        GREATEST(0, (1 - (100 - LEAST(round((dh.計(jì)劃消耗總工時(shí) / ('{person}' * dh.總?cè)藬?shù) * 8)) * 100, 2), 100)) / (100 - 95)) * 2.5) AS 得分 -- 確保得分不低于0  
    FROM (      
        SELECT          
            dept,      
        round(
        SUM(  
            CASE  
              WHEN zt.realStarted = '0000-00-00 00:00:00' THEN  
                CASE  
                  WHEN zt.deadline BETWEEN '{startday}'  AND '{endday}' AND zt.estStarted < '{startday}'  THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF(zt.deadline, '{startday}' ) + 1)  -- 任務(wù)未開始，預(yù)計(jì)開始日期在計(jì)劃外，截止日期在計(jì)劃內(nèi)
                  WHEN zt.estStarted BETWEEN '{startday}'  AND '{endday}' AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, zt.estStarted) + 1)) * (DATEDIFF('{endday}', zt.estStarted) + 1)  -- 任務(wù)未開始，預(yù)計(jì)開始日期在計(jì)劃內(nèi)，截止日期在計(jì)劃外
                  WHEN zt.estStarted < '{startday}'  AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF('{endday}', '{startday}' ) + 1)  -- 任務(wù)未開始，預(yù)計(jì)開始日期，截止日期均在計(jì)劃外
                  WHEN zt.estStarted > '{startday}'  AND zt.deadline < '{endday}' THEN  
                    zt.left  -- 任務(wù)未開始，預(yù)計(jì)開始日期，截止日期均在計(jì)劃內(nèi)
                  ELSE 0  
                END  
              WHEN zt.realStarted != '0000-00-00 00:00:00' THEN  -- 任務(wù)已開始
                CASE  
                  WHEN zt.deadline BETWEEN '{startday}'  AND '{endday}' AND zt.estStarted < '{startday}'  THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF(zt.deadline, '{startday}' ) + 1)  
                  WHEN zt.estStarted BETWEEN '{startday}'  AND '{endday}' AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, zt.estStarted) + 1)) * (DATEDIFF('{endday}', zt.estStarted) + 1)  
                  WHEN zt.estStarted < '{startday}'  AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF('{endday}', '{startday}' ) + 1)  
                  WHEN zt.estStarted > '{startday}'  AND zt.deadline < '{endday}' THEN  
                    zt.left  
                  ELSE 0  
                END  
              ELSE 0  
            end  
          ),2) AS 計(jì)劃消耗總工時(shí),  
            count(DISTINCT zu.id) AS 總?cè)藬?shù)  
        FROM          
            zt_user zu          
           inner JOIN zt_task zt ON zu.account IN (zt.finishedBy, zt.assignedTo) and zt.parent != -1
                AND (zt.deadline BETWEEN '{startday}'  AND '{endday}' OR zt.estStarted BETWEEN '{startday}'  AND '{endday}')          
                AND zt.deleted != '1'           
        WHERE          
            zu.dept IN (148, 149, 150, 151, 152, 119, 32, 62, 92, 102, 75, 26, 76, 19)          
            AND zu.deleted = '0'          
            AND zu.realname NOT IN('仇佳麗','郭鴻志','譚曉龍','鄭朋','韓世飛','陳偉','付龍明','于瑞豐','譚小龍','張文斌','賀成利','寧杰','張琪','楊海燕','朱博','趙少飛')
        GROUP BY          
            zu.dept      
    ) dh      
    ORDER BY       
        部門;
    """

    # 執(zhí)行SQL命令
    cur.execute(sql_query)
    fileds=[filed[0] for filed in cur.description]#所有字段
    all_data=cur.fetchall()#所有數(shù)據(jù)值，數(shù)組形式存放
    book=xlwt.Workbook()
    sheet=book.add_sheet(sheet_name1)
    for col,filed in enumerate(fileds):
        sheet.write(0, col, filed)
    row=1
    for data in all_data:
        for index, datacol in enumerate(data):
            sheet.write(row, index, datacol)
        row = row + 1
    book.save(file_path)  # 使用完整的文件路徑來保存工作簿

    # 設(shè)置輸出目錄
output_dir = r'C:\Users\Administrator\Desktop\2024年工作文件夾\研發(fā)績(jī)效考核\負(fù)載得分'

    # 導(dǎo)出Excel，使用帶日期時(shí)間的文件名
export_excel(output_dir)