[6 Lines Of Code] How To Connect Oracle Database In Python

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.

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:

df_ora.to_csv(r"/home/ubuntu/oracle/my_query_results.csv", sep='|',encoding='utf-8-sig')

Lastly, If you want, you can close the connection after you are done with it:

con.close()

That’s it. In a few lines of code, you can query your Oracle database from Python code.