Looking for an up to date example to query mysql

[UtiliseIT]

Hi,

My use case is to query a MySQL database as part of a (AWS) Lambda function that returns results in json format via an api. Queries may be selects, selects with variables, or firing sp's.

My current situation is I have a working example that I think is quite old. In addition im note sure that the output is in the correct json format. An example from the api is

Output:
[[""], ["Adelaide"], ["Alexandria"], ["Alice Springs"]]

My current code is

def handler(event, context):
    try:
        conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=20)
    except:
        logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
    """
    This function fetches content from mysql RDS instance
    """
    rows = []
    with conn.cursor() as cursor:

        sql = "SELECT * FROM tablea"
        cursor.execute(sql)
        for row in cursor:
            rows.append(row)
            print(row)

    return {
    'statusCode':200,
    'headers': {'Access-Control-Allow-Origin': '*'},
    'body':  json.dumps(rows)
    }

The folders in my project are (im not even sure how to update)

.idea
_pycache_
pip
pip-9.0.1.dist-info
pkg_resources
pymysql
PyMySQL-0.7.11.dist-info
setuptools
setuptools-36.6.0.dist-info
wheel
wheel-0.30.0.dist.info

With Lambda there is the ability to run Python 3.7

Thanks for any assistance, greatly appreciated.

[buran]

I am not sure I fully understand your question, but json.dumps(rows) will create string and your function will return a dict. I think what you actually need is to make the return like this

return json.dumps({
    'statusCode':200,
    'headers': {'Access-Control-Allow-Origin': '*'},
    'body': rows
    })

full example

import json

def handler():
    # assume here it retrieve rows from db
    rows = [[""], ["Adelaide"], ["Alexandria"], ["Alice Springs"]]
    
    return json.dumps({
    'statusCode':200,
    'headers': {'Access-Control-Allow-Origin': '*'},
    'body': rows
    })
    
print(handler())

Output:
{"statusCode": 200, "headers": {"Access-Control-Allow-Origin": "*"}, "body": [[""], ["Adelaide"], ["Alexandria"], ["Alice Springs"]]}
>>>

as you can see it's a valid json
i.e. I think your function should return str that is valid json, not dict

[UtiliseIT]

Thank you @buran for your reply.

My main question is around the age & technique of the code I am using. Is this the latest (production) way that one would use to query a MySQL database via Python that sits inside a Lambda capable of executing at Runtime 3.7.

Ive done some searching and couldn't really find some current examples of how to do this.

Cheers

Todd

[UtiliseIT]

In regards to the json, whilst it is valid my version doesn't seem to have column titles

like

{ "location":"Adelaide", "location":"Alexandria", "location":"Alice Springs" }

[buran]

{ "location":"Adelaide", "location":"Alexandria", "location":"Alice Springs" }

First of all this is not valid json, because of duplicate keys in a single object. You need array of objects:

Output:
[{"location": "Adelaide"}, {"location": "Alexandria"}, {"location": "Alice Springs"}]

Also, strictly speaking, your json is correct only because "body" value is string. But as i said one cannot properly/easy use it later on. Even if you add "location" key.

You can get the result as dict by adding cursorclass when creating the connection, like this:

from pymysql.cursors import DictCursor # extra import
def handler(event, context):
    try:
        conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=20, cursorclass=DictCursor)
    except:
        logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
    """
    This function fetches content from mysql RDS instance
    """
 
    with conn.cursor() as cursor:
        sql = "SELECT * FROM tablea"
        cursor.execute(sql)
        rows = cursor.fetchall()
 
    return json.dumps({
    'statusCode':200,
    'headers': {'Access-Control-Allow-Origin': '*'},
    'body': rows
    })

I also replaced your loop iterating over cursor with cursor.fetchall(). It's better/OK to iterate over cursor if you are going to use each row in the result one by one, so you don't fetch in memory all the results. In your case you need all the results in memory anyway, so use fetchall().

I haven't used AWS and lambda, so cannot tell if there is anything specific, but otherwise I think your code is fine to query MySQL.

I personaly use MySQL python connector, but pymysql looks up to date and maintained, so it's OK.
Others would prefer using ORM like SQLAlchemy, but there is added overhead/learning curve. Otherwise it works on AWS lambda

[UtiliseIT]

Thank you