Python27 with Oracle Sample(Windows OS)

Install oracle client

• Download url: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
• Extract the zip file, and configure the file path to system environment
• Restart the computer

Install python oracle driver

• Name: cx_Oracle
• Download url: https://pypi.python.org/pypi/cx_Oracle/5.2.1
• Download version: Based on which oracle version you use

Install the package

Double click the package and click Run to continue to finish installation.

Test if installed successfully

Open windows command line and type below command, if there is no error then it is success.

py
>>> import cx_Oracle

Code with example

import cx_Oracle

def get_connection(url):
    try:
        connection = cx_Oracle.connect(url)
        return connection
    except cx_Oracle.Error as err:
        print err
    return None

def query_demo1(connection):
    cursor = connection.cursor()
    cursor.execute("select * from test_table")
    for result in cursor:
        print result
    cursor.close()
    connection.close()
    return

def query_demo2(connection):
    cursor = connection.cursor()
    cursor.execute("select * from test_table")
    row = cursor.fetchone()
    print row
    row = cursor.fetchone()
    print row
    cursor.close()
    connection.close()
    return

def query_demo3(connection, sql):
    cursor = connection.cursor()
    cursor.execute(sql)
    res = cursor.fetchmany(numRows=3)
    for result in res:
        print result
    cursor.close()
    connection.close()
    return

def query_by_args(connection, sql, args):
    cursor = connection.cursor()
    cursor.prepare(sql)
    cursor.execute(None, args)
    res = cursor.fetchall()
    print res
    cursor.close()
    connection.close()
    return

def query_all(connection, sql):
    cursor = connection.cursor()
    cursor.execute(sql)
    res = cursor.fetchall()
    for result in res:
        print result
    cursor.close()
    connection.close()
    return

def call_func(connection, funcName, returnType, inputParam):
    cursor = connection.cursor()
    #cursor.callfunc('myFunc', cx_Oracle.NUMBER, ('TEST',2))
    res = cursor.callfunc(funcName, returnType, inputParam)
    print res
    cursor.close()
    connection.close()
    return

def call_procedure(connection, procName, in_outParam):
    cursor = connection.cursor()
    #myVar = cursor.var(cx_Oracle.NUMBER)
    #in_outParam = (123, myVar)
    cursor.callproc(procName, in_outParam)
    #print myVar.getValue()
    cursor.close()
    connection.close()
    return

connectionUrl = "<username>/<password>@<url>:<port>/<sid>"
conn = get_connection(url=connectionUrl)
if conn != None :   
    query_demo3(connection=conn,sql="select * from test_table")
    conn = get_connection(url=connectionUrl)
    query_by_args(connection=conn,sql="select * from test_table where id = :id", args={'id':100010027300474})
else: 
    print "Connection is none"

References

http://stackoverflow.com/questions/12538238/python-module-cx-oracle-module-could-not-be-found
http://www.oracle.com/technetwork/articles/dsl/python-091105.html