Oracle Database - Python - for Oracle (using sqlplus)

I am going to demonstrate how to run sqlcommands using python using sqlplus
it is interesting, because it gives inherent capabilities of sqlplus to be used in python.

How does Python do it ? 
Python has a module Popen which can open a process of sqlplus inside it and call it
and once the sqlplus process is open, you have all the capabilities of sqlplus

In below demonstration I am going to demonstrate how to run a sqlquery and then parse the output line by line ( and column by column using python)

The complete code is given below.  The code is reusable that you only need to set the sqlCommand variable and run any sql you want
Note - Using triple quotes you can set a paragraph too in python, that is exactly what I am doing, there can be many other ways like reading from a file or probably running a sqlfile but for sake of demonstration I will run  a query in the infamous scott/tiger schema.

How to do it

Use function setSqlCommand to define your sql commands using triple quotes as highlighted below.

The main function defines user connection string, it takes variable username and password
Examples of username and password are like
username = '' and password = ' as sysdba'  ## if you want to connect passwordless as sysdba
username = 'scott' and password = 'tiger'  ## Connect directly
usrename = 'scott' and password = 'tiger@orcldb_pr'   ## Connect over tns

Next in the main function is to call the setSqLCommand function to set the sql command as we just discussed

Finally we are calling the runSqlQuery function to run the sqlquery,
It takes 2 arguements, sqlCommand and user_string (generated from username and password)

queryResult, errorMessage = runSqlQuery(sqlCommand, v_user_string)

The output is returned in queryResult and if there is an error it is returned in errorMessage
Next I have used the print command to print my query result.

As part of this module, I have added another function to parse the query output line by line and column by column. It is python logic defined below in stringParse function which takes queryResult (string as input).

Example for query
select empno, ename, hiredate  from emp where rownum < 4;
***Output*** 

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81

This is line #  3
7369
SMITH
17-DEC-80
This is line #  4
7499
ALLEN
20-FEB-81
This is line #  5
7521
WARD

22-FEB-81

I have split the lines based on space character, if your column is going to have multiple space characters, the logic might not work exactly. 

The line numbers are mentioned 3 , 4 and 5 because first couple of lines are line header and a line separator.

Complete script

#!/usr/bin/python

## Make Imports
from subprocess import Popen, PIPE
import os
import logging
import sys
import getopt
import traceback

## Define Function
def setSqlCommand():
        sqlCommand = '''spool sql_with_more_than_1plan.txt
set lines 500 pages 500
select empno, ename, hiredate  from emp where rownum < 4; ''' 
        return sqlCommand

## Function to Execute Sql command using sqlplus
def runSqlQuery(sqlCommand, v_user_string):
   session =  Popen(["sqlplus", "-S", v_user_string], stdout=PIPE, stdin=PIPE)
   session.stdin.write(sqlCommand)
   print 'Running Now'
   return session.communicate()

# Function to Parse String output
def stringParse(queryResult):
        splitlist = queryResult.split("\n")
        #print splitlist
        #print len(splitlist)
        #for str in splitlist:
        #       print "The String is %s" % str
        for counter in range(3, len(splitlist) - 2 ):
                #print splitlist[counter]
print "This is line #  %u"  % counter
                splittab = splitlist[counter].split()
                for tb_counter in range(0, len(splittab)):
                        print splittab[tb_counter]
        return

# 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@orcldb_pr'
        #
        v_user_string=username + "/" + password
        #
        try:
                sqlCommand = setSqlCommand()
        except Exception as e:
                logging.info('In Exception')

        try:
                queryResult, errorMessage = runSqlQuery(sqlCommand, v_user_string)
        except Exception as e:
                logging.info('In Exception')

        print queryResult

# Parse the Query Result
        stringParse(queryResult)

if __name__ == "__main__":
    main(sys.argv)