what is going on guys welcome back in today's video we're going to learn about SQL Alchemy or SQL Alchemy which is an SQL toolkit an object relational mapper a python module that allows us to map python classes and objects to database tables and entries so let us get right into it [Music] alright so SQL Alchemy is an object relational mapper which basically means that it allows us to translate python classes and python objects to database tables and database entries and when we work with these python objects for example we create a new python object we delete it we change it whatever the respective action will also be translated into a database action without us having to write any SQL code so we don't need to do anything like create table insert into select from everything will be done automatically by SQL Alchemy and we can focus on the python object on the python code and in today's video we're going to learn how to do that we're going to use a simple example of a person class and a thin class and a thing can be owned by one person for the sake of Simplicity we're not going to have one thing being owned by multiple people but we will have the possibility of one person owning multiple things so this is going to be the simple example of today's video and to start we're going to install SQL Alchemy by the way for those of you who say it's called SQL Alchemy uh or those of you who say it's not called SQL Alchemy actually both ways of pronouncing SQL are fine so you can say SQL you can say SQL both are accepted I think the original name um is actually SQL but yeah so pip install SQL Alchemy and once you have that we're going to start with a bunch of imports so once this is done we're going to say from SQL Alchemy import create engine we're going to import also the foreign key relationship uh we're going to say from SQL Alchemy we're going to import actually we can do that in the same line here column now maybe I'm going to just put this down a little bit here so that I'm not blocking this with my camera we're going to also import column we're going to import string we're going to import integer and we're going to import character so those are basically just data types for the columns this is the column itself this is the foreign key relationship of the database that we're going to Define and this basically creates the engine that we can connect to because SQL Alchemy is compatible with a bunch of different database types so we can use sqlite we can use MySQL and all that in today's video we're going to use sqlite because that's just the most simple database we can create by just creating a file then we're also going to import from SQL Alchemy dot extension or dot X declarative we're going to import the declarative base this is essentially the base class that we're going to extend uh that we're going to inherit from uh for our person in think class and then we're going to say also from SQL Alchemy Dot orm which is the object relational mapping we're going to import the session maker and the session maker will essentially make a session then we can start the session and we can do stuff in the database so those are going to be the Imports and we're going to now say that the base class that we want to inherit from will be created by saying declarative base we're going to call declarative base this is going to return a class and this class will then be the base for our first class which is the person class it will inherit from the base and we're going to now Define a bunch of things here first of all we have to set a table name so we need to say underscore underscore table name underscore underscore and this will then be the table name inside of the database inside of the sqlite database in this case so we're going to say table name equals person or actually we specify the plural usually so we're going to say people will be the table name now some might argue that persons even though that's not English might be a good name because um yeah it will be easier to find automatically but we're going to use people here and then we're going to specify the columns and the columns are essentially the attribute so we're going to say first of all the person should have a unique identifier the primary key so to say and this will be the social security number of that person this will be a column and it will be it will have the name SSN also in the database it will be an integer for the sake of Simplicity here and it's going to be the primary key which is why we set primary key equal to true then we're going to say first name will be um column first name will be a string then we're going to copy this we're going to say the last name will be a column called last name so essentially the pattern is quite simple you provide the attribute name um the variable name inside of python then you say it's a column then you specify the database name then you you specify the database data type and you can also set some optional parameters like is this nullable is this a primary key is this unique and so on um then we're going to say gender will be equal to um column gender and this will be a character and then we're going to say um H will also be a column H and this will be an integer so this is our basic person class now we're going to write a very simple Constructor so a very simple init method uh where we just say that we can pass all these things right away so first last gender age now professional uh if you're writing professional code you should probably also use first name and last name here but I want to do it like that so that I have to write less so SSN is SSN self.ssn will be equal to SSN self DOT first name will be equal to First self.lastname will be equal to last self.gender will be equal to gender and self.h will be equal to H and then last but not least we're going to write a simple Raptor function basically just a function that allows us to specify how we want to print a person so when we print a person object what do we see I'm going to return a basic F string um we're going to start here with a primary key the self dot SSN the social security number followed by self DOT first name followed by self.lastname and then we're going to have self.gender comma self dot h that will be the basic string so let me zoom out a little bit that's our person class and now we're going to add another class um or maybe let's do it first only with a person class and then we're going to add another class but let's start just with a person class and uh see what the process afterwards is and then we're going to add the thin class where a person can own a thing and a thing can be owned by one person but a person can own multiple things we're going to model that here in a second as well but let's just move on here before we do anything else uh just with the basic interaction with the database so what we need to do next once we have the classes uh or in this case the one class is we need to create an engine so we say engine equals create engine um and now we need to specify the database so if you have MySQL there is a way to connect to mySQL and for all the different database types you have different uh ways to connect to them in this case we're going to just say sqlites um colon slash slash slash and now you can choose to use an in-memory database so in the ram which will be a new database every time you run the script or you can choose a file database which is what I'm going to do so I'm going to call this mydb.db and I'm going to say Echo equals true and this then connects to uh to a sqli database this will create a file and work with a file so what we do next is we say base dot metadata dot create all bind equals engine and what this does essentially is it takes all the classes in this case just one that extend from base and creates them in the database so it connects to the engine and creates all these tables so the person table will be created after running this line of code and then what we do is we say session with a capital S this is just a convention equals session maker bind equals engine so we create a session maker in the actual session will be a lowercase s session um which will be just the Constructor so this is essentially the class and this is then the instance that's the basic idea we create a session and with that session we can now do all sorts of things so for example we can say the person is equal to a person object and we're going to say now uh that this person will have just some social security number it will be Mike Smith he will be 35 years old uh actually we need to pass the gender first he will be male and he will be 35 years old and with that person we can now just say session dot at that person and this person will end up in the database so I can just say session dot add person and without writing a single line of SQL code this person will be created in the database and the table will also be created by this line as I mentioned and this line will then um create the person now to actually create the person in the actual database we also need to commit so into say session dot commit this will then apply the changes to the database this will then flush it so to say so if you have a file stream usually you have to close it or flush it in this case we need to commit whatever we want to do uh and we can do that with a bunch of different persons so we can copy this now or people sorry not persons uh we're gonna say P1 P2 P3 and we can create a bunch of people here with different Social Security numbers since this is a unique value and we're going to have Anna blue she will obviously be a female and let's say she's 40. then we're gonna say we have Bob will be also blue so they're maybe related maybe not he will be let's say 35 this was the value before but it doesn't matter and then we're going to say that we also have Angela I don't know cold or something and this will also be female and we're gonna say 22 years old and then we can just say session dot at P1 P2 P3 and then session Dot commit and this will commit these people to the database I can run this now uh you can see actually what happened here so create table people this is the generated SQL code and here we also have insert into people blah blah blah so you can see how all of the actions here are translated to SQL code we can double click the database in pycharm we can open it and we can see here in the tables in the people table here that we have the entries in the database so this was done without writing any SQL code um now we can also do the same thing with queries so we cannot only create objects and insert objects we can also query objects in a very simple way by just saying people which will be our result set so let's call this not people but results will be equal to session dot Quarry and we can then specify uh what table we want to query in this case the person class so the person table and we can just get all the people for example so this would be select everything from person or select everything from people and this will be the result set so I can just print this here and in this case we'll get an exception because we already have a database but when I run this here you can see this is the result set we have Mike Smith we have Anna blue Bob blue Angela code so we get the actual python object as a result here so when we when we uh query the data from the database so actually I can show you that this is really part of the database I can uncomment or I can comment all of this so we don't create these objects again we already have the database and I will still get the values here I will still get the objects and I get as a result here a list of python objects so I get actual python objects from the database this is quite impressive and we can also use that to filter so I can not only say all I can also say okay from that result set filter because I only want to have the person uh or the people that have a first name uh or actually let's do something else let's do the last name is equal to Blue so in this case I should get two people here um what's the problem here I didn't get much do I have any results here or did I do something wrong this is a quarry maybe I do have to iterate over it so let's do four R in results print or I'm not sure if that's the problem there you go we got Anna blue Bob blue okay this worked so we can filter like that we can also do some more advanced filtering but first of all let me show you we can also use numbers so we can say person age has to be above uh 25 so this should exclude Angela as you can see and then we can also do some more advanced stuff like for example the first name has to be like so first name like and then I can use the function where I say okay it has to uh what names do we have a n for example has to be like percent a n percent this will find Angela and Anna um there you go as you can see here in the result um and we can also do stuff like checking if the name is in the list so I can say okay the first name has to be one of the following by saying in underscore and specifying then a list of let's say Anna and Mike and this will then give me Anna and Mike so this is how we can do queries um let's now move on and implement the think class as well this will add a foreign key relationship and you can see that this will you will see that this will also be modeled very very easily without us having to do anything so I can just say class thing which will also be extending from the base class we're going to say table name equals things and we're gonna say that a thing can have an ID let's call it tid for thing ID because ID is as far as I know a python function so let's go with tid is going to be equal to um column integer um actually let's call it tid y integer primary key true then we're going to say the description of the thing will be description string and then we're going to say the owner will be a column it will be an integer and it will be a foreign key to people dot SSN so this string will refer to the Social Security number of the person class so of the people table so people.ssn will be the foreign key and this will refer to the owner um now basically all we need to do now is we need to also Implement a simple init method so we're going to say that we have the tid the description the owner and again what we're going to do here is we're going to say self.tidst ID self dot description is description self.owner is owner [Applause] and then we're going to add a simple representation function here I'm gonna just return F string and we're going to say that this will be self dot tid and we're going to provide self Dot description and owned by self Dot owner that's the basic idea and now let me see if we need to make any changes I don't think so to be honest I think that everything we did up until now should also work with that change so I should be able to delete the database uncomment all that and nothing should actually change other than that we have a new table uh so if I what did I do now if I open up the database I should be able to see that we now have two tables or don't we have two tables not sure if we have two tables okay so let's see what happens when I just create a new thing so what we're going to do now is we're going to say instead of quarrying I'm going to say T1 equals thing with the ID one it will be a car I don't know and the owner will be P1 SSN and then I'm going to say session dot add T1 session dot commit and let's see if that works now we have a problem what's the problem unique constrained failed oh I think this is because we already have a table uh because we already have a database so let's zoom in again and run this again now as we can see here create table things was actually performed so this should actually work or do I have a display problem here maybe I have a display problem I don't know maybe maybe the interface doesn't work so let's go select everything from things and let's see if that is something that produces an output there you go we have a thing owned by um by this person here so we actually do have the changes in the database maybe I'm just too incompetent to use the graphical user interface of pycharm but we can now go ahead and create a bunch of things so teeth three four five I can see we have a laptop we have uh I don't know PS4 we're actually five in this case um then I might have some tool and then I might have a book and those have different IDs I'm gonna say this is also owned by person one this will be owned by person two this will be owned by person four uh we don't have a person four so let's do it like that um and then I can just add all these things and after that we should be able to also make some Quarry that is a little bit more complicated because now what we can do is we can say okay give me all the things that are owned by person one and how do I do that I do that by saying results is equal to session query thing and person are the two uh tables that we're going to Target and we're going to say that the filter will be the following we're going to say that the thing owner the owner of the thing has to be equal to the Social Security number so the primary key of the person and we want to have an additional filter this person that owns this thing has to have a first name being equal to Anna for example and then I want to get all the entries so this is the full line um and then we're going to say four are in results print r um and again I think we need to delete the database obviously what's the problem now I cannot delete the database why is that okay let's just use a different name uh where is the engine declaration let's use my db2 and then it should work there you go and you can see car owned by whatever and laptop owned by whatever so you can see that we didn't write a single line of SQL code but we modeled a foreign key relationship we had two tables we had a person that is able to own multiple items or things in a thing can be owned by only one person and we are able to create new uh new people and new things and we can also query for the things that are owned by a person and we can filter and we can do all these things without engaging in any way with SQL so that's it for today's video I hope you enjoyed it and hope you learned something if so let me know by hitting a like button and leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free other than that thank you much for watching see you next video and bye [Music]