import pyodbc
import pandas as pd
from datetime import datetime
import win32com.client as win32
import openpyxl
#SQL Server connection established
cnxn = pyodbc.connect(
    "Driver={SQL Server};"
    "Server="ip";"
    "Database='';"
    "UID=Jaydeb.bhunia;"
    "PWD=password;"
    "Trused_Connection=yes")
#Query executie and export csv
query = pd.read_sql_query('''select top 10 * from CashOrderTrn(nolock)''',cnxn)
df = pd.DataFrame(query)
#Export output to local folder
df.to_excel(r"C:\Users\THL1012\Desktop\Python Output\FTD_MTD_Sales.xlsx", sheet_name='Sheet1')
#data import to master file
book = openpyxl.load_workbook(r'C:\Users\THL1012\Desktop\Daily Alert_Automation\FTD_WTD_MTD_Sales.xlsx')
df = pd.read_excel(r"C:\Users\THL1012\Desktop\Python Output\FTD_MTD_Sales.xlsx", sheet_name='Sheet1', index_col=[0,1])
with pd.ExcelWriter(r"C:\Users\THL1012\Desktop\Daily Alert_Automation\FTD_WTD_MTD_Sales.xlsx", mode="w",
                    engine="openpyxl", ) as writer:
    writer.book = book
    writer.sheets = {ws.title:ws for ws in book.worksheets}
    df.to_excel(writer, sheet_name='import')

with pd.ExcelWriter(r"C:\Users\THL1012\Desktop\Daily Alert_Automation\FTD_WTD_MTD_Sales.xlsx", mode="w",
                    engine="openpyxl", ) as writer:
    writer.book = book
    writer.sheets.update(dict((ws.title, ws) for ws in book.worksheets))
    
    df.to_excel(writer, sheet_name='import')