Connecting to an Oracle RDS instance from Python

Setup:

  1. Create an RDS instance on AWS. you can follow the steps here to create one.
  2. Install the python library that connects to an Oracle database.
    pip install cx_Oracle
  3. The cx_Oracle package requires Oracle Instant Client to connect to the database. So download the Oracle Instant Client for windows from here.
    Ref: https://oracle.github.io/odpi/doc/installation.html#windows
  4. Install Oracle Client libraries, make sure it matches your python installation (64bit/32bit).
  5. Unzip Instant Client into a directory.
  6. Add Environment Variable Path pointing to this directory.
  7. Restart Your System for the path variable changes to take affect.

Now that you have all the tools required. Open up python and follow the code below to connect to an RDS and start querying.

#Import Libraries
import cx_Oracle
import pandas as pd
#Connect to Database
#connstr = cx_Oracle.connect("user/password@server/ServiceName")
connstr = "admin/password@connection:1521/orcl"
connection = cx_Oracle.connect(connstr)
cursor = connection.cursor()
#Execute Query
cursor.execute("select * from db")
result = cursor.fetchall()
#Fetch results
for row in result:
print(row)
#Transform Query results into a Pandas Dataframe
df = pd.DataFrame(result)
df.columns=[row[0] for row in cursor.description]
#Commit Changes
connection.commit()
#Close Cursor and Connection
cursor.close()
connection.close()

Documentation of the cx_Oracle library:
https://github.com/oracle/python-cx_Oracle

--

--