Oracle database

Pre-requisites:

• cx_Oracle package - See here for all versions
• Oracle instant client - For Windows x64, Linux x64

Setup:

• Install the cx_Oracle package as:

sudo rpm -i <YOUR_PACKAGE_FILENAME>

• Extract the Oracle instant client and set environment variables as:

ORACLE_HOME=<PATH_TO_INSTANTCLIENT>
PATH=$ORACLE_HOME:$PATH
LD_LIBRARY_PATH=<PATH_TO_INSTANTCLIENT>:$LD_LIBRARY_PATH

Creating a connection:

import cx_Oracle

class OraExec(object):
    _db_connection = None
    _db_cur = None

    def __init__(self):
        self._db_connection = 
            cx_Oracle.connect('<USERNAME>/<PASSWORD>@<HOSTNAME>:<PORT>/<SERVICE_NAME>')
        self._db_cur = self._db_connection.cursor()

Get database version:

ver = con.version.split(".")
print ver

Sample Output: [‘12’, ‘1’, ‘0’, ‘2’, ‘0’]

Execute query: SELECT

_db_cur.execute("select * from employees order by emp_id")
for result in _db_cur:
    print result

Output will be in Python tuples:

(10, ‘SYSADMIN’, ‘IT-INFRA’, 7)

(23, ‘HR ASSOCIATE’, ‘HUMAN RESOURCES’, 6)

Execute query: INSERT

_db_cur.execute("insert into employees(emp_id, title, dept, grade) 
                values (31, 'MTS', 'ENGINEERING', 7)
_db_connection.commit()

When you perform insert/update/delete operations in an Oracle Database, the changes are only available within your session until commit is issued. When the updated data is committed to the database, it is then available to other users and sessions.

Execute query: INSERT using Bind variables

Reference

Bind variables enable you to re-execute statements with new values, without the overhead of re-parsing the statement. Bind variables improve code re-usability, and can reduce the risk of SQL Injection attacks.

rows = [ (1, "First" ),
     (2, "Second" ),
     (3, "Third" ) ]
_db_cur.bindarraysize = 3
_db_cur.setinputsizes(int, 10)
_db_cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)
_db_connection.commit()

Close connection:

_db_connection.close()

The close() method closes the connection. Any connections not explicitly closed will be automatically released when the script ends.