FastAPI - using SQLAlchemy for DB queries [part - 1]

hey folks welcome back so in the last video we talked about uh alambic so by using alambic we do the uh data database migrations so in this video we will uh use sql alchemy with a fast degree to create the apis so let's get started with the basic architecture of our app so yeah here it is so client sends a request and we will be having a main.pa file which consists of a fast api app and it connects the routers middleware configurations etc and when we send the request from uh app the request are passed to routers and routers uh sends the data to schemas and schemas we validate the data if the data is passed and from there we send the request to services um so by using services we do main business logic like validating and that stuff uh we want to make some database queries and then validate that so all that logic goes in the services dot p wifi and services connect to um queries uh to fetch the data from the database so this is the basic architecture of our fast api application so um let's talk about um sql coming a bit so this is the basic architecture of the sql algorithm so here we can see we have a sql algorim and sql algorithmic code we have our two components here so by using sql algorithmic core we talked to the database so we actually don't need sql uh algorim for this but we'll be using a both uh sql upcoming orm and blog cover to connect to the db and make some queries okay so um yeah let's uh go to vs code and start decoding yeah so uh to get started with our application so we need to uh install these uh packages so first api identity and sql algorithm and uh that's like a pg or binary so i already installed all these packages so let me try installing it again with install first api identic let me copy sql coming and psychopathy too so we can see that uh requirements are already satisfied so so without wasting this time let's get started let me um go here and create a app supposedly and from fast api import first api sequence to fast api so we have created the fsdb app and then let's run it let me go here and you'll be con src dot main app so when you change the uh i mean when we do some changes to the file so our app needs to be restarted so let's pass reload and let's run it so now we can able to access the url on uh browser so we already started the development server so let's go back to um browser and let's check it so we can see that our application is running and there are no operations demanded because we didn't create any so let's um go to our application architecture first and so in order to talk to the database we need to have the database connection and the queries needs to be ready so let's develop these two parts first let's go back to vscode again let's create um db and base so let's uh import from upcoming import create engine and from sql algorithm import session maker let's create the engine so here we can see that we have uh postgresql and then username so this is the string that we need so we can see the same thing in sql documentation as well let me go and uh show you that let's go here so here we can see that this is the thing that we need let me copy this and come back to our code let's place that so we need this database uh url let's change it to db1l and paste it here let's define the actual db url equals to first resistor and username postgres username postpass password and host and port and db name let's change this to dynamic uh because we don't want to use the uh load the uh passwords and the user names from uh directly into the code so we need to load that from environment so let's make those changes this needs to be db user and tv password db host db port so let's uh import os db user equals to start with dnv so this will uh get the variable from the environment and let's do the same for a password host port and dpn we also need uh db name so now we have the um db name so we are loading all this from uh environment itself so we need to export the data so let's do that so i already uh added all export statements in here so rdb name is awesome store and to use the postgres root with the password so you need to use the strong first but this is not recommended for uh um production so let's export all these let's stop our development server and export all these variables so we have that let's rerun our application let's go to uh so now we have the uh engine created so we need to uh use the session for this so let's create this session local session equals to session maker so we need to bind the engine for this find engine so we need to use this session so in order to use the session so we need to create object for this local session and use that so uh every time we don't need to do that so that's the reason we need to have uh two functions which will create the session and we can simply use that uh decorator and function if needed so let's create a function get system and doesn't need any parameters so um session equals to local session and try yield okay so we need to close the session after using okay so for this uh this doesn't work without the context uh manager so let's import that from context import context manager so now we have this and we need to use this in here so now we have the engine session so once we use the session so it will automatically close the utilization so let's have one more uh function our decorator to get the session in our queries let me create that session and it's a decorator so i am expecting a function so let's write the wrapper function inside this and um let's pass arguments and keyword arguments and now we need to uh use we need to pass this session uh as a first argument to the function so let's do that as this is a generator we need to use with statement to get this session so with uh english session as session and return [Music] function pass session as first um argument and the remaining args let's return wrapper so now we have this ready now we need to uh create queries and write the required functions for that so before that let's see the uh our database uh table so that we'll get to know what table we are using to connect to and insert and fetch the data let's go back to uh pg admin so this is the table that we will be uh using so it has id name description timestamp so um let's get back to our architecture so now we have the database connection so we need to implement these two things queries and models let's do that so let's create here so we need to create that on service level so model start uh yeah what else inside that we need to create um category so here we will be using sql again to uh you know create the table sql alchemy dot ext dot um declaring to import degrees so by using this function we will create a degree to base class this equals to the query to base so now we need to define the uh table or category so let's do that let's do one line so now um category let's inherit the base class and then in this we need to mention um table name a table is a category now we need to write all the required columns so um we already have the um some kind of uh table definition in uh questions that we already created so let's go there and copy the definitions from there let me copy these and come back in here let's paste that let's indent it properly id equals to and name equals to description equals to and timestamp equals to okay so we don't need these commas anymore but uh we need to import all these individual column and walker so let's copy that as well if we copy this that should do yep so now we have the table now we need to write the queries let's create um okay let's create queries inside that let's create a category dot py file and let's import session from the db that we create from db port base import session decorator let's write uh query um insert category so this needs a validated data for uh um for uh creating or sorry for inserting the uh data so we will be validating uh the data with fast epa sorry identic so we need to use that pedentic model so let's do that um okay let's uh okay let's expect a identic uh schema model here let's write model um yeah okay let's define the bident model first let's go here and create schemas inside that we need a category schema let's create category our viewer file and let's write these theme from identic import base model class category model inherit a base model and so we need two things here so one is name and the other one is a description so let's define that name which is a string so we need a constrained string con sdr con str here and the minimum length is 1 and the max length is 30 characters and we also need the description oh description which is also constrained string minimum length is 1 and maximum length is 255 characters so let's save this so we will be using this uh model so let's go back to queries and import it let me just for our typing from category import category model so let's use this as a type now we have this you know uh how to work with pedentic if not let's go back to the our previous videos and see how we can use identic so let's uh write the query so in order to do insert a query so we need to write sql algorithm so we will be using sql domain uh core components from sql alchemy port insert and we also need the table from uh models let's import that as well from dot models dot category import category so this is the table that we will be using uh we can call it as model as well um statement insert into category and values so this expects a date so we need to convert model bident model to date so we have the addict function on addict method on a pedentic model so modern dot date okay so now we have the statement we need to execute the query i mean this statement so i forgot to use station here so because uh we are using this uh decorator so this will pass the first variable i mean first argument as session so we get this session so we need to execute the query execute statement and yeah we need to save the result now we have the result we need to uh commit the uh query so session dot all right so this will commit and let's click on the cell dot inserted primary key let's return the primary key inside insider primary key so now we have insert query and um yeah so let's go back to our architecture and see uh the remaining components so we have uh insert category query um ready so let's uh um let's go to our article and see what uh what is there i mean what is there to implement let me go back to google chrome again and we can see the architecture so this part is done now we need to create routers and services and connect this to okay let's go back in here um yeah so we have yeah we need to create routers let's create a directory routers and we need to create file category dot py inside that now let's import from first api import api router let's create the router now we have the router let's define the endpoint router dot post categories and this is the posting point so we need to return uh two nut one status score to not one and create new category and this so we need to uh we expect the data here so let's uh use pedentic model which we already created in schemas let's import that from schemas dot category import category model so let's use that model category model and for now just written the uh data i mean the model data dot let's save that and go to my docs okay so if you need to add the router to app let me go back to vs code again so let me go to main.py file import the router from dot router start category okay let's import category modulation category so app dot include router category dot cloud okay so we use this um dot include router to include the creator router uh in the category so let's save this and go back to the docs and just uh test it let me go back to our google chrome and refresh it so now we can see the uh categories end point let's try it out let me send some um sports or some sports or something so we can see we are getting the two not one response score status forward and the data back but we need to um validate the uh software identity is already validating that so we need to insert this data into the database so we already created the insert queries but we haven't tested it it's the time to test it let me go back to the code okay let's go to routers and yeah so we are missing the um services layer let's create that so this is let's create category dot py file and create a category so we this will talk to our queries so let's insert the queries here dot dot queries dot category import insert category so we need a identity model for this category um okay but now we need to use this insert query to insert the data so let's directly pass the model okay but uh let's talk about the uh categories in point so if the category is already there then we don't want to create the category again so for that we need to talk to the db and if the category name is already there then we need to throw some validation error otherwise we need to insert the db i mean insert the data into the db so in order to do that we need to have one more query to check whether the category already exists with the given name or not let's go back to queries and implement that queries category and let's write session switch category by name okay name um yeah so we need to uh do a select query on this let's insert uh so statement select category where um category dot name equals true name so we can check all uh the query syntax in sql algorithm documentation so for now just uh follow this assume you know that and follow that um okay we need to we need to okay we i also forgot we need to have a session here because we are using the session decorator so let's uh do session result equals to sasha dot execute the statement and return reserve dot scale house dot one or none let's import the statement in services okay let's write if dot me is not none then we need to place um exception that category is already exists so otherwise so now we have this now we need to uh connect this to um routers let's go back to routers and let's use strike lock try exit [Music] as e so now we have that and return pk for now if there is any error happens then we need to return the um 400 response status code let's do that right okay so we actually raise exception and fast ap will automatically take care of that so raise http [Music] exception so oh not this we should be inherited sorry we should import that from fast api okay so we didn't have uh we didn't uh we didn't import the category create category from services so we need to do that services dot category import create category so now we have that okay we need to raise the exception properly status code equals to 400 and detail equals to tier of e so we convert exception to string and then pass it back so now let's go back to um docs and test the end point to do okay let me refresh and let's try it out name electronics and description let's execute we can see that we got the uh id back but that is not we wanted we need to get the proper response which is a json response let's go back and write the identity response model for this um let's go back to code now we need to return it so um we need to get the object from the database and return that let's go to sub services and write get okay category um [Music] so we need to get that by um primary key id so we need a pk as argument for this so um now we need to send the um um now we need to uh create a query and get the uh data from database let me go back to queries and write that first so now we are fetching the data by name so now we need to get this with the primary key which is apk let me replace this with id and this will be get because we need to have vk attribute on our table so id is the primary key for that so that's the reason we are using that now we need to save this and oh yeah we need to change name to id okay let's copy this and go to [Music] services and import that sorry yeah we put that here so now we need to return which category by id and let's pass the uh now we have these let's do that and come back to routers and now let's switch the object object equals to get category we also need to import this from services now we have that we need to pass the apk here let's return object so we have this in point id and let's go back to our docs in browser and let's try this out let's um change this electronics to sports and awesome sports you can have any description you want let's try this now we can see uh the data coming back let me try it again we can see that the category already exists so it's working fine but we need to have um identical response model if we don't want to um i mean if we want to skip some attributes or something like say let me change the spots to something and execute it so now we have all these uh let's say i don't want to send uh timestamp back then i just need to have a response uh model which will where i'll specify what needs to be sent back so let me go back to code again and specify response model that should be category response model so we haven't uh written that but we need to let me go back to schemas and uh class category model and base model we just need uh id integer oh yeah and we also need these two fields but so that's the reason i'm just inheriting the category model in here so that's it and so here if we go to routers so it's returning the sql algorithm object so which is uh which can be directly converted back let me so let's uh try that and see if this works category response model okay what's that one oh yeah we can ignore that let's go back to um browser and check the rocks let's try um something to we can see uh exception here so it's not a valid date so we need to pass the proper response or we need to specify over a mode so let me let's see what can be done okay if we go to identity so here we have a config that needs to be set to or remote through so if we do this so the error should uh should be fixed let me go back this code and add that to schemas yeah let's go to identity mode response model and add that so now let's go back to docs and try it out let's try awesome category description can be anything so yeah we can see that now we can see only these three attributes that we mentioned if we didn't want time stamp then we can do something like this so this is how uh we connect to sql alchemy i mean the database using sql alchemy and make the queries so let me simply explain this using the architecture so now when we send the request it comes to main dot py and then it will be passed to routers based on the request path and if there is any data validation the basic data validation that we perform using schemas and after that we send that data to services where we have the main logic that is written and the services connects to connect to queries i mean the db queries and queries talks to the db and which is the data back or insert the data into the database so this is how we use the sql algorithm to insert the data into the db so if you find this content is useful then hit like button share and subscribe to the channel

Channel: Learnbatta

Views: 1,594

Keywords: learnbatta, fastapi, sqlalchemy, database, postgresql, python

Id: 6RrwKDGKcxM

Length: 31min 53sec (1913 seconds)

Published: Sat Aug 27 2022