I decided to write a new series of blogposts related to python programming language and its interactions with Oracle.

Python is a powerful scripting language with a lot of packages that can be installed for various stuff (scientific computing, dataviz, machine learning, deep learning etc.).

In my case, I used python for many things and specially for running my sql tuning scripts on an Oracle database, and then present the results with beautiful graphs.

This first blogpost will present how I use python on my laptop and how to install the required package to connect an Oracle Database.

1. Work Environment

I used python on my MS Windows laptop, and I used two versions of python depending on what I have to do or packages I will have to use.

So I installed two distributions of python :

• Python 2.7.15 (64bits) downloaded from Python’s official website  (https://www.python.org/downloads/release/python-2715/)
• Python 3.6.4 bundled with MiniConda installer (https://conda.io/miniconda.html)

You are free to use the version of Python you want to use and/or bundled with the package installer of your choice.

Next, I use a very well known python IDE name PyCharm (https://www.jetbrains.com/pycharm/download). PyCharm is released with 2 versions : a professional one (not free) and a free distribution (named community edition). The community edition is enough for my needs.

In PyCharm, you can run what is called a virtual-environment which is a kind of clone of your python installation where you will be able to deploy the package you want.

That can be assimilated to a “project” because in PyCharm, when you create a new project, you have to configure an interpreter, and pyCharm will automatically create a new virtual environment (default) based on the interpreter you chose :

Once the project has been created, we have to install the package required to connect to an Oracle Database : cx_Oracle.

In your pyCharm project, you have to open the project settings (in the “File” Menu) and then click on the “Project Interpreter” sub menu. The packages installed in your virtual environment appear.

Now, it’s easy to add a package by clicking on the “+” icon and search for cx_oracle package, and click on Install Package to install it. (For compatibility matrix, visit https://oracle.github.io/python-cx_Oracle/)

Don’t forget to install an Oracle Client (Instant client or full client) and configure your windows environment with ORACLE_HOME and PATH environment variables. In the example below, I decided to set the environment variable in the script (because I have many clients installed on my laptop) :

from __future__ import print_function

import cx_Oracle
import os

oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]

print("Running tests for cx_Oracle version", cx_Oracle.version,"built at", cx_Oracle.buildtime)
print("File:", cx_Oracle.__file__)
print("Client Version:", ".".join(str(i) for i in cx_Oracle.clientversion()))

If this piece of code runs fine … you have correctly configured your environnent.

2. Connect to an Oracle database

Once your development environment configured, connecting a python script to an Oracle Database is really easy.

You just have to execute Connection constructor from cx_Oracle and then you will get a connection object. Of course, you have to catch the correct exception if an error occured during Connection call:

from __future__ import print_function

import cx_Oracle
import os

oh="D:/tools/Oracle/instantclient_12_2_x8664"
os.environ["ORACLE_HOME"]=oh
os.environ["PATH"]=oh+os.pathsep+os.environ["PATH"]
os.environ["NLS_LANG"]="AMERICAN_AMERICA.AL32UTF8"

def printConnectionAttr(connection):
    if connection is not None and isinstance(connection,cx_Oracle.Connection):
        print("Data Source Name  = ",connection.dsn)
        a="true" if connection.autocommit==1 else "False"
        print("Autocommit         = ",a)
        print("Session Edition    = ",connection.edition)
        print("Encoding           = ",connection.encoding)
        print("National Encoding  = ",connection.nencoding)
        print("Logical Tx Id      = ",connection.ltxid)
        print("Server version     = ",connection.version)

def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx_Oracle.Connection (user=username, password=password, dsn=url)
    
    return connection


# main
if __name__ == '__main__':
    c=cx_Oracle.Connection
    try:
        c=connectToOracle("192.168.99.2:1521/orcl","sys","oracle",mode=cx_Oracle.SYSDBA)
    except cx_Oracle.DatabaseError as ex:
        err, =ex.args
        print("Error code    = ",err.code)
        print("Error Message = ",err.message)
        os._exit(1)

    printConnectionAttr(c)
    c.close()

 

This piece of code will connect to an Oracle Database and will print the connection details:

Data Source Name  =  192.168.99.2:1521/orcl
Autocommit         =  False
Session Edition    =  None
Encoding           =  UTF-8
National Encoding  =  UTF-8
Logical Tx Id      =  
Server version     =  12.1.0.2.0

Source code is fully available on my gitlab: https://gitlab.com/lolo115/python/tree/master/oracle

Ok, so that’s it for this first part … in a next blog post, I will present how to execute a basic statement with Python and Cx_Oracle