If you want a small service that accesses database and performs certain operations such as returning data, you can use an AWS Lambda since it will cost you only when it is invoked.T his way, you will be paying less than an ec2 instance.
In this tutorial, you will see how you can use an AWS Lambda function that saves data in a table in an MYSQL database created using the AWS RDS service. You can perform the following steps to do the same:
Step 1: Create a Mysql database using AWS RDS
Click on Create database button which will take you to the next page in the process of creating a database
This page allows you to select the database engine from the options given. We have chosen MySQL since we would be creating a MySQL table which would be accessed by our Lambda function.
Check the option Only enable options eligible for RDS Free Usage Tier.
This option allows customers to start working with a managed database service for free, on AWS. You can use the AWS RDS Free tier service to develop new applications, test existing applications or gain hands-on experience with the service. Since we have used this service for a demo purpose, we have used the Free usage Tier service.
The AWS RDS free tier service allows you to use the following resources for free:
- 750 hours ( every month ) of Amazon RDS Single A-Z db.t2.micro instance usage running MySQL, MariaDB, PostgreSQL, Oracle BYOL, or SQL Server (running SQL Server express edition).
- 20 GB of general purpose (SSD) DB storage.
- 20 GB of backup storage for your automated database backups and any user initiated DB snapshots.
This free service is available for 12 months starting with the date on which you create your AWS account.
Click Next Button which will take you to the next page in the process of creating a MySQL database using the RDS service.
This page allows you to specify the database details such as Instance specifications and configure the settings.
In the settings section, we have used the following credentials for creating the database :
DB instance identifier: rdslambda
Master username: rdslambda
Master Password: rdslambda123$$
Click Next Button which will take you to the next page in the process of creating a MySQL database.
This page allows you to configure advanced settings for creating a database.
In this page, besides filling in other details, click on Yes in the Public Accessibility section.
If you select No, Amazon RDS will not assign a public IP address to the DB instance. In this case, no EC2 instance or devices outside of the VPC will be able to connect the RDS instance.
In this page, in the Database options section, fill in the details including the Database name.
If the Database name is not specified, then no initial MySQL database will be created on the DB instance.
In this case, we have specified our Database name to be rdslambda.
In the Log exports section, select all the log types that you need to publish to the Amazon Cloudwatch Logs. In this case, we have we have not selected any one of the log types.
Finally, click on the Create Database button to create a database
Once the instance is created, you can check the details of the instance from the Instances section in the dashboard. Besides other details, you can also check the endpoint of the instance that is created.T he Endpoint enables you to connect to the RDS instance from various sources including an AWS Lambda function, as illustrated in this case.
The endpoint generated for the instance that we created is :
rdslambda.clcjqqx9lzkn.us-east-1.rds.amazonaws.com
The port no of our instance is 3306
You need both, the endpoint and the port no, to connect to the AWS RDS instance.
In the security group section, you can also change the inbound and outbound rules of the instance created.
In this case, we have edited the inbound rules by changing the Source values to anywhere.
Once you have created the RDS instance, you can test your connection in multiple ways. One way is to connect using your MySQL workbench as shown below.
Using the Mysql workbench you can now create your table in which you want to save data from the lambda function.
Step2: Write code in Python to save data in the above table
In this use case, we have written the Lambda code in Python.
However, before writing the code you would need to execute the following steps to ensure that your Lambda function is able to connect to the MySQL database of the RDS instance that you have created.
- Install PyMysql
You must install any one of the following packages in the virtual environment to access MySQl database using Python
MySQL-python
mysql-connector-python
PyMySQL.In this use case, we have installed PyMySQL to connect to the MySQL database of the RDS instance.
You can install PyMySQL on your local machine by executing the following ubuntu command :
[sourcecode language=”plain”]sudo apt-get install python3-pymysql [/sourcecode]
- Zip the code and installed contents in a directory and upload it for the lambda function
- After installation, the PyMySQL contents gets saved in the /usr/lib/python3/dist-packages directory in the pymyql folder.You can now zip your python code for the Lambda function, and the installed contents lying in the pymysql folder and upload it in your AWS Lambda function as shown below.
The AWS Lambda function:
The first part of the code calls an external API to retrieve the weather information of Washington. The second part of the code connects to the MySQl database of the RDS instance and saves the weather information in the database.
the code in Python :
[sourcecode language=”plain”]#import libraries here
import pymysql
import sys
import logging
from botocore.vendored import requests
from datetime import datetime
REGION = ‘us-west-2’
#connect to the rds instance using the details below
rds_host = “rdslambda.clcjqqx9lzkn.us-east-1.rds.amazonaws.com”
name = “rdslambda”
password = “rdslambda123$$”
db_name = “rdslambda”
logger= logging.getLogger()
logger.setLevel(logging.DEBUG)
def save_events(event):
# Get the current date and time in yy–mm-dd hours:minutes:second format
now = datetime.now()
now_str = now.strftime(‘%Y-%m-%d %H:%M:%S’)
# API call
response = requests.get(‘http://api.openweathermap.org/data/2.5/forecast?q=Washington,us&APPID=53d6430c1eccacb54e827045d1aee3d3’)
# Check if the request completed successfully.
if response.status_code == 200:
# Convert response data to python dictionary object
datadictionary = response.json()
# Return the desired weather information for today from the dictionary object
for data in datadictionary[‘list’]:
if data[‘dt_txt’] > now_str:
# Return data and print data
description = data[‘weather’][0][‘description’]
humidity = data[‘main’][‘humidity’]
sea_level = data[‘main’][‘sea_level’]
temp = data[‘main’][‘temp’]
#print(“weather:{}, humidity: {}, sea_level: {},temperature:{}”.format(description,humidity,sea_level,temp))
#return “weather:{}, humidity: {}, sea_level: {},temperature:{}”.format(description,humidity,sea_level,temp)
#print(event)
result = []
# Using a try catch block to catch exceptions
try:
# Define the connection string
conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
except:
logger.error(“EEEEEE”)
sys.exit()
with conn.cursor() as cur:
#Insert weather information of Washington in the rdslambda table
query = “INSERT INTO rdslambda(reg_date,weather,humidity,sea_level,temperature) VALUES (sysdate(),%s,%s,%s,%s)”
data = (description,humidity,sea_level,temp)
cur.execute(query,data)
conn.commit()
#Retrieve records from the table rdslambda
cur.execute(“””select * from rdslambda”””)
#Commit the transaction
conn.commit()
cur.close()
#Print all rows from the rdslambda table
for a row incur:
result.append(list(row))
print (result)
#handler
def main(event, context):
save_events(event)[/sourcecode]
Step 3: Test the lambda function and check the logs
Once done with the code, you can test the Lambda function by clicking on the Test button.
You can also check the logs of the lambda function to check if the execution was a success or it contained errors.
Step 4: Check if the records are inserted in the table by connecting with MYSQL workbench
You can now use the MySQL workbench to check whether the records are inserted in the rdslambda table or not.
Hope this article has been useful for you and helped you in understanding how an AWS Lambda function can be used to connect with an AWS RDS instance and save data in the database.
Keep watching this section for more useful articles on AWS.
References :
https://www.youtube.com/watch?v=-CoL5oN1RzQ&vl=en
https://jee-appy.blogspot.com/2018/02/aws-lambda-function-with-rds-mysql.html
https://www.a2hosting.in/kb/developer-corner/mysql/connecting-to-mysql-using-python
https://stackoverflow.com/questions/33446347/no-module-named-pymysql
