[PYTHON] RDS tutorial on Lambda

Assuming that you will create an API that exposes the data stored in RDS to the outside using AWS API Gateway, Lambda and RDS, first connect from Lambda to RDS

Assumed table structure

#table name: link_clicks
|Column name| description       | 
| path      |url path|
| clicks    |Link clicks|
| stat_date |Aggregation date|

1. Create a Lambda function

Create a program in python that connects to RDS (mysql) using lambda and retrieves and returns information according to the query parameter (path).

1.1 Create a python package (api)

mkdir api

1.2 Install python dependent packages

Install PyMySQL for connection to mysql

pip install PyMySQL -t api

1.3 Describe the main function

Prepare the main function (handler) description file and configuration file (Replace YOUR_XXX)

api/api.py

# -*- coding: utf-8 -*-
import sys
import logging
import rds_config
import pymysql
import datetime as DT
import json

#rds settings
rds_host  = rds_config.db_host
name = rds_config.db_username
password = rds_config.db_password
db_name = rds_config.db_name


logger = logging.getLogger()
logger.setLevel(logging.INFO)

try:
    mysql_client = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
except:
    logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
    sys.exit()

logger.info("SUCCESS: Connection to RDS mysql instance succeeded")

def handler(event, context):
    """
    This function fetches content from mysql RDS instance
    """

    path = event["path"]
  
    query = "select sum(clicks) from link_clicks where path = '%(path)s' group by path" % dict(path=path)
    logger.info(query)

    results = []
    with mysql_client.cursor() as cur:
        cur.execute(query)
        for row in cur:
            clicks = int(row[0])
            results.append({"clicks": clicks})

    return json.dumps(results)

api/rds_config.py

db_host = "YOUR_HOST"
db_username = "YOUR_USERNAME"
db_password = "YOUR_PASSWORD"
db_name = "YOUR_DB_NAME"

1.4 Upload the program to lambda

deploy.sh

#Zip creation
(cd api && zip -r - *) > api.zip

# create lambda function
## YOUR_Lambda for ROLE-vpc-execution-Specify role
aws lambda create-function \
--region YOUR_REGION \
--function-name api  \
--zip-file fileb://api.zip \
--role YOUR_ROLE \
--handler api.handler \
--runtime python2.7 \
--vpc-config SubnetIds=YOUR_SUBNET_IDS,SecurityGroupIds=YOUR_SECURITY_GROUP_ID

# update lambda function
aws lambda update-function-code \
--region YOUR_REGION \
--function-name  api  \
--zip-file fileb://api.zip

2. Check the Lambda function

2.1 Confirm that the Lambda function has been uploaded

Check the following to confirm that the api function has been added https://ap-northeast-1.console.aws.amazon.com/lambda/home?region=ap-northeast-1#/functions?display=list

2.2 Set a test event

Register a test event from actions> configure test event The settings are as follows

{
  "path": "/11111"
}

2.3 Test

Click save & test in the image above to test

Finally

With the above procedure, you can now connect to lambda => RDS and browse the data. Details are also written in the aws document, so please refer to that http://docs.aws.amazon.com/ja_jp/lambda/latest/dg/vpc-rds-create-lambda-function.html