How to connect Jupyter Notebook to Oracle Database

Hi all,
I am learning Python Path for Data Analysis, I have installed Jupiter Notebook on my desktop at work and do some analysis by csv files. Now, I want to connect Jupiter Notebook directly to my company databases where I store my Oracle server 's tables. I have the name of server, users and password already. I have searched some tips on Internet but it is quite difficult to me for step by step configuration. Please help me to do it !

You can try the example given by geeks for geeks - oracle database connection:

For installing cx_Oracle :

pip install cx_Oracle

Import database specific module

import cx_Oracle

How to use this module for connection

  • connect(): Now Establish a connection between Python program and Oracle database by using connect() function.
con = cx_Oracle.connect('username/[email protected]')
  • cursor(): To execute sql query and to provide result some special object required is nothing but cursor() object
cursor = cx_Oracle.cursor()
  • execute method :
    cursor.execute(sqlquery) – – – -> to execute single query.
    cursor.execute(sqlqueries) – – – -> to execute a group of multiple sqlquery seperated by “;”

  • commit(): For DML(Data Manuplate Language) query in this query you have (update, insert, delete) operation we need to commit() then only the result reflecte in database.

  • fetch(): This retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available.

  • close(): After all done mendentory to close all operation

cursor.close()
con.close()

Example:

# importing module 
import cx_Oracle 


# Create a table in Oracle database 
try: 

	con = cx_Oracle.connect('scott/[email protected]') 
	
	# Now execute the sqlquery 
	cursor = con.cursor() 
	
	# Creating a table srollno heading which is number 
	cursor.execute("create table student(srollno number, \ 
					name varchar2(10), efees number(10, 2)") 
					
	print("Table Created successful") 
	
except cx_Oracle.DatabaseError as e: 
	print("There is a problem with Oracle", e) 

# by writing finally if any error occurs 
# then also we can close the all database operation 
finally: 
	if cursor: 
		cursor.close() 
	if con: 
		con.close() 



1 Like

Thank you! I will try it next week

1 Like

I failed to install cx_oracle

I did a search on Google search engine and below is what I found.

According to pip install falling with 407 proxy authentication required,

This is the first thing you should try:

Open the command prompt(CMD).

Export the proxy settings :

:\set http_proxy=http://username:[email protected]:port

:\set https_proxy=https://username:[email protected]:port

Install the package you want to install:

:\pip install PackageName

I access successfully to my company DB thanks to this link: https://datatofish.com/install-a-package-in-anaconda-connection-timeout/
I hope that you can reference it to other students. Thank you for your support.

1 Like

Thanks for the information. I have listed your resources at

1 Like

Hi, I need more support. I fetch the data from my tables on Oracle server but it returns a string lists, How can I create a table that is similar to csv files that I am learning on Dataquest ? I have not completed the courses and is learning on job. I want to practice what I learn on DQ daily on my current job

Can you type out your code?

Please use triple back ticks ``` to format a code block. See the post below on how to format code block using triple back ticks.

You need to use Pandas.

import pandas as pd
# list of strings 
mylist = ['a', 'b', 'c', 'd', 'e', 'f', 'g'] 
  
# Calling DataFrame constructor on list 
df = pd.DataFrame(mylist) 
df 

If pandas is not installed, then do conda install pandas.

1 Like


It shows on Jupiter and below is what format I want, it have to a structured table like SQL results

Try using pd.read_sql.

import pandas as pd
import cx_Oracle

ip = 'XX.XX.X.XXX'
port = YYYY
SID = 'DW'

dsn_tns = cx_Oracle.makedsn(ip, port, SID)
connection = cx_Oracle.connect('BA', 'PASSWORD', dsn_tns)

query = """SELECT * FROM AATD_PERFORMANCE_BY_BUCKET_FC"""

df = pd.read_sql(query, con=connection)
1 Like


Finally, Now I dont have to export to csv files for Python ! Thank you and I will keep learning !

1 Like

Glad to hear that you are learning.

Just a future remainder. Typing code > pictures.

  1. Someone in the community can try out the code by copy and paste.
  2. Because of 1, a faster reply.

Also, I believe there’s a limit on the number of photos you can upload to this forum.

1 Like

This is my first question on DQ community. It’s really helpful ! Thank you again.

1 Like

No worries. Looking forward to your next question.

1 Like

Hi, I do some analysis on Jupyter Notebook, when I open the file .ipynb and run Cell -> Run All, if it extracts data from csv files , it will restart all my coding and return outputs quickly, but if I connect directly to Oracle server’s tables, It take a lot of time but no outputs after that. I have to press run for each coding frame (input) and wait for outputs simultaneous. ? Could you instruct me to do the job more smoothly daily ?

Write a script .py file instead of using jupyter notebook .ipynb.

Once you come up with the order of execution of codes, you can put them into a .py and execute the script once.

Or you put them into a function.

1 Like

It’s not clear to my question ~~ I means that I would like to run all cells when I open the workbook , results like charts, numbers will show in output cells. To connection by csv files, it run fast and shows all outputs, but to Oracle server connection, It will be stuck ~~