Alternative for CX_Oracle with Subprocess in Python

Operations team or production support team, do self-kick start by automating their day-to-day tasks as nobody happy to do monkey job. In order to automate the stuff, many support guys choose dynamic interpreter language like Bash, Python, Perl, etc which can do their repeated task wisely without manual intervention and by doing so, the productive of the team also drastically increase.

There are some bottlenecks faced when we do automation as most of the Unix servers prebuild with some dynamic scripting languages Perl & Python with minimal standard libraries installed or windows servers with batch & PowerShell. When we convert our own requirements into dynamic code, there might be external libraries needed and certain libraries are really easy to install from central repository tools ( Eg: Perl from CPAN & Python from PyPI), while some other libraries would require additional setup tools.

Connect to DB:

Our most of the application in banking industries, use Oracle as the backend database for OLTP. As support guy, I have to execute some SQL queries by connecting SQLPLUS tool to prepare the daily transactions stats reports. The manpower required to execute these queries needs at least 1 hour and it is error-prone also. So I have spent extra one hour to automate this stuff.

Understanding requirements:

Since Python is our chosen language to automate our operation pieces of stuff, I directly tried to get python CX_Oracle ODM library (object data model) to be installed on our production server but that was not that simple as I was thinking. The installation process required admin privileges and along with that, it’s required some setup tools as well. So as per our internal process, I raised the ticket to Unix admin with necessary approvals but again our admin also not able to install since it was required additional libraries to install, moreover installing the libraries directly on production may lead impact.

Now it is my call whether to involve development team who are not pythonist or leave the stuff be always manual, but then I was thinking about my original requirements which were to execute the select statement and did not involve any DML or DDL statements.

So this can be achieved with simple SQLPLUS command as it is generally used in a bash script. But my concern is to have pure python code rather having another module in a bash script, at least to have platform independent as Unix & Windows OS level. To achieve this requirement, I have written simple SQL_CONNECT python class using SQLPLUS command in subprocess library and will discuss its code style here.

Security concern:

Since we are going to use SQLPLUS, it is obvious that the user & password is visible to all. So with DBA assistance DB role can be created with specific tables &  columns be privileged to the respective user with only select permission.

Libraries to import:

import subprocess
import types
import re

The SQLPLUS is an external command which can be invoked and communicated using subprocess, types module to check whether the input parameters that passed are my expected types and re module to filter out the ORA errors.

Wrapping the code with class base:

class sql_connect():
        def __init__(self,ora_userid,ora_passwd,ora_sid,ora_proj,ora_role=None):
                self.ora_user=ora_userid
                self.ora_pwd=ora_passwd
                self.ora_sid=ora_sid
                self.ora_proj=ora_proj
                if type(ora_role) == types.TupleType:
                        self.role_name,self.role_pwd=ora_role
                else:
                        self.role_name = None

It is always good practice to use OOPS (object-oriented programming concept) which increase our level of coding knowledge as well as code reusabilities. Python is pure OOPs language, so the class concept in python has all the standard mechanism such as inheritance, derived class, annotation &  override, etc.,

Init method is to give visibility to our class variables and self is to protect the variable behavior to be exposed to outside unless until called with an object reference.

In the above variable declaration part, ora_userid,ora_passwd,ora_sid are the specific parameter for SQLPLUS command and ora_proj to be passed if shared DB and ora_role to get input as tuple format. The ora_role is validated with if condition and leveraged to be None also if the role is not set on your DB side.

Connection method using subprocess:

def conn(self,ora_query):
                connt_sid = " %s/%s@%s "% (self.ora_user,self.ora_pwd,self.ora_sid)
                error = re.compile('(ORA-)\d+')
                sql_conn = subprocess.Popen(['sqlplus','-S',connt_sid], stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE)
                sql_conn.stdin.write(self.ora_proj)
                if self.role_name:
                        sql_conn.stdin.write('\n set role '+self.role_name+' identified by '+self.role_pwd+';')

                sql_conn.stdin.write('\n whenever sqlerror exit 2;')
                sql_conn.stdin.write('\n set feedback off;')
                sql_conn.stdin.write('\n set head off;')
                sql_conn.stdin.write('\n set pages 0;')
                sql_conn.stdin.write("\n set null '0';")
                sql_conn.stdin.write("\n set colsep '|';")
                sql_conn.stdin.write('\n set lines 1000;')
                sql_conn.stdin.write("\n select 'PYTHONSTRINGSEPSTARTSHERE' from dual;")
                sql_conn.stdin.write("\n "+ora_query+";")
                out,err=sql_conn.communicate('\n exit;')
                if sql_conn.returncode == 0 and not error.search(out):
                        return out.split("PYTHONSTRINGSEPSTARTSHERE ")[-1]    ---  To split the output.
                else:
                        return None

The connection to DB made using SQLPLUS through subprocess library and perform a primary set operation to make my SQPLUS command line output be clear to execute the query that passed through variable ora_query.

The regular expression (re) module precompile with ‘(ORA-)\d+’ to check ORA- followed by expected digits as oracle standard error format. 

Here the subprocess should be enabled with STDIN, STDOUT & STDERR through PIPE to ensure two-way communication to SQLPLUS command. The SQL_CONN is the variable to carry the connection session, so write a method to pass the query and communicate method to retrieve the output or error.

The subprocess has another useful method called return code which ensures the called command passed the output with proper exit status and make my life be confidence.

In the above class, I have totally suppressed the errors and get only the query output but you can try changing this script by adding meaning to your errors.

Hope this article would have enlightened you to work more on your automation and finding the alternatives to continue your journey of coding. This might be simple class but it would definitely either encourage you to write code or write articles.

Leave a Reply