Oracle Database - Python - Database Report Framework / Heath Check Framework (in Excel)

I have created a re-usable framework from querying data from Oracle Database and giving output in excel format.

It can be used in many cases like
1. Pre/post-Upgrade Report Creation
2. Quick documented Health check of database
3. Normal report preparation from database.

The framework is plug and play i.e., I can add more queries to get data from and those will be automatically picked up and reported.
I will not be discussing python code, but only how to use the framework. 
complete Code is provided in the end of the blog.

Here is how it works 
 Ini file has definition of everything. Let see a small example of ini file - I have written my comments in # and different font for better understanding.
There is a section globalproperties which defines global properties for reporting
Then each subsequent section has sql files and check names, the explanation of those is given in detail below. 



[GlobalProperties]

# Global Variables

output_file: /home/oracle/pre_upgorcldb.xlsx
#This is the output file in which report is generated
report_title: Database Health Check Report for Database orcldb
#Report Title in Summary Sheet
# Database sub-section
# this section defines below which I believe have not to be explained
Username:vineet
Password:vineet
HostName:rac-scan
PortNumber:1521
SID:orcldb_1
# Output Date format
# This Date format will be used in date column outputs
date_format:dd/mm/yy hh:mm
# Output Font Name (Match this with the name in Excel (case also)
# Font Name
font_name: Times New Roman

# The complete explanation of Section Parameter is as below in the ini file
## Each Section Below is a sheet in the excel sheet having below attributes
# Note - all attributes are mandatory
#[] - First is the section name - it can be anything per your own use - describing Name of the section. 
# A logical name probably to the report section
# Sheet - Sheet Name that will come in the saved excel file
# SQL - SQL query to run for the report generation
# Column,Threshold - These 2 combined define a threshold for a column for output verification
# For example if you specify 3 for Column and 100 for threshold, all the values for column 3 in your query output
# will be checked for values greater than 100 and corresponding entries will be red higlighted
# Finally Threshold type lt lets you specify less than or gt lets you specify greater than
# lt: if values should be less than specified threshold - Fail
# gt: if values should be greater than specified threshold - then - Fail 
# date_cols : Specified the date columns in your output - to this date_format defined in global properties will be applied
# Report:Only - 'Y' or 'N' : put Y which will mean column, threshold, threshold_type will have no impact and no thresholds will be checked

# Description: Summary Sheet will display this description with the check name with pass/fail (based on threshold violation)

[Invalid_Objects]

Sheet: Invalid_Objects

SQL: select owner, object_name, object_type from dba_objects where status <> 'VALID'  order by 1,2,3

Column:4

Threshold:500

threshold_type: lt

date_Cols:20 

Report_Only:Y

Description: Invalid Objects Information

[Objects_Summary]

Sheet: Objects_Summary

SQL: select owner, object_type, status, count(*) as Count from dba_objects group by owner, object_type, status

Column:4

Threshold:500

threshold_type: gt

date_Cols:15

Report_Only:Y

Description: Objects Information

Let me show you a summary sheet output and one of the generated sheets

Summary Sheet 

Example: Objects Information Sheet 

Usage - <script_name> <ini_file_name> <log_file_name>

Example - ./pre_upg.py /home/oracle/pre_upg.ini /tmp/pre_upg.log

Complete Code for reference

#!/usr/bin/python

## Make Imports

from subprocess import Popen, PIPE

from pprint import pprint

import os

import logging

import sys

import getopt

import traceback

import cx_Oracle

import xlsxwriter

import ConfigParser

#########################################################

#### Define Global Variables#############################

def loadGlobalVars(out_file, date_f, font_name):

global wb

global ws

global bold_format

global red_color_format

global date_format

global full_border_format

global heading_format

global pass_format

global fail_format

global seq_format

global merge_format

# Define a Global Workbook Object

wb = xlsxwriter.Workbook(out_file)  # Create Excel Object for new workbook

        ws =  wb.add_worksheet("Summary")  # Create Summary Sheet

# Define Formatting Formats

bold_format = wb.add_format({'bold': True})

bold_format.set_border()

bold_format.set_font_name(font_name)

 

red_color_format = wb.add_format()

red_color_format.set_font_color('red')

red_color_format.set_border()

red_color_format.set_font_name(font_name)

date_format = wb.add_format({'num_format':   date_f })

date_format.set_border()

date_format.set_font_name(font_name)

full_border_format = wb.add_format()

full_border_format.set_border()

full_border_format.set_font_name(font_name)

fail_format = wb.add_format({'bold': True})

fail_format.set_bg_color('red')

fail_format.set_align('center')

fail_format.set_font_color('white')

fail_format.set_border()

fail_format.set_font_name(font_name)

pass_format = wb.add_format({'bold': True})

pass_format.set_bg_color('green')

pass_format.set_align('center')

pass_format.set_font_color('white')

pass_format.set_border()

pass_format.set_font_name(font_name)

heading_format = wb.add_format({'bold': True})

heading_format.set_bg_color('black')

heading_format.set_align('center')

heading_format.set_font_color('white')

heading_format.set_font_name(font_name)

seq_format = wb.add_format({'bold': True})

seq_format.set_align('center')

seq_format.set_font_color('black')

seq_format.set_border()

seq_format.set_font_name(font_name)

merge_format = wb.add_format({

   'bold': 1,

   'border': 1,

   'align': 'center',

   'valign': 'vcenter'})

merge_format.set_bg_color('black')

merge_format.set_font_color('white')

merge_format.set_font_size(14)

def load_main(ini_file):

        Config  = ConfigParser.ConfigParser()

        try:

Config.read(ini_file)

        except Exception as e:

                logging.info('Error Parsing ini file : ' + ini_file )

logging.info(traceback.print_exc())

sys.exit(2)

        # Get Global Properties as Defined in Global Properties Section 

out_file = Config.get('GlobalProperties','output_file')

username = Config.get('GlobalProperties','Username')

password = Config.get('GlobalProperties','Password')

hostName = Config.get('GlobalProperties','HostName')

portNumber = Config.get('GlobalProperties','PortNumber')

sID = Config.get('GlobalProperties','SID')

date_f = Config.get('GlobalProperties','date_format')

report_title = Config.get('GlobalProperties','report_title')

font_name = Config.get('GlobalProperties','font_name')

loadGlobalVars(out_file, date_f, font_name)

# this is Summary Sheet Configuration

ws.merge_range('B1:D1',report_title, merge_format)

ws.write(2,1,"#",heading_format)

ws.write(2,2,"Check_Name",heading_format)

ws.write(2,3,"Description",heading_format)

ws.write(2,4,"Status",heading_format)

ws.set_column(2,2,25)

ws.set_column(3,3,60)

ws.set_column(4,4,10)

# Get the Connection Object

db = createConnectionObject(username, password , hostName, portNumber, sID)

rw_cnt = 3

section_list = Config.sections()

for section in section_list:

if section == 'GlobalProperties':

logging.info ( 'Skipping Global Properties')

else:

        sheet = Config.get(section,'Sheet')

sQL = Config.get(section,'SQL')

column = int(Config.get(section,'Column'))

threshold = float(Config.get(section,'Threshold'))

date_col = Config.get(section,'Date_cols')

dtList = date_col.split(',')

rptOnly = Config.get(section,'Report_Only')

description = Config.get(section,'Description')

threshold_type = Config.get(section,'threshold_type')

        try:

               logging.info('Processing Section: ' + section)

flg = write_to_excel(db, sheet, sQL, column, threshold, threshold_type,   dtList, rptOnly) # Send the Cursor to writetoExcel Function

ws.write(rw_cnt,1,rw_cnt - 2,  seq_format)

ws.write(rw_cnt,2,sheet,  bold_format)

ws.write(rw_cnt,3,description,  full_border_format)

if flg == 'false':

ws.write(rw_cnt,4,"Fail",  fail_format)

else:

ws.write(rw_cnt,4,"Pass",  pass_format)

rw_cnt = rw_cnt + 1

        except Exception as e:

               logging.info('Function - writeToExcel In Exception')

                logging.info(traceback.print_exc())

db.close() # Finally Close DB Object

## Define Function

def write_to_excel(db, sheet, sQL, column, threshold, threshold_type,   dtList, rptOnly):

cur_sor = db.cursor()

        cur_sor.execute(sQL)

        worksheet = wb.add_worksheet(sheet) 

format_dict = {}

# Provide row and column offsets i.e from where output should be printed in the sheet

row_offset = 1 

col_offset = 1 

row = 0 + row_offset

col = 0 + col_offset

# Set flag to true as default

flag = 'true'

# Print Headings to Excel Sheet using cursor.description

for i in range(0, len(cur_sor.description)):

worksheet.write(row,i + col_offset, cur_sor.description[i][0], heading_format)

# Initialize dict for column width setting

format_dict[i + col_offset] = len(cur_sor.description[i][0])  + 4

# Increment row to work from next row after headings

row = row + 1

for tupple_row in cur_sor:

col = 0 + col_offset

for list_item in tupple_row:

worksheet.write(row, col, list_item, full_border_format)

# Logic to verify threshold as per column given and less than, greater than given in config sheet

if (col ==  column - 1 + col_offset and rptOnly == 'N'): 

if threshold_type == 'lt': 

if list_item <  threshold:

worksheet.write(row, col, list_item, red_color_format)

flag = 'false'

if threshold_type == 'gt':

if list_item >   threshold:

worksheet.write(row, col, list_item, red_color_format)

flag = 'false'

# Now check for Date format

for dt in dtList:

if col == int(dt) - 1 + col_offset:

worksheet.write(row, col, list_item, date_format)

max_len_temp = len(str(list_item))

if max_len_temp > format_dict[col]:

format_dict[col] = max_len_temp

col = col + 1

row = row + 1

# routine to adjust Column Widhts as Captured in the Dict

for key in format_dict:

worksheet.set_column(key,key,format_dict[key])

# Now return flag as true/false based on checking - This is updated in Summary Sheet

        return flag 

# Function to return Connection Object for Query Executions

def createConnectionObject (username, password , hostName, portNumber, sID):

        dsn_tns = cx_Oracle.makedsn(hostName, portNumber, sID)

        db = cx_Oracle.connect(username, password, dsn_tns)

        return db 

#

# Program Execution Starts here

############################################################################################################

def main(argv):

#Setup Logging Information

if len(argv) < 2:

print "#########################################"

print "######Correct Usage of Script is ########"

print "###### <script_name> ini_file log_file###"

print "#########################################"

sys.exit(2)

try: 

ini_file = argv[1]

log_file = argv[2]

if os.path.isfile(ini_file): 

print 'Success - Verified Ini File Exists'

else:

print 'Error - Ini_file Does not Exists - Exiting' 

sys.exit(2)

except Exception as e:

                logging.info('Parsing Sys Arguements')

                logging.info(traceback.print_exc())

        logging.basicConfig(filename=log_file, level=logging.INFO, format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p')

        logging.info(' Script Execution - Started')

try:

load_main(ini_file)

# Make sure to close workbook

wb.close()

except Exception as e:

                logging.info('Function - Load_Main Exception')

                logging.info(traceback.print_exc())

        logging.info('Script - Execution Ended')

if __name__ == "__main__":

    main(sys.argv)