In this post we’ll explore how to connect to an Oracle Autonomous Database using Jupyter.
I have divided this post into the following 7 parts:
- Pre Requisites before you get started
- Configure your path to the Oracle client libraries
- Create a directory for your Jupyter projects
- Install Jupyter
- Download your Autonomous Database wallet
- Adding the Wallet location to your Notebook
- Making thing more secure
To begin with, these steps have been verified in both Jupyter-lab as well as Jupyter Notebooks so you should be good to go with which ever tool you prefer.
1. Pre Requisites before you get started
Some pre-reqs to setup before going through this process are:
- Setting up PIP and Python3
- download and install oracle instant client
- provision an autonomous database (can be Always-Free: ATP, ADW or AJDB)
NOTE: Let me know if there are any items in the pre-req list that you would like me to cover and I would be happy to do so with another blog entry…
2. Configure your path to use the Oracle client libraries.
Before we get to installing Jupyter, we should make sure we have the Oracle Instant Client installed and configured. This is very straightforward and for the purpose of using Jupyter we can go with the smallest file. In my case I am using the Basic Light Package version 19.8.0.0.0. You can download the software for free from here: https://www.oracle.com/database/technologies/instant-client/downloads.html
Once installed there are no files to modify or change, but you will want to make sure you have set up LD_LIBRARY_PATH to the location of your instant client…
export LD_LIBRARY_PATH=~/instantclient_19_8
echo $LD_LIBRARY_PATH
I like to echo $LD_LIBRARY_PATH to confirm it’s set correctly.
3. Create a directory for your Jupyter projects
Setup a directory to store your jupyter projects and notebooks. This is not mandatory and can be called anything you wish. To keep things simple I created a directory called Jupyter:
mkdir Jupyter
cd Jupyter
mkdir myModules
ls -l
4. Install jupyter
I am on a MacBook running Catalina with pip installed. So I installed jupyter using:
pip install jupyter
but you can just as easily use conda, miniconda or miniforge.
Once installed you can fire up jupyter. But before doing so, it will make things easier to follow my examples by changing your directory to the new directory we created called Jupyter if you have not already done so…
Start using jupyter by entering:
jupyter-lab
–or–
jupyter notebook
5. Log into your Autonomous Database console and download your wallet:
I use jupyter-lab because I like the the multi-panel interface, but I have tested my steps in Jupyter Notebook and this works just fine with that setup too.
If you are unsure how to do this, I have detailed the steps to perform this in my blog-post: https://seanstacey.org/how-to-use-json-soda-python-and-oracle-autonomous-json-database-part-3/2020/10/#PT2
As I mentioned in that post, all connections to the Autonomous Database are encrypted using TLS 1.2 encryption and the wallet is required for authentication. This is provided out-of-the-box with very little effort required to get started.
Oracle provides a zip file with the wallet and additional connection information to connect to your autonomous database. If your not familiar with the Autonomous Database setup, here’s how you download the wallet:
In my set up I downloaded the zip file to the directory that I created and called Jupyter. The next step is to unzip the file contents. Unzipping the wallet will create a directory called the same name as the wallet. In my case it’s called: “Wallet_AJDBSEANS”
We will need to configure the sqlnet.ora file located in the unzipped directory.
The first line in the sqlnet.ora file contains:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
Replace the DIRECTORY entry with the directory location that we unzipped our wallet into. In our case it is:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/sstacey/Jupyter/Wallet_AJDBSEANS")))
Next we’ll need to get a database service name from the tnsnames.ora file.
We need to find a service name to connect to the Autonomous database. You will see that there are several service-names that provide different resource consumption levels that we can choose from. For our setup we are going with the “AJDBSEANS_MEDIUM” service which provides us with a profile suitable for a middle-of-the-road workload, hence the “_MEDIUM” suffix.
If you are unsure what the different service names are for, then you can take a look at the documentation here:
(I have linked the documentation for the services for the ATP database, if you are using an ADW database you will only see HIGH, MEDIUM and LOW.)
6. Adding the Wallet location to your Notebook.
Now that we have setup the connection information we are now ready to get started with using the wallet to connect to the Oracle Autonomous Database. The first thing we’ll need to do is import the cx_Oracle python module that we downloaded and installed. We’ll also need the python os module too. Using those two modules is all we need to get started with connecting to the Autonomous Database with our Wallet.
import os
import cx_Oracle
The os module provides us with a simple way to map a TNS_ADMIN environment variable to point to our Wallet location. The cx_Oracle module provides a connection to the oracle Database and in this case it uses the TNS_ADMIN environment variable to locate it’s network settings.
os.environ['TNS_ADMIN'] = '/Users/sstacey/Jupyter/Wallet_AJDBSEANS'
connection = cx_Oracle.connect("scott", "Temp_password20", "ajdbseans_medium")
With these four lines we are good to go with our setup.
Here’s a sample of running the code.
But wait… the password is displayed???
In the real world, I often find myself demoing code and new database features and the last thing I want to do is share my password with the whole world. So in the next section I’ll show you how you can hide this information.
7. Making things more secure
In my example I am going to obfuscate the password in the same manner as my post: Part 3- How to Use JSON, SODA, Python and Oracle Autonomous JSON Database (https://seanstacey.org/how-to-use-json-soda-python-and-oracle-autonomous-json-database-part-3/2020/10/)
The first step I have taken is to create a subdirectory to store the connection information. This is not necessary, but I find it easier to manage when I separate out my connection information in it’s own sub-directory. But you can just store your connection file in the same directory as your Jupyter Notebooks if you prefer.
NOTE: You should have already created this sub-directory if you followed the steps I covered when you created your Jupyter directory in step 2 above. If you have not created the directory you can create it now…
import os
os.environ['TNS_ADMIN'] = '/Users/sstacey/Jupyter/Wallet_AJDBSEANS'
# Username
usrnm="scott"
# Password
psswd="Temp_password20"
# Data Source Name
dsn= "ajdbseans_medium"
This file can be created using any editor including the Jupyter notebook or text editors. But you will need to rename the file to have a .py extension as this is required for python to import the file as a module.
You may have noticed I have also moved my operation to set the TNS_ADMIN entry into the file as well. Once again, it is not mandatory to do things this way, but I find this makes things more modularized for when I have different data sources with their own wallets and user accounts that I wish to connect to.
Once the file is created and renamed, we can move onto simplifying our Notebook to include the new module.
The quirky piece of the puzzle here is that you need to direct Jupyter to where the file is located. Like all things this is relatively easy to do once you know how:
sys.path.insert(0, os.path.abspath('myModules'))
Once the directory is mapped to our path, we can now import the module we just created …and then our final step is to simplify the connection string to use the parameters we defined in our connection module:
import myConnectInfo
connection = cx_Oracle.connect(myConnectInfo.usrnm, myConnectInfo.psswd, myConnectInfo.dsn)
Voile! You’re good to go with using Jupyter Notebooks with the Oracle Autonomous Database.
Let me know if there is something I may have missed or you would like to see more details on.
Enjoy.