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
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
# 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()
Thank you! I will try it next week
I did a search on Google search engine and below is what I found.
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.
Thanks for the information. I have listed your resources at
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
pandas is not installed, then do
conda install pandas.
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)
Glad to hear that you are learning.
Just a future remainder. Typing code > pictures.
- Someone in the community can try out the code by copy and paste.
- Because of 1, a faster reply.
Also, I believe there’s a limit on the number of photos you can upload to this forum.
This is my first question on DQ community. It’s really helpful ! Thank you again.
No worries. Looking forward to your next question.
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
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.
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 ~~