Step 1: Create a Mysql database using AWS RDS<\/strong><\/p>\n Click on Create database<\/strong> button which will take you to the next page in the process of creating a database<\/p>\n 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.<\/p>\n Check the option Only enable options eligible for RDS Free Usage Tier.<\/strong><\/p>\n 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.<\/p>\n The AWS RDS free tier service allows you to use the following resources for free:<\/p>\n This free service is available for 12 months starting with the date on which you create your AWS account.<\/p>\n Click Next<\/strong> Button which will take you to the next page in the process of creating a MySQL database using the RDS service.<\/p>\n This page allows you to specify the database details such as Instance specifications<\/strong> and configure the settings.<\/p>\n In the settings <\/strong>section, we have used the following credentials for creating the database :<\/p>\n DB instance identifier: rdslambda Master Password: rdslambda123$$<\/p>\n \u00a0 Click Next <\/strong>Button which will take you to the next page in the process of creating a MySQL database.<\/p>\n \u00a0<\/strong><\/p>\n This page allows you to configure advanced settings for creating a database.<\/p>\n In this page, besides filling in other details, click on Yes<\/strong> in the Public Accessibility<\/strong> section.<\/p>\n If you select No<\/strong>, 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.<\/p>\n \u00a0<\/strong><\/p>\n In this page, in the Database options<\/strong> section, fill in the details including the Database name.<\/strong><\/p>\n If the Database name<\/strong> is not specified, then no initial MySQL database will be created on the DB instance.<\/p>\n In this case, we have specified our Database name to be rdslambda.<\/strong><\/p>\n In the Log exports<\/strong> section, <\/strong>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.<\/p>\n \u00a0 Finally, click on the Create Database <\/strong>button to create a database<\/p>\n Once the instance is created, you can check the details of the instance from the Instances<\/strong> section in the dashboard. Besides other details, you can also check the endpoint<\/strong> of the instance that is created.T he Endpoint <\/strong>enables you to connect to the RDS instance from various sources including an AWS Lambda function, as illustrated in this case.<\/p>\n The endpoint generated for the instance that we created is :<\/p>\n rdslambda.clcjqqx9lzkn.us-east-1.rds.amazonaws.com<\/strong><\/p>\n \u00a0<\/strong><\/p>\n The port no of our instance is 3306<\/strong><\/p>\n You need both, the endpoint and the port no, to connect to the AWS RDS instance.<\/em><\/strong><\/p>\n \u00a0<\/em><\/strong><\/p>\n In the security group section, you can also change the inbound and outbound rules of the instance created.<\/p>\n In this case, we have edited the inbound rules by changing the Source<\/strong> values to anywhere<\/strong>.<\/p>\n 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.<\/p>\n Using the Mysql workbench you can now create your table in which you want to save data from the lambda function.<\/em><\/strong><\/p>\n \u00a0<\/em><\/strong><\/p>\n Step2: Write code in Python to save data in the above table<\/strong><\/p>\n \u00a0<\/strong><\/p>\n In this use case, we have written the Lambda code in Python. You can install PyMySQL on your local machine by executing the following ubuntu command :<\/p>\n [sourcecode language=”plain”]sudo apt-get install python3-pymysql [\/sourcecode]<\/p>\n The AWS Lambda function:<\/p>\n 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.<\/p>\n the code in Python :<\/strong><\/p>\n [sourcecode language=”plain”]#import libraries here<\/p>\n import pymysql<\/p>\n import sys<\/p>\n import logging<\/p>\n from botocore.vendored import requests<\/p>\n from datetime import datetime<\/p>\n REGION = ‘us-west-2’<\/p>\n #connect to the rds instance using the details below<\/p>\n rds_host = “rdslambda.clcjqqx9lzkn.us-east-1.rds.amazonaws.com”<\/p>\n name = “rdslambda”<\/p>\n password = “rdslambda123$$”<\/p>\n db_name = “rdslambda”<\/p>\n logger= logging.getLogger()<\/p>\n logger.setLevel(logging.DEBUG)<\/p>\n def save_events(event):<\/p>\n # Get the current date and time in yy–mm-dd hours:minutes:second format<\/p>\n now = datetime.now()<\/p>\n now_str = now.strftime(‘%Y-%m-%d %H:%M:%S’)<\/p>\n # API call<\/p>\n response = requests.get(‘http:\/\/api.openweathermap.org\/data\/2.5\/forecast?q=Washington,us&APPID=53d6430c1eccacb54e827045d1aee3d3’)<\/p>\n # Check if the request completed successfully.<\/p>\n if response.status_code == 200:<\/p>\n # Convert response data to python dictionary object<\/p>\n datadictionary = response.json()<\/p>\n # Return the desired weather information for today from the dictionary object<\/p>\n for data in datadictionary[‘list’]:<\/p>\n if data[‘dt_txt’] > now_str:<\/p>\n # Return data and print data<\/p>\n description = data[‘weather’][0][‘description’]<\/p>\n humidity = data[‘main’][‘humidity’]<\/p>\n sea_level = data[‘main’][‘sea_level’]<\/p>\n temp = data[‘main’][‘temp’]<\/p>\n #print(“weather:{}, humidity: {}, sea_level: {},temperature:{}”.format(description,humidity,sea_level,temp))<\/p>\n #return “weather:{}, humidity: {}, sea_level: {},temperature:{}”.format(description,humidity,sea_level,temp)<\/p>\n #print(event)<\/p>\n result = []<\/p>\n # Using a try catch block to catch exceptions<\/p>\n try:<\/p>\n # Define the connection string<\/p>\n conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)<\/p>\n except:<\/p>\n logger.error(“EEEEEE”)<\/p>\n sys.exit()<\/p>\n with conn.cursor() as cur:<\/p>\n #Insert weather information of Washington in the rdslambda table<\/p>\n query = “INSERT INTO rdslambda(reg_date,weather,humidity,sea_level,temperature) VALUES (sysdate(),%s,%s,%s,%s)”<\/p>\n data = (description,humidity,sea_level,temp)<\/p>\n cur.execute(query,data)<\/p>\n conn.commit()<\/p>\n #Retrieve records from the table rdslambda<\/p>\n cur.execute(“””select * from rdslambda”””)<\/p>\n #Commit the transaction<\/p>\n conn.commit()<\/p>\n cur.close()<\/p>\n #Print all rows from the rdslambda table<\/p>\n for a row incur:<\/p>\n result.append(list(row))<\/p>\n print (result)<\/p>\n #handler<\/p>\n def main(event, context):<\/p>\n save_events(event)[\/sourcecode]<\/p>\n \u00a0<\/strong><\/p>\n \u00a0<\/strong><\/p>\n Step 3: Test the lambda function and check the logs<\/strong><\/p>\n Once done with the code, you can test the Lambda function by clicking on the Test<\/strong> button.<\/p>\n You can also check the logs of the lambda function to check if the execution was a success or it contained errors.<\/p>\n Step 4: Check if the records are inserted in the table by connecting with MYSQL workbench<\/strong><\/p>\n You can now use the MySQL workbench to check whether the records are inserted in the rdslambda table or not.<\/p>\n 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.<\/p>\n Keep watching this section for more useful articles on AWS.<\/p>\n References :<\/p>\n https:\/\/www.youtube.com\/watch?v=-CoL5oN1RzQ&vl=en<\/a><\/p>\n https:\/\/jee-appy.blogspot.com\/2018\/02\/aws-lambda-function-with-rds-mysql.html<\/a><\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image001-1024x498-800x389.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image0031-1024x498-800x389.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image0051-1024x498-800x389.png\")
<\/p>\n\n
![\"Amazon](\"http:\/\/aqbsolutions.com\/blog\/wp-content\/uploads\/2018\/08\/Amazon-RDS-Usage-Components.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image0071-1024x501-800x391.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image009-1024x498-800x389.png\")
<\/p>\n
\nMaster username: rdslambda<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image011-1024x459-800x359.png\")
<\/strong><\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image013-1024x498-800x389.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image015-1024x501-800x391.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image017-1024x463-800x362.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image019-1024x463-800x362.png\")
<\/strong><\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image021-1024x463-800x362.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image023-1024x463-800x362.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image025-1024x575-712x400.png\")
<\/p>\n
\nHowever, 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.<\/p>\n\n
\nYou must install any one of the following packages in the virtual environment to access MySQl database using Python
\nMySQL-python
\nmysql-connector-python
\nPyMySQL.In this use case, we have installed PyMySQL to connect to the MySQL database of the RDS instance.<\/li>\n<\/ul>\n\n
![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image027-1024x501-800x391.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image029-1024x463-800x362.png\")
<\/p>\n![\"\"](\"http:\/\/www.aqbsolutions.com\/wp-content\/uploads\/2018\/08\/image031-1024x501-800x391.png\")
<\/p>\n