Python Forum
Looking for an up to date example to query mysql
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Looking for an up to date example to query mysql
#1
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.
Reply
#2
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
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
Reply
#4
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" }
Reply
#5
(Feb-19-2019, 12:13 AM)UtiliseIT Wrote: { "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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
Thank you
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Compare current date on calendar with date format file name Fioravanti 1 123 Mar-26-2024, 08:23 AM
Last Post: Pedroski55
  Python date format changes to date & time 1418 4 518 Jan-20-2024, 04:45 AM
Last Post: 1418
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,684 Feb-09-2022, 09:55 AM
Last Post: ibreeden
  Date format and past date check function Turtle 5 4,069 Oct-22-2021, 09:45 PM
Last Post: deanhystad
  Problem Using SQL Placeholder In MySQL Query AdeS 11 5,934 Jul-31-2021, 12:19 AM
Last Post: Pedroski55
  How to add previous date infront of every unique customer id's invoice date ur_enegmatic 1 2,191 Feb-06-2021, 10:48 PM
Last Post: eddywinch82
  How to add date and years(integer) to get a date NG0824 4 2,804 Sep-03-2020, 02:25 PM
Last Post: NG0824
  MYSQL Update Query format simdo01 1 2,188 Aug-31-2020, 12:59 AM
Last Post: nilamo
  Python mysql query help please tduckman 4 4,245 Mar-13-2020, 03:42 PM
Last Post: Marbelous

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020