Build your first REST API with Flask and PostgreSQL

hi guys Jose here with teclado I know we haven't posted any YouTube videos for a while but we are back today with a good one how to get started developing rest apis using flask and Python and postgresql for your database for today's project let's imagine you've set up some sort of home automation and you're collecting temperature in various rooms in your house and you want to be able to store those temperature readings in some sort of web service and then you can maybe retrieve those readings calculate averages and calculate Trends over time and that sort of thing so let's imagine you've connected your temperature sensors to some sort of system like an Arduino and that can send requests over the Internet we're going to make a rest API that can accept those requests and store the data in a database and it's going to be able to understand which rooms the requests are coming in from so it's a pretty simple program it's mostly an introduction to flask and rest apis but it's going to show you a lot of stuff the main thing is it will show you how to write a rest API that interacts with clients and accepts data from them it's also going to show you how to store data in postgres SQL and how to connect to a postgres database how to read data back and process it and also it's going to show you how to structure a simple flask app to store secrets in the app that you don't want to share with other people and later on in a future video we'll also show you how to extend this app how to deploy it and make it publicly available and that sort of thing so it's a good project to get started it's not very complicated but it's going to give you a good base understanding for this rest API our clients are going to be able to tell it to create a new room if it doesn't already exist then in a room we're going to be able to create a new temperature reading and we're also going to be able to retrieve the average temperature of all rooms over all of time there aren't many endpoints so the python code is going to fit in a single file let's get started let's begin by getting our project set up first create a virtual environment I use Pi M to manage my local python version so I'll set 3 10 7 as the version and then use it to execute the virtual environment command then activate the virtual environment make sure to select the virtual environment you've just created you with your editor so in vs code we'll go to python select interpreter and then select the one in the current folder that's just so when we install any libraries which we're going to do now by creating a requirements.txt file the editor will read them and be able to understand the Imports in requirements.txt let's put flask and then we're going to go back to the terminal and type pip install Dash R requirements.txt and that's going to install everything that's in this file now let's build the most basic flask app let's create a new file called app.py and I'm going to hide the console and close requirements.txt for now the most basic flask app you can build imports from flask the flask class and then we create the flask app passing name as the unique flask app name we Define what's called an endpoint so this is a function that has Associated to it a uber rail so we'll Define the home function and return hello world and now what happens is when we run the flask app a client which is really any other program including our temperature sensors in our arduinos or another program like a JavaScript website or something like that they can all make requests to our API and as long as they pass in the correct endpoint which in this case is just the forward slash then we are going to return whatever the function returns we're going to run the function obviously first and return its value so this function could do things like going into a database and retrieving data from it or if it accepts data from the client which I'll tell you how to do that in a second then it would be able to store that data in a database or do some processing really it can do anything now that we we've got this we can run the flask app let's save that file then type flask run making sure your virtual environment is active and then you can see that the flask app starts but we do get a couple of things going on first this debug mode is off and it says that do not use this in a production deployment so we are running in production mode and when we're developing we want to use debug mode when we use debug mode it's also going to do things like automatically restart our app whenever we make code changes so it's just a little bit easier to work with to do this we're going to go to requirements.txt and add python-env in our requirements then we're going to press Ctrl C in the terminal to quit the flash cap and we're going to reinstall the requirements libraries so flask is already installed so it doesn't re-install that but it installs everything else which in this case is just this Library now that we've got that we can create another file called dot flask EnV for flask environment and here we can define environment variable style variables to configure the flask app for example we can say flask underscore app equal app and although this is the default it's a good thing to get used to doing it says that the flask app should be found in app.py that's what this means we can also say flask debug equal 1 or true I use true more often than one but either is fine and what this does is it sets debug mode so if we re-run the flask app now you can see debug mode is on so we're good to go we still get the warning about the development server but don't worry about that because when you deploy your application which we won't show you in this video but later on we'll make another video to show you that we won't be using flask run to run our app this is only useful for development now we've got the debugger and we got the automatic restarting of the app whenever we make a code change for example if we go to app.py and just save this file you can see that it restarts the app this is good because as we make changes we're going to be doing that a lot and we can test our app without having to go here and press Ctrl C and then start it again now if you go to your browser and access 127.0.0.1 colon 5000 you should get Hello World back that's because when you access this you're accessing the root of the domain in this case this is the domain what you're accessing is the forward slash endpoint that's the default and so we return this value and that's what the browser sees usually when testing rest apis locally though you're not going to use the browser there are programs specifically made for testing rest apis so let me just tidy this a little bit and bring up the program that I like to use this is insomnia and it is a program for testing rest apis another option is called Postman and you can use that instead of insomnia and insomnia you create projects and then collections within the project for example and we can create the first video collection and then in here you can create HTTP requests an HTTP request is what the browser makes to RS API in order to retrieve information such as this string or send it information here we're going to create a new request and then in here we can put the URL there are many many configuration options such as the method and the body authentication query strings headers and a lot of stuff but we're not going to use most of them in this video for now let's press send you'll see that I've made a mistake because I've put https there you don't want https When developing locally you only want https when you deploy the app again more on that in another video you can see we get the hello world string which is fantastic now here you can double click the new request and give it a name you can do something like that the next step for our project before we get started is to get a database so we're going to use postgresql and I recommend elephantsql.com for free databases the free offering is quite simple and not very powerful but it's plenty for making small projects like this one and for learning about push for SQL so if you go to elephantsql.com and you create an account then you'll be able to see this this is your dashboard as you can see I use elephant SQL quite a lot let's go to create a new instance and we're going to give it a name first rest API video you can give it tags and you can select a plan I'm going for the free plan later on you can upgrade to a more powerful plan if you need more power and the pricing of elephant SQL is quite good then let's select the region we're going to go with something that's close to me so that requests happen faster I'm going to go with Ireland and that's it you can create the instance and then you can find it in here and what you need is you need the URL so let's copy that and we're going to go back to our project now in our project let's create a new file called dot EnV and these are environment variables that aren't specifically related to flask these environment variables are not going to be public so if you ever share your code with someone else make sure to not send them this file we're going to put database URL and we're going to press equal and then we're going to paste it in so that's it next let's install a library that allows us to connect to the database and that is psycho pg2-binary now that's installed we can go ahead and do pip install Dash R requirements.txt and install that if you are already familiar with senko pg2 and you're using Mac OS or Linux you can just install psycho pg2 but sometimes some of my students run into a bit of trouble with cycle pg2 cycle pg2-binary gives them a bit fewer problems so I recommend using that now let's go over to the app and we're going to connect to the database connecting to the database is pretty straightforward first we'll need to import OS because when we load the environment variables in a.n file we're then going to be able to read them using the OS module we're also going to Import Cycle pg2 and then from the dot end package we're going to import load.in then before doing anything we're going to do load dot end and that is going to take the dot end file and it's going to read the different variables in there at the moment that's just one and it's going to put them in the environment variables of this process that means that we can do URL for our database is OS dot get EnV database URL and as long as we execute this line after this one this variable will now get this value we can then do connection is psychopg2.connect and pass in the URL and now we've got a connection to our postgresql database and we can use that to insert data into the database or to read data from the database next let's prepare the SQL queries that we're going to need for this project and by the way we've got a comprehensive blog post that goes along with this video it's linked below and at this point the blog post and this video deviate so that the video can be a bit shorter we're going to Define all our SQL queries now and then all our flask endpoints in the blog post we build each flask endpoint at a time coding the SQL as we need it it's just a little bit of a different approach there's no right or wrong answer to how you should do it but if you want to read some text or you want to look at the other approach you can check out the blog post also the blog post covers a few more flask endpoints than this video because if not this video is going to be extremely long the queries that we're going to need are first one for creating tables at this point I'm going to hide away insomnia because I don't think we need it just now we'll bring it back later to create the tables we're going to say create table if not exists and then we're going to have two different tables in our app one for the different rooms of our house for which we're collecting temperatures and the other one for the temperatures themselves there are two different tables the room stable has an ID a unique identifier for each row as well as a name for the room each room can be identified by the name or the ID and then in the temperatures table we're gonna have a room ID the temperature itself and the date and the room ID is what identifies which room this temperature is for for example if we have a room with id1 then if we take a temperature in that room we'll put one in this column this allows us to link or relate the temperatures and the rooms so that then we can find out which temperatures are for which room when we want to insert a room we're going to use this query insert room return ID we're going to say insert into rooms and then we pass in the name column and we're going to pass in a value later on and that is the name that will be inserted into a new row the ID column gets populated automatically by postgresql so we don't have to give it a value ourselves but when we insert a room we are going to return the ID that postgresql generates and that is going to then let us insert temperatures we need the ID for the temperatures table when we want to insert a temperature then it's something extremely similar we will insert into temperatures then inside brackets we pass in the columns that we want to give values for and here none of the columns are serial so we do have to pass values for everything we pass in room ID temperature and date and then we pass in three values like that the values that we pass are going to be passed in a little bit later when we actually create a temperature or a room so that's why they look like percent s for now finally we are going to be able to find how many days worth of data we have collected and that is done with this query select the count of distinct dates as days this is just an alias for this column from the temperatures table so we're going to grab every different date that we've got and we're going to count how many they are finally we're going to be able to find the global average or basically across all the temperatures that we've collected what's the average temperature in our house and that is then we select AVG of temperature where that's the temperature column from the temperatures table so it's just going to sum them all up and then divide them by the number of temperature rows if you want to learn about SQL in detail we've got a course on udemy Linked In the description of this video that talks about Python and postgresql the rest API course that I mentioned earlier only talks about using SQL Alchemy which is another library that simplifies interacting with SQL databases slightly but it doesn't cover SQL code itself okay let's start by creating a room every time we save a temperature reading we have to tell the database which room the reading is for so before we can create temperature readings we need to create rooms in order to be able to create a room we need to ask the user or the client for the room's name so they are going to have to send us that let's delete this endpoint and create another one I'll do at app.post API room and here we're defining the slash API room endpoint but what does this post mean well this is the HTTP method earlier we used dot get and that's the HTTP method most commonly used for clients to retrieve data from the API post is usually used for clients that want to send us data so that's why we're using that here then we'll create the python function which I'm going to call create room and then all we have to do is get the data that the client has sent us we're going to ask the client to send us data in Json format that is just a string of text with a specific structure I'll show you more about what that looks like in a second we could ask the client to send data in a different format almost any format works as long as the clients are happy to send you it but Json is the standard so that's what we're using here we're going to do data is request.get Json and we have to import request from flask so let's copy that and go here and import that now we've got a python dictionary of the data that the client has sent us the client will send us a string with a specific structure request.getjson takes that string and turns it into a dictionary that's quite handy because now we can do name is data name we'll assume that the client has sent us some Json that has the field name inside it I'll show you what that looks like in a moment then let's connect to the database so we'll do with connection and this allows us to start a connection to the database and then we'll do with connection dot cursor as cursor and a cursor is an object that allows us to insert data into the database or iterate over rows that the database returns if we make a query to selected data it's very common to use cursors even when you're not selecting data just to insert data we're going to do cursor.execute and now we put in the query that we want to execute and the query we're going to execute is create rooms table now every time we create a new room we're going to execute this create rooms table query although it should only be necessary the first time that we create a room that's okay because the query says create table if not exists so if the table already exists it's not going to create it then we'll do cursor execute insert room return ID remember that this query takes in a value it's got the percent s there so we have to pass in the value and how you do that is you pass in a tuple of values where each element of the Tuple will be assigned to one of the percent essays here we want to pass a tuple with a single value here we would want to pass a tuple with three values to pass a tuple with a single value we have to do name comma inside brackets so this defines a tuple with a single value it's not enough to do this this is a very common mistake what this is doing is it's passing name as a singular value to the execute function method but it's not passing a tuple to pass a tuple we have to put this inside brackets this inserts the room and Returns the ID now we have to retrieve the ID from the cursor to do that we'll say room ID is cursor fetch one and this Returns the first row that the cursor has given us in this case it's only one row that the cursor returns so fetch one will give us the only row but nonetheless that's how you do it and then we want to access a specific column of the return cursor the cursor is pointing at the first row but each row has two columns ID and name so we want to access the first one we do that with this type of notation as if it were a python list we access the index 0. once we're done with the connection we can exit this with block or both of them and here we can say return and we're going to return the ID which is room ID and we're also going to return a simple message to tell the client what happened so we'll say Rune name created then we're also going to return a status code the status code is 201 which means created that's our first endpoint now just with this we can run our app let's type flask run making sure your virtual environment is active and then let's bring back insomnia to test this out here we're in insomni and we're gonna send some data to our API the first thing to do is change the method to post then the endpoint to API room and then in the body we're going to select Json and here we're going to put something that looks a lot like a python dictionary we're going to put name and then for example living room let's send this data and you see we get id1 that's the first run we've created and the message says room living room created now if you go to your browser in elephant SQL you can click on table queries and select everything from the rooms table and you can see there now we've got id1 and the name living room let's see how we can add a temperature reading to that room again we do app.post now the endpoint is slash API temperature and the function name is ADD temp now we're going to expect the client to send us two different data points the room name the room ID and the temperature itself optionally the client may also send us the dates at which the temperature was taken if they don't send us a date we're going to assume the date is now we start by doing this data equal request okay Json just as we did before another temperature and the room ID are coming in the temperature and room keys of the Json payload then we're going to try to calculate the date we're going to assume the date comes in a specific format so we're going to try to parse that format we'll say date is date time.strp data date and the format that we're going to expect is percent M percent D percent y set H percent M percent s and the format we're going to expect is first the month then a dash then the day then a dash and then the year then a space and then hours minutes and seconds I know that this looks very cryptic but this is the date time string format mini language that you can see there if you go to the website strftime.org if I'm not mistaken that's the URL and then you can see a description of every single percent based character and what they mean now let's import the daytime module so it'll import date time there it comes with python so there's nothing to install and there we have it now if the payload didn't have a date key in it then we're going to do accept key error because that's what's going to get raised when we try to access the date key of data if data doesn't have a date that was a bit of a mouthful but then we're going to say date is date time dot now and we're going to get the time zone as UTC so let's import time zone and actually I made the small mistake here it's not import date time is from date time import date time and time zone there we go now we'll assume that we've got a date so let's do with connection with connection dot cursor as cursor and then we're going to create the temperatures table and then we're going to insert a temperature again we have to pass a tuple of values where each value is one mapped to the percent s so now we have to pass a three value Tuple we open the brackets and we say room ID temperature and date finally exiting the with blocks we can say return message temperature added let's add one more endpoint for good measure they want to calculate the average so I'm just going to copy it and paste it in and then I'll explain what it does this time it's a get endpoint and it's API average the function name is get global average it connects to the database and then first runs the global AVG SQL query and what that does is it selects the average temperature out of all the temperatures in the table and then that grabs the First Column and the only column returned by the first row again reminder that cursor.fetch1 gives you the first row and the zero here on access is the First Column of that row now that we've got the average in a variable we're going to execute another query Global number of days and this query selects the count of distinct dates in the database so how many different days we've collected temperatures and we're gonna again grab the first row and the First Column and that's the number of days then we're going to return the average using the python built-in round function which rounds it up to two decimal places and then we're gonna return the number of days as well let's go to insomnia and create another HTTP request with post again we're going to do HTTP 127.1.1 colon 5000 slash API slash temperature and in the Json now we're going to include the room which is going to be one the temperature which is going to be nine let's say and we could include a date or it will default to today's date if we don't so let's send that in and we can see the temperature was added let's also include another date for let's say yesterday so again that was a month the day which was the 16th of 2022 and the time which let's say 1755 35. and that temperature was added if you put the wrong format in here you're going to crash the app because we don't have that much error handling in the application it's quite a complex thing to do error handling for a lot of different potential inputs that the user might include but we've got more information on error handling and things like that in our rest API course on udemy let's rename these endpoints so then we go there they're renamed and if we go back to Elephant Sequel and refresh the browser you'll see the temperatures table has been created let's execute the select to get everything and you can see we've got two temperatures there since I inserted the same temperature let's add another temperature on a different day as well so we have two different temperatures at least and then let's create another endpoint this time is going to be for the average so this one doesn't take in a body and it is a get request let's send it in and you can see the average is 11.67 and there's three distinct days worth of data if you have multiple data points on the same day this shouldn't change it should still show three this is already quite a long video so we're going to leave it there but the blog post that is linked at the bottom of this video in the description also covers a couple more features like how to get the average for a specific room over the last seven days or 30 days so it does a little bit more data processing using SQL and also a couple other things so do have a look at the blog post if you want to learn more and there's also other blog posts that we've written related to this same project on deployment so you can also find that in the blog if you'd like to learn more about rest API development using flask and python please consider enrolling in our all new course on udemy we've spent over a year rewriting and re-recording everything and I'm sure you'll love it but remember that udemy has a 30-day money-back guarantee so you can try the course risk free I'd really like to see you on the inside but as always no pressure thank you very much for watching but have you used flask and postgresql for rest API development before do you have any other tips or suggestions for our viewers I'd love to read more about them in the comments below again thanks for watching and I'll see you next time

Channel: teclado

Views: 22,648

Keywords: teclado, telcado, tecado, postgres, postgresql, postgressql, flask, fask, python, quart, fastapi, rest api, rest, restapi, flask api, python api, python rest api

Id: DlNIXC9SaF4

Length: 27min 41sec (1661 seconds)

Published: Mon Oct 17 2022