Connecting Python and Node.js applications to an Oracle database

In the era of artificial intelligence (AI) and running microservices in containers, Python and Node.js are common choices for developing current generation applications. These applications may need to connect to various data sources for different types of data. For customers who use Oracle Database as their operational database, the cx_Oracle and node-oracledb modules provide an easy method for connecting Phyton or Node.js applications to an Oracle Database running on any platform.

Learning objectives

This tutorial will show you how to connect a Python or Node.js application running on Linux on IBM Power Systems to an Oracle Database running on IBM AIX by using cx_Oracle or node-oracledb, respectively. The availability of these modules on Linux on the Power platform makes it easy for application developers to write the code and integrate it, then enable the IBM Watson Machine Learning Accelerator platform to connect to an Oracle Database as one of the data sources.

Prerequisites

• IBM Power Systems (POWER8 or POWER9)
• IBM AIX
• Oracle Database
• Ubuntu 16.04 LTS

Estimated time

Completing this tutorial should take about 30 minutes.

Overview

Many Python and Node.js applications use deep learning or machine learning frameworks to create trained models. The accuracy of the models heavily depends on a range of vast data from different sources. One of the databases most widely used in enterprise systems is the Oracle Database. An overview of the various components described in this tutorial follows.

• Linux on Power Systems provides an open and scalable infrastructure that is built to process a massive amount of data quickly, efficiently, and cost-effectively to formulate real-time actionable business insight. The Linux ecosystem, alongside legacy IBM AIX workloads, provide a stronger value to the business that is looking to modernize its operation using artificial intelligence, machine learning, and deep learning frameworks.

• Python is widely used in the industry in numerous fields such as scientific packages, mathematical libraries, and web applications. JavaScript is one of the vital components of the web, along with HTML and CSS. Because it is lightweight and has attributes for ease of use, JavaScript has been widely implemented in all the websites. Most web browsers have a dedicated engine to process JavaScript.

• Node.js is a server platform built on JavaScript and contains numerous JavaScript modules with features such as non-blocking I/O, which makes Node.js one of the most widely used platforms to develop scalable web and network applications.

• IBM PowerAI is a software distribution that includes deep learning frameworks and building block software designed to run on the IBM Power platform. PowerAI makes deep learning, machine learning, and AI more accessible and more performant.

• cx_Oracle is a Python extension module that can be used to enable access to Oracle Database. The recent version of cx_Oracle is 6 and has been tested to work with Python versions 2.7 and 3.4. cx_Oracle can be used with Oracle Database 11.2, 12.1, and 12.2 libraries. cx_Oracle is an open source project licensed under BSD terms and is maintained by Oracle. The GitHub link for cx_Oracle is: https://github.com/oracle/python-cx_Oracle.

• node-oracledb is an add-on for Node.js that allows high performance Oracle Database applications which are developed using Node.js and Python. Node.js is an open source, cross-platform runtime environment for writing mid-tier and networking applications in JavaScript. The node-oracledb driver connects to an Oracle Database for fast and functional applications. It is an open source project with Apache 2.0 license. It is maintained by Oracle and is under active development. The GitHub link for node-oracledb https://github.com/oracle/node-oracledb.

Figure 1, shown below, depicts an IBM Power server with Oracle Database running AIX connecting to the PowerAI frameworks, web applications, UI frameworks, mathematical, scientific and numerical libraries, and applications on an IBM POWER9™ processor-based server (such as the IBM Power System AC922) running Linux. cx_Oracle and node-oracledb, along with Oracle instant client, provide built-in functions to connect the Oracle Database to applications built using Python and Node.js respectively. For this tutorial, these applications are running on Linux on the Power platform.

Installing and configuring cx_Oracle

This section provides the steps to install and configure cx_Oracle for Python applications running on Linux on Power Systems to access the Oracle Database.

Perform the following steps to download and configure python-pip and cx_oracle on UBUNTU 16_04 LTS ppc64le.

1. Install python pip. pip is a package management system used to install and manage software packages written in Python.

   $ apt-get install python-pip

2. Using the python pip install and upgrade cx_oracle package.

   $ python -m pip install cx_oracle –upgrade

3. Download the Oracle instant client available from the Oracle technology network at http://www.oracle.com/technetwork/topics/linux-power-le-2835260.html

4. Create a directory and extract the instant client in the directory using the following commands.

   $ mkdir -p /opt/oracle

   $ unzip instantclient-basic-linux.leppc64.c64-12.2.0.1.0.zip

5. Create the necessary links and add the files to the library path using the following commands.

   $ sudo sh -c "echo /opt/oracle/instantclient_12_2 > /etc/ld.so.conf.d/oracle-instantclient.conf"

   $ sudo ldconfig

   $ export LD_LIBRARY_PATH=/opt/oracle/12.2/client64/lib:$LD_LIBRARY_PATH

6. Refer to the following URL to set up a schema for cx_oracle, examples demonstrate connectivity to the Oracle Database: https://oracle.github.io/python-cx_Oracle/

7. Navigate to the sql folder section under the sample section. Then, download and run the following SQL files:

   • DropSamples.sql
   • SampleEnv.sql
   • SetupSamples.sql

8. Download any sample copy (for example sampleEnv.py) to the folder where the SQL files are present, modify the parameters in the sampleEnv.py file as per the Oracle instance details and run the example file.

   $ python Query.py
   Get all rows via iterator
   (1, 'Anthony')
   (2, 'Barbie')
   (3, 'Chris')
   (4, 'Dazza')
   (5, 'Erin')
   Query one row at a time
   (1, 'Anthony')
   (2, 'Barbie')
   Fetch many rows
   [(1, 'Anthony'), (2, 'Barbie'), (3, 'Chris')]

Important connection parameters

The connect function in the cx_Oracle package used to describe connection parameters is shown below.

connection = cx_Oracle.connect(‘user_name/password@IP_address: port/servicename’)

Where:

• user_name is the database user name
• password is the database user password
• IP_address is the host IP address
• port is the port at which the Oracle database is listening
• servicename depicts the name of the Oracle database service

Steps 1 to 8 described in the Installing and configuring cx_Oracle section can be followed with the yum install command in place of apt-get install to download and configure the dependent packages on Red Hat Enterprise Linux ppcle64.

Linux kernel asynchronous I/O access library (libaio1) is a prerequisite package for cx_Oracle to work. If not available, you need to install it using the following command:

$ apt-get install libaio1

Installing and configuring node-oracledb

Perform the following steps to configure and use node-oracledb on Ubuntu 16.04 LTS ppc64le:

1. Download the Oracle instant client available from the Oracle technology network at: http://www.oracle.com/technetwork/topics/linux-power-le-2835260.html

2. Create a directory and extract the instant client in the directory using the following commands:

   $ mkdir -p /opt/oracle

   $ unzip instantclient-basic-linux.leppc64.c64-12.2.0.1.0.zip

3. Create the necessary links and add the files to the library path using the following commands:

   $ sudo sh -c "echo /opt/oracle/instantclient_12_2 > /etc/ld.so.conf.d/oracle-instantclient.conf"

   $ sudo ldconfig

   $ export LD_LIBRARY_PATH=/opt/oracle/12.2/client64/lib:$LD_LIBRARY_PATH

4. Install the build essentials and SSL development libraries using the following commands:

   $ sudo apt-get install build-essential

   $ sudo apt-get install libssl-dev

5. You need to use the node version manager command (nvm) to download and install node.js. Run the following command to download nvm:

   $ curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.33.0/install.sh | bash

6. Close the current terminal and then open a new terminal.

7. Run the -v nvm command. The output displays the current nvm version, and nvm fetches the required node packages.

   $ command -v nvm

8. Download and install Long term support version of node.

   $ nvm install 8.9.4

9. The node version manager command will install node v8.9.4

   $ npm install oracle/node-oracledb#v2.1.2
   make: Leaving directory '/root/cx_examples/node_modules/oracledb/build'
   npm WARN saveError ENOENT: no such file or directory, open '/root/cx_examples/package.json'
   npm notice created a lockfile as package-lock.json. You should commit this file.
   npm WARN enoent ENOENT: no such file or directory, open '/root/cx_examples/package.json'
   npm WARN cx_examples No description
   npm WARN cx_examples No repository field.
   npm WARN cx_examples No README data
   npm WARN cx_examples No license field.
   + oracledb@2.1.2
   added 2 packages in 62.078s

1. Download the examples from: https://github.com/oracle/node-oracledb/tree/master/examples

2. Download the demo.sql file.

3. Run demo.sql on Oracle Database to set up the basic schema.

4. Set up the connection string in the dbconfig.js file.

5. Run one of the example files (in this case, date.js) in the downloaded directory.

   $ npm install async –save
   $ node date.js
   Inserting JavaScript date: Mon Jun 04 2018 17:48:07 GMT+0530 (IST)
   Rows inserted: 1
   Query Results:
   [ [ 2018-06-04T12:18:07.077Z, 2018-06-04T12:18:07.000Z ] ]
   Result Manipulation in JavaScript:
   2018-06-09T12:18:07.077Z
   2018-05-30T12:18:07.000Z
   Altering session time zone
   Query Results:
   [ [ 2018-06-04T17:48:07.077Z, 2018-06-04T17:48:07.000Z ] ]
   Result Manipulation in JavaScript:
   2018-06-09T17:48:07.077Z
   2018-05-30T17:48:07.000Z

Summary

The cx_Oracle module provides a wide range of in-built functionalities which are available out of the box and work with Oracle Database instant clients. Because most of the modern applications make use of Python, which is adopted due to its support for large third-party modules, readability and user-friendly data structures it is very important for these applications to interact with enterprise databases such as the Oracle Database.

Node.js is an event-driven, lightweight, extremely efficient, and non-blocking I/O package. With npm package manager (having a large open source ecosystem), Node.js along with Linux on Power Systems becomes the primary choice for Power AI, Internet of Things (IoT), machine learning, and applications in scientific and deep learning frameworks because of its enhanced performance, virtualization, and resilience.

Because cx_Oracle and node-oracledb are both widely used with open standards, the steps described in this paper can be used with all the applications using python and Node.js applications on Linux on Power Systems connecting to the Oracle Database.