Oracle Database - Python for Oracle (using cx_Oracle)

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)