Oracle is generous to give everyone a full cloud database for free. I am frequently using this database because Oracle SQL is really a powerful tool for data analysis. Currently, I create many CSV files, from webscraping, financial data from APIs, etc.. So, a connection between Python and Oracle database is an absolute requirement for me. Thanks to cx_Oracle library, it is super easy to connect Oracle database in Python.
In just 6 lines of code, you can connect to Oracle database from Python, send an SQL query from your Python code and write the query results to a CSV file. Can you believe it? In this post, I am going to show you how to connect Oracle database in Python. I am doing this on a Linux Server, but as long as you have an Oracle client installed, you can use the same code on Mac or Windows, too.
In order to create an Oracle database connection in Python, first you must have an Oracle Client installed on your machine. If you don’t have one, don’t worry. IN THIS POST , I am showing how to install Oracle Instant Client on a Linux machine step by step.
import cx_Oracle import pandas as pd con = cx_Oracle.connect("MY_USERNAME, "MY_PASSWORD", "MY_DATABASE_SID") query="""select * from MY_TABLE""" df_ora = pd.read_sql(query, con=con) df_ora.to_csv(r"/home/ubuntu/oracle/my_query_results.csv", sep='|',encoding='utf-8-sig')
This is all. Just 6 lines of code, and you connect to Oracle database, perform a query, get your query results to a pandas dataframe and lastly you can save it to a CSV file.
Let me explain main steps:
1. Install cx_Oracle:
This is easy. Just use pip:
python3 -m pip install cx_Oracle --upgrade #and you probably have pandas, but in case: python3 -m pip install pandas
2. Set the connection parameters:
con = cx_Oracle.connect("MY_USERNAME, "MY_PASSWORD", "MY_DATABASE_SID")
Your username and password are the database username and password. SID is system identifier. Oracle SID is the unique name that uniquely identifies your instance/database. You can find it in tnsnames.ora file that you have downloaded in the wallet file, as I show in this article, on step 5. SID name is at the beginning of tnsnames.ora:
SID = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myHostname)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = mySID.whatever.com) (SID = mySID) (SERVER = DEDICATED) ) )
3. Write your SQL Query:
query="""select * from MY_TABLE"""
You can write any query here.
4. Store the query results to a pandas dataframe:
df_ora = pd.read_sql(query, con=con)
5. Write the dataframe to a CSV file:
Lastly, If you want, you can close the connection after you are done with it:
That’s it. In a few lines of code, you can query your Oracle database from Python code.