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 220.127.116.11.0 >>> cursor = db.cursor() >>> cursor.execute('select count(*) from my_tab'); >>> pprint(cursor.fetchall()) [(100,)]