AWS Lambda function to connect to mysql – python

If you are new to AWS lambda and want to create a lambda function to interact with your mysql DB, this post is for you. Here we are using python to create a lambda function which makes a connection with mysql DB and after creating the lambda function, we can use it in AWS cognito triggers.

First, create a directory which will contain lambda function.

mkdir lamda_function
cd lamda_function

Create a virtual environment. We will install “pymysql” module in the virtual environment.

$ virtualenv -p python3.6 venv_aws
$ source venv_aws/bin/activate
$ pip install -t $PWD pymysql
$ nano lambda_function.py

Here is the code to connect to mysql using python module “pymysql”. Open “lambda_function.py” file and use following code.

import pymysql
import datetime
 
import uuid
 
def lambda_handler(event, context):
   endpoint = "ENDPOINT IP"
   dbname = 'DBNAME'
   user = 'YOUR USERNAME'
   password = 'YOUR_PASSWORD'
   try:
       connection = pymysql.connect(endpoint,
                                           db=dbname,
                                           user=user,
                                           passwd=password)
 
       if connection.open:
           db_Info = connection.get_server_info()
           print("Connected to MySQL Server version ", db_Info)
           cursor = connection.cursor()
           cursor.execute("select database();")
           record = cursor.fetchone()
           print("You're connected to database: ", record)
 
           cursor.execute("show tables;")
           tables = cursor.fetchall()
           print(tables)
          
 
           # SQL Query to retrive the rows
           sqlQuery    = "select * from TABLE_NAME"  
 
           #Fetch all the rows - for the SQL Query
           cursor.execute(sqlQuery)
           rows = cursor.fetchall()
 
           for row in rows:
               print(row)
 
   except Exception as e:
       print("Error while connecting to MySQL", e)
       return None
   finally:
       if (connection.open):
           #cursor.close()
           #connection.close()
           print("MySQL connection is closed")
 
   return event

You can deploy and execute this lambda function to check your connectivity. For lambda functions, AWS provides logs in cloudwatch. You can find the option for logs in the lambda function dashboard.

Deploy lambda function

To deploy your lambda function, you can make a zip file which includes the installed pymysql package and the lambda_function.py file.

After creating the zip file at your local system, you can create a lambda function in aws console and upload the zip file you have created.

Here is sample code to sync Amazon cognito user pool data with your Database. Create a lambda function as shown below and use this lambda function in cognito triggers:

import pymysql
import datetime
 
import uuid
 
def lambda_handler(event, context):
   endpoint = "ENDPOINT IP"
   dbname = 'DBNAME'
   user = 'YOUR USERNAME'
   password = 'YOUR_PASSWORD'
   try:
       connection = pymysql.connect(endpoint,
                                           db=dbname,
                                           user=user,
                                           passwd=password)
 
       if connection.open:
           db_Info = connection.get_server_info()
           print("Connected to MySQL Server version ", db_Info)
           cursor = connection.cursor()
           cursor.execute("select database();")
           record = cursor.fetchone()
           print("You're connected to database: ", record)
 
           # cursor.execute("show tables;")
           # tables = cursor.fetchall()
           # print(tables)
          
           # Insert rows into the MySQL Table
           print(event)
           userAttributes = event.get('request',{}).get('userAttributes',{})
           print("*" * 20)
           print(userAttributes)
           print("*" * 20)
           username = userAttributes.get("name",None)
           email = userAttributes.get("email",None)
           first_name = userAttributes.get("custom:first_name", None)
           last_name = userAttributes.get("custom:last_name", None)
           hash_token = str(uuid.uuid4()).replace('-', '')
          
           # check user exist
           sql_statement = "SELECT id, is_email_verified from users where email=\"{}\"".format(email)
           cursor.execute(sql_statement)
           user_data = cursor.fetchone()
           if user_data:
               #print("user_data", user_data)
               user_id = user_data[0]
               is_email_verified = user_data[1]
               print("user_id:", user_id, "\n", "is_email_verified:", is_email_verified)
               if is_email_verified:
                   print("user verified")
               elif is_email_verified==2:
                   print("account deleted")
               else:
                   print("update verified status")
                   update_statement = "UPDATE users set first_name=\"{}\", last_name=\"{}\", is_email_verified=\"{}\" WHERE id={}".format(first_name, last_name, 1, user_id)
                   print(update_statement)
                   cursor.execute(update_statement)
                   connection.commit()
           else:
               print("insert user")
               current_datetime = datetime.datetime.now()
               print("current_datetime:", current_datetime)
               insertStatement = "INSERT INTO users (first_name, last_name, username, email, is_email_verified, is_phone_verified, hash_token) VALUES (\"{}\",\"{}\",\"{}\",\"{}\", {}, {},\"{}\")".format(first_name, last_name, username, email, 1, 0, hash_token)
               print(insertStatement)
               cursor.execute(insertStatement)
               connection.commit()
              
               # Get the primary key value of the last inserted row
               #print("Primary key id of the last inserted row:")
               #print(cursor.lastrowid)
 
           # SQL Query to retrive the rows
           #sqlQuery    = "select * from users"  
 
           #Fetch all the rows - for the SQL Query
           #cursor.execute(sqlQuery)
           #rows = cursor.fetchall()
 
           #for row in rows:
           #    print(row)
 
   except Exception as e:
       print("Error while connecting to MySQL", e)
       return None
   finally:
       if (connection.open):
           #cursor.close()
           #connection.close()
           print("MySQL connection is closed")
 
   return event

When trigger is executed this lambda function, You can access user pool data in the “event” argument. Following line is used to get user attributes:

print(event)
userAttributes = event.get('request',{}).get('userAttributes',{})

Hope, It helps you.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top
Shares