Load AWS-RDS / PostgreSQL tables with AWS-Lambda + Python

Load AWS-RDS / PostgreSQL tables with AWS-Lambda + Python

Introduction

`Although it is an article on Mac environment, the procedure is the same for Windows environment. Please read and try the environment-dependent part. ``

Purpose

After reading this article to the end, you will be able to:

Execution environment

Source code

I think that understanding will deepen if you read while actually following the implementation contents and source code. Please use it by all means.

GitHub

Related articles

• Psycopg -Run web scraping regularly with AWS-Lambda + Python + Cron

Features of AWS-Lambda

This service is a pay-as-you-go system. Please note.

-Features -Price

Overall flow

1. Write Python code
2. Create a Lambda function
3. Set environment variables
4. Set basic settings
5. Set up your VPC

1. Write Python code

coding

app/lambda_function.py

"""app/lambda_function.py
"""

import os
import sys

import psycopg2


class Database():
    """Database
    """
    class Parameter():
        """Parameter
        """

        def __init__(self, host, port, dbname, table, user, password, query):
            self.host = host
            self.port = port
            self.dbname = dbname
            self.table = table
            self.user = user
            self.password = password
            self.query = query

    def __init__(self, param):
        self.db = param
        self.header = tuple()
        self.records = list()
        self.counts = int()

    def _connection(self):
        """_connection
        """
        print('connect to db: {}/{}'.format(self.db.host, self.db.dbname))
        return psycopg2.connect(
            host=self.db.host,
            port=self.db.port,
            dbname=self.db.dbname,
            user=self.db.user,
            password=self.db.password
        )

    def query(self):
        """query
        """
        with self._connection() as conn:
            with conn.cursor() as cursor:
                try:
                    cursor.execute(self.db.query)
                    self.header = cursor.description
                    self.records = cursor.fetchall()
                    self.counts = len(self.records)
                except psycopg2.Error as e:
                    print(e)
                    sys.exit()
        return True


def lambda_handler(event, context):
    """lambda_handler
    """
    print('event: {}'.format(event))
    print('context: {}'.format(context))

    param = Database.Parameter(
        host=os.getenv('DB_HOST', ''),
        port=os.getenv('DB_PORT', ''),
        dbname=os.getenv('DB_DBNAME', ''),
        table=os.getenv('DB_TABLE', ''),
        user=os.getenv('DB_USER', ''),
        password=os.getenv('DB_PASSWORD', ''),
        query=os.getenv('DB_QUERY', '')
    )

    db = Database(param=param)
    db.query()

    return {
        'status_code': 200,
        'records': str(db.records),
        'counts': db.counts
    }


if __name__ == '__main__':
    print(lambda_handler(event=None, context=None))

2. Create a Lambda function

Perform up to zip upload by referring to the article AWS-Lambda + Python + Cron to perform web scraping regularly

3. Set environment variables

Environment variable

1. Set from Edit in the Environment Variables section

`{} is different for each environment. ``

4. Set basic settings

basic configuration

1. Set from Edit in the Basic Settings section
2. Timeout can be set up to 15 minutes 0 seconds
3. Memory can be set up to 3008MB

5. Set up your VPC

VPC

1. Set from Edit in the VPC section
2. Set up VPC, subnet, security group

Must match with RDS settings