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

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

Reporting Anonymous   March 18, 2017

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)

Oracle Database - Python for Oracle - Excel Write Module

Oracle Database - Python for Oracle - Excel Write Module

Reporting Anonymous   March 13, 2017

In this blog, I will discuss how to write data to excel file, with some formatting in python.

I have already discussed how to query data from python using oracle in previous blogs. 
Python for Oracle (using cx_Oracle) and Python - for Oracle (using sqlplus)

In this module, I will be using output from the executed query and writing to an excel file. 
For details on query execution you can refer to any of the above blogs, I will be using cx_oracle for this particular blog.

xlsx writer  - is a python module which I will be using to write to a new excel file. 
The details are mentioned in the above link
For Details on installation refer end of this blog.

I will list the requirements first and show the output and then go over each step in the newly introduced excelWrite function

1. Print all columns from emp table
2. Column - Headings should be in bold
3. Date format should be 'dd/mm/yy'
4. If Salary is greater than 2000, then mark it red
5. Print Sum of Total Salary

and here is the output..

Code Explanation - I will only be explaining the new code introduced and not the one discussed in previous blogs.

I have passed the cursor to the excel write function as in the line
writeToExcel(c) # Send the Cursor to writetoExcel Function

Then I have parsed the cursor in the excelWrite Function.

The workbook class and worksheet class are used to add a new workbook and create a new worksheet

workbook = xlsxwriter.Workbook('/home/oracle/excel_write.xlsx') 
worksheet = workbook.add_worksheet("scott_schema") 

The below function worksheet.write is used to write data to the excel cells
 (row,col start with 0,0 in xlsxwriter)
Data - is what you need to write
format - format of the data

worksheet.write(row, col, Data, format)

Format
Below are few examples for format types used here

1. Date Format
date_format = workbook.add_format({'num_format': 'dd/mm/yy'})

1. Color Format - Red
color_format = workbook.add_format()
color_format.set_font_color('red')

1. Bold format
bold = workbook.add_format({'bold': True})

Finally I am writing a sum function using below to sum the salary

worksheet.write(row + 1, 5, '=SUM(F1:F' + str(row) + ')', bold)

The rest of the code is either repeated from previous python blogs or if / else statements so is pretty obvious, so I will not be explaining in detail on those sections.

Complete Code 

#!/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

## Define Function

def writeToExcel(cur_sor):
workbook = xlsxwriter.Workbook('/home/oracle/excel_write.xlsx') # Create Excel Object for new workbook
worksheet = workbook.add_worksheet("scott_schema") #Add a New Worksheet Name - scott_schema
row = 0
col = 0
# Write Headings
bold = workbook.add_format({'bold': True})
worksheet.write(row, col, "Employee Number", bold)
worksheet.write(row, col + 1, "Employee Name", bold)
worksheet.write(row, col + 2, "Job Title", bold)
worksheet.write(row, col + 3, "Manager Emp#", bold)
worksheet.write(row, col + 4, "Hiring Date",bold)
worksheet.write(row, col + 5, "Salary", bold)
worksheet.write(row, col + 6, "Department Number", bold)
date_format = workbook.add_format({'num_format': 'dd/mm/yy'})
color_format = workbook.add_format()
color_format.set_font_color('red')
row = row + 1
for tupple_row in cur_sor:
col = 0 
for list_item in tupple_row:
worksheet.write(row, col, list_item)
if col == 4 : 
worksheet.write(row, col, list_item, date_format)
if col == 5:
if list_item > 2000 :
print list_item
worksheet.write(row, col, list_item, color_format)
col = col + 1
row = row + 1
worksheet.write(row, 0, 'Total Salary', bold)
row = row - 1
worksheet.write(row + 1, 5, '=SUM(F1:F' + str(row) + ')', bold)

workbook.close()


def setSqlCommand():
        sqlCommand = 'select empno, ename, job, mgr, hiredate, sal,  deptno from emp order by empno'
        return sqlCommand


# Function to Execute Sql commands over TNS
def runSqlTNS (sqlCommand, username, password , hostName, portNumber, sID):
        dsn_tns = cx_Oracle.makedsn(hostName, portNumber, sID)
        #print dsn_tns
        db = cx_Oracle.connect(username, password, dsn_tns)
print db.version
        cursor = db.cursor()
        cursor.execute(sqlCommand)
#cursor.prepare('select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp order by empno')
        return cursor

#


# Program Execution Starts here
############################################################################################################

def main(argv=None):

#Setup Logging Information
        logging.basicConfig(filename='/tmp/myapp.log', level=logging.INFO, format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p')
        logging.info('Started')

        username = 'scott'
        password = 'tiger'
        #Define dsn entries to create a tns connection
        hostName = 'rac-scan'
        portNumber = 1521
        sid = 'orcldb_1'
        #
        try:
                sqlCommand = setSqlCommand()
        except Exception as e:
                logging.info('Function - sqlCommand - In Exception')
logging.info(traceback.print_exc())

        try:
c = runSqlTNS(sqlCommand, username, password , hostName, portNumber, sid)
        except Exception as e:
                logging.info('Function - runSql In Exception')
logging.info(traceback.print_exc())
        try:
writeToExcel(c) # Send the Cursor to writetoExcel Function
c.close()
        except Exception as e:
                logging.info('Function - writeToExcel In Exception')
logging.info(traceback.print_exc())

if __name__ == "__main__":

    main(sys.argv)

Install - 
There are many different ways of installation which can be seen on the base site of xlsx writer, however I prefer a manual install and I use below.

# tar -zxvf /media/sf_RAC12c/rpm/XlsxWriter-master.tar.gz 
# cd XlsxWriter-master/

# python setup.py install
Truncated output
Installed /usr/lib/python2.7/site-packages/XlsxWriter-0.9.6-py2.7.egg
Processing dependencies for XlsxWriter==0.9.6

Finished processing dependencies for XlsxWriter==0.9.6

To test if python xlsxwriter is installed properly use below - 
python -c "import xlsxwriter"

Oracle Database - Python for Oracle (using cx_Oracle)

Oracle Database - Python for Oracle (using cx_Oracle)

Reporting Anonymous   March 09, 2017

If you have followed the previous blog Python-sqlplus, I had discussed querying using sqlplus from python.
In this blog I will be talking about querying using an API called cx_Oracle.

If you need to know how to install cx_Oracle - google it, or what I follow is this

It is pretty much similar as the last code I had written. This code is completely re-usable and can be used to insert sql queries for executing.

There is a new function I have defined here which is  def runSqlTNS this function takes input as sql command, hostname, port number, username, password,  and Sid and returns the cursor with executed query. 

Below is the breakdown of the function line by line.
There are 2 types of queries am using, one of the example here is to show you how to use bind variables as well.

        dsn_tns = cx_Oracle.makedsn(hostName, portNumber, sID)
This Line creates a dsn connection based on input. I am using scan IP for my demo.
        db = cx_Oracle.connect(username, password, dsn_tns)
create a database connection object
print db.version
Print Database Version # Not mandatory
        cursor = db.cursor()
create a Cursor Object
#        cursor.execute(sqlCommand)
cursor.prepare('SELECT empno, ename, mgr, sal  FROM emp WHERE sal >:min')
cursor.execute(None, {'min':2000})
Execute Cursor object
        return cursor

Finally I have used  small bit of code to iterate over the output and print the output row by row (and col by col as well).

The output of the query is a list of tuples, which can be easily parsed using Python, if required by below code. 

for row in c:
print row
for list_item in row:
print list_item

I am going to run example query using the infamous scott/tiger schema and show you the output.

[oracle@rac1 python]$ ./orcl_python.py
12.1.0.2.0
(7566, 'JONES', 7839, 2975.0)
7566
JONES
7839
2975.0
(7698, 'BLAKE', 7839, 2850.0)
7698
BLAKE
7839
2850.0
(7782, 'CLARK', 7839, 2450.0)
7782
CLARK
7839
2450.0
(7788, 'SCOTT', 7566, 3000.0)
7788
SCOTT
7566
3000.0
(7839, 'KING', None, 5000.0)
7839
KING
None
5000.0
(7902, 'FORD', 7566, 3000.0)
7902
FORD
7566
3000.0

Complete Code - 
#!/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

## Define Function


def setSqlCommand():
        sqlCommand = 'select empno, ename, mgr from emp'
        return sqlCommand


# Function to Execute Sql commands over TNS
def runSqlTNS (sqlCommand, username, password , hostName, portNumber, sID):
        dsn_tns = cx_Oracle.makedsn(hostName, portNumber, sID)
        #print dsn_tns
        db = cx_Oracle.connect(username, password, dsn_tns)
print db.version
        cursor = db.cursor()
#        cursor.execute(sqlCommand)
cursor.prepare('SELECT empno, ename, mgr, sal  FROM emp WHERE sal >:min')
cursor.execute(None, {'min':2000})
        return cursor


# Program Execution Starts here
############################################################################################################

def main(argv=None):

#Setup Logging Information
        logging.basicConfig(filename='/tmp/myapp.log', level=logging.INFO, format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p')
        logging.info('Started')

        username = 'scott'
        password = 'tiger'
        #Define dsn entries to create a tns connection
        hostName = 'rac-scan'
        portNumber = 1521
        sid = 'orcldb_1'
        #
        try:
                sqlCommand = setSqlCommand()
        except Exception as e:
                logging.info('In Exception')


        try:
c = runSqlTNS(sqlCommand, username, password , hostName, portNumber, sid)
for row in c:
print row
for list_item in row:
print list_item
c.close() # Close Cursor as best practice. 
        except Exception as e:
                logging.info('In Exception')
if __name__ == "__main__":
    main(sys.argv)