Connecting Jupyter Notebook to Oracle Autonomous Database

Jupyter Notebook is an open source web application for Machine Learning and Data Exploration.

In this post I will show you how to connect a Jupyter Notebook to Oracle Autonomous Database and explore the data using Python.

The assumption is we already have a Jupyter notebook sandbox running on Oracle Cloud compute instance.

Prerequisites:

Connect to your OCI compute instance via SSH.

Start your Jupyter notebook environment and create a new notebook.

$jupyter notebook

or if running as root

#jupyter notebook --allow-root

Access your notebook using the browser > http://<your public IP>:8888

Install the required Python libraries.

# Install required libraries
!pip install sqlalchemy pandas numpy cx_oracle matplotlib

Import the libraries

import sqlalchemy as db
import pandas as pd
import numpy as np
import os
import matplotlib.pyplot as plt

Create a new connection with Oracle Autonomous Database:

os.environ['TNS_ADMIN']='<path to your Oracle db wallet on OCI instance>'

engine = db.create_engine('oracle://<user>:<password>@<service type>')

ie.

os.environ['TNS_ADMIN'] = '/opt/oracle/instantclient_19_3/network/admin/'

engine = db.create_engine('oracle://ADMIN:Welcome!@smatATP_TP')

Import this data set to your Oracle Autonomous Database using the Jupyter notebook:

Since you are using OCI compute, you will have to upload the dataset on your compute instance. Then using the Jupyter notebook, load the data to your Database.

i.e

df = pd.read_csv('/home/opc/applications/Data/iris.csv')
 df.to_sql('iris', engine, index=False, if_exists='replace')

Retrieve data from the table you just created.

df = pd.read_sql("select * from iris", con=engine)

df.head(10)

 

 

total = pd.read_sql("select count(*) from iris", con=engine)
print(total)

 

Use Matplotlib to visualize the data.

df.groupby('species')
plt.scatter(df.sepal_length, df.sepal_width)

 

Happy Coding!

References:

Build a machine learning sandbox on Oracle Cloud

Connect to Oracle ATP through Jupyter Notebook

Python, Oracle ADWC and Machine Learning

Advertisement

Author: Labanish

I am in the field of IT, currently working as a cloud architect. I have experience living and working abroad, spent some years in Romania, then relocated to the UK for work. In my spare time, you will find me biking or hiking, exploring new cities or watching my favorite sport, F1. The views expressed here are mine and do not associate to any organization. Laban.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: