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:

No. Overview keyword
1 coding Python, psycopg2
2 Lambda settings Lambda

Execution environment

environment Ver.
macOS Catalina 10.15.3
Python 3.7.3
psycopg2 2.8.4

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

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
Key value
DB_HOST {host}
DB_PORT {port}
DB_DBNAME {dbname}
DB_TABLE {table}
DB_USER {user}
DB_PASSWORD {password}
DB_QUERY {query}

`{} 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

Recommended Posts

Load AWS-RDS / PostgreSQL tables with AWS-Lambda + Python
[Python] Join two tables with pandas
Load gif images with Python + OpenCV
Use PostgreSQL with Lambda (Python + psycopg2)
[Note] Get data from PostgreSQL with Python
Save / load in-memory DB with python sqlite3
FizzBuzz with Python3
Scraping with Python
Load the network modeled with Rhinoceros in Python ③
Extract text from PowerPoint with Python! (Compatible with tables)
Statistics with python
Scraping with Python
Python with Go
Integrate with Python
AES256 with python
Tested with Python
python starts with ()
with syntax (Python)
Load the network modeled with Rhinoceros in Python ②
Bingo with python
Write CSV data to AWS-S3 with AWS-Lambda + Python
Zundokokiyoshi with python
Load the network modeled with Rhinoceros in Python ①
Excel with Python
Microcomputer with Python
Cast with python
[Linux] [Python] [Pandas] Load Microsoft Access database (* .mdb) with Pandas
Serial communication with Python
Zip, unzip with python
Primality test with Python
Python with eclipse + PyDev.
Socket communication with Python
Data analysis with python 2
Scraping with Python (preparation)
Try scraping with Python.
Sequential search with Python
"Object-oriented" learning with python
Run Python with VBA
Solve AtCoder 167 with python
Serial communication with python
[Python] Use JSON with Python
Learn Python with ChemTHEATER
Run prepDE.py with python3
Collecting tweets with Python
3. 3. AI programming with Python
Kernel Method with Python
Non-blocking with Python + uWSGI
Scraping with Python + PhantomJS
Posting tweets with python
Drive WebDriver with python
Use mecab with Python3
[Python] Redirect with CGIHTTPServer
Voice analysis with python
Think yaml with python
Operate Kinesis with Python
Getting Started with Python
Zundko getter with python
Handle Excel with python
Ohm's Law with Python
Primality test with python
Run Blender with python