How to build a FastAPI app with MySQL database (step by step guide)

in this fast MySQL tutorial I'm going to show you step by step on how to set up the world's most popular SQL database with the fast API application my name is Eric Roby I'm an instructor a software engineer and a cloud Enthusiast my channel covers everything Tech and with that let's dive in and let's set up MySQL with fast API alright friends in this video we are going to set up fast API with the most popular relational database MySQL so the very first thing as I always do in every fast API video let's go ahead and open up our terminal and let's create a virtual environment for our application in this virtual environment we'll siled this fast API and MySQL application from all the other python applications you may have on your PC so let's go ahead and say python3 Dash m v e and v e and V when you do this you'll see that we created an environment within our directory and now we can go ahead and install all the dependencies we need for fast API to work with mySQL now what we need to install is fast API uvicorn SQL Alchemy which is the orm the object relational mapping for our fast API application to be able to communicate with our MySQL you know do some of the queries for us we can just use Python to fetch data from the database and then lastly we need to install Pi MySQL which is a connection that will make our fast API application be able to communicate easily to our mySQL database after you type that in let's go ahead and say enter and I totally forgot to even start my environment so from here let's go ahead and say Source EnV slash bin slash activate once you do that let's go ahead and just redo our pip install of fast API uvicorn SQL Alchemy and Pi MySQL and that will install all the dependencies we need on our application all right so now the very first thing we need to do is let's go ahead and just create our database dot Pi file this is going to have all of our connection strings for our application to be able to connect it to my SQL so the very first thing we can say here is from SQL Alchemy we want to import our create engine and this will be able to create the engine for our database to communicate with our application we want to say from SQL Alchemy dot orm let's go ahead and import our session maker and then let's say from SQL Alchemy dot EXT dot declarative and from here we just want to import a declarative base so this is awesome stuff we are getting closer to being able to connect our database to our fast API application so the next thing we want to say here is URL to our database and let's just leave this empty for now until we set up our mySQL database and then let's go ahead and say engines we need to create a engine variable with our create engine where we will pass in our URL database and then let's go ahead and say we need to create our session local which is going to be equal to our session maker where we can say Auto commit is equal to false we can say our auto flush is equal to false and our bind is equal to our engine so last thing we need to do now is just go ahead and say base equals our declarative base now once you do this we are like 99 connected to our database we just need to get our URL database but we'll do that in just a little bit the next thing we want to do is go ahead and create our models dot Pi file now our models.pi file is going to be what SQL Alchemy which is our orm uses to be able to create the tables that we need in our mySQL database so it's super powerful where we just need to create the database and then SQL Alchemy using this model that we're about to create and some other keywords will initialize and instantiate the database table that we need with the columns that we need to be able to save records so this is an awesome thing to be able to implement into our fast API and MySQL application so from here we can just say from SQL Alchemy we want to import booleans columns integers and a string and we want to say from database import our base so what we just made in our database which is our base equals declarative base we want to then bring this over into our models.pi all right so the application that we're building is going to have a users and posts so it's like a mini simple blog application where a user is only going to have a username but they're going to be assigned to a post and that post is going to have a couple different pieces of data so we are essentially going to be creating two MySQL tables that are going to be communicating with one another for our application so what we want to do here is just say class user and pass in base and we can instantiate the table and name the table right here by saying underscore underscore table name underscore underscore where we can just call this the users table database all right and now let's say and now we need to create our first column which is going to be an ID and we want to say this is going to be a column where it's going to be an integer it's also going to be our primary key so we can say primary key equals true and then we want it to be indexable and we're going to make that equal to true and index just means it's going to increase the performance of my SQL a little bit when we search by ID so essentially we're just saying that this is going to be a term that we're going to be using to fetch data from the database we also want the user to have a username so we can say username equals column where we pass in a string and we want to instantiate the string with a varchar of 50. so in the database it's going to be a varchar which means we need to tell the database how long the string is going to be so we can say it's going to be a string of 50. and it's going to be unique so a primary key will always be unique but since a username is not a primary key we just also want it to be unique because we don't want multiple users in the application to have the same username we then want to say class post and pass in base and now inside here we want to say underscore underscore table name underscore underscore and we want this equal to posts and we want to say ID equals column so we also want our post to have a primary key which is going to be an integer primary key is going to be equal to true and it's going to be able to be indexed we want the title to also equal a column where the string is going to be 50. we want the content to equal column where the string will be equal to 100 because we want to be about twice as long so the title is going to be 50 characters and the content can be up to 100 characters and then we want the user ID which is going to be the foreign key that tracks it back to the user database and we want this to just be a column of integer so here we have two tables that we're going to be creating a user known as users in the database with an ID and username and a post which is going to have a table name of posts ID title content and user ID all right right on now the next thing we want to do here is right click and say a new main.pi file which is going to be the main application for this fast API application which is going to be the main file for this fast API application we need to say from Fast API import fast API and while we're here we might as well add the other dependencies that we need from Fast API so we can say HTTP exception we can say depends which is our dependency injection and then we can also just import status we want to say from pedantic import base model and this is going to be our data validation we then want to say from typing import annotated we want to import all of our models from our models.pi file and then we want to save from our database import engine and our session local and then lastly we need to import our session from SQL alchemy.orm so we can say from SQL alchemy.orm import our session all right so this is awesome awesome stuff now as always with every fast API application we need to instantiate the app so we can say app equals fast API with parentheses and we want to say models.base Dot metadata Dot create all where we can pass in our bind that is going to be equal to our engine and now we can move on with creating our pedantic models so the very first model that we want to create in this application is for our posts so we can say class post base and pass in our base model where we can say we want a title of string content of string and a user ID of int now again we're using this for our data validation so the request that comes in we want to be able to validate the data and then return the response that's correct from the database we then want to say class user base where we're also going to be calling the super of base model but this one's only going to have a username of type string all right great stuff now we want to move on and create our dependency for our database and we can do this by saying def get DB and inside here we want to say DB equals our session local we want to try and yield our DB and finally DB Dot close and what this really means is we're going to be creating a method of get DB where we can create a DB of our session local we'll try and get our DB but no matter what happens we're always going to close our connection because we don't want to keep our connection to our database open for too long now the next thing is we need to create a annotation for our database for dependency injection where we can just say DB underscore dependency equals annotated where we pass in our session and this depends on get TV all right right on now we can kind of move on to creating our mySQL database and that connection string that we need to our get to our application so the very first thing we need to do is let's go ahead and open up our MySQL workbench and now if you don't already have my Sequel and MySQL workbench installed I will be creating a video on how to install my SQL and my SQL workbench on your local machine you'll be able to find it somewhere around me and you'll be able to switch to that video watch that video and then come back but for the time being let's go ahead and just say right click and when we right click here we can say create schema and that's going to be on the left hand side we want to say create schema and inside of here let's go ahead and just say blog application we type that in the schema name and then let's come down here where we see apply and let's click apply and then it'll give us what the script is going to look like where it's create schema blog application and we can just click apply again and then close when you do that we can see that we now have a new blog application and this is going to be a database this is a database or blog application is a database but we currently don't have anything in our tables so let's go back to our application let's hop into our database.pi file and right here we want to say MySQL Plus hi MySQL colon slash slash and now we need to type in the username and for my local environment it's root and then colon our password and for me it's test1234 exclamation mark and then we need to say at our Local Host and then our localhost MySQL sits on the port of 3306 by default so just say colon 3306 and then we need the name of our database and mine is blog application all right so once we type all that in let's go back to our main dot Pi file let's now create our first API endpoint where we can create and save a user and we can see that we're going to instantiate these tables in my SQL automatically using SQL Alchemy and fast API so from here let's just go ahead and say app dot post and in here we want this to be slash users and we want this to return a status code of status dot HTTP 201 created let's make this an async Def of create user where we can pass in our user of user base for our data validation of pedantic and then DB of DB dependency let's then say our DB user is going to be equal to our models.user and inside here we can say star star user.dictionary of user.dictionary and what this means is we are able to get all of the data from our data validation of user and we can deserialize it into an object that we already have defined which is user and then we can say DB dot add our user and then DB dot commit after typing that in and we want this to be inside here actually so DB user inside our ad before committing now let's go back to our terminal and let's go ahead and say uvicorn main colon app dash dash reload and now that we're here let's open up this user and let's go ahead and just say try it out and we'll call this user coding with Ruby let's then scroll down and say execute here we can see that we get a 201 and a null response body which means our API endpoint request was successful so now let's go into MySQL workbench and here we can see our blog application database and now let's right click on our tables and then say refresh all when you do that we can see that we now have a post table and a users table if we say select all from Posts which we can say that by saying select star posts and we click this lightning bolt we can see that the table is currently empty but we do have an ID title content and content and user ID if we change this to users and we click the lightning bolt we can see that there is a record in our database and it has an ID of one and a username of coding with Roby alright so let's go back to our code and let's create a new API endpoint to be able to fetch this user and we can do this by saying at app.get and inside here we want to say slash users slash and then I'm going to pass in user underscore ID and we want this to have a status code of status dot 200 okay we then want to say async death read user where we can pass in a user ID of type int and a DB of our DB dependency we then want to say user equals DB dot query where we want to pass in our models of our user where we can filter by models.user.id and seeing if that's equal to our user ID and then just return the first element from our database that matches this filter we then want to say if user is none we want to raise and HTTP exception with a status code of 404 where we can say detail equals user not found however if the user is found we want to return that user so if we go ahead and try this application and we close the post and we open up the git and we pass in a one as the ID for the path parameter we're going to get our coding with Ruby user returned all right so now let's go ahead and move from users and let's go ahead and create the posts so the very first thing we want to do here is I'm going to do it above these user dependencies app dot post and inside here we want to say slash posts comma our status code is going to be status dot http 201 because we're creating the post async Def create post where we pass in a post of our post base which we're using for by dantic for our data validation which is going to accept three things our title content and user ID and we need our DP of DB dependency we want our DB post to equal our models.post and inside here we can deserialize post again by doing star star post dot dictionary we can then say DB dot add and we want to pass in our DB post and then DB dot commit all right so we go back to our application now and we refresh and we say we want to use this post we can say try it out and we can say title of learn fast API this is a super cool piece of tech and we want the ID B from our coding with Ruby user so one and then we can say execute and here we're going to get null of 201 and we check and if we check out our MySQL workbench we can now say from Posts and when we run this we can now see there is a record for posts which has an ID of one title learn fast API content this is a super cool piece of tech with a user ID of one all right so let's just keep chugging along and let's create a couple more API endpoints for our post let's now go ahead and say at app dot get and here we want to say slash posts by the post ID so we are now checking for only one post based on the post ID so we want to say at app.get slash posts post ID and we want to say status code is equal to our status code of status Dot 200. we want to say async Def and here we want to say read host where we can pass in our post ID that is of going to be of type int and we of course want our DB of DB dependency all right and now from here we want to say post equals DB or we can say DB dot query and pass in our models dot post where we can then filter by our models dot post dot ID when this equals our post underscore ID DOT first and then as before so if post is none post is none we want to throw an HTTP exception with a status code of 404 saying that post was not found and we need this to be the detail equals and if everything's good we'll just return the post so let's go back into our Chrome browser and we will say we want to read the post pass in the one for the ID and there now we have literally the MySQL record of the post all right this is awesome stuff let's go ahead and just do one more request for delete where we can say at app dot delete and here we can say slash posts slash post ID where this status code will be a status of 200. and we can say async Def delete post where we will pass in our post ID of type int and again our DB of DB dependency and just like we did before we want to say DB post equals our DB dot query where we can say models.post dot filter our models.post.id is equal to our post ID and then grab the very first record that's returned from our database and if the DB dot post is none we want to raise an HTTP exception with a status code of 404 with a detail of post was not found we then can say DB dot delete pass in our DB post foreign so if we save this and go back to our browser and we refresh we know that we have an post with an ID of one and if we come down here we can now try it out and delete that exact same post so if we come back up here there is no post inside here so this is awesome stuff we have essentially created a fast API application using my SQL database if you like this video please give it a like or a comment and I will see you in the next video

Channel: Eric Roby

Views: 20,369

Keywords: fastapi, mysql, fastapi mysql, my workbench, fastapi mysql workbench, fastapi uvicorn, fastapi database, fastapi tutorial, fastapi python, pydantic, fastapi pydantic, fastapi data validation, fastapi python project, fastapi python tutorial, fastapi crash course, fastapi project, mysql database, mysql workbench, fastapi localhost, how to connect fastapi mysql

Id: zzOwU41UjTM

Length: 28min 9sec (1689 seconds)

Published: Sun Jun 18 2023