Python MySQL Tutorial - Selecting Data & Altering Tables

Key Takeaways

hello everyone and welcome back to their MySQL tutorial so what we're gonna do in today's video is do some more advanced queries we're gonna retouch on that select query that I mentioned a little bit in the last video and we'll talk about some new ones about how we can modify the table and alter it and then in the next video we'll probably touch on something called foreign keys and then from there we'll actually deploy this out to a server and maybe do some example code and show how we actually use this in a real project so with that being said let's go ahead and get started so I've actually cleared the table I made in the last video so I'm kind of starting off fresh here so that means the first thing I need to do is actually create a new table so what I'm gonna do is say my cursor executes create table and now what I'm gonna do is actually add some different kind of values into this table to show you some different ones that you can use so I'm just gonna call this one test because it's not really gonna have any I don't know unified information so there's gonna be random so we're gonna start by having a name that'll be equal to variable character 50 and then what else could we have well I want to have a date so the date that this was created on so I'm going to say created equals date/time then I want to have a gender so I'm gonna say gender and this is actually going to be equal to an enum and when enum is is allows you to select between a few different values and I'll talk about this in a second once I finished typing it here so essentially when I put enum here what this means is you can select either male female or other so oh all right those are the three options that will be accepted for this gender block here and that's just what an enum value is now what date/time is it's actually a Python date/time object and you can see up here I've imported date/time from daytime so what I want to insert an entry here what I can do is use this date/time module it will give me the date and time and I can store it in the correct format in this now the last thing I want to add is an ID and I'll say the ID is int primary key I think I spelled that right and then what else do we need not to know Auto underscore increment like that and I'm just gonna add not note to the rest of these because we don't want them to be nope like that and last one and then we can execute this and we should be good to go so let's run this command the we go we're good a quick recap I mean that we did the enum thing that's kind of the only new value here primary key means this is now gonna be auto incrementing because we have this and this is gonna be the way that we access any element in test is by the ID so let's comment this out now and now let's actually insert some items in and talk about selecting so again quick recap here what I'm gonna do is just insert some items so I'm gonna say Mike my cursor dot execute and in this case we'll say insert into what is its test like that then we're gonna put the name of the column so we need name like that we created and we need gender okay so now that we have that will say values here and then we'll do % s % s % s like that now we can actually put the values in a what do you call it topple here and then we can add them in so for the name we will say Tim for ice will leave L capitals for the created date what we actually need to do is say date/time dot now and that's gonna give us the current date and time like at the current point in which we entered this in and then after this what do we need well we need to pick a gender and in this case I'm gonna pick male so again you have to choose between one of these three values otherwise that's not gonna work okay so now that we have that let's actually commit these changes so say DB commit like that run this and we're good to go and let's insert some more here just so we have some so let's say like Joe that'll be male as well and then let's add I don't know Joey we can do that one so we can test some comparisons let's just say that's a female and okay we'll stick with that for right now okay so now that we have that I want to show you the Select command and how we can actually select specific values from our table so I talked about it briefly in the last one but if I want to select different values that are inside my table I can actually put some conditions on them so I can select maybe all the females or all the males or all the people that have you know the name Joe year that start with the T I can do as specific as I want and that's what I can do with this select man so what I'm gonna do here is I'm going to a my cursor dot execute and in this case we're gonna say select star from test where and now what I can do after we're is put a condition so in this case I could say something like where and let's just pick a column let's do where gender equals and in this case we will say male now what I can do is loop through this so I'm gonna say for X in my cursor print X so let's run that and now we can see that we actually get the two entries that I put in my table which were Tim and Joe because they are both males okay so I just tried to order these a messed up but the way that we can actually order these entries when we get them so let's just run this one more time so we can see them say we want to order them by ID and we want the ID to be you know greatest to least what we can actually do is add another clause here that says order by and then when we can do is pick the column name so in this case maybe I'm gonna say I don't know ID and then here I will say either a SC which stands for ascending or DSC DSSC sorry which stands for descending so if I do that now you can see I when I go descending we're gonna get the greatest first and then it's gonna go downwards and that is kind of how we can execute these queries now say we don't actually care about all this information like we don't care about the date we don't care about the name maybe we just want the gender and maybe we just want I don't know some specific piece of information well what I can do is actually just put the column that I want here and it will only select that column from the from the entries so I say select ID from test where gender equals merit order by ID and now you can see that I just get two and I just get one now if we want more than one piece of information I could go like ID name and now you can see it's actually gonna give it to us in that order as well where we get the ID first and then the name and again obviously these are corresponding to the elements that are in there now I could change this to female and you can see that now we get Joey because that's our only female in there and that is kind of how this Select works you can get pretty advanced with this and do some like pretty complex queries but for now I'm just gonna leave it at that as that's pretty straightforward and you can already do quite a bit with that okay so now that we have that I want to talk about how we can a fire table so maybe once in a while you know we add some stuff into our table and we realize we need to add a new column or we need to change the name of a column or maybe you know it was a small Internet we need to make it a big int or something like that well how do we do that well there's actually a command for this in Maya school as you guys probably guessed and it is called alter so we can actually alter our tables so the way to alter table is you start by saying alter table and then you type the name of the table in this case it's going to be test and then whatever the alteration is that you want to do now in this case the most basic one we can do is add a new column so to do that I'm gonna say add column and I'm going to give the name of the column so maybe in this case we'll do like I'm trying to think of a decent example let's just do like food and this could be equal to var chair 50 so from that first example in that first video where we said you know maybe each person has a favorite food that's something we could do there now we can also add you know they can strange not know like that and now we can commit this so I guess DB Dutch commits or actually sorry I don't need to commit that I don't know what I'm doing now if I run that we're good to go and now what I'm gonna do is just run another command here so my cursor executes and I'm just gonna describe this table so that we can see what it looks like so describe test and then what I'm gonna do is print out my cursor and I'm gonna show you a cool way that we don't have to loop through it every time to see the output so what I can do is if I know I'm only getting one line of output I can actually just print out my cursor dot fetch one now you're gonna see this isn't gonna work for this but if I do that what it will do is just get me the first entry that is returned by my cursor so say we know we're only having one line of output we can just do my cursor don't fetch one and print that out directly rather than having to loop through it now since this has more than one column I need to show all of them so I'm gonna say for X in my cursor print X okay so there we go now we get all of our columns so we have name created gender ID and our newly added column food now let's say we decide that you know we'd actually don't want food anymore that's we don't want that how do we remove that from our table well to do that and actually I'll leave the describe so we can see it after I'm gonna say my cursor dot execute as usual and in this case all I have to do is say alter table test drop and then the column name so in this case the column name was food so let's do food and if I run this now you can see that when we read ascribe the table there is no column name food as we dropped it and got rid of it okay so those are some basic alterations but another one that we can do is actually change the name of our columns and even the name of the tables as well so to change the name of a column what you can do is say alter table test and then you can put the keyword and change the name of the previous column which in this case will just change name to be equal to first underscore name and then you have to redefine the type that you want to change it to so in this case varchar' 50 like that let's run that and you can see now we've changed name to first name so I just want to show you something that you have to be careful with when you do that do this so let's say I want to change the first name column we want to leave it the same name so first name but I want to change the type of it to var chair two or something like that now what do you think is gonna happen when I do this remember I already have entries in my table that have names right they already have variable character names and some of them have a length greater than two so what happens when I try to change it so that that information might not be able to be stored anymore well when I run this we actually get an error and it's saying here data truncated four column first name at Row one so it's essentially saying there's an issue here you can't do that because based on some of the entries you currently have in the database well we cannot change this type because it won't be local the information now if I change this to four though that'll work fine because there's no name inside my database that has a length greater than four so that is kind of how you go about changing the name of the columns now we can also change the name of the tables we can do all kinds of stuff there's tons of different alter commands but that is kind of the basics of how to do that so what I've kind of shown you guys so far here is just how to what do you call it create a table using like enums and dates and all that kind of stuff how we can alter the tables after we've done that and how we can select specific entries from our table using custom at bay queries so I'm gonna cut it here there is so many more queries I could show you I'm just trying to introduce you guys to some of them so you have an idea what's possible in MySQL because the reality is most of the time you're gonna have to look up how you want how to do what you want to do but in the next video what I'll be doing is showing you something called foreign keys which are really important and powerful for referencing other tables from specific rows in one table so notice that has been it for MySQL if you guys enjoyed this make sure you leave a like and subscribe and I'll see you in the next tutorial