import mysql.connector
import datetime
import glob
import os
import pandas as pd
import csv
import pyminizip
import pathlib
import shutil

def exec():

    # 1.SQL execution in python------------------------------------

    #DB connection
    conn = mysql.connector.connect(
       host = 'host',
       port = 'port',
       user = 'User',
       password = 'password',
       database = 'Database' 
    )    
        
    #Get SQL file
    os.chdir("SQL folder")
    sql_file_list = glob.glob("*.sql")
    
    #Create folder
    now = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    os.mkdir(now)

    #SQL execution
    for sql_file in sql_file_list:

        print(sql_file)        

        with open(sql_file, 'r') as f:
            sql_query = f.read()
    
        df = pd.read_sql_query(sql_query, con=conn) 

　　　　　# 2.Output the result in Excel------------------------------------

        #For excel output
        df.to_excel(now + "\\" + sql_file.replace('.sql', '.xlsx'), sheet_name=sql_file.replace('.sql', ''), index=False)
            
        #For CSV output
        df.to_csv(now + "\\" + sql_file.replace('.sql', '.csv'), encoding="utf-8_sig", quoting=csv.QUOTE_NONNUMERIC, index=False)

    os.chdir(now)
        
    #If you have individual edits, do it here

    #Get result file
    result_file_list = glob.glob("*.xlsx")    

    # 3.ZIP------------------------------------

    #Result file compression(Japanese file name not supported)
    file_path = []
    for i in range(len(result_file_list)):
        file_path.append('\\') 
    
    pyminizip.compress_multiple(result_file_list,file_path, now + '.zip','pass',0)
            
    # 4.Put on file server------------------------------------

    #Result file upload
    share = pathlib.WindowsPath(r'file server' + now + '.zip')
    shutil.copyfile(now + '.zip', share)
            
    #Disconnect
    conn.close()

#Run
exec()

Recommended Posts