FastAPI Python framework - SQLAlchemy and Database integration

hi there in the last video we built this very simple htmx driven table here where we can dynamically load more data from our back end which is a fast api application now at the moment this is quite a dumb application because we're using a hard coded list of film objects within our fast api function what we want to do now is add a database to this application we're going to add a sql lite database and we're going to set that up using the sql alchemy orm in python and we're going to be able to get this data from the database rather than hard coding it so let's dive in and we'll see how this can be done now we're going to reference a particular page in the fast api documentation and it's this page here about relational databases now as it says we don't need to use a database if we don't want to but we can and we can use any supported database such as postgres mysql oracle and sql server but we're going to use sql lite because it's very simple to set up it's actually just a file on your file system and python also has integrated support for sqlite so we don't need to install any drivers we're just going to use that in this tutorial so this is the page and it contains all the code you need to set up a database we're going to work through that in this tutorial now if we go back to the project now there are a couple of changes from the last video i have embedded all the code within a sql app directory so within that we have our templates directory containing the html templates and we also have a main dot pi file with a fast api function as you can see here it's called movie list you should follow this structure in your own application and make sure you change the directory referenced when you're setting up your templates to sql app slash templates now what you want to do to begin with is we want to install sql alchemy and if we go to the sql alchemy documentation you see that it's a python sql toolkit and an object relational mapper that gives app developers the full power and flexibility of sql so we're going to use that here and it's very commonly used with fast api when you're integrating relational database applications and we can install sql alchemy with pip so if we stop the server that's running at the moment and we say pip install sql alchemy then that will install that onto your environment and you'll be able then to use the package so now we're going to get started with this tutorial we're going to create a couple of files within the sql app directory i've already created a net.pi but we're also going to create a couple of extra files here and the first one is going to be database dot pi and this is going to contain our sql alchemy setup code so let's start with this one and we're going to grab some code from the fast api documentation so let's go back to this page here now there's a right hand sidebar here and one of the headers it says create the sql alchemy parts if you click that we can basically just copy this code into our database dot pi and we'll walk through what this is doing now so first of all we set up a sql alchemy database url and that points to our sql lite file that's going to be our database file then we use that sql alchemy database url as the first argument to the sql alchemy create engine function when you create the engine you point it to the database file and we have some extra keyword arguments here which we don't need to worry about and we also have a session maker now this is what creates a database session we bind that session to the engine that we've created in line eight and that is stored at a session local class now we can instantiate that class in our fast api endpoints when we want to create a database session and finally we have a base class that's retrieved by calling declarative base this is going to be used when we create a model to represent the film objects that we've got here in the films list the films list has two fields name and director we're soon going to create a model that represents that and sql alchemy allows you to do that the models will inherit from this base so we can save this file now and go back to the documentation and on the sidebar here we also have a section for creating the database models and by the way if you're interested in what each of these lines does fast api has fantastic documentation where they are walking through what each line is doing and what it represents so you can check this out if you want more information for now we are going to create a model so what we're going to do is copy one of these models that's defined in the documentation and then we'll adapt that to our film class so let's create a new file called models.pi and in there for now i'll paste this code and we can get rid of the relationship because we're not going to be using any foreign keys in this particular application so remove that from your model and we're also going to remove this boolean column here and we'll get rid of that import as well at the top so as you can see we're importing this base class from our database dot pi file this module here when we call declarative base we get a base object which will then importing into the models and the model class inherits from this base and we're going to change the name of this model to film and we're also going to change the table name from users to films as well so the table that will actually be created in the database will be called films now we're going to keep this id column because we need a primary key for our table and that's going to be an integer field you can specify that this column is the primary key with this keyword argument and you can also create the index with the index equals true keyword argument now we're going to create two fields the first one is going to store the name of our film and that's going to be a string data type so that will be varchar in the database and we also want the name of the film to be unique because we don't want duplicates stored in the database so we'll keep that unique constraint there and we can get rid of the index because we don't need that now the other field in our data was the director field as you can see here so we're going to copy that and into our model we will create a director field here and it's going to be a string field and this string field doesn't need any unique constraints or indexes or anything like that so this is perfectly fine that's all we need for our model so that's the basic setup of the model and indeed the database objects as well that we're going to use so the next step is actually to create the database as you can see on the file system we don't have a file with this name sqlapp.db that's what we're going to create but how do we do it now the simplest way of doing this is documented in the fast api documentation if you go to the sidebar here and we can scroll down to the main fast api app we can skip pedantic because we're not using json api responses we can simply go to this section here for creating the database tables and this is a simple line of code that we can add models.based.metadata.createall and we bind that to the engine and we put that in our main.pi file so let's paste that in to the main.pi file containing our fast api routes just above the app object i'm going to paste that in there and we need to do a couple of imports here so from the current directory we'll import the models and we're also going to import the engine as well as the session local class from our database dot pi file so from the database module we import session local and the engine so this line of code is going to create the database tables if they don't already exist when you start up the server but i will add a comment here you would normally want to use migrations to do this and to properly manage your database tables and manage any changes to those tables now migrations are built into django using the django orm but you can use migrations with sql alchemy using a library such as alembic which i will link to in the description but this is a simplistic way that's going to get us up and running for this tutorial so we'll use that for now so what i'm going to do now is start the uvicorn server and hopefully we'll see the sqlap.db file being created so let's run the server here and we should now see as you can hear the sqlap.db file and using the visual studio code sql lite extension we can open this database and hopefully we'll see a film table within it so if we extend this you see we've got a film stable with three columns id which is an integer and two varchar columns name and director and of course these correspond to the columns that we set up in their models.pi file for the sql algorithm model so that's our setup done what we now want to do is use the database and actually get objects from it when our fast api route is called so how do we do that now first of all let's quickly explain the session local class we want to use that to actually create a session each time a request comes to our endpoint we're going to create a database session do our query get the film objects from the database and then we'll return those to the front end so what we're going to do is inject a database dependency into our fast api function this is the function here it's called movie list we're going to use a dependency injection mechanism that's built into fast api to inject a database object so let's see what i mean by that but first i'm going to format these parameters so that we can read this a little bit better when we start adding more save that and we'll go back to the documentation so just underneath the creation of the tables we've got our dependency section so as it says here we need to have an independent database session or a connection for every request and we use the same session through all of that request and then we close it once the request is finished when another user comes in or even the same user with the next request that'll create another session a new one in the database so what fast api recommends that we do is we create a dependency that yields this session local object that you can then use to do your queries so let's see how we achieve that by copying this code here this is the dependency called get db so we're going to paste that into our main.pi file just below the templates let's paste that in there and basically this instantiates the session local class and that gives us a db object we try and yield that db object to the caller and within this dependency we finally close this session at the end of the processing so this is our dependency how do we actually inject that into our fast api function let's go back to the documentation and underneath this dependency you can see that we have some functions here and we're injecting the database session here with this line here so let's copy this just now and we'll put that into our function as an argument so database is an instance of session and we set that equal to a dependency on the getdb function now we need to import this depends object from fast api so we can do that at the top we import the pins and we also need to import the session from sql alchemy so i'm going to paste that import at the top here from sqlamy.org import session so we can save that now and now within our function we now have access to this database session and then we can query the underlying database to get film objects or any other objects you might have in your tables so what i'm going to do for now is we're going to comment out or rather we're going to delete the films that we've got here and instead of hard coding that list we're now going to create a database query using this db object that we've got now injected as a dependency now sql alchemy sessions have this query function that you can use and we pass a model to that and it's going to be the film model that we created in the models.pi file so we're querying over that table and we can just say dot all here to grab all of the records in that table so that should hopefully get all the records if we print those out we should be able to see them in the terminal now of course we've just created this database there are no records in the table so we should see nothing here if we refresh the page on our front end we now see that there's nothing in this table and the terminal shows an empty list that is the print statement on line 34 here and what we actually need here is a mechanism for populating a database so we're going to use something called a startup event and startup events allow you to perform processing before your application starts in fast api so let's go to the documentation quickly and we can see what a startup event is now this is under the advanced user guide on the docs if you scroll down you should see less startup events somewhere here events startup and you can also have shutdown events as well which happen when your server is shutting down and as the documentation says you can define event handles which are just python functions that can be executed before or after your application has started or stopped so to do this we use a decorator as the app.on event decorator so let's paste that into the main.pi file we'll do that below the dependency here now this is beginning to get a bit unwieldy this file you would maybe want to break this out into different modules but for now we're just going to put everything in this file and let's define the function and it's going to be called startup populate db so the purpose of this function is to populate the database when we start our fast api application so what we're going to do within here is get an instance of this session local basically we're creating a database session here and then we're going to get the number of films that exist in the database we can use a query to do that and it's going to be the query over the film model and we used the dot all method earlier we're now going to use the dot count method to return a number representing the number of records in that table and we're then going to do an if statement and we're going to check whether the number of films is equal to zero and in the case that it is equal to zero we want to then populate the database with some new records so we're going to create six films in the database i'm going to paste this code here it's a list of six films and we can iterate over these films so for film and films and for each film we can add to the database session an instance of our film model so models.film and we can unpack the keys and the dictionary for each of the films we're iterating over by using this syntax here so we're adding to the database session a film object for all of the films in this list of dictionaries and then finally we want to call the database.commit function this will commit our new changes to the underlying database so we should then see in this block of code that we have created six objects six rows in our database table now in the case where the number of films is more than zero it's not equal to zero we're just going to print this number of films already in the database statement to the terminal when the application starts so this is only going to add these films if the number of films is equal to zero so if we already have films nothing's going to happen except this terminal print statement so what we can do now if we stop the server and reload we should see that there are six films already in the database and that's because when we save this particular code what's happened is uvicorn has reloaded and then it's input the six films and when i manually stop and start that has already got the six films in the database so that's why we see this line here so now if we go to the front end again we should refresh this page and we actually get the six films that are now in our database and if we inspect the fast api function at the bottom of main.pi this is actually coming from line 53 where we query the film's database table you can see the print statement of these film objects here so this is now being driven by a database which is arguably the way most applications would do it we don't want to be hard coding data we want it to come from a reliable database store so that's all working fine we want to now demonstrate how to use pagination and this view and paginate our database records so we want to pass the page in the url so what we can do in our url for example is we could define a url parameter page equals one page equals two and so on so we can do that and then we can use that page extract it in our fasta api function and then use that to get the correct set of records from the database so let's see how we can do that if we go back to our function here we're going to define another argument to this function and it's going to be an integer which defaults to 1. now url query parameters are accepted as arguments here this will try and match an argument here in the url called page and if it finds that it will use the value otherwise it will use the default of one and i want to add that page to the context here so we'll add another key value to our context dictionary so now that we have that we can access the page in our templates what we want to do when we hit load more is we want to increment this page now by default it's one so when we hit load more we want to get the next page of data so the page would then be two so we're going to attach the page as a url parameter to our button so let's go to our template now and it says index.html template and this is the hx get request it's here that we want to attach the page parameter and we're going to use the current page value and we're going to increment that by one as so so if we save that and refresh the page we should see when we hit load more we are getting more records but it's not actually using the page data yet but you can see that it's getting the page here but the problem is the actual content here in the index.html is never replaced we need to move this button into our table because this is the partial that htmx deals with if we don't replace the button then it'll always be page equals two if that makes sense so to demonstrate what i mean let's cut this button out of this template here and we're going to move that into the table.html so we have a for loop in here that iterates over all of the films in the context what we're going to do below that is we're going to paste this button and we're going to place it within a table row so we'll place a table row around this button and we can close that off here like that and we want this button to be in a td and the call span should be two there's two columns and the table on the front end so we want the call span to be two here so let's fix the indentation of this button here and if we now refresh the page we have the button at the center of this table it's called span is two so that spans the two columns and if we hit load more we're not getting the effect we want at the moment but this is what we're building towards so what we want to do if we refresh the page when we hit this button we want to replace the button the table row containing the button with the new content and this new content is returned and this partial so we get a new set of films based on the page as well as the new table row at the end containing the button to load more data so this button at the end is continually going to be replaced with the new data coming from this for loop as well as another button placed at the end so to do that we need to change the hx target what we want to replace is the closest table row so that's going to be the parent table row for this button so what we can do is use the closest table row here closest tr and the hx swap mechanism we want to swap the outer html and that's because we want to replace this table row containing our button with all of the new content coming back from this partial so if we save that and go back to the page when we now hit this button we see that we're getting more rows in the table and the new buttons placed at the end and that pattern can continue indefinitely so the final step in this tutorial we want to actually paginate the database data that's coming from sql alchemy now to do this we're going to go back to our fast api function in main.pi let's get rid of the print statement here and what we're going to do to this films query is we're going to amend this we don't want to return all of the records we now want to paginate the data now i'm going to create two variables to help us do this the first variable will be called n and this is going to determine the number of objects per page and we want to set this to two for demonstration purposes and rather than returning all of the records we can limit that to the n records that we want so if we save that and go back to the page instead of loading six we now get two but the problem is it's always the same two that are loaded so we also need to add an offset that is based on the page number you can see the page number coming in here it's incrementing from one to two to three so we need to set an offset based on this n here and based on the page so to do that let's create an offset variable and we're going to do a little bit of maths to calculate what this should be it should be the current page number minus 1 and we multiply that by n to get the offset that we want and when we have that we can then add an offset to this column so it's going to be dot offset and we'll pass that calculated value there and then once we have the offset we'll limit it to n results after that so let's see what happens now on our table in the front end we start with two records then we hit load more and we get the correct offset for the next two records and we load the next two records and this can keep happening until we have six records at which point we're no longer going to get more records because the offset is actually greater than the number of records in the table so just to demonstrate why this works when we get the first two records the page number is one here so one minus one will be zero so the offset is going to be zero in this case so we start from the very first row and the database table then when we hit the button here it's going to load the next two records and you can see here the page is now equal to 2 2 minus 1 is 1 and we multiply that by a number per page which is 2 1 times 2 is 2 so our offset will be 2. so this will basically start after the first two records in the database table the offset will be from record 3 and we'll get the next two records so i hope that makes sense that's all we want to cover in this video we've learned how to add a database to our fast api application we've learned how to do sql alchemy models to represent our data and we've learned how to inject dependencies into our fast api functions as well as how to do startup actions using the app.on event decorator and finally we've learned how to perform basic queries using sql alchemy such as getting all of the objects getting a count of the number of objects and also how to do offsets and limits and this is all integrated with htmlx on the front end so hopefully you've learned something about how to use fast api and databases with the hdmx library if you've enjoyed this video please like and subscribe check us out on twitter and we'll see you in a future video

Channel: BugBytes

Views: 21,458

Keywords:

Id: 8SPF6TBVj28

Length: 22min 11sec (1331 seconds)

Published: Sun Feb 20 2022