How to connect Jupyter Notebook to Oracle Database

[danhthenguyen_nt]

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 !

[alvinctk]

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() 

[danhthenguyen_nt]

Thank you! I will try it next week

[danhthenguyen_nt]

I failed to install cx_oracle

[alvinctk]

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

[danhthenguyen_nt]

fetch() :

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.

[alvinctk]

Thanks for the information. I have listed your resources at

Additional Resources Wiki

References that users use to resolve their issue:

pip install package timeout

-install a package in anaconda connection timeout

[danhthenguyen_nt]

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

[alvinctk]

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.

Discourse post - markdown formatting a block of code to improve readability

By formatting a block to show indentation helps in better code readability, and easier to read and faster response from the community if you ever post a question here.

Using triple back ticks to format a block of code

Transform the text from x = 67 for i in range(100): if i < x: print(i) else: print(x+i) by using ``` triple back ticks to format a block of code to show indent ``` x = 67 for i in range(100): if i < x: print(i) else: print(x+i) ``` , and transform the code int…

[alvinctk]

How can I create a table that is similar to csv files that I am learning on Dataquest

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 

[alvinctk]

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

[danhthenguyen_nt]

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

[alvinctk]

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)

[danhthenguyen_nt]

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

[alvinctk]

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.

[danhthenguyen_nt]

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

[alvinctk]

No worries. Looking forward to your next question.

[danhthenguyen_nt]

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 ?

[alvinctk]

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.

[danhthenguyen_nt]

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 ~~