Last active
September 13, 2022 08:29
-
-
Save RyanMDavies7/ea4b344456a903eeea7627a6be648457 to your computer and use it in GitHub Desktop.
Python Code that Automatically Updates Excel Spreadsheets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment