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

Author: Labanish

Through embracing an agile mindset, and an attitude of learning, unlearning and re-learning, with my teachers being experience, customers and colleagues, I have attained vast knowledge in solution engineering. My technical skill set ranges from; Oracle APEX, Node.js, Machine Learning, Python, Low Code development, Integration, Data Management, Digital Assistants and Analytics. Feel free to reach out to me for more info.

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 )

Google photo

You are commenting using your Google 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