top of page
    • Nov 22, 2021
    • 2 min read

AWS RDS PostgreSQL in Python - Part 2



Hi All! Hope you all had a great weekend! This week, I am will be going over the concept of how to import our panda's data frame into a cloud database. Reason? Absolutely!! its to keep track of the real-time data. We would like to have an inflow of real-time data for this project.


Remember I used the Youtube API for getting statistics on one of my favorite channels? Yes, you can refer to that tutorial here!! I am importing the data frame generated to a cloud database. For this project, I am using AWS RDS (Relational Database Services).


Setting up a Database in AWS RDS:

I created an account with AWS RDS and created a PostgreSQL database micro instance of the name database-1(default). Below is the overview on how to set up. You can find the detailed setting up tutorial here.

  • Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  • In the top right corner, select your region, and in the navigation pane select Database and click Create Database.

  • Select Standard Create in Choose a database creation method.

  • From the Engine options, choose any of your preferred engine types: MariaDB, Microsoft SQL Server, MySQL, Oracle, or PostgreSQL. I choose PostgreSQL here.

  • For the rest of the options, I just selected all the default options.

  • In the password region, enter your password for Master Database.

  • Choose create database.

The below image shows the database instance created:


Note: It may take a while to create the DB instance so please keep patience.


Setting up PostgreSQL python library:

The python library for PostgreSQL is psycopg2 and we can install the library using pip install method as shown in the code below:

!pip install psycopg2
import psycopg2 as ps

Making a connection with Database using Python:

Now we need to a have few variables set up to connect to the DB such as:

  • hostname which is nothing but the endpoint

  • port

  • dbname

  • username

  • master password.

All of this can be accessed from Connectivity & Security.

Below are the fixes I made for successful DB connection through python:

  • When creating database, make sure your dbinstance have Public accessible 'yes' under Additional configuration.

    • If in case you have created with Public access 'No', you can modify the DB and change to Publically accessible under Additional Configuration.

  • Setting up a rule for Inbound Security group. Click 'Create Security Group', select Action and click 'edit inbound rules' and navigate to Inbound rules and click add rules. Select the below variables:

    • All traffic type

    • My IP Source

    • Your local IP address will be populated

  • Click Save rules

Below code is to connect to the database using python: 


host_name='xxxx.rds.amazonaws.com'
dbname='xxxx'
port = '5432'
username = 'xxxx'
password = 'xxxx'
conn = None
conn = ps.connect(host=host_name, database=dbname, user=username, password=password, port=port)
curr = conn.cursor()

Once it is run successfuly the connection is made!!

This marks the end of the tutorial. In next tutorial, we can see how to insert data to the database and more in coming weeks!!


If you like this post, please like, share and subscribe! See you next week!




Comments

bottom of page