数据库导出数据至本地

import json
import calendar
import re

from bin import *

ismonthwrite=False#是否按月写入，数据量大时可开启
start_time="2024-02-27"#开始时间
end_time="2024-03-26"#结束时间
wirte_path = r"C:\Users\isoftstone\Desktop\导出.xlsx"#写入的文件路径
read_path = r"C:\Users\isoftstone\Desktop\sql.txt"#读取文件的路径
global between_time
between_time="o.action_time"#sql的batween时间的字段名称






global data
datas={}
with open(read_path, 'r',encoding="utf-8") as file:
    sql = file.read()
aa=datetime.datetime.now()#记录代码执行时间
start_timelist=start_time.split("-")
end_timelist=end_time.split("-")
start_time_rq=int(start_timelist[2])#开始日期1-31号
start_time_yue =int(start_timelist[1])#开始月份
start_time_nian =int(start_timelist[0])#开始年份年份
end_time_r=int(end_timelist[2])#开始日期1-31号
end_time_yue=int(end_timelist[1])#结束月份
end_time_nian=int(end_timelist[0])#结束年份
sql_list=[]
while start_time_nian<=end_time_nian:
    for yue in range(start_time_yue,end_time_yue+1):
        day, daynum = calendar.monthrange(start_time_nian, yue)#获取该年该月的一个月有多少天
        # 获取当前日期时间
        now = datetime.datetime.now()
        # 获取当前的月份和日期
        month = now.month
        day_of_month = now.day
        data_vale = []#写入的数据表
        if yue==start_time_yue:
            start_time_rq=start_time_rq
            if end_time_yue ==start_time_yue:
                end_time_rq=end_time_r
            else:
                end_time_rq=daynum
        else:
            start_time_rq=1
            end_time_rq=end_time_r
        for start_time_r in range(start_time_rq,end_time_rq+1):
            yuanshisql=sql
            if yue == month and day_of_month ==start_time_r-1:
                break
            print(yue, start_time_r)

            str_yue=str(yue).zfill(2)
            str_start_time_r = str(start_time_r).zfill(2)
            sql=sql+f"""
                      and {between_time} between '{start_time_nian}-{str_yue}-{str_start_time_r} 00:00:00'
                      and '{start_time_nian}-{str_yue}-{str_start_time_r} 23:59:59' """
            sql_list.append(sql)
            sql = yuanshisql
    start_time_nian+=1


# for i in sql_list:
#     print(i)
def select_data(sql,data_vale={},ku="生产_营销_十分到家_USS2.0_workorder_MYSQL_从(10.68.70.32)",biao="uss_workorder"):
    while 1:
        try:
            statu2, data= DB_sql(sql, database=biao,example=ku)
            # statu2, data =["a","b","c","d"],[1,2,3,4]
            break
        except:
            sleep(2)
            statu2, data = DB_sql(sql, database=biao, example=ku)
    # print(between_time)
    check_headle = between_time
    check_tail = "and"
    sqltime = re_get_list_str(check_headle, check_tail, sql)
    # print(sqltime)
    # print(len(sqltime))
    sqltime = re.findall(r'\d+', sqltime[-1])[0:3]
    timedate = ""
    for vales in sqltime:
        timedate += vales

    timedate = int(timedate)
    # print(timedate)
    data_vale[timedate] = []
    datas[timedate]=[]

    if statu2:
        if len(statu2) != len(set(statu2)):
            return False,"列表中存在重复项,写入的表头不允许有重复项"
        global header
        header = statu2

        # print(len(datas))
        for i  in data:

            data_vale[timedate].append(i)
            datas[timedate].append(i)

    else:
        # print(yue, start_time_r,data,sql)
        return f"{timedate}没有查到数据"
    return timedate


executor_Thread(30,select_data,sql_list,num=1,isprint=True)
datakey=[]
data1=[]
for i in datas:
    datakey.append(i)
datakey.sort()

for values in datakey:
    # print(datas[values])
    if ismonthwrite:
        data1.append(datas[values])
    else:
        for value in datas[values]:
            data1.append(value)


append_data_to_excel(wirte_path, data1, header=header)

print(aa,datetime.datetime.now())