Category: Python

Access Oracle from Python

If you are a data engineer you might have to pull data from various sources and relational databases (RDBMs) is one of them. Python has separate modules to connect to each of relational databases (Like Oracle, MySQL, PostgresSQL etc.). Today we will discuss how we can connect and query Oracle database from Python.

I hope you have working Python environment set up already. Here I’m using Python CLI with Python 2.7 for this tutorial.

1. Download and install oracle 12c database.

I have downloaded Oracle 12C database from OTN (otn.oracle.com) and installed in my CentOS 7 laptop. Since it is installed in my laptop, server name is localhost and the database name is pdborcl.localdomain

2. We need to install an python module called cx_Oracle which can help us to connect and extract data from the Oracle instance. It gave me couple of errors when I first tried to install on my machine.

[oracle@ora-c7 ~]$ sudo pip install cx_Oracle
 Collecting cx_Oracle
 Using cached cx_Oracle-5.2.1.tar.gz
 Complete output from command python setup.py egg_info:
 Traceback (most recent call last):
 File "", line 1, in 
 File "/tmp/pip-build-kkqi2h/cx-Oracle/setup.py", line 170, in 
 raise DistutilsSetupError("cannot locate an Oracle software " \
 distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

----------------------------------------
 Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-kkqi2h/cx-Oracle/

This is because some packages are missing and we need to install them before installing cx_Oracle. Lets install setuptools and ez-setup.

[root@ora-c7 oracle]# pip install --upgrade setuptools
 Collecting setuptools
 Downloading setuptools-28.7.1-py2.py3-none-any.whl (472kB)
 100% |████████████████████████████████| 481kB 857kB/s
 Installing collected packages: setuptools
 Found existing installation: setuptools 20.10.1
 Uninstalling setuptools-20.10.1:
 Successfully uninstalled setuptools-20.10.1
 Successfully installed setuptools-28.7.1

[root@ora-c7 oracle]# pip install ez_setup
 Collecting ez_setup
 Downloading ez_setup-0.9.tar.gz
 Installing collected packages: ez-setup
 Running setup.py install for ez-setup ... done
 Successfully installed ez-setup-0.9

Now lets try to reinstall cx_Oracle.

[root@ora-c7 oracle]# pip install cx_Oracle
 Collecting cx_Oracle
 Using cached cx_Oracle-5.2.1.tar.gz
 Installing collected packages: cx-Oracle
 Running setup.py install for cx-Oracle ... done
 Successfully installed cx-Oracle-5.2.1

Lets try to run a sample program which connects and pull some data. We will connect and get the oracle database version and query a table. This is a simple program we can do much complex processing with Cx_Oracle, I will list them in another blog.

Oracle version I have is 12C and the table has 100 records in it.

[oracle@ora-c7 ~]$ python
 Python 2.7.5 (default, Sep 15 2016, 22:37:39)
 [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux2
 Type "help", "copyright", "credits" or "license" for more information.
 >>> import cx_Oracle
 >>> from pprint import pprint
 >>> db=cx_Oracle.connect('******','*****','localhost:1521/pdborcl.localdomain')
 >>> print db.version
 12.1.0.2.0
 >>> cursor = db.cursor()
 >>> cursor.execute('select count(*) from my_tab');
 >>> pprint(cursor.fetchall())
 [(100,)]
Advertisements

Running system commands from python

I’m sure there are multiple ways of doing it, but the easiest way is to use the system function from os module.

import os
>>> os.system (‘ls -lrt’)
total 120
drwxrw-r–   2 ****** impvs       4096 Mar 19 2015   wallet
drwxrw-r–   3 ******impvs        4096 Mar 19 2015   oradiag_*******
-rwxrw-r–    1 ******impvs        6364 Mar 27 2015    reset_wallet.ksh
-rwxrwxrwx 1 *******impvs      484 Oct 21 2015        sqlnet.ora
-rwxrwxrwx 1 ****** impvs       58923 Apr 12 15:02  tnsnames.ora
drwxrw-r–   2 ******impvs       4096 Jun 23 16:08   python
-rw-r–r–      1 ******infdevlpr 836 Jun 30 15:00      cdc_test_log.lst
0

Zero in the last line is the error code of the command, zero means successful execution and any other value indicates an error.

 

Finding Python & Modules version

This is how you can find python and installed modules version.

#import the sys module which contains the version function.

>>> import sys
>>> print ‘Installed Python version is :’, sys.version

Installed Python version is : 2.4.3 (#1, Oct 23 2012, 22:02:41)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-54)]

For other modules we can use the __version__ function. To find the version of csv module,

>>> import csv
>>> print ‘CSV module version is’, csv.__version__

1.0

This method may not work for all modules, but in my experience it works for most of the modules.