FastAPI Essentials - CRUD API with a Database and SQLModel

in the last video we created a crud api with the fast api framework but we used in-memory data in this video we're going to convert that and we're going to use a relational database along with the sql model library this is a new library for interacting with databases and it's built by the same author as fast api so in this video we're going to use that and we're going to use a relational database now let's start by looking at github we have here a repository and this contains starter code and also in the video one branch the code from the previous video what we're going to do in this video is convert this code that we have from the last video to use a relational database now the sql model library is here and it makes interacting with sql databases easier when you're using frameworks like fast api sql model is built on python type annotations and it's powered by pydantic as well as sql alchemy so we're going to see how to use that in this video now the first step in this video is we're going to actually set up a database so i'm going to create a new file within this project and it's going to be called database.pi and this file is going to contain the setup code for our database and i actually wrote a blog post on this quite a while ago so we're going to follow the steps throughout this post here the first step of course is to install the sql model library so let's go to the terminal and we can run the pip install sql model command and once that's installed we can go back to the documentation for sql model and on the right hand side there's an example and we're going to follow that to set up the framework and we're going to set up a sqlite database so let's scroll down here and we have a few inputs i'm going to copy these into our application into the database dot pi file as well as optional we're importing a couple of things from sql model field and the sql model base class this is what we're going to extend when we create a class that represents a database table under the hood and if we go back to the documentation you see that it creates a class here and it's called hero what we're going to create is a track class that represents a music track this is in line with what we did in the previous tutorial as you can see here we have a pedantic model representing a track and what we're going to create now with sql model using this bass class is something that's very similar and it's going to represent the structure of the underlying database table so let's create a class and we're going to call it track model and this is going to inherit from that sql model class above and like the documentation we also specify table equals true which will tell sql model that we want this to be a database table now within the class we define fields that represent the columns in our database table going back to the documentation you see there's columns here for id name and so on and these each have a type associated with them for example a string data type will map to a varchar column in the underlying database and that's what these type annotations are designed to do they tell sql alchemy what the data type should be in the underlying database but they also provide validation because this sql model class actually inherits from the pedantic base model so as well as database structure we also get validation built into these classes so let's now define these columns within our class we're going to copy this id column to the table because our data does have an id and you see that this is an instance of the field class that we've imported from sql model it's an optional integer data type and it has a primary key equals true parameter and that tells the table under the hood that this is the identifier for each row now what we're also going to do is basically copy these from the pedantic model that we defined in the last video and we're going to paste them into the class like that and we also need to import the date time object at the top here so this is our sql model class and it represents the data that we're modeling and what we now need to do is actually create the database and create the table under the hood so what we're going to do is go back to this blog post that i wrote and here we have a function called create tables and i'm going to copy this into our file just down here below that class and this line within the function says sql model and there's a metadata field on that with a create all function to that we pass an engine that will interact with our underlying database which might be sqlite or mysql or postgres or any other database what we now need to do is create that engine now we're going to work with sqlite which is a very simple database it's just a file so let's go back to the blog post and we can see a couple of attributes at the top here we've got a database file and an engine definition so let's paste these into the file and finally we need to import the create engine function from sql model so we have a database file it's called db.sqlite3 and then we define an engine on line seven we call the create engine function and we pass a reference to this file that we're going to create that represents our database so now we have an engine definition that we've created with this create engine function the final thing we're going to do in this file now is create a main function and within that we're going to call this create tables function which is going to create the database and the table so let's go back to the blog post and we're going to copy this bottom part here and we'll paste that into our file at the very bottom now what this means is that when we call the database.pyscript using a command then these lines are going to be fired and it's going to call this function which creates our table using this sql model metadata create all function and there's more information about this function here i'll leave this link in the description so let's try calling this file now it's going to be python database dot pi and we should see some statements have been executed you can see a create table sql statement being executed here and that creates all of these columns and also specifies the primary key so that should have created our database and you can see on the left hand toolbar we've got a db.sqlite3 file and we can inspect this database using the sqlite explorer and you see that there's a track model table with those columns so that seems to have worked let's now convert our application which is in main dot pi to use sql model and a relational database now remember the first step was to load this json data into the application what we did in the last video is we used a startup event and we loaded it into memory into this data variable here on line 13 which is a simple python list we're now going to demonstrate some sql model here and in this startup event we're going to change the way that data from the json file is loaded and we're going to load it into a database instead of an in-memory variable now in order to do that we're going to need to import some of the things that we've just created in the database file firstly we're going to import this track model class that we've created that represents the database table and we're also going to import this engine here remember we need the engine to actually interact and query the database so we need to import that so let's go back to main.pi and at the bottom we're going to import from the database module the track model and the engine and above that we're going to do a couple of imports from sql model we're going to import the session object as well as the select statement and we're going to use these to query and insert data into the database we're going to see how to do that now and the startup event now let's clear out all the code below this data file we're going to point still to the same data file which is tracks.json you can get that on the repository but now instead of loading it into memory we're going to load it into the database now the first thing i'm going to do is create a session object and we're going to pass the engine to that and that will create a database session that allows us to query and interact with the database once we have that we're going to check if the database already contains the tracks that's so we don't create duplicates when we start the server so in order to do that we set up a database statement and we're going to select all of the track model instances basically all of the tracks in that table and when you want to select all rows of a particular type you can create this select command here and you pass to the model that you want to get the data for so that's the statement to actually execute the command and get a result we're going to use this line here it's the session object which has an exec function we pass the statement to that function and that will give us back the objects from the database and to get the first row from the database we can call the dot first function that's very similar to what you can do using the django orm that will give you the first row in that result set now the purpose of that query is to check if there's any rows in the database what we want to do if there's no rows and only if there's no rows we're going to load the data from that json file so we can say if the result is none then we're going to load the data from the file using the same code from the previous video we opened the data file in read mode we load the json data into a dictionary called tracks and then we are going to iterate over those tracks and add them to the database now we're going to see how to insert data using sql model so for each track in our json data we're going to use the session objects add function and to that we're going to pass a model now we have a json record for the given track that we're iterating over so we're going to create a track model instance from that and we're going to destructure the track that we're iterating over to create that and that'll pass the data in this dictionary as keyword arguments to the model so in other words for every track in our json we're creating a track model instance and we're adding that to the session here and after that's completed we're going to commit these changes to our database so we call the session.commit command and that's all we're doing in this function but we do need to close the session as well so we call the session.close function and it's worth noting that later in this video we're going to use a context manager to create the session and that will allow us to do that without having to close it off at the end so once this is done what we're going to do is start up our uvicorn server and when we start the server it's going to invoke the startup event and that should hopefully load the data into the database let's check the sql explorer here we're going to show the table and you can see we now have data in our database so that seems to have worked what we're now going to do is we're going to modify our crud api rather than working with the list of data we're now going to query the database to make these changes to get data to update data to create data and to delete data so let's start with this simple endpoint here this is the one that gets all of the records from the data so for this endpoint we simply want to select all from our table so let's see how to do that now so in order to query all records from the database we're going to use a context manager to create the session we pass the engine to that session as we did before and now we don't need to manually close the session at the end of the function we then create a statement and we're going to create a select statement and we're going to select all of the track model instances we can then get a result using the session.exec function and to that we pass the statement above and then we can call.all to get all of the rows from the database and rather than returning data we're going to return within the context manager we're going to return the result and all of the parameters to the app.getdecorator can stay the same we're still returning a list of tracks this time it's going to be results from the database that we grab using this query here so let's go back to the api here and we're going to test this out and see if it still works you can see that we are still loading all these json records and it has the data that we expect so that's the first function done to get all of the tracks what we're now going to do is modify this function here which selects a track by its id so let's start by clearing these lines here and again we're going to create a session object so i'm going to copy this code here to create a session we're going to copy that down here and in a minute we're going to refactor this and create a dependency that will inject the session so we don't need to keep writing that in every function for now we're just going to create another context manager and we're going to find this track by its id now if we jump back to the sql model documentation you can see that we have a shortcut to get an object by its primary key because as it says that's a very common operation and there's a session.get function to that you pass a model instance as well as the primary key itself that you're searching for so we're going to use the session.getfunction in our code here remember the track id is passed in the url as a dynamic parameter so what we're going to say is the track is equal to session.get we pass the track model that's the sql model class as well as the track id itself and that will search for that track id in the database and we're then going to check if the track is none so we'll tab that code from the previous video over if it is none we'll return a 404 not found otherwise we're going to return the track itself nothing else here needs to change let's test that out on our api now here's the data as you can see for all of the tracks if we add the id of 1 to the url and if we execute that we see we get back only the track with the id of one so that's working fine let's see now if it works for an id that doesn't exist such as 9999 and you see we get back track not found so that's all working fine we now have an endpoint that allows us to get tracks individually by the id and it uses the session.get function from sql model now we're going to move on to the post request in a minute but before we do that you should notice this repetition here within two different endpoints of the session we're creating a context manager and two of these endpoints and we're going to have to do that in all of them because every one of these endpoints will interact with the database therefore it needs a session now we don't really want to write this code in every function so what we're going to do is create a fast api dependency and we can look at the documentation for fast api to see what that is it allows you to perform dependency injection and your fast api functions now i'll link this page below the video on the right hand side there's a section for creating a dependency now the key point is a dependency is just a normal python function and this function either returns or yields a particular object that you can then inject into your fast api functions themselves using parameters here and you can see this depends construct from fast api and that's exactly what we're going to do now and we're going to follow the guidelines from this page on the sql model documentation and i'll link this one below the video as well and what we're going to do is create a dependency that yields the session and then we can inject that into our functions in order to do this we simply have to copy this code here and we need to paste it into our file so i'm going to do that at the top just below the startup event we paste that function and this function is called get session and we can then use that in our fast api functions by injecting them as parameters if we go back to the documentation you can see that how we use this is that we specify a session parameter of type session and then we set that equal to a dependency using this depends construct from fast api and we pass our reference to our function to that depends construct so let's copy this code here and we're going to inject that into the two functions that we've already created firstly the track function and also we're going to copy it into this function here that gets a track by its id and we also need to import the pens from fast api so we can do that at the top and once we've done that we can actually remove the context managers from these functions so let's do that and tab over and the same with the function below here we don't need these anymore because we're using dependency injection so let's quickly check that these are still working by going to our api and we can see we get back the detail page and that also works for all tracks as well we're getting all of these objects back so now we don't need to create a context manager in every function now all of our fast api endpoints are going to use this session so to the post endpoint we're also going to inject it into the function now this post function takes the track as json data from the client and it's going to create a track object in our database from that data now we're going to replace all of this code including this line here that assigns to the id because we specified in the database model that it's a primary key we don't need to manually take care of creating that id anymore instead the database is smart enough to do that using auto incrementing schemes so let's just remove all of this code and what i'm going to do is we're going to change the track that's being injected here from a pedantic model to a sql model so now instead of just the pedantic class we're actually using this class here which remember it's a subclass of pydantics based model so we can also do our validation with this class as well as using it to interact with the database so let's go back to main.pi and what we're going to do within the body of this function is we're going to add the track data we get from the client to the session remember we're type printing it now as a track model and we can now add it directly to the session because we're type hinting it as a sql model subclass once we've added it to the session we can then commit it to the database and that will actually create the role within the database table and once we've committed we're going to refresh that track from the database now what that means is as you can see here the id is optional when a client creates a new track it sends json data but we don't want the client defining an id we want the database to create that id so when the client sends the data this will not be sent in the json data what we need to do within main.pi when we commit the track to the database it's going to create an id for us but to actually get that id back to a python application we need to call the session.refresh function and pass the track instance to that now this is well documented in the sql model documentation i'll leave a link to this below the video in their example they add a hero to the database and then they refresh that after committing and finally we're going to return that new track to the client and that will give back the data that we've created in the database so let's test that out using a curl command let's go to get bash and i'm going to execute this statement here it's a curl command that sends a post request and we're creating a new track in the database with this data here it's by the band sonic youth and it has some other fields as well we're now going to go and see if that has been created within our api if we refresh this page since it's a new track we're expecting to see it maybe at the bottom of this data and you can see down below here that it exists here so we're successfully creating new data in our database using sql model and you can see that we do that by adding an instance of the class to our session and then committing that to the database and as i said before when we actually send a post request as you can see here we don't specify an id we let the database define that id and that is returned when database object is actually created and that's because we use the session.refresh to get that id from the database after the object has been created so i hope that makes sense let's now move on to the put endpoint now the put endpoint allows us to change data that already exists in our database let's see how we can do that using sql model and let's start by removing this track from the function here and we're going to copy the code that gets a track by its id from this function here remember the session.get function i'm going to paste that in here and you can see that we don't know what the session object is and that's because we need to inject it into the function as we did with all of the other functions so we can do that here and to clean this up you might want to put each parameter on a new line but we're going to leave it like that and what we're going to do is if the track is none we simply return a 404 not found otherwise we're going to modify this code here the for loop to update that to work with sql model and our database so what we're going to do is we're going to take the updated track data and this is the data that's been sent to the server that we're going to update the record with we're going to take that and we're going to convert it to a dictionary so let's call that track dictionary and we're going to set that equal to the updated track converted to a python dictionary and we're going to use an argument called exclude unset and that is going to be set to true what that means is because we're sending data to the server we don't want to be sending ids we don't want to change the id of our record so that will exclude that id from the dictionary that's created from the pedantic model and there's a good explanation of this in the sql model documentation it's under this section here for getting new data basically if you call the dot dict function on a pedantic model if there's a value that's not set it will set that value equal to none and that's the case for our id we don't want that id to be none so pedantic models have this parameter exclude unset which you can set to true and if you use that any values are not set and the pedantic model will not be converted into the dictionary so that's the purpose of the exclude unset parameter let's now update the database record in our code we're going to update this for loop and we're going to reference the track dictionary.items here so for each key and value in the dictionary that we get back from line 81 what we're going to do is we're going to call the set attribute function that's a built in in python and the first parameter to that's going to be the track that we got on line 73 above that's a sql model instance and on that class we're going to set the key equal to the value that we're getting from the updated tracks dictionary so for the updated track sent to the server we convert that to a dictionary that does not contain the id or any other unset parameters and then we loop over that dictionary's items and we update the values on the track that we pulled out of the database here on line 73 with the new value and once we've done that we're again going to use the session and we're going to add the track to the session and then we're going to commit that change to the database and finally we're going to refresh that from the database and that will return the up-to-date data so now that we've done that let's test this in the command line we're going to execute a put statement and we're going to change the track with the id of 2 to that sonic youth track if we execute this we should then get the data back and we can then see whether or not that's been changed by going to our api and we'll refresh if we look at the track with the id of 2 which is this url here and we execute that we now see that that data has been updated so the put request has worked we're updating the fields based on the data that was sent to the server and this put request using the dash d parameter as you can see here so that's the put request we're now going to finish the video up by using a delete request in order to remove data from the database so let's scroll down to the delete function here and what we're going to do again is inject the session into this function using the dependence and we can remove this code here on line 94. again we're going to fetch the track from the database so we're going to get the track by the id that's in the url as you can see here so we're getting a track model instance and we're getting it by the track id and the url again we're going to check if that track is none and return of 404 not found if so otherwise we're going to remove these lines here and we're going to delete this item from the database so we use the session.delete function that exists on the session object and we pass the instance to that the track instance that we got from the database on line 93. and once we've deleted that we need to commit the change to the database and that'll actually make that change happen once we've done that that's basically all we need to do for our function we can now test it out on the command line let's try removing the track with the id of one to do that we specify a delete request using the dash x delete and then the url we have track id 1 here let's execute this and we'll see what happens we don't get a response back on the terminal but if we go back to the application and the juvicon server here we've got 200 okay for that delete request that means it's hopefully worked fine and we can go back to the browser and we can go to the api if i replace the id with one here and we execute that we get track not found so that has been deleted from the database so that's all for this video we're using sql model and relational databases to create this crud api with fast api and we have endpoints for creating data for updating data and deleting data as well as endpoints for retrieving all data and retrieving items by their id and we're using sql model here we're using the session that we're injecting as a dependency and this is a very basic example of an api for track data and if anyone's interested in more videos with these tools we can delve into authentication with jwts we can delve into things like searching through records by query parameters so if you're interested in any of that leave a comment below and if you enjoyed this video please like and subscribe to the channel and we'll see you in the next video

Channel: BugBytes

Views: 17,958

Keywords:

Id: Jl39FZs-uz8

Length: 23min 3sec (1383 seconds)

Published: Tue Jun 28 2022