Oracle Database - Python for Oracle - Excel Write Module

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"