Tuesday, September 3, 2013

Configuring Oracle for Python

Configuring Oracle for Python
Python code for testing ORacle DB COnnectivity

# assuming python and pip are already installed
# installing the instantclient is usually where problems happen
# download the following files from oracle
# oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
# oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm
# oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm
# install the rpms
rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm
rpm -ivh oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm
# the sqlplus package isn't specifically needed, but is usually useful for testing and command line sql connections
# configure oracle env (modify exact path based on version of rpm you download)
vim /etc/profile.d/oracle
#!/bin/bash
LD_LIBRARY_PATH="/usr/lib/oracle/11.2/client64/lib:${LD_LIBRARY_PATH}"
export LD_LIBRARY_PATH
TNS_ADMIN="/etc/oracle"
export TNS_ADMIN
ORACLE_HOME="/usr/lib/oracle/11.2/client64/lib"
export ORACLE_HOME
# copy/create your tnsnames.ora file
touch /etc/oracle/tnsnames.ora
# symlink headers to ORACLE_HOME to avoid "cannot locate Oracle include files" error
mkdir /usr/lib/oracle/11.2/client64/lib/sdk
ln -s /usr/include/oracle/11.2/client64 /usr/lib/oracle/11.2/client64/lib/sdk/include
# done. Install cx_Oracle
pip install cx_Oracle
----------------------------------------------------------------------------------------
import os
import cx_Oracle
import csv
SQL="SELECT * FROM SOME_TABLE"
# Network drive somewhere
filename="S:\Output.csv"
FILE=open(filename,"w");
output=csv.writer(FILE, dialect='excel')
# You can set these in system variables but just in case you didnt
os.putenv('ORACLE_HOME', '/oracle/product/10.2.0/db_1')
os.putenv('LD_LIBRARY_PATH', '/oracle/product/10.2.0/db_1/lib')
connection = cx_Oracle.connect('userid/password@99.999.9.99:PORT/SID')
cursor = connection.cursor()
cursor.execute(SQL)
for row in cursor:
    output.writerow(row)
cursor.close()
connection.close()
FILE.close()

No comments: