How to Access Amazon RDS for PostgreSQL Database Using Python

Introduction

In this article, you will learn how to access Amazon RDS for the PostgreSQL database using Python.

Pre-requisites

1. Create and connect to a PostgreSQL DB instance

Tools

1. Visual Studio Code

Task 1. Create the project folder

In this task, you will see how to create the project folder.

Step 1. Open Windows Command Prompt and run the following commands to create the new folder for this project.

mkdir ReadAmazonPostgreSQL
cd .\ReadAmazonPostgreSQL

Task 2. Install the required packages

In this task, you will see how to install the required packages for this project.

Step 1. Open the ReadAmazonPostgreSQL folder in Visual Studio Code. Click Terminal -> New Terminal.

Step 2. Run the following command to install the packages required for this project.

pip install psycopg2

Task 3. Implementation

In this task, you will see how to read the PostgreSQL table.

Step 1. In Visual Studio Code, create a new file, app.py, under the ReadAmazonPostgreSQL folder.

Step 2. Copy and paste the below code.

Note. Update the connection string, and all these values can be retrieved from AWS Console.

import psycopg2

def execute_query():
    # connection string
    conn = psycopg2.connect(
        dbname="postgres",
        user="admin",
        password="admin2023",
        host="dev.cpdi37***c7f.us-east-1.rds.amazonaws.com",
        port="5432",
        sslmode="require",
    )
    try:
        sql_query = "SELECT * FROM employees"
        cur = conn.cursor()
        cur.execute(sql_query)
        results = cur.fetchall()
        print(results)
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print("no success", error)
    finally:
        if cur is not None:
            cur.close()

if __name__ == "__main__":
    execute_query()

Task 4. Test the API

In this task, you will see how to test the code.

Step 1. In Visual Studio Code, run the following command in the terminal to view all the records from the employee's table.

python.exe .\app.py 

Output

[(1, 'Vijai', 'Architect', '15'), (2, 'Anand', 'Developer', '5'), (3, 'Arjun', 'Manager', '10')]

Summary

This article describes how to access Amazon RDS for the PostgreSQL database using Python.