Python3 with Oracle Database

Installation

These instructions are for Mac OS:

First install homebrew. Never use sudo for brew.

$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
$ which brew
/usr/local/bin/brew

Install Python3. The installation comes with pip3.

$ brew install python3
$ which pip3
/usr/local/bin/pip3

Install cx_Oracle, the library that allows python3 to communicate with Oracle database.

$ sudo pip3 install cx_Oracle

Next, you have to install an oracle client:

1. Download an instant client from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
2. mkdir -p /opt/oracle
3. sudo mv ~/Downloads/instantclient-basic-macos.x64-12.2.0.1.0.zip /opt/oracle
4. sudo unzip instantclient-basic-macos.x64-12.2.0.1.0.zip
5. mkdir ~/lib
6. ln -s /opt/oracle/instantclient_12_1/libclntsh.dylib.12.1 ~/lib/

Connect to Oracle database

import cx_Oracle
con = cx_Oracle.connect(user='myusername', password='mypassword', dsn='oracleserver:1521/schema')
print(con.version)
con.close()

Populate the values of user, password, and dsn to match your oracle database. This code will simply print the version of oracle database. It’s purpose is to test that the code to connect to the database is working correctly.

Select

import cx_Oracle
con = cx_Oracle.connect(user='myusername', password='mypassword', dsn='oracleserver:1521/schema')
cur = con.cursor()
cur.execute('select id, name from website') 
for rs in cur:
    print(rs)
cur.close()
con.close()

This code will print the selected rows to stdout.