How to send a POST request to AWS Lambda using Python and MySQL-CodePudding

I have a Lambda function that connects to my EC2 MySQL database and can successfully connect, query and use it to retrieve json data. I wanted to improve on this Lambda function by providing parameters to the query such that I can retrieve the corresponding data using the API gateway and the parameters defined.

The Lambda function retrieves a specific hotel using the 'hotel_id'. As shown in the first figure, the AWS console can test the Lambda function with the body parameters: "{ "hotel_id": "10717" }" and retrieve the corresponding hotel.

The following is the simplfied Lambda function, 'handler.py':

import pymysql  # library for python x mysql
import json     # json library
import requests


# Configuration Values
endpoint = 'secret'
username = 'secret'
password = 'secret'
database_name = 'secret'

# Connection
connection = pymysql.connect(host=endpoint, 
                             user=username, 
                             passwd=password,
                             db=database_name, 
                             charset='utf8mb4', 
                             cursorclass=pymysql.cursors.DictCursor)

##############################
# lambda_handler function
# to return json response from the db query
# using db Hotels, table hotelxml
#############################
def lambda_handler(event, context):
    
    print("EVENT:", event) 
    
    response = event.get('body')
    
#unused vars
    url ='https://vdg8o9gq6i.execute-api.ap-south-1.amazonaws.com/dev/fetch_pethotels2'
    headers = {"content-type": "application/json" }

    params = (
    hotel_id := event['hotel_id']
    )

    query = 'SELECT * FROM hotelxml WHERE hotel_id = %s'

    responseBody = ""
    
    hotelList = []
    
    try:
        cursor = connection.cursor()
        cursor.execute(query, params)
        rows = cursor.fetchall()
        
        for row in rows:
            print(row)
            hotelList.append(row)
            print("/n")

        responseBody = json.dumps(rows, separators=(',', ':'))

        response = {
            "isBase64Encoded": False,
            'statusCode': 200,
            'headers': {
                'Content-Type': 'application/json',
                'Access-Control-Allow-Origin': '*'
            }, "body": responseBody
        }
        
    except Exception as e:
        
        response = {
            "isBase64Encoded": False,
            'statusCode': responseBody.statusCode,
            'headers': {
                'Content-Type': 'application/json',
                'Access-Control-Allow-Origin': '*'
            }, "body": responseBody
        }
        
        print("exception occured: {}".format (e))
        
        
    finally:
        #connection.close()
        pass
        
return response

Attempting to retrieve the same query/data using Postman and the browser, I am met with a different result. As shown in the figure below:

In this figures below, I am showcasing the AWS Cloud watch logs of these requests. The first figure shows using the AWS Console working request log and the second figure shows the Postman request log.

AWS Console request Cloud watch Log:

Postman request Cloud watch Log:

Any advice and help is very much welcome. Thank you very much for taking the time to read this.

CodePudding user response：

First things first, it looks like a GET request is more appropriate for your use case here. GET is used for simple retrieval and IDs are typically specified with path parameters (ex: https://api.com/hotels/10717 where 10717 is your hotel_id). You can see more about REST methods here.

Now as to why your code is failing, it is because API gateway events are structured differently from Lambda console test events. Take a look at the docs to see the event format from API gateway.

If you refer to your CloudWatch logs from the Postman request you'll see that the hotel_id is in the body field on the event. ("body": '{\r\n "hotel_id"_: "10717"\r\n}). Since you're looking for the hotel_id at the top level of the event, you're not finding anything.

You could parse event.body and continue as normal. However, I strongly encourage you to read up on HTTP verbs and understand when each is appropriate. A GET request with a path parameter will make your life easier here.

CodePudding user response：

The AWS Support Center helped me out. This the method used:

python function call: "params = ( hotel_id := json.loads(event['body'])['hotel_id'] )"

import pymysql  # library for python x mysql
import json     # json library
import requests

# Configuration Values
endpoint = 'secret'
username = 'secret'
password = 'secret'
database_name = 'secret'

# Connection
connection = pymysql.connect(host=endpoint, 
                             user=username, 
                             passwd=password,
                             db=database_name, 
                             charset='utf8mb4', 
                             cursorclass=pymysql.cursors.DictCursor)

##############################
# lambda_handler function
# to return json response from the db query
#############################
def lambda_handler(event, context):

    
    print("EVENT:", event['body']) 
    print("event:", json.dumps(event))

    params = (
        hotel_id := json.loads(event['body'])['hotel_id']
    )

    query = 'SELECT * FROM hotelxml WHERE hotel_id = %s'

    responseBody = ""
    
    hotelList = []
    
    try:
        cursor = connection.cursor()
        cursor.execute(query, params)
        rows = cursor.fetchall()
        
        for row in rows:
            print(row)
            hotelList.append(row)
            print("/n")


       # json.dumps(mylist, separators=(',', ':'))
        responseBody = json.dumps(rows, separators=(',', ':'))

        response = {
            
            'statusCode': 200,
            'headers': {
                'Content-Type': 'application/json',
                'Access-Control-Allow-Origin': '*'
            }, "body": responseBody,
            "isBase64Encoded": False
        }
        
    except Exception as e:
        
        response = {
            
            'statusCode':500,
            'headers': {
                'Content-Type': 'application/json',
                'Access-Control-Allow-Origin': '*'
            }, "body": "internal server erro occured please try again later.",
            "isBase64Encoded": False
        }
        
        print("exception occured: {}".format (e))
        
        
    finally:
        #connection.close()
        pass
        
    return response