How to Query RDS MySQL From AWS Lambda in Python | Step by Step Tutorial

hello everyone this is the video where I show you how to query your my sequel database hosted in AWS RDS from a lambda function keep in mind the steps that I'm going to show you will also work for those you trying to query from your local machine but it also includes some extra steps to allow access from a lambda function this video is also a continuation of a previous video where I show you how to create an access and RDS my sequel database from your desktop in that video there's some configuration steps that you'll need to perform in order for some of the steps in this video to work so make sure you go and check that one out first so starting off from where we left off in my previous video I have my database setup here and that database is called AWS simplified and we have an endpoint here that's associated with it and where we left off was we were in the my sequel workbench where I was performing some queries on our database just to populate it with some data so let's quickly recap on what we already did us we created a database called transactions prod we're using that database we created a table called transactions and in that table there's a transaction ID which is an integer an amount which is a decimal and a transaction type which is an enum we also describe the table then we inserted some data inside of it so two records here and then we just selected that data back so let's just run over this really quick and just query that so we can see the description of the table so we have these three fields here and let's just do a quick select to show what is inside it so there's those two rows here okay so the first thing we're going to do is install some needed dependencies so the dependency that we need for this exercise is one called pi my sequel which is a library that allows us to query my sequel databases so we're going to be using pip which is the Python install tool so if you don't have that setup already you're gonna need it for this exercise and the commands are going to show you are gonna work on both Mac Linux and Windows so it doesn't matter which platform you are on so the first thing we're gonna do is head over to a terminal I'm using PowerShell here now make sure you have a temporary directory created for this exercise and if we just take a look in this path here we just have one file called handler dot PI that's what we're going to be using to write our code in so now what we need to do is install PI sequel using pip but to this directory if you just do pip install PI my sequel on my sequel it's not going to install to this directory and since we're using a lambda with an external dependency we need to bring that dependency in to our lambda function through a zip file so we need to actually install this locally in order for this to work so instead of using pip install PI my sequel we're gonna use a slightly different command which is PIP installed - T for targets dollar sign PWD which stands for print working directory and then we're gonna say PI my sequel so this should okay that was quick so if we just do LS now we can see now we have two folders here PI - equal and another metadata folder here as well so I'm just going to bring up sublime text now which is where we'll write our code okay so this is the inside of that Handler dot PI file and I just have some stuff here already pre-prepared so I'm importing PI my sequel and I have some notes for you here for afterwards so the command that you want to run is here and this stuff is going to be available on pastebin in the description section below once this video is posted and then I'm just giving you some instructions for the next steps I'm going to show you how to do this stuff in this video so don't worry about it now and this is the lambda function permission that you're gonna need in one of the later steps okay so let's get started so we have a handler function we're just calling it back here for testing purposes so the first thing that we want to do is actually go up here and we want to define our configuration values right so the first one we need is the database endpoints which is what we are connecting to so I copied mine from the previous step so that's mine so if this is your first time doing this go into the console and it's this guy right over here endpoints so you need to grab that going back to sublime and we also need to define our username and mine was in men and the passwords AWS simplified don't try to connect to this database as well because I'm going to be deleting it afterwards and the database name is going to be what we said before transactions underscore prod right so that's done and we need to establish the connection now so we're going to say connection and we're going to be using PI my sequal doc connect need to pass in a bunch of things the first one is the endpoint which we just established above we need to specify the user and the user is the username we also need to specify the password for the database or password and that's my password variable and then we also need to specify the database name which is transactions or prod and that's in the database name variable okay so that's all we need to do to actually establish the connection so one word of the wise here make sure that you're you're writing this code outside of the handler function because for each container that spun up on your lambda this connection is going to be reused but if you put it inside the handler function a new one is going to be established every time that your lambda function is run so keep in mind establishing a connection every time is a little bit of a stressful process on your database engine so you want to try and limit that ideally you'd be using some kind of database proxy for this but in this example I'll just show you how to connect to it and run some basic queries okay so now inside of our handler we can actually start writing our code to actually interact with the database so we need to get a reference to the cursor objects where as a connection dot cursor and then we're just going to say cursor dot execute and we just want to select star from our database name our table name was transactions and now we want to get a hold of all the rows from that query so we're gonna say cursor at all and then we're get a array here in this rows objects we want to say for row in rows and then we're just gonna print out the result so remember there were three fields that I had in my database that transaction ID the amount and the type so let's just do a fancy print statement here so that's the first item the second item and the third item and then we're gonna say format row 0 so in the row object itself there are also three items that correspond to each of the fields in the database so row 0 being the transaction ID row 1 being the amounts and row 2 being the type ok so that's pretty much all we have to do to connect to our database in query it's so if we just save this file now go back to where our function is and if we just run Python handler app hi we should see that we get two results back there we go those are the two things from our database so if you're just writing a basic REST API or this is just an experiment this is how you connect and write code to a my sequel instance from your local so we're going to go through the extra steps now on how to actually upload this code into lambda so you can create maybe a REST API to interact with your database that way so let's go through those steps now so the first thing we need to do is just change them names around so the lambda handler function by default I'm gonna call mine land underscore Handler and needs to input two arguments one is the event and one of the one is the context I'm also going to delete this as well because it's not going to be needed in our lambda function so we have lambda Handler and then takes two arguments and then the same stuff here right so now what we need to do is we need to go and zip this file and the containing files in the folder so here's my folder where we installed the PI my seek one this is my handler file so what we want to do if you're on Windows you can just select all these three things here right click go to a belief 7-zip if you have it installed and then add two RDS query zip and that'll create a zip file right here I'm going to call mine rename it to function now if you're doing this from Linux or Mac I have a command here for you to run so it's this one here zip our PWD function and slash zip regardless of which mechanism you you use the result will be the same you should have a function dot zip file that contains this data so if we actually expand this out now we can see it contains a copy of this data ok so now what we want to do is go and create our lambda function so let's go and do that now so we're gonna go to lambda in the console I'm gonna click on create function we're doing this from scratch so let's say our TS query example and we're gonna use python 3.7 and so we need some special permissions for this so we want to use create a new role from AWS policy templates let's name this already as query from lambda role and we're gonna leave the policy template blank so let's go ahead and click on create function sometimes this can take a minute or so if it does take too long I'll come back when it's all done okay here we are after we created the function so we can see that we successfully created it and we're gonna leave everything default here we're not going to touch anything yeah we want to go and create the IEM role first so let's go to permissions and we can see the role that we just created let's click on that guy it's going to bring up the iam section and we want to attach a policy so we click on attach policies and we're gonna search in the search box here for a policy called AWS lamda VPC access execution roll so this is the guy that we want if we look inside it we see it contains some ec2 network interface permissions so we want to click on the checkbox there click on attach policy and now we are done with the iam stuff so we can close that out so now the next step is to actually upload our code that function dot zip that we created previously so if we go down over here to function code instead of using edit code inline which you can do for some pretty basic lambda functions we want to select upload a zip file I'll keep in mind this has a 10 megabyte limit so if it's larger than that you need to do it in s3 upload s3 and then point oops and then point the lambda function to the s3 file but since our dependency is quite small we're not going to worry about that so the first thing I want to do is go and change this around so my function its name its file name was handler dot PI our function name is lambda underscore handler so it's file name dot function name for the format here so make sure you modify that if you're using different file names and function names than I am in this example I'm going to click on upload and I'm gonna click the function zip file and so that uploaded pretty much instantly so let's click on save now and ok so everything is saved so now we can see in the inline editor here that we have the file that I uploaded we have the two dependencies with all the stuff that's in there so that's great we should be pretty much ready to go to test this out so let's go to the top right and click on test just create a dummy test event we're not reading off the input arguments here so this doesn't matter click on create and hopefully if everything worked if I click test here we should see the results and it was successful and if we look down here here are the results of me printing the data in the database so here's my first row and my second row if you liked this video I have many more AWS videos on my channel so be sure to check those out also please don't forget to Like and subscribe so that you don't miss out on next week's thanks so much folks and I'll see you next time

Channel: Be A Better Dev

Views: 37,482

Keywords: aws, cloud, amazon web services, aws cloud, amazon, aws certification training, rds mysql tutorial, aws mysql, aws mysql rds, lambda rds, lambda rds mysql, lambda rds connection, lambda rds example, lambda rds connection example, aws mysql access, how to, tutorial, guide, aws simplified, awssimplified, mysql aws, mysql tutorial on aws, mysql rds tutorial aws, how to connect to mysql from lambda, python, lambda python mysql tutorial, serverless, serverless nodens, aws developer

Id: vyLvmPkQZkI

Length: 11min 29sec (689 seconds)

Published: Mon Apr 20 2020