How to connect with Oracle Database using cx_Oracle connector from TIBCO Team Studio Python Notebooks

How to connect with Oracle Database using cx_Oracle connector from TIBCO Team Studio Python Notebooks

book

Article ID: KB0070390

calendar_today

Updated On:

Products Versions
Spotfire Data Science Team Studio 7.0.0 and Higher

Description

This article has information on how we can set up a connection to an Oracle database from the Python Jupyter Notebook in TeamStudio.
This information and process is specific to V7.0.0 and higher as it involves Docker containers thus the process to establish the Oracle  connection differs.

Issue/Introduction

This article has a information on how we can set up a connection to an Oracle database using a Python Notebook.

Environment

Linux : CenOS 7

Resolution

Pre-requisites: You need an Oracle client installed on the Python Notebook Server/Container to connect to the Oracle Database using cx_oracle connector. 

Steps to install Oracle Client  and configuration:  

1. Download an Oracle 21,19,12, or 11.2 "Basic" or "Basic Light" RPM compatible with your Oracle database from the below website and copy it into the Python Notebook server.

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

Example: Basic Package (OL7 RPM) - oracle-instantclient-basic-21.11.0.0.0-1.x86_64.rpm
 
2. Log into the notebook container and move the client library under the location /data/shared/tds-notebooks which is the location of the persistent volume and Install the downloaded Oracle RPM package. 
       

yum install oracle-instantclient-basic-21.11.0.0.0-1.x86_64.rpm


3. It is required that environment variables like ORACLE_HOME, and LD_LIBRARY_PATH are configured. You can pass these environment variables to the notebooks container. To make these environment variables persistent we can tweak the docker-compose.yml file. Also, In the installers/current/compose/fragments shell script, under the tds-notebooks section, we can add these environment variables

4. The default location of the Oracle client library is /usr/lib please change it the /data/shared/tds-notebooks so that we don't lose it after the notebook restart. 


cx_Oracle Installation: 

1. Any additional package can be installed into the tds-notebooks image while building it. To do so, in the tds platform configure step we can add the packageconda:cx_oracle.
 

2. Stop the existing application, Drop the existing tds-notebook image, and rebuild it using $ tds platform build step. 

3. Once the notebook is built with a new image you can just import the package in all the notebooks. 

This is a standard procedure to add Python packages that will be available in the notebook environment.
 

Note: As it is not convenient to incorporate the username and password everywhere in Python scripts to establish the connection to the Oracle Database, we can save the text file to read a password from it by placing the file under /data/shared/tds-notebooks directory. 

Additional Information

For an older version of TeamStudio refer article: https://support.tibco.com/s/article/000047996