 Right, so hey guys and welcome back to part two of how to create a Program that shows you how to use mysql using Python. So as promised in this tutorial I'm going to go through rest of the commands that will pretty much help you use the Mysql library in Python. So in the last tutorial we go up to being able to display specific columns of data From our database. We also did different stuff such as connecting to the actual database creating Tables renaming the table adding data to those tables and so on So in this tutorial we're going to go a bit more further into what we can do with more commands so first off what I'm going to do is open up visual studio code and If you guys haven't already watched my previous tutorial or the part one on this You should go ahead and watch that because this will make a lot more sense if you watch that Because some of the commands we're going to be doing today are going to be explained more in detail in the last tutorial So without further ado, let's go get on with today's tutorial. I'm going to zoom in a bit here So it's easier for you guys to see creating a new file save it as Using mysql.py make sure you don't save it as mysql.py because then it will clash with the actual module that you want to use So let's save this up and as I said before if you haven't watched part one go ahead and do that So first off what I'm going to do is since you guys are watching part two I'm just going to do what we did in the first tutorial like the basic serve on port Mysql.connector as mysql So that's the import done and we're going to have a few variables up here Variables and then we're going to have post which equals local house because our database is going to be hosted on local house The user is going to be rude because that's the default user and the password is going to be empty string because that's the default password Cool now, we're going to go ahead and connect to our database by doing database equals mysql.connect We're going to use the host equals to host then we're going to provide the user Which is going to be equal to the user then we're going to provide the password Which is going to be equal to the password that we've already saved as our Variables and then we also are going to be specifying the database we want to connect to Which the name of our database that we created last time was called ford So before we go any further i'm going to go ahead and start my PHP my admin service By going here by going to zam control panel which will be installed in the last tutorial And i'm going to click on the apache and the mysql service so that the mysql will let us pretty much Use the database service and let us connect to it So i'm going to click on the admin tab to show you a visual view of what we created in the last tutorial So as soon as this loads up we can carry on Hopefully very soon okay cool So in the last tutorial we learned how to connect to this PHP my admin interface right here or the mysql Interface we created this car's database using python We then go went ahead and created a table called ford in there Which is one of the companies in this car database and then in that table We had different columns so if i open this database we had id name and engine size We had an auto increment for the id which means it will go plus one each time a new record is added And we were able to manually add these records in there So this is what we go up to in the last tutorial So now that i know the name of my database is car's so i know that so i'm going to go back And i think i accidentally typed in the name of my um Table which is not what we need we need the name of the database which is car So database equals cars you could also save that as a variable, but i'm just going to save time and not do so So once that's done we're going to print print connected to database In the last tutorial we also did a bit of error handling, but if you like you can add that I'm just going to save time by not doing so So in this tutorial first command we're going to actually learn would be um Let's take a look at the list i've got here So it's going to be displaying one row of data instead of just showing all rows of data that you extract So when we do a query like um select star From ford This pretty much means that we want all the records. So star means everything. So we are telling the um SQL database to return all the records that exist in our ford table So let's say we only wanted the first item from these this record list That's what we're going to be doing in this section of the tutorial. So let's make a comment displaying one row of data instead of All of it cool. So what I want to do first of all is make a print statement right here and say displaying only on row of data And then i'm going to go ahead and also initialize my um command handler. So i'm going to do Up here actually i'm going to go up here and then i'm going to type in command handler Equals database dot cursor So this cursor right here is going to be assigned to my variable called command handler now every time I want to execute a query I'm going to use the command handler object right here. So i'm going to do command handler dot execute Which is the command to actually execute a query And then we provide the query in here. So i'm going to go select name Form ford. So I've said select specifically just a name from ford instead of selecting everything So if I did select star it would select id name and engine size But i'm specifically saying select only name full ford. Now this will still return all the names in the ford table So i'm going to show you how to um extract only one record So create me a variable called record or whatever you like and then command assign that to command handler Dot fetch one. So in the last tutorial we used fetch all to grab all the available records that are coming back But in this tutorial we're only going to be using um command handler dot fetch one I'm going to go ahead and print record. We don't need a loop because we know that we're fetching just one record So that's fine. Let's go ahead and run this quickly And see if that works. So voila, it says connected to the database displaying only one row of data ford focus So it's going to show the most um the first record that was inserted which is ford focus So that's fine. That's how you display just one record of data instead of showing everything. Let's close it off Now moving on we're going to learn how to actually filter rows of data that are returned by um actually adding a condition to So what do I mean? Let's find out so filtering Filtering the rows of data returned And we are able to pretty much add a condition. So I'm going to do print filtering the rows of data returned And then in here once again, we're going to have to change the um query So we're going to have to reassign command handler dot Execute to a different query. So I'm going to do a query Where I type in okay, let's get rid of this right here. Oops. What am I doing? Cool, so I'm going to what's going on. So I'm going to go here and type in my query. So I'm going to say select star which means select everything from my ford table, which includes the Engine size id and name for the different ford model cars Select star from ford, but I'm also going to add a condition where the name equals And then I use single quotes to mention what name I'm on about and I'm going to type in the name that I want to be Returned so where the name equals ford focus So now what's going to happen is it's only going to select the rows of information from my ford table Where the name of the car equals ford focus the rest of the records are going to be excluded So if the name is mustang or ford fiesta, it's going to be excluded So I'm going to prove that to you by actually running this and hopefully it works fine So I'm going to go ahead and create my variable call records Now I'll call this record now because we're going to be receiving multiple records. So command handler dot fetch All because we want we know that we're receiving multiple records this time Not just one and then we need a loop because we're receiving multiple records. So for record In records We're going to print record Cool, let's run this and hopefully it will work just fine. So quick run And we have an error So what does it say on red result form? So this error is happening because of our cursor initialization So what we need to do to resolve this error is this is mainly happening because we we already have some information That's already stored in our fetch variable So if you want to store multiple fields of data in here without it having to complain or mourn about it What you want to do is go back up to line 10 We where we assigned our database dot cursor to our command handler and then type in an argument in there called buffer Which is going to be equal to True rather capital t now. I believe that when I run this it should work just fine Let's take a look Got an expected unexpected keyword buffer. Why did that happen? Let's take a look So what do I have going here? That's weird it shouldn't have done that but let's take a look I'm just going to pull up my notes really quick. Okay. Actually, it's not buffer. It's buffered So I forgot to spell it correctly. So it's buffered equals true, which means we're going to be able to buffer multiple Records that are returned to us without crashing. So when I run this now Finally, it works fine. So as you see right here all the records that we have are forward focused based So all of them are forward focused now just to prove that my database also has different models I'm going to open up my database And if I show you right here, I also have mustang port fiesta um Yeah, I have forward fiesta and mustang apart from forward focus But my results are only forward focused based because that's my query. I've added a condition in there So that's how you filter rows of information using a where So using the where that we used right here. Cool. So now that we know how to do that. Let's move on Um, let's take a look at what we're doing next Okay, so in this um in this bit of the tutorial, I showed you guys how to select a Select the rows of information based on the exact name that's given So we can also create like a little search function Where we um return results based on similar outcomes to words. So what do I mean by that? Let me show you so filtering The rows of data based on Similarity of words now if you use google before you may have noticed that they have something called google suggest So before you actually finish typing something It suggests, um things that you may be looking for So most of the time they're pretty accurate and that's what this is sort of like so I'm just going to finish this off So in here instead of typing the specific thing that you want, um to be looking for as a criteria You can enter a couple of keywords So it will make more sense once I've finished typing it up actually So let's see query So to run a query, we're going to have to open up command handler again and type in execute And then in there we type in our query. So select star from Ford Where pretty much everything is the same so far name Name and then now instead of um equals we type in like so we're saying select star Select everything from the full table where name is like or similar To these keywords right here. We're going to have to use um What's it called percentage signs to format the string. So we type in I'm going to type in let's say must So this these are the three words three characters or one word that I'm giving my um query To filter off so anything that has mus in it. We're going to be what's going to be What's going to be returned as a result? So pretty obviously mustang has a must in it So this is like when you're using um a search filter or something like that You would find this pretty useful because you can use just um part of the word that you're looking for So let's go ahead and see if this actually works by um running this query and then um finding out the results that are returned So records once again equals Come on And then Front record cool, let's run this up to see if that actually worked Uh and as you see right here filtering data that um is similar to a keyword provided So the keyword we've provided was must mus So the most similar results that were returned was mustang because the name has mus in it If we had something else that also had mus in it, it would return the results for that too But this is working pretty fine. So let's go ahead and look at what we're going to be doing next So we are going to learn now How to sort data. So we've learned how to filter data We're going to be looking at how to actually sort data now So we could learn about how to sort data alphabetically first So sorting rules of data by name Cool So if you wanted to sort the data that we are returning or being returned from our database Based on aphabetic order. So from a to z we could do that by using a sort um query So first off i'm going to do a print Sorting data based off name. So we're sorting the database of the name of the car now Now we're going to do command handler execute Select Select that's going to star Select star from forward And then we're going to do order by name So what this does is we're telling it to sort all the information that's being returned Alphabetically by name because name by default has alphabets. So it knows that it needs to return it in the alphabetic order So lastly, we're going to do our for loop. So record records equals command handler dot fetch all And then for record in records Print record cool. So hopefully that works in the first go and Looks like it did we have sorting Data based off name and if you look at the alphabetic order, it's pretty perfect. So f comes before m which is fine So it is returning all the data or the results that we have based on alphabetic order So f first so all the forward results come up first for forward fiesta and forward focus And then the mustang comes up Cool. So we've learned how to sort by name alphabetically. So next we're going to be learning how to Sort data by most recently added information because by default The data that's returned is in the order where we are returned the item that was first added to the database Not the most recent entry that was made to the database. It's the first added Um, uh, column of information that's returned instead of the most recent So let's take a look at how we're going to be doing that. So we're going to be typing in sorting data in descending order So that's how it works So we're pretty much sorting data in descending order because by default it's in ascending order to show up all the information from first inserted Role of information to the very last but we're here. We're going to be doing the opposite. So print Sorting data based of id In descending order now, we're going to be using the id to find out the descending order because id is a number obviously So we're going to pick up the highest number in the id which is possibly or which needs to be um, the most recent um Record of data that was inserted into the database, which is what we want So we are going to do command handler execute And then we're going to be typing in select star Form um four And then we're going to be doing order by This time we're not ordering by name We're ordering by id because we need a number to go in descending order and we type in descending dsc in caps Then lastly we need to do our records thing again. So record equals Command handler dot Fetch all then for record and print record Cool, let's run this up to see if it actually works No errors, which is a good sign Uh sorting data based off id in descending order. So as you see right here um, we have been returned the Data based on descending order. So it's going to show me id is that will last inserted Um to the id that was first inserted Cool. So that's working fine as well. Let's actually confirm that this is correct So let's take a look at the order of information. So if I go to my database and refresh Ford focus was the first one to be inserted But the most recent one was a forward fiesta. So it needs to be forward fiesta then mustang and then Then it needs to be forward focus. So forward fiesta mustang forward focus So as you see right here, forward fiesta comes up first Then comes forward focus and then mustang as I said in here So it's pretty straightforward forward fiesta was the most recent one Then the most recent after that was mustang and the last most recent one was forward focus So it's working just fine showing us the information based on most recent to the last Inserted row of information. Cool. So that's how you do that Let's now learn how to do the dangerous bits, which is deleting records and tables This these commands need to be used very carefully because you might lose a whole load of data that you may have saved in your Databases so make sure that you use these commands pretty carefully So deleting records, we're going to learn first of all so we're going to learn how to delete a single record print Leading records on a database. Oh wait, actually from a table Okay, cool. So and this is going to be based on a criterion. So we're going to do come on and low once again Execute and then in here we're going to provide a query so delete from forward where The name equals Mustang now as you may have already guessed it's going to go ahead and look through all my records for my For my forward table and then it's going to find out the records that have the name Mustang And any any record that has the name Mustang is going to be deleted. That's pretty straightforward. That's what it's doing We're here. So we give it a criteria on what it needs to delete. So lastly, we're going to be typing in Database commit to save all the changes and refresh and then lastly we print the number of The number of rows that were affected so command and dot row count Record records Cool, let's run this up quickly to see if that works Hopefully it did. Yes, it says deleting records from a table and it says five records deleted Now, let's go ahead and refresh our database. So this is our database right here Let's refresh it to see if Mustang is in there So refresh and as you see it has worked fine So the Ford Focus and Ford Fiesta so here but the Mustang has disappeared because that's what our query was meant to do It was meant to delete every record with the name starting with Mustang Cool, so that's how you do that. Now. The next one is going to be even more riskier This command is going to show you how to delete an entire table. So to do this I'm going to create a dummy table in here. So I'm going to create a new table come on Okay, a new table. I'm going to call this dummy and then I'm going to click on go because I don't want to lose the Ford database that we already have going so Let's click on go Sit down it Okay Doesn't seem like it So I'm going to call this dummy. Oh, actually I need to click on save. I'm being dumb I'm adding columns by accident. So I am the dummy. So let's click on save down here Missing value inform. What is missing? Okay, let's just type in something like name and let's save that as a rack here save And so a valid length. Okay, very picky So if we did it through command line, we wouldn't really have these issues, but hey, there we go So we have a dummy database with a I mean, we have a dummy table in our cars database Which has name field. We're going to be deleting this dummy table now using Python So let's learn how to do that. So first of all, let's make a new comment saying deleting an entire table Cool. So like always we're going to have to query this but before that I'm going to print it out So come on Handler.execute and then this is pretty simple. We just do drop Table and then we type in the name of the table So I'm not going to type in forward because I don't want to drop my actual table I want to drop in drop the table called dummy So once that done we are going to print We're going to print Table deleted cool now We don't need to come it because this is just deleting literally the entire table So we don't need to check save any changes table will just disappear so let's run this up and Hopefully we have a message. Okay, it does say table deleted. Let's go ahead and verify it. That's true Let's refresh this page and as you see right here if I click on cars database the dummy The dummy table has disappeared. It's only forward right now We're just correct according to what we've done over here So let's close this off and what I'm going to do now is actually Comment these two outs because they might otherwise error. So control C save Because it might look for the database now called Dummy, but obviously it's already been dropped. So it's not going to be there So you don't want any errors. So I've commented that out Now, what is the next command? Let's take a look Do to do so. Okay, so we also have a command. That's literally made for what I just spoke about So we delete a table if it only exists. So Deleting a table only if it exists. So this comes with an error handle in its already so print Deleting a table if it exists. So if it exists, it will delete. Otherwise, it won't do anything about it so command Handler dot execute and then we type in Drop table if exists, so that's the Condition we are giving it and then we're going to type in dummy Obviously, it's not going to drop the table because dummy doesn't exist, but it won't error out either, which is a good thing about it print done Cool, we're gonna run this and we shouldn't see any errors because obviously it says done But it's not done because it's I mean it is done It's checked whether we have a dummy table. We don't and so it's not done anything about it But if we did have a dummy table, it would have deleted cool So that's how that's done now The last or the second last thing actually that we're gonna learn is how to update records in a table Now let's say let's go back to the database. Let's say you had this table forward, right? and you had forward focus and All of a sudden due to the pandemic or whatever all the forward focus 1.8 liter cars Have to be changed instantly to 3 liter cars. Obviously. It's a bit unrealistic, but hey, let's say you need to do that So you if you went to edit each one of these using the GUI that's jarring very long There's actually a query that you can use for this. So that's what we're gonna be doing So make a new comment up here and let's pull this Updating existing reports in a table So we're gonna print the same updating Existing reports in a table Tablet and then what we need to do is write up the query for it. So we're gonna do command And execute and then the query is going to be updates because we're updating updates with caps Then we type in the name of the table update table forward and then set the engine size Because we said we wanted to change the engine size equals 2 and then you speak I mean single quotes and then type in I would say we sent changing it to 3 liters from 1.8 so type in 3 liters and Then we need to do where so we need to say where we want to change it where engine size equals 1.8 And Name equals Was it Ford fiesta? Let's take a look and Name equals Ford focus actually so let's grab that actually I can type then it needs to be exactly the same the Ford focus Okay, and let's actually run that once we've printed it out. So print Record updated Cool, let's run this and If we go down here, it says records updated nice. Let's go into a database and see if that actually worked refresh Well clearly that hasn't worked Let's take a look at why to Let's try and get rid of and name equals and Type in this one in here because I think it's the and sign and not the and word Let's take a look. I can't remember the exact syntax for this Okay, that hasn't worked either Ford focus have I spelled it right? What workers I have? Let's try using a comma Run it now Okay, we have an error this time What's the error name equals? Okay, so I'm not sure how to actually use the right syntax for this So I'm going to get rid of the name. Actually, let's just do it like this and we'll see if it actually works. So let's run it Okay, still have an error. So I'm going to get rid of this where name equals Ford focus Let's save that up. So we're gonna be changing every engine size that equals 1.8 to 3 liters So let's run this and then if I go back here and refresh Hmm, that's weird Still hasn't changed Okay, I see so This is a stupid mistake on my behalf. So I'm gonna press control Z a couple of times So the and was actually working the whole time So we will stick to our query where engine size equals 1.8 there. We want to change The engine size to 3 liters and name also needs to be Ford focus But the thing that you're forgetting to do is actually commit this this is the important bit about commits You actually don't see any changes until you commit the change So let's run this up now and hopefully it does an error. Okay, it does an error. Let's go back and check Okay, it still hasn't worked. Let's refresh All right Hmm, let's try and get rid of the and name equals Ford focus because maybe that is wrong after all Run it now. Okay, refresh That is super weird Let's take a look update Ford set engine size to 3 liters where engine size equals 1.8 and then we are doing a commit Should have worked one-handed execute Hmm, why is that not worked? It's one this again sets engine size equals three liters where engine size equals 1.8 Okay, and then we're committing it down here. Is it actually called database DB? Okay, it is. Let's run this Go back and let's actually hard refresh this so control F5 Okay, still hasn't changed So I'm not sure actually what I'm doing wrong here So if you guys can correct me in the comments that would be great It did work when I was experimenting with it earlier So I'm not sure why exactly this is happening. I'm gonna try one last time So we're gonna actually store this query in a different variable. So we're gonna type in update Ford set engine Engine size equals to let's go with two liters this time and then where Engine size equals 1.8 Come on 1.8 Okay, that's the query done and then we just execute the query that we pass in here So let's get rid of this and type in query in there Okay And that's good. And then we are also doing the commit down here, which is fine. Let's run this Okay, what does it say it says records updated. I'm not sure if it has them Ford focus That's super weird because it this actually needs to have needs needs to have worked I'm not sure why it's not working. Anyway, if you guys could Sort it out, that would be amazing So let's move on to the last bit of this tutorial Which is actually limiting the records to a specified number of results So if you want to receive only a specified number of results, this is how you do it Limiting the records To a specified number of results so print Limiting How many results are returned? Then we do command handler that executes Select and start from Ford and then we add a limit of three records to be returned. So we're only wanting to return three records So the courts will be equal to Command handler dot fetch All and then for let's go on next slide for record in the courts Print record Cool, so let's bring this out now And has it worked? Okay. It has so limiting how many results are shown and we have only three records for the query that we wrote Um, that's about it. Cool. So that's fine. I'm still pretty bummed about why this Editing didn't work. So why we weren't actually able to edit out information from our database Are we actually looking at the correct information? Let's see I'm going to try closing this and opening in in chrome or something because sometimes it can actually Use just cached information localhost PHP My admin let's try this cars Ford Okay, it's let's see what the edit query would look like edit What if I wanted to edit this so if I type in 1.8 Type in two instead zoom in a bit and then let's go So right here does say what the query actually is so it says update Ford Set engine size equals two Where id? Where Ford id equals one so it's pretty similar to the um the Query we had going so It was update Ford set engine size equals 2 liters Okay, where engine size Was equal to 1.8 liter. Oh, I see my mistake. Oh my god. I missed out the l guys I'm so sorry. I took the biggest l from this so it needs to be exact the same So 1.8 liters and we can use the actual and now name equals Ford was it Ford. What was it? I'm so sorry guys. It's such a silly mistake. So name equals Ford um focus So let's go in here and type in Ford focus And let's actually run this query. So hopefully this will work this time around Let's refresh And oh my god, finally it feels amazing to see this working So it was a silly mistake on my behalf. I forget I forgot to put an L in the 1.8 which means it wouldn't match any records that already existed So now if you see it's gone ahead and matched pretty much all the Engine sizes that had 1.8 liter and had the name of Ford focus and it's replaced it with 2 liters to l So that's about it for today's tutorial guys. I'm sorry for extending it to such a long time But I'm pretty sure that you've learned about a lot more commands in MySQL and that you can use these in future projects If you guys have any new ideas for the channel, please make sure to drop them under the community tab under the post that I have Which is ideas for new videos If you guys would like to donate to the channel directly You can do so by either becoming a patron on my patreon account Which is linked in the description as well as on my channel tag Or you can buy a super chat emoji or a highlighted message when this video premieres I'm not forcing anyone to do so, but if you'd like to that would be amazing Also guys, thank you for all the support you have been showing I'm very grateful for it If you would like to follow my socials and discord as well, it would be pretty nice Um, and that's it. I'll see your beautiful faces in the next tutorial. Peace