# It is recommended that you extract your username, password and database variables from an external file rather than having them directly within your code else you maybe exposed

username = "#"
password = "#"
database = "database:port /servicename"
directory = r'#:\...\file.xlsx'

SQL = """SELECT * FROM Table"""


def SQL_Excel_Data_Generator(username, password, database, query, path, create=True, sheetname='Sheet 1',
                             disclaimer=True):
    # Disclaimer to warn user not to use username, password and database connection within this code
    if disclaimer == True:
        print(
            'When running queries please ensure that your username, password and databse connection is not visible within your python code when put into production. Use , disclaimer=False to disable this warning.')
    elif disclaimer == False:
        pass

    # Import required packages
    import os
    import cx_Oracle
    import pandas as pd
    from openpyxl import load_workbook
    import time

    # Connect to Oracle using required variables
    connection = cx_Oracle.connect(username, password, database)

    # Read SQl query into pandas dataframe
    df = pd.read_sql_query(query, connection)

    # Experimental code that aims to remove time from datetimes within Excel
    # for date in df.date:
    #    df[date] = df[date].dt.floor('d')

    # Create an Excel document from scratch if create parameter is True, removing the index and starting at row 0 (A1:##)
    if create == True:
        writer = pd.ExcelWriter(path)
        df.to_excel(writer, index=False, sheet_name=sheetname, startrow=0)
        writer.save()

    # Sometimes Excel takes some time to update, wait 3 seconds to check if the file has been prepared
        time.sleep(3)
        if os.path.isfile(path) == True:
            pass

    # If it isn't ready try waiting another 3 seconds and check again
        else:
            time.sleep(3)
            if os.path.isfile(path) == True:
                pass

    # If Excel hasn't saved correctly raise the below value error
            else:
                raise ValueError('Cannot write to path specified')

    # Update an existing Excel dashboard if create variable is False.
    elif create == False:
        if os.path.isfile(path) == True:

    # Load the existing workbook from the path variable into the book variable
            book = load_workbook(path)

    # Tell Pandas to use the ExcelWriter function to read from the path variable. Excel writer is needed to create a new workbook
            writer = pd.ExcelWriter(path, engine='openpyxl')

    # Tell Pandas that the book attribute is equal to the book variable
            writer.book = book

    # Tell Pandas to put the dataframe into an Excel instance
            df.to_excel(writer, index=False, sheet_name=sheetname, startrow=0)

    # Now save the Excel instance
            writer.save()

    # If it cant save then the file must not exist within the directory so raise a value error
        else:
            raise ValueError('File not found within directory when specifying the path')

    # If create parameter has been entered incorrectly then raise value error
    else:
        raise ValueError('create parameter unknown')

    # Close all open connections to Oracle when finished
    connection.close()


print('Loading Data Into Excel')
SQL_Excel_Data_Generator(username=username, password=password, database=database, create=True, query=SQL, path=directory, sheetname="my_sheet")