Python Language Database Access Oracle database
Example
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
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.
