Retrieving data from Amazon Relational Database Service (RDS) — PostgreSQL

The aim of this article is to demonstrate how to connect to PostgreSQL and convert the SQL tables into DataFrames in Python.

I have encountered a few data scientist interview tests that require me to connect to AWS relational database in order to retrieve data as the starting point of the tests, so I felt I should share my experience on Medium.com. Without further ado, let’s jump into it!

STEP 1: The prerequisite packages are Psycopg 2 and Pandas, and you can install these two Python packages by pip install:

pip install psycopg2
pip install pandas

STEP 2: After installing the required packages, we can import these two packages:

import psycopg2
import pandas as pd

STEP 3: Now we are ready to create an engine to connect with PostgreSQL database in AWS:

engine = psycopg2.connect(
                     host = "xxxx.rds.amazonaws.com",
                     database = "NAME_OF_DATABASE",
                     user = "guest",
                     password = "PASSWORD",
                     port = "5432" )

Usually the above 5 parameters will be given by the SQL database owners, and here are my experiences:

• The host is the host address for the database and it should usually look like a URL, if it’s a RDS from AWS it should end with something like “rds.amazonaws.com”.
• The database is the name of the database, and should be given to you by the database owner.
• The user is the user name for accessing the database, which should be created and provided by the database owner.
• The password is the password for accessing the database, which should be also created and provided by the database owner.
• You can always assume the default port is “5432”, unless provided otherwise.

STEP 4: The connection is established in step 3, and now we can retrieve data from AWS:

cur = engine.cursor()
cur.execute("select * from TABLE_NAME")
data= cur.fetchall()
data_df = pd.DataFrame(data)
engine.close()

Here a cursor was created first, then you simply execute a SQL query, such as “select * from TABLE_NAME” to get the desired data from the database. Thirdly, use pd.DataFrame method to convert data into a DataFrame. Finally close the engine, as you don’t want to send unwanted queries to the database by accident.

Viola! I hope you can now use this article to help you to retrieve data from PostgreSQL database from AWS RDS.