Choosing a Database and ORM
And now it's time to save the data in the database. So whatever option we were doing like creating, updating, deleting and reading this should be done through the database. Now database you can use anything which you want. Maybe a CSV file, you can use NoSQL databases or you can use RDBMS. And here we are going to use the RDBMS.
Now you have multiple options to work with. You can use MySQL, you can use Postgress, you can use Oracle, whatever you prefer. Now I'm going to use Postgress but because it's one of the best advanced open-source tool that's what they say. So if you go to Postgres, this is what they say the world's most advanced open-source relational database but of course there are other options as well whichever you prefer will work.
Understanding Object-Relational Mapping (ORM)
Now question arise how will we are going to do this? Now whenever you work with Fast API one of the recommended way is to go with SQL alchemy. But why we need to use SQL alchemy here? Now SQL alchemy is your Python SQL toolkit that's good the other options as well but it also provides you something called a object relational mapper. Now why this is important see if you go back to our code which we have done we basically have a class right a class like this where you have a product and then you got certain fields there.
Now let's say if you want to create a database and if you want to create a table. Now if I ask you hey you got this particular class here and I want you to create a table for it. Now in your mind actually you can do the mapping right? You can say okay I got a class which is product. Let me create a table called product. I got this fields like ID, name, description, price, quantity. I can create the table with those columns like ID, name, description, price and quantity. As simple as that, right? So, and then you got the table name, you got column names, but you also got the column type because in the class also when you define those properties, you mentioned, hey, that's an ID which is integer. So, here also we can say integer. Now, based on what DBMS you're working with, the name will change from numbers to integer. So, here we can have string or wcad or text and list goes on. So, the type defined in the class will be defined for the table as well.
But you will say okay mapping is done but what about the data? What about the row data? So each row will represents one object. So let's say we have created four products right that was in the list. Now each product here is the object of a product class. Now that object goes to database and becomes a row. So what we are trying to do here is we are trying to connect the object and the relations which is tables and we are doing a mapping for it and that's what we call ORM.
The Advantage of Using an ORM
Now you will say what's the benefit of it? You know this looks fancy but why do we use it? See when you work with databases like let's say Postgress or MySQL and if you have a language like Python and whenever you want to save data basically what you will do is you will take the object and this object will have the values. Now you will write the SQL query. So let's say if you want to create a new record in the database. So you will write the SQL query which is insert. So you will say insert into product. Then you will say values and in bracket you will mention the values. Right?
Now from where you will get this values from the object then manually you have to pick up the value from the object put it there. Pick up the value put it there. So you have to create that query as a developer. So that's for the insert. What about let's say if you want to fetch then you will write the query which is select star or based on what columns you want to fetch based on how many rows you want to fetch you will write the query in that format what if you want to update for everything you got queries and you have to write those queries now you will say okay I know how to work with queries but the question is do you want to really write that in the python code what if someone else can do it for you so they will say let me give you a tool use this and let me take care of creating all the queries for you. You just say if you want to insert, you just say add. If you just want if you want to fetch maybe you can say get whatever method is. So basically you can use those methods or functions and this will do the things for you. Cool, right?
Installing SQLAlchemy and PostgreSQL Driver
And that's why we are going to use SQL alchemy. Then the question is how do you get this in your project? Now if you head back to your project here, okay, let me stop the server because we are going to make some changes. Now if I check pip list, nowhere you will find SQL alchemy. You will get pydantic by default but nowhere we got SQL alchemy right. We need to get that. Apart from it we also need to get the driver for Postgress.
Now based on what DBMS you're working with. So if you're working with MySQL you need a MySQL driver. If you're working with postgris you need a postgris driver. And we need those two things in this project. SQL Alchemy and the driver for Postgress. So let's install it. So I will say PIP install. So we need SQL Alchemy and we also need the driver which is psycopg2.
Preparing the PostgreSQL Environment
I don't know why they went for this weird name because this is PG makes sense, posgress and psycho. If anyone knows this let me know in the comments. I've not not explored why they have this weird name. For my it's a very simple MySQL connector for Python, perfectly makes sense right but I don't know why they went for this name but this is a driver for postgress and this is your SQL alchemy and you have to also make sure and before you work with Postgress you have to also make sure that you have Postgress in your machine if you don't have it click on download it will download the postgress for you based on which OS you're working with I'm using windows and these are the version I think in my machine I got 17 or 16 doesn't matter because the last update was in 2022. So that is 17.6. Uh they are releasing the beta. 18 beta is released. That's great. Uh if you want to use beta, you can try it out. But if you're new to the development world, stick to the stable versions because if something goes wrong, you will always blame the system because it's in beta. Maybe it's your mistake. Now when once you get experience, once you have worked on multiple project and if you if something goes wrong, you can still blame it. But at least you will know are you really responsible for it anyway. So you will get this download.
Now make sure that when you get this you also get PG admin. Now if you don't have PG admin in your machine or if you don't get this by default search for PG admin somewhere and you should be able to get it. I think you will get that by default. PG admin. There should be a tick mark. Now how the PG admin looks like. So this is how the PG admin I'm just opening it now looks like bit heavy to start with in fact earlier PG admin 3 was very heavy four they have changed the interface to make it lightweight but since it's connected with the Postgress service which is running behind the scene it takes time so by time it is opening let me just go back to our code and let's install this so this should take some time now since I already had this in my system it will be faster Yeah, it it was fast. And now if I check PIP list, there should be SQL alchemy and there should be the driver. Perfect. Now once you got these two things, we can get started and work with the database. Okay, but I also want to show you the PG admin. This is how it looks like. Uh I should zoom it a bit. Okay. So this is PG admin and by default I think you have to add the server by specifying the username password. Username by default will be postgres. The password you have to set when you're installing it. And I forgot what is my password for this particular pg admin. But we'll figure it out because I use different machines for project and for recording and I always mess up the names. Okay. So we'll use this. We'll try to save our data in the postgres database. And if you're using MySQL, the only thing you have to change is uh in fact you have to change two things. One is a driver and second some lines in the database connection. That's it. And it will work. How that works? Let's see that in the next
