Using MySQL Databases With Python

1. Python Mysql 1 Intro: All right. Welcome to the course. My name is John Elder from kota me dot com, and I'll be your instructor today. In this course, I'm going to teach you how to use my sequel, The Database with Python. So my Sequels and open source database it's been around forever. It powers things like WordPress and just way too many websites to even name. It's one of the older databases that's been around for a long time, and part of its popularity is that it's free. It's open source. Anybody can use it, but also it's really kind of fun and easy to use. So in this course, I'm gonna teach you all about my sequel database itself. But mostly we're gonna focus on how to connect High Thon to my sequel. And there's just tons of reasons why you would want to do that. You know, if you're doing big data stuff, where you need to access the database to your python script, if you're doing machine learning things like that, or if you just have a website that needs to access a database and you're using python as your programming language of this will help you to so um, you don't really need any prior knowledge to take this course. You certainly don't need to know anything about databases or my sequel. A little bit of python. If you already know some pythons, Probably good, but you don't necessarily have to know it. I'm gonna walk you through everything. And in the next couple of videos, we're going to install Python in my sequel in all the tools that we need and I'll walk you through everything you need to know. So I should note, I'm on a Windows computer. If you're on a Windows computer, too, that's great. If you're on a Mac or Lennox, you should be able to follow along. All the tools that we use are available on Mac analytics as well as Windows. Some of the commands may be slightly different here and there. I'll try and point those out when we get to those areas, but for the most part you should be OK. So ah, in the next couple of videos, we're going to start downloading all the tools we need. Before we do that, I want to spend a few minutes in the next video just talking about sort of one of databases . Just just give you sort of an idea of what we're talking about. Just a broad sort of overview in orderto So you have sort of a solid picture in your mind of what a database is, how it works. I find that Ah, if you just understand the broad overview of the thing, it makes using it a lot easier. So if you have absolutely no experience with databases, next video will be good for you. Um, but yes. So that's the course. One more thing. If you have questions along the way, be sure in and, uh, you should ask them. I'm here all day, every day, answering questions. I enjoy doing that a lot, so that's totally cool. If you have questions anywhere along the course, there are no stupid questions. I get all kinds of questions, and I'm happy to answer them all. So my name is John Elder and let's get started 2. Python Mysql 2 What Is A Database: okay, Before we get into downloading and installing all the tools we're gonna need, I want to spend just a couple of minutes talking about sort of broad overviews of what databases are. So I find that a lot of people have a hard time understanding or even sort of visualizing what a databases. So you know, they're these weird things exist usually up in the cloud. You put data and you take data out. What do they look like? How do they actually work? So if you're a little bit confused, if you just don't know, think of a database as a spreadsheet and I've got Excel. You know, the famous Microsoft spreadsheet software up right now. And databases air basically just big fancy spreadsheets. They have columns, these things that go up and down. They have rose, things that go across you can put you know things in and take things out. You can put all kinds of different things, and we'll talk about that in just a minute. But that's really pretty much all there is to it. You know, uh, all databases pretty much work the same way they and they sort all act like this So, um, here, let's talk about this a little bit. You have going across these air called arose in sort of spreadsheet terms. In database terms. They're called records. Same thing record row. Everything in a row in a record goes together. So, you know, you see, here we have John Elder me at dot com phone number, Uh, 10 West ohm, Las Vegas, Nevada. 89111 I don't know, 40. So this is a row, right? All of the stuff goes with me, right? If we then have another entry, we might have Mary Smith. Mary Smith. Weird formatting here. Only changed that road. Quick. You go. Uh, let's go marry at mary dot com or remember is 2 to 3234444 She lives at 11 Maple Street. That had Oh, Chicago, Illinois. Six of 610 She is 23 right? Clean this up a bit. Okay. So, you know, we have another record in another row, and everything in this road goes with Mary. So everything in a row goes together. Sort of keep that in mind. On the other hand, columns. They don't go together, but they're all of the same type, the same type of data or the same data type. We'll talk about data types in a little bit. But like, for instance, here age, everything in this column is gonna be an age you wouldn't put, you know, blue. That doesn't make any sense. Blue is not an age. Blue is not a number, right? Everything else in this column is a number in this column. Everything is a number. ZIP codes in this column. Everything is a state, right? So everything in a column has to be of the same data type. They're not necessarily. They don't belong to the same person. Like, you know, this age goes with this record. This age goes with this record. They don't go. They don't both go to John. But there are the same type, so of that's about it, Really. So when you think of databases, think of a big spreadsheet. In this case, Excel itself is the database, and you notice we're not really doing anything with the database. It's the table, which is this whole thing. The table is what we're doing stuff with. We add information. We add data into the table we can update information in the table data in the table. We can remove data from the table. We can retrieve data from the table. It's the table inside of the database that were really sort of gonna be interacting with. And you can see down here at the bottom. We have sheet one sheet to its empty sheet. Three. It's empty. These air additional tables so a database can have lots of tables. You know you can have a table for your customers. You can have a table for your email list Subscribers. You could have a table for, um, old customers that haven't bought anything in over five years. You know, whatever you want, you can have as many tables as you want, and you can pull stuff from each table. Um, at will. So that's pretty cool. But the database itself, you're not really doing anything with its all the table. It's all the records and the rows within the tables. So that's really sort all I wanted to talk about. I will talk about data types a little bit later. Um, there are certain types of data that you can use in databases, and you sort of need to know what those are. Every database has different data types. They're all pretty much the same. But they're all called slightly different things. So we'll learn about what Micro My Sequels, data types are a little bit later, but yeah, that's pretty much all I wanted to talk about. Just think of databases as big spreadsheets and you won't go wrong. So in the next video, we're gonna start to download and install the tools that we're gonna need in that will be coming up in the next video. 3. Python Mysql 3 Install Python: OK, in this video, we're gonna start to download and install the different tools that we're gonna need throughout this course, and we're gonna need three or four different things. They're all free. So that's cool. But we're just gonna knock these out one at a time. In this video, we're gonna look at installing Python, so just go to python dot org's. And I should say, If you have Python on your computer already, it's probably a good idea to uninstall it and reinstall it, and I'll show you why in just a minute, if you're on a Mac or Linux, you're gonna want to update a lot of Mac and Lennox. Come with Python already, but it's a very old version of python that you do not want to use. So you want to be using Python version three and above, and Mac and Lennox computers often come with Python to so we don't want that. So it's always a good idea to install this. If you're already have python on your Windows computer, I'm gonna It just clicked on my Windows Start menu and clicked control panel and then just go down to uninstall a program and Let's find Python and I'm just gonna really quickly uninstall this just to show you how to do it. It just takes a second. We might as well. It's really a lot better to do this, and I'll show you why in just a second. So I'm going to go ahead and pause this video while this goes in UN installs, and they will pick up right when it finishes. Okay, so I went ahead and finished uninstalling close that if you look, there's also his python launcher. Um, if you want to uninstall that to you can, it's not probably a big deal. I'll go ahead and do that. Just because you can see it isn't taking quite so long. And boom, that's gone. So once that's gone, we can head over to python dot org's and just click the download button right here at the top and you can see we can download for Windows. It knows I'm on a Windows computer. If you're on a macro Lennox, it'll give you the macro Lennox version and you see python 3.7 point. Oh, that's the current version. If it's a different version, by the time you watch this video. That's perfectly fine. Just download the latest version and should be fine. So I'm gonna go ahead and click that now we can save this anywhere. I'm just gonna save it on my Windows desktop, and it's not very large. 24.3 megabytes. It downloads on my computer almost instantly. So go ahead and click this and let's open this. You go. Okay, so here says, install python. Now this is the important part. See down here it says Add python 3.72 path and this is unchecked by default. This is why I told you to uninstall Python if you already had it on a computer, because chances are last time you installed it. You didn't notice this little check box, and you just click next like people dio what we really want. This checked it. Adding python 3.7 to the path means in Windows, at least that you can run python from anywhere on the computer, and we really want to do that. So it's very important to check that little box. If you don't check out, all kinds of problems can happen, and we just don't know how to deal with that. So go ahead, make sure that's checked and then click the install now button and initializing. So this is going to take just a second. Um, I think while this is going Oh, no, it's going fast. Well, in the meantime, while that's doing this, I also want to show you let's head over to sublime text. Just search for it on Google and you see the sublime tex dot com. This is the text editor I'm gonna be using throughout this course. Now you can use any text editor you want. It absolutely doesn't matter as long as it's a coating. Text editor. You know, Adam is a popular one. Um, what are some other ones? No pad plus plus is a popular one. Sublime is super, super popular. It's absolutely free. And ah, I recommend it highly. So go ahead and download and install this. If you want to follow along and use the same text editor, I'm not going to show you how to download and install. This is just a basic text editor. Just click the button and click the button again and it'll install for you. But you go ahead and do that yourself. In the meantime, let's check back and see how our python is going up. Almost done. So I will go ahead and pause the video. All this finishes up should be just a second. And we will. There goes almost done. I will pick up as soon as it finishes. Okay. So set up was successful. Everything looks OK. Go ahead and click clothes and that's it. We're done. We're ready to use python now. Um, so in the next video, I think we'll start to install something called Get Bash. I'll tell you what that is and why we need it. And that'll be in the next video. 4. Python Mysql 4 Install Git Bash: Okay, So even stop python. Now we need to install something called Git Bash. And if you go to get desh SCM dot com, you can download this cool little thing and all this is is just a little terminal, and it allows us to run terminal commands much like mackerel Lennox, but on windows. So, you know, in order to run Python, you need a command line terminal of some sort. And Microsoft has the command prompt and power shell and those work. But get bash is just much, much better. Um, it allows us to do version control using get, which I don't think we'll be doing in this course because we don't really need to. But in real life, you want version control. So you want to use something like this, get bashed thing, and it's super free and super easy and very lightweight and very cool. So we're just gonna go ahead and download and install that in this course. So come down here and you can see this in this little monitor picture. Here. You see this download 2.18 point zero for windows. If it's a different version, by the time you watch this video again. That's perfectly fine. Just download the latest version. So I didn't click that. And your download is starting Pops up, Save this anywhere. I'm just going to save it to my desktop again. And this is 39.2 megabytes. But for some reason, every time I downloaded, it takes forever. It must be the website or something, because it always takes me a long time. Download this. It should be almost instantly. But it isn't for some reason. So I'm gonna go ahead and pause the video while this downloads and we'll pick it up right after it finishes. Okay, so we've gone ahead and downloaded it so we can go ahead and click this and run it. And this actually pull this over here is a little bit tricky to install. There's a couple of things we need to click in order to make this work crackly, so I'll walk you through it here. So right away. Make sure this get bash here. Thing is clicked. We probably need that. Not a big deal, But go ahead and click next. Uh, next. Okay. Here's one weird thing. It asked for the default editor to use and it's saying use of them now we're not ever gonna use get in an editor, so we could just leave this as the default. I don't even know what this is Kind of weird, So just click next. And here's the important part. Adjusting your path environment. We want to use git and optional UNIX tools from the Windows Command. Prompt. Right, So this will allow us to use Get from the Windows Command prompt if we want. We probably won't do that. We're going to use the little terminal. It comes with this instead, but you know, it's good to click that anyway, So click next. Now we want to make sure open Ssh is clicked and SSL library check out Windows style with commit unique style line endings. That's good use men TT Why, that's good, too. All of these could be the default, and that's it. So this should install pretty quickly, but I'll go ahead and, you know, I was gonna pause until it's finished, but already halfway done. So I think we could just chat for another couple of seconds until it finishes in that school. So, um, it really doesn't matter like I said earlier, what terminal you use for this course. Because all of the commands we're gonna be running in our get bashed terminal. You can run in Windows Power Shell or the Windows Command prompt that come installed with windows. Um, but for people that are on macro Lennix, I can use Mac or Lennix type commands in this get bashed terminal, which is really, really cool. So everyone should be able to follow along in that school so we can uncheck this view, release notes. So let's go ahead and launch. Get bash, um, finish in the future. If you want to turn this thing on, just go to your Windows Start menu and search. Forget G I T. And that should work. So that's it. We can type in sort of commands and the school so we'll use this going forward. I'll tell you how to use this and what it can do all kinds of cool stuff. But I think that's all for this video and the next video. We'll jump in and start to download actual sequel, my sequel and ah, look at the different tools we're gonna need for that. And that's coming up in the next video 5. Python Mysql 5 Download Mysql: Okay, so we've got Python installed. We've got get bash installed. Now we want to start to install my sequel. And this could be a little tricky for people because I don't really know why. It's open source and a little glitchy sometimes. So it may take a little bit of doing to get this installed properly. So we need two things here. We need my sequel, the database software, the server. And we also need something called the My Sequel Workbench. And the workbench is sort of a gooey a graphical user interface that lets us access are my sequel databases add information at data removed data do all kinds of cool stuff. Now, we're gonna be doing most of our stuff in this course with Python. So we're gonna be writing Python code to do this. But it's still nice to have the work bench to check and see that things were correct. And it's just a cool of told 1/2. So Well, download. Install both of those. You can install them separately or you can install them together. We're gonna go ahead and download them and install them together in this video. So go to my sequel dot com You concert for my secret Google Or just go to my sequel dot com and click the downloads link here at the top. And you see, there's all kinds of different options of what you want to download. So you see, the ones at the top are all commercially have pay for him so that my sequel Cloud Service, Commercial Enterprise Edition, Commercial Cluster commercial and Heard The Bottom is the my sequel community addition. And that's what we want. GPL is open source license so but we click this community GPL download link. Okay, so now we get this my sequel, Community server link. And here is the workbench, but we don't need to install it separately. It'll install with the version we're gonna download in just a second. There's all kinds of things. Here are connectors repositories. Uh, we don't really need any of those things were going to get everything we need bundled together in one download. So go ahead and click this my sequel community server link and scroll down here and then click on your operating system is probably already selected. Mine is this is Microsoft Windows and then come down here and we want this my sequel installer for Windows, and it has everything we want all in one package, you see. So we'll go ahead and click this. Otherwise, you're gonna have to download a Zip archive, and it's all kinds of hassle, and we don't want to deal with any of that, so I could go to the download page. Now this is the download, my sequel installer download page and again we go down here and select the operating system that we want in our case. Microsoft Windows. So here's the weird thing. Here's two versions, right? One is only 15 megabytes. The other one is 273 megabytes, and you may be tempted to download this smaller version. But don't do it. What this is is the small version here. It just downloads a small little package, and then you run that package, and then that package downloads the rest. Right? But it's a little buddy. I found it not work very well. It'll start to down load things, and then the download will stop and it'll freeze and it won't start back up again. And there's no restart button. It's just a hassle in my opinion. Just download the full version, 273.4 megabytes. Get it all downloaded at once, and then you can install it. And it works just much easier. So go ahead and click the download button for this thing, and it asks you to sign up for an account. You do not have to do that. I don't recommend you do that. There's no reason to just go down here to the bottom is and click this. No, thanks. Just start my download link. Do that pops up, install this anywhere you want. I'm gonna install it on the desktop. They see I've already installed on my computer, so, um, don't have to do it again. But you go ahead and do that. It is 273 megabytes. It's gonna take a while. I'm gonna go ahead and pause this because, like I said, I've already downloaded it. Cancel. But you go ahead and do that. And in the next video, I think we'll go ahead and actually install this thing 6. Python Mysql 6 Install Mysql and Workbench: Okay, so we've downloaded the thing. Now we can go ahead and install it. So you want to click this thing right here in the bottom? Once it's finished downloading myself? I've already downloaded it. So I'm gonna go over to my desktop where I unloaded it and double click it there. Either one works well. This over. Okay, so we get this my sequel installer, you may have to restart your computer after you install all this stuff. In fact, I probably recommend that you do that just to make sure a little message we want to on. Yes. Okay. So gathering some information now, this could take a little while to install. So that's why I sort of put it on. It's own video, but we'll walk through it and see what we can see and should be fun. Okay. My sequel installer. 1.4. If it's a different version, by the time you watch this video, no big deal. Okay, so right off the bat, we get a license agreement, okay? And then next. Okay, so there's a bunch of different things we can install. Weaken, go full week ago, Custom. We probably want this developer default and its social. All the products that we need for development purposes. So going click that already it's already clicked. Just go ahead and click next If you wanna go back and look through here. These are the type of things that comes with it. Sequel server itself a shell router. The nice equal work mention which we're definitely gonna use. My sequel for Excel. I don't know what that is. Some other things. So go and just click next. Now let's see some products have path conflicts. Let's see, I'm getting this. Probably cause I've installed us in the past. You probably won't get this. Um, Selected path already exists. Yeah, so we just leave this way it is. You won't get this, Eric is like I said, I'm only getting this because I installed this previously click next. Sure. I want to continue. Yes. Ah, there's some weird requirements. It says 3.6 version of python has not been installed, but we installed version 3.7. I think so. It doesn't really matter. We can just ignore these If you want to try and, um, fix them manually, you can. But I think you could just ignore this. Always have in the past. Click next if you continue. Yes. OK, so these are all the things that's gonna install, right? So we could just go ahead and click execute and let it just do its thing so it can take a while to do this. Or sometimes it could be very speedy. Just sort of depends on how your computer, how fast it is and how honoree it is. I think so. Already. We've got my sequel server installed. Next. We're working on the workbench and these two things these top two things. The workbench in the server. Those are the two most important things that we really need. You probably need some of this other stuff, but it's not that important. Uh, that heard the bottom samples and examples. Well, actually, look at that. Just to get some get an idea of what we can do with the database. You know, there's, like, fake data in there that we could play with. It will play out, play around with that a little bit, but it's not terribly important that you ah, you know, install all that stuff. My secret documentation. You can take a look at that. If you want. I don't think I ever have, but it's It's in there if you want to, you know, take a look at it. That should be cool. So I'm gonna go ahead and pause the video because this is gonna take a while to install. We'll go ahead and pick this up as soon as it's finished. Okay, so everything has installed. It has been completed. Go ahead and click next. Okay, so now these things need to be configured. Click next here. We just want this stand alone. My secret server thing at the top. The default that works. Fine. Click next. All of these things we can keep is the before the default. Uh, recognize here this port 33 of six. Sort of important. Um, I don't really need to make a note of it, but just keep that in mind. TCP I p All these things we don't really care about any of this stuff. Click next. Okay, so it's gonna ask for a password, and, well, first, it wants to use strong passwords. That's good. Go ahead and click next. Okay. So every time. So you've got your database right has a user, and then that user has a password. And in order to connect to the database, especially from like Python, you need to know the password and the user name. Most of the time, the user name is just route. That's just what it is. But then we need to pick a password. So if you're gonna be serious about this stuff, if you're gonna use this for, like, actual web development stuff, pick a very strong password We're not going to be, and we don't really care. So I'm just gonna type in password 123 Let me do that again. Password. 123 Repeat it. Password. 123 Again, I'm not suggesting you do that, cause you can see right here is very weak. But just for our purposes of learning this thing, it doesn't really matter. So that's why now you don't need to add a user because route already is a user. So we're gonna be the root user in computer science route is like the all powerful super user. It's called the root user. So just for your information, So go ahead and click next. Ah, we want to configure my secret servers. Windows Service? That's fine. Leave all these things as the default. Okay, so now we just click this execute to apply all those things we just did, which wasn't much anything. We took the default for most of those things, all right? And you could see now it's initializing the database, which means it's turning it on. And this will, I think, pop up or this will The database will turn on in the background. I think every time you start your computer and you can configure it not to do that if you want, but it's gonna be on from now on. And that's good, because we don't want to turn it on every time we want to access it from our program. We want it sort of just running in the background, always. But if you don't want that, you can. You can change the settings. I'll leave that to you. It's pretty simple. You can just google it or something. All right, so let's see. I'm gonna go ahead and pause this again while it goes through all these, and we'll pick it up a soon as it's finished. All right, so all the things have finished and we're pretty much done so we can click this finished thing. Oh, no. We need to figure some other things. So go ahead and click Next. Um, we don't want to click this. This in O d B cluster thing is not something we want any part of the click finish. Next again, we want this standalone server thing. Yes. Okay, so let's go ahead. It wants us to type in your password. Remember? That's just passed. Has word. 123 All connections succeeded. And this just runs a little test to make sure it's running that we're able to connect to it , that our password works and all that stuff. So that's good. Next, Um, one last execute, I think. Okay, so we're done with that, Like, start my sickle workbench after set up. Actually, let's not do either of those. You can if you want to go and click this moment, gonna show you how to start it from scratch. So in the future, you could start it without having to go through all this again. So go ahead and click finish. And I think we're done. So in the next video, we'll dive right in and start learning how to connect Python to my sequel and make sure everything was set up correctly and move on from there. 7. Python Mysql 7 Install MySQL Connector: Okay, so we've installed my sequel. We would still get bash. We've installed sublime text. We've installed Python, so we have all the tools that we're gonna need. Now, we just need to start putting all these things together. Start using them. So first, let's head over. Target Bash Terminal. If you've already closed this good, just go back to your Windows start menu and typing get G I t. And then the little bash thing will pop up and you can click on that to open it. And here we have the terminal. This is it. So you could see I'm in this pwd pwd. I'm in this sea users Flat Planet directory. This is just my default directory. And it's flat planet because that's the name my computer, because I think it's hilarious that some people still think the world is flat. It's gonna be whatever your computer user name is, that's the directory you're gonna be in. So let's go ahead and make a new directory to store our python files that we're gonna be creating throughout this course. So just type in em que de ir that stands for make directory that works on mackerel. Lennox to and let's go forward slash c And let's put let's call this created directory called my sequel. Okay, so now if we go CD, which stands for change directory, we can change into my sequel with screen so I could see Yes, I'm in my sequel type in l s. There's not actually anything in there at the moment. That's fine. We haven't created anything yet, but before we move forward, we need to install one more thing in the ah, in the background here. So, in order for hi thon to connect to my sequel, we need ah, module that allows us to do that. And there's three or four of them. They're all the same, but, well, slightly different, basically the same. But they're all they're all called slightly different things. And the reason why is because sometimes one of them won't work on your computer. So the thing we're installing is called my sequel connector, and it's just let me just copy Hiss here to install this. We're going Teoh type in the command Pip install and Pimp is the Python program installer and one of the reasons why we click that ad path box when we install Python is so that Pip can install programs anywhere on our computer. So have been stall. And the thing we're gonna install is my sequel connector. Now, sometimes this works. Sometimes it doesn't right. So I'll tell you what to do if it doesn't work and I'm getting a little anti virus thing on my other monitor, you can't see it. You can just abort that. It says this is trying to connect to the Internet. Is it OK? Yes, it's OK. Um, so now it's doing its thing. I've already installed this in the past, right? So it doesn't have to install it again. Years will go through a little thing and it'll install it. Okay, so that might be enough, right? But there's two other ones we can install just to make sure, because when I install my sequel connector, it doesn't work for me on this computer. It's worked on other computers for me, but not this one. So I go to the next one, which is hip. Install my sequel Connector Dash Python. You see, the 1st 1 was my sequel Connector. That was my sequel. Connect your dot or dash python. Same basic things slightly different. This one might work for you. You see, I've already installed it, so I'm getting the same thing. Finally, there's 1/3 1 and clear this screen here and it is Just see haste, Pip, Install my Super Bowl connector dash python dash R f Right, so we're just adding things on to the end to make them slightly different. But if the 1st 2 don't work, go ahead and try this one. And I'm not going to solve this one because the 2nd 1 worked for me just fine. I know it's kind of, you know, kind of wonky, but that's just the way this is. I'm not sure why. Okay, so in the next video, we'll go ahead and start to write our first python code. 8. Python Mysql 8 Connect To Database In Python: Okay, so we've installed everything we need to install. Now it's time to start writing some python code. So let's just open a file here in sublime text, and I'm going to import that thing we just installed in the last video. So my sequel dot connector. All right, so we want to save this right away as a python file. So come up here and click, Save As and navigate to that directory we created a while back. It wasn R C. My sequel folder, and let's just call this I don't know database that's fine and come down here to all files and programs or whatever. And look for High Thon right here. It's like that python and then click safe Another way you might do it. Hit, cancel here, come up here to view and then syntax and then scroll down to Python. You see Now the colors change a little bit. So now Sublime knows this is Python. So now when we come apparent hit file save as it already see, it's already selected python for us. Just another way to do it. So again, let's scroll down to R. C. My sequel folder. Let's call this database and save it. Okay, so we've imported the my sequel connector module that we just insult now if you had to. If you try this and it doesn't work, and then you have to go back and Pip, install my sequel Connector, Dash Python or Pip Install my sequel Connector Dash Python Dash R F. You can still get away with just importing my sequel dot connector. You don't have to import my sequel dot connector dot high Thon, for instance. I believe that's the case. If if you run in and stole doesn't work, try doing that. You never know. OK, so, uh, now we need to create an instance of our database that's running in the background. Remember when we installed the database? It fired up it initialized, and it's just sort of in the background. Scuse me running now we need to connect to it. So let's create an instance of variable sort of speak. And let's call this I don't know my database and set it equal to this. My sequel dot connector dot connect. Now we need to pass in a few variables, so we need to pass in at this 0.3 variables. The 1st 1 is host and then a comma. The next one is user Mama. And then finally, password pss W d Um, two quotation marks And then I don't think you have to put a comment the end, but I always do, just in case, because we're gonna be adding things in the future. And if we forget to put it on there in the future, we'll get in there. So I just put it on there now. So, host, this is the earl of your database. So most of the time when you're working in actual Web development or something, your database is up in the cloud somewhere and you'll have a U R L for that database like 192 0.168 dot 40.9 or something, right? We're running our database on our local computer on her own computer so we can just type in local host here right now. Remember, when we set the database are user was route, That's route and that password we picked when we set up the database was password. 123 right, so that's pretty much it. We can now connect using just that. So to prove that this worked, we can print out this my DB variable that we just created. So if we save this head over to our little bash terminal thing and pwd to make sure we're in this see my sequel directory LS to list this stuff. We could see hope there's that database that pie file. So let's just run this thing. So python database dot p y. And this is how you run python programs. You type in python and then the name of the program. Boom. We get this my sequel dot connector dot connection, not my sequel connection dot object. So this is Ah, we've created an object here and this is just Ah, you're Elna TRL. It's a an address in memory where this thing is sitting, So this means absolutely nothing to us, except it means that it worked. It means the database was set up correctly. It means we're able to connect to it from our my sequel or from our Python program, and we haven't done anything. We haven't entered any data or queried any data or anything like that, but this is important. We now know it works so This is very cool. And really, this is Aziz AEA's. It gets. I mean, it's not any more complicated to connect to a my sequel database from a Python program. You just import this my sequel connector thing. Create a connection here, add in your host name your user name and your password, and then, boom, you're connected. So now it's just a matter of learning. The different commands in Python to, for instance, create a new table, then to add data to the table than to retrieve data from that table ordered in different ways. Things like that. All the basic database stuff you want to dio, and that's what we're gonna spend sort of the rest of the course learning how to do. But meat and potatoes of this stuff is done. We've created a database, we've connected to it, and we're good to go. So in the next video, I think we'll look at the my sequel workbench and sort of get ah, sort of understanding of what is all available to us in our database, and that's a good way to look at it through the my sequel Workbench, because it's a graphical user interface, and that's really cool. So we'll I think we'll start to look at that in the next video 9. Python Mysql 9 MySQL Workbench: OK in this video, we're gonna take a quick look at the my sequel. Workbench is the little software that installed when we installed my sequel, and it's actually really cool. It gives us a graphical user interface, a gooey for our my sequel database. So now, throughout the rest of the course, we're going to be doing mostly all of this stuff in Python. But I just want to spend a few minutes kinda looking at this software because it's really cool and you'll find it useful. Ah, you know, for debugging for making sure you've got things correct and generally just looking at your database. Sometimes it's better to just look at it instead of running a python program and pulling data out manually. So that's what we're gonna look at in this video. So it over to your Windows Start menu. This is on my other monitor. You can't actually see it, but go to the little search thing and just type in my sequel. And the first thing that pops up will probably be the my sequel Workbench and Ours Aversion . 8.0. C. E, which is Community edition, I think, which is the free thing that we downloaded the free version and pull this over and this is it. So it's really cool. You see, right here we have our little instance of my sequel that's running here's route local host . Why it's called Lam p. My sequel. But, uh, I think I have LAMP, which is another program PHP program installed. Maybe that's why it says that, but you can add other connections or whatever, and we have some things here on the side, so I'm just gonna double click this and boom, this thing pops right up and this is it. It's sort of everything right here, and it kind of looks overwhelming. There's a whole lot of stuff here, but it's not too bad. So over here you see these schema as when you create database tables. Um, a schema has created. It's sort of like a I don't know, a snapshot of the database, and you can see we have three of them already. Three tables or three databases, I should say, already installed. And that's because when we installed my sequel stand this a little bit, we installed some documentation and ah example data. That's what this stuff is some of this example Data. So here we have this world database inside. We have some table. So if we click on that, we see City, County, country, city, country, country language, Um, and some other things. Let's click on city. And we have even more than we have columns. Ah, I d name country codes. Let's go on name. So if we double click this right appear just his name. So see this little lightning bolt up here? This allows us to run sequel queries, and we'll talk about sequel queries coming up in, You know, some further videos from now, but basically, whenever you do anything to the database whenever you pulled it out, take data in, do anything at all to a database, you're running a query on the query language. We use a sequel. That's why we call this my sequel. So this is just a very basic Secret Command, I guess if we hit this little thing, um, the air code Yeah, that's not a good secret command. I'm gonna go ahead and close this or delete it. Okay. Ah, instead, that's right. Click here, and what we want to do is select Rose limit 1000 and we hit this boom. We get this actual graphical unit user interface representation of all of the names in this specific column in this specific table. I guess so. We seek a bowl. Rita, you know what some of these are Era San Fernando, and we can expand this. Make it bigger. Sydney, Perth It's just the list of looks like city names around the world. And here's actual sequel statement of the sequel Query Select Name from World dot City. And we'll talk about a sequel queries and things like that later on. Not that important. Um, let's see. Let's go back, get a better view. I'm just gonna click this city table here. Let's click Entire thing. So we have name, country, code, district and population Cool. Interesting data here, and we don't care about this. Data is just example text that came with this, but kind of interesting. No country. So it's in countries. The right clicks like Rose. Basically, what we're doing here is we're selecting the 1st 1000 rows in this table all right away kinds of stuff in this one. So we have the name or Ruba continent, North America region Caribbean surface area population, life expectancy, all kinds of need information. Here. We might play around with this a bit. Government form republic, non metropolitan territory of the something Netherlands Looks like fun. Head of state George W. Bush. Uh, so I guess the head of State of the of America Samoa in Oshi, Anna, which is in Polynesia is George W. Bush. About that, this must be a little bit outdated. Capital codes. Interesting. Very cool. So and this is just, you know, just a way to look at your data and going forward. We're gonna build our own tables and put some sort of practice data inside of them, and we can come in here and look at them in here if we want Teoh. Um, you know, it's kind of useful from time to time, so, you know, if you're doing like Web development, you're not gonna probably use this workbench. But if you're doing something locally, um, and you just want to visualize your data, just want to see it without having to write a bunch of python code to do it. This is a great way to do it. So not only can you see things, but you can actually add things and edit things. Say we want to change Aruba. We can call this a room buzz if we want right and we just come down here. Actually, we tab away and we come down here to apply. Click that it creates the sequel code for us. Update. World Country, where we want to set the name equal Aruba's where the country code is a VW. So if we apply that done, if we come up here and run this again Rubio's it was disappeared completely. That's running again. Aereo Aruba's very cool. If we double click this, we can change it back again. Enter, Come down here and apply. Apply this again and boom! It's back to Aruba. Refresh it, Aruba So very cool. And we'll come back to this workbench from time to time as we go through this course just to sort of look at things and, uh, you know, teach a few things here and there that are interesting is may, but, uh, very cool comes free with my sequel. That's the my sequel work, Pension. That's all for this video. In the next video, we'll start to actually write some more complicated python code in order to create tables and at our data and do all the fun stuff that will be starting in the next video. 10. Python Mysql 10 Create Database: OK in this video, I want to create a new database and show it from a python program. So we're here in the workbench right now. We can create our a new database here. If we wanted Teoh, we'll look at doing that. Maybe a little bit later, but we want to do this. And Python. This is a course in how to do all this stuff in Python. So the one thing I do want to show you before we go is see my mouse. It's sort of floating around here. That's a mouse cursor, right? And what I want to do things in here, I do them with my cursor. If I want to refresh this, I take my cursor and I refresh that right click this little thing if I wanna, you know, apply something. I take my cursor and I apply. So we sort of do the same thing in Python. But we, instead of like, obviously dragging a cursor around we're not gonna do that were actually writing python code. But we're still going to create sort of a an instance of a cursor. It's called a cursor and that cursor consort of execute commands in our sequel databases in our case, my sequel database. Right. So let's take a look at how to do this. So we don't need to print this anymore. We're gonna keep all of these things always right here at the top of our program, cause we always need these whenever we want to do my sequel stuff from a python program. So what we can do here is let's create an instance of a cursor and I'm just call this my underscore cursor S o r o k. You can call it anything you want, but it's a cursor, so I'm gonna call it my cursor. Now, we need to create an instance of this in our, um my I d be connection. Right. So we just go my db dot Her, sir. And then But the little things there, because this is kind of like a a function. We can pass things in through there, Right? So Okay, we've created this thing. Now what do we want to dio? Well, let's create our own database. We've got the data base running, but we don't have our own database that we've created yet. You know, we did this. We pull us up here. We looked at this world database, right. Well, that's fine, but we want to create our own database. So how do we do that? Well, we can just go use our cursor. We're gonna use the cursor for most everything from now on. So just sort of get used to this her sir thing. Right? So we always start out with naming at my cursor, and then we want to execute a command, so we type in, execute and then pass in some parameters. So what do we want to do in this specific case? We want to create a database. Just a database. Not the table yet. We'll do that little a little bit later. We just want to create the database. So we passed in some quotation marks and we just type and create database, and then we need to name our database. And let's just call this test D B right? And that's it. So notice these air capital letters. These air this air sequel commands, right? So we're passing a sequel command into my sequel. And the sequel command in this case is create database misfolded database. There we go. I knew that should be a different color. So we're creating a database. We're calling it test DB Now we can just run this and it should work. So let's pull far or get Bash and we could just run Python database. Stop people I and nothing happens on the screen, right? Because we didn't actually tell it to show anything. But we can pull up are, um, my secret workbench here and come over here to scheme us and click this little refresh button and then boom. You see, we have this test DB Now there's nothing really in. If we click tables, there's no tables, there's no anything. But we created the database and it's in my sequel and ready toe. Start working so very, very cool and pretty simple. Just one little. Well, too little lines, really. But this doesn't count, cause we're just always going to do this to sort of initialize are cursor one little commanded to execute. So very cool. Now, before we move on, Let's we just looked at my sequel workbench to see if the database existed or not. How do we do that from a python program, if that's what we want? Very simple. We just go My underscore cursor dot Execute, Execute a lot of Koreans here and inside of here. We want to pass more sequel commands. And so we want to show databases, All right. And then this is going to show a list of databases, because if we pull back up our work, which we see, we have 1234 right. We can just run this command. Um, c plus back up. We could go Something like print. Um, my cursor. Right. But it's going to show an object like we did earlier. We could save this here. Let's come up here and we don't We don't need to run this create database command again because we've already created it. So let's just say this real quick and come up here and run it. And you could see my sequel cursor show databases. It shows us that command. It doesn't show us what we actually want to see. So that doesn't work that great. Instead, we can create a four loop that allows us to look, loop through all of the databases that air there and print them onto the screen. So let's do that real quick. instead. So we just go for create a variable databases in my cursor, and then we just print that d be right. Let's save this and over here and run it. And boom, we get a list A nice little sort of truffle list of each of these databases in our my sequel database. So we have information schema. We have my sequel performer schema at the secularist Sister Tess TV and world. And if we open this up here, we have the same secure assist testy B and world. Those other two are hidden ones that are just sort of system databases that we don't really care about. So very cool. And ah, pretty simple. So, you see, when we printed this out, it printed it out as a sort of, Ah, big tubal. And that's what this is. It's a tubal. So if we want to, you know, be a little nicer about this, we could do something like, um, called zero with item of the tubal. Something like that that might work. Let's see what that looks like s and then it looks a little bit nicer. And then you can run some formatting and we'll talk about that later on, As we create reports and stuff in python have a format will show you how to do. That would be cool. So I think that's all for this video and the next video. We'll create our own table and then start to put data into the table and that'll be cool. That's coming up in the next video. 11. Python Mysql 11 Create Table: OK, in this video, I'm gonna show you how to create a table. So up until now, we just created this test DB database and then we showed the database. So how do we create a table? Well, first things first. Now that we've created a database test database, we need to sort of let our program no. From now on, we want to use just that database. So we head up here to our configuration at the very top here, and it's pretty simple. We just need to just type in database and set it equal to something and put a comma at the end. Now, our database was called test DB, and that's really all we have to do. So I'm gonna go ahead and save that. Then you could see here. I've started Teoh comment out the thing we did in the prior video, and I think I'm gonna leave these in here and then at the end of the course, I'll give you all this code so you could have all this as sort of a quick reference guide. I think that probably helpful, and we'll just keep on moving down the page here, doing new things. So in this video, like I said, we want to create a table. So, like everything we always want to do, we want to start out creating cursor and set that equal well, actually want to execute. And then this time we want to pass in a whole bunch of stuff. So first off, we want to do a little sequel. Command in the Secret Command for creating a table is create Abel. You're gonna find that sequel commands Air. Pretty simple and straightforward, you can learn sequel as a programming language. It's not really a programming language. It's just this easy. If you want to create a table, you type create table. If you want to show a database see type show database, you know it's pretty straightforward, so we'll see that moving forward. So let's think about the type of table we want to create. Let's say we've got a I don't know a website, and we want people to be able to sign up, so we want to create users. And so let's call our table users now inside of here. We need to pass in all the stuff that we want in this table and we do that like this. So we haven't actually talked about data types yet, and we will very shortly. But every time you create a new column, that's what we're basically doing here. We're creating all the different columns we want for each row of our database here of our table. So each each row or each column has to be of the same data type we talked about in that very first video. And here's where we designate what that data type is. So I'm just gonna go through here and type some of these out, and then we'll talk about him afterwards. So let's say we want ah, name, field. Now you can have first name last name. I'm just gonna keep it simple for this. So we want name, and we want the data type for that name to be var car. And then inside of that, we want to say to 55 now to 55 is the number of text items. You know, the number of things that will be in that column. So the max is 255 so I'm gonna put 255. So, for instance, you know if somebody types in. John Elder, right, that is 1123456789 10 characters long, right? As long as this, as long as it's less than 255 we can put that in there in that column. So it's just sort of ah, you know, tells you the max amount of characters that you can add. So that's far car. And we'll talk about Var car and some of these other data types. Um, you know, moving forward. So let's see, what else do we want? Let's put email, and that's gonna be a var car again. Let's just put to 55 now. Back in the day when databases were new, Um, you really wanted to be careful and put like just the number of characters that you needed because memory and space computer space I was very expensive back in the day and there wasn't much memory available. Toe. Add these things. There wasn't much, you know, disk, space, toe. Hold this stuff these days, you know, appears a huge Web servers are huge. It really doesn't matter how many characters air in each column, so I tend to just put the max for everyone because, you know, who cares? If you're working for Google and you need to scale and it needs to be lightning fast, then maybe you're gonna Instead of putting 2 55 you might put 50 or something. You know, you might scale it down to just to get that 100 1000th of a percentage point speed, you know, thing we don't care about that. Right? So okay, Mark our email comma. Um, let's say we want the person's age, right? So age is gonna be an integer and I don't know, 10 10 characters. I mean, really, we need three characters that the most Nobody's more than 900 years old, right? 999. So I'll just put 10 just because, um OK, so and then finally, we want to put a user i d. So I'll just go, user, I d And this is a little a little interesting. We want this to be an integer, and we want this to auto increment, and we want this to be the primary key. Now, this is a whole lot of stuff. You don't know what any of this is yet but we want some number that unique for each row. Right? So if we want to look up John Elder Well, there might be 10 John Olders. And are you know, people have the same name, you know? How do we designate? Which one's which will they need a user? I d And those numbers need to be unique, right? So that's what this does. It creates a unique number. It assigns it automatically. So he just says, Who was the last one of the last one was number eight. This guy's number nine. Next, we'll give me number 10. You know, he does that automatically. Auto increments for us and primary key. Actually, we need Teoh make that capitalized. Primary key means that this is the main key that we're going to use to keep things. Um, you know, uh, keep track of things. You can have lots of different ideas. This is gonna be the primary one. It's just a database thing, so that's pretty much it that will create a table. Now we can test this to see if it works. We could go my cursor that execute, and then we can pass in show tables, right and then we could do the same thing we did in the last video. We can go for, um, table in my cursor and then go just print table. So let's save this and run it, see if we made any mistakes. I make a lot of mistakes. I'm always doing typos. So let's just run this and see. And boom we get users is the only table. And likewise, since this is a couple, we can also do it like this at 00 with item in our trouble, you can run this guy again. That's what we do. Oh, I ran this again. It tried toe, create another table. It comment that out. Since we've already created the table. All right, run this again. Boom users in a nicely better formatted. And you notice if we pull up our workmen? Sure, we have. In this world thing, we have lots of tables, right? City, country, country, language, etcetera. None of those things. They're showing up on our get bash. When we run this command that second here, you know, we're showing tables. Why isn't it showing all those other ones? Well, that's because up here we told the program. Oh, no, we don't care about any of those other databases. We only want the test TV, so it's going into the test tp. It's calling all of the tables in that. In this case, there's only one table, the see what we call it users table. And likewise, we could pull it the workbench again, come over to our schema as hit, refresh and then come down to test EBI tables. And then, boom! We see this. Users select rose 1000. There's none left where there's none yet. We haven't added any people into our database, but you could see here the different columns name, email, age and user I d. So very, very cool and pretty simple. Pretty straightforward. So that's all for this video. In the next video, we'll look at adding data to our new table, and, uh, it should be a lot of fun, and that'll be in the next video 12. Python Mysql 12 Insert One Record: OK in this video. I want to talk about inserting data into our new table. So unless we do, we added a table. We've got this users table. Now. How do we put stuff actually in it? Oh, there's a couple of different ways. And I think in the first video, we're gonna look at the 1st 1 and that's just adding one row one record. Now you can add a bunch of records at the same time, but in this video we're just going to do one thing in the next video. We'll show you how to do many at the same time, so it's pretty straightforward. We just go. Basically, we create, um, a variable. And let's just call this I don't know, sequel stuff. It doesn't really matter and set this equal to now. We need to create a sequel command, and when we add things into a table, were inserting them into a table. So the sequel command to do that is insert into right now, we want to insert it into our users table, right. And now we just need to pass in, um, what we're gonna be putting in. So we had a name and email and an age we don't need to put the user i d because the database will add that automatically, since we put this auto increments thing so we don't have to deal with that. So let's go. Um, name comma age. No, it's going name common email. Common age doesn't really matter. The the, um Well, yeah, probably doesn't matter the where you put them. We probably shouldn't do age email the email age because that's the way they look in the actual table itself. So then we go values, and here we're not gonna put the actual values right here. We're gonna put placeholders and for a placeholder, we put a percentage sign s. And that's sort of like when you interpret late in a string with python, you'll do the same sort of kind of thing s oh, that's what that is. So comma. And then we'll do it again and then we'll do it again. I go and OK, so we do this three times because we we have three things we're putting in here, right? Makes sense. And let's see what else I think that works. So now we need to create a record right, and we can call this anything we want. Let's just call it record one and let's go. Let's add John and my email is john at kota me dot com, and my age is 40 and we need to put this all in brackets. Let's forget that for some reason. Okay, so you'll notice John goes with name. This email goes with email, and this age goes with age. They're in the same order that we listed up here, and that's important because the placeholders will put them in the order that you put them in here. You'll also notice that the strings or of our cars, which we still haven't talked about what we'll talk about in a minute, uh, are certainly by quotation marks. And the numbers are not generally speaking. When you do numbers and python, you don't put them in quotation marks, because if you do, they turn into strings. They're no longer numbers when you put it in quotation marks. So no quotation marks. Okay, so what's so now? We need to actually put this into the database, and like everything we've done so far, we need to use our cursor. So let's go my underscore. Underscore cursor dot execute cute and then we need to pass in two things. First, this sequel stuff you go and then this record one. Very simple. Now, if you just run this, it'll put this in the database, but it won't save it. So if you know anything about databases, if you've ever done like Web development databases with, say, ruby on rails or Django anytime you put stuff into the database, you need to commit those changes. It's calling its called Commit. You're committing a change to the database, and we have to do that here. So our database connection is called my DB. So down here we go my db dot commit, and then that and that should work. So let's go ahead and save this now. We didn't write any code toe show anything on the screen. So if we run this, nothing will happen on the screen. Hopefully, must we get in there? Nothing happened, but now we can pull up our work bench here. This is why this is kind of handy. We just have all this head over to schema as and then click on testy be tables, users and now It's right. Click and do this. Select Rose Limit 1000 and Boom! John John Eco to me, Age 40 and look at this. It has a user i d of one Very cool, huh? So that's how you had one record. Pretty simple. Pretty straightforward. Yeah, And you see up here, This is the sequel, right? Select. This star stands for everything from test db dot users, then this. Ah, semicolon And we'll talk more about sequel as we go along. Actually, you'll start to pick it up as I just keep doing things. And it's very simple, straightforward. So that's all for this video. In the next video, we'll look at adding multiple records at once, and then after that we'll talk about data types because we should have talked about that a couple of videos ago, but I tend to gloss it over, but we will definitely talk about data types, and if I forget, remind me because it's important. So that will be the next couple of videos 13. Python Mysql 13 Insert Many Records: okay. In the last video, we added one record in this video, we want to add a bunch of records, and it's pretty much the same process is just slightly different. So we're going to start out. I have commented this out, but we need this insert into, um line. So we'll go ahead and copy and paste that into into here, and this stays the same because we're doing the same thing. We're adding into users writing, name, email and age. Same three placeholders. Right. So we're good to go there now, In the last video, we just did this. We created one record, right? And it was John John Economy and 40. So now we want to create lots of records. So I'm just gonna call this records, and what we need to do here is we need to create an array, and this is just a python array. And if you're familiar with python arrays, that's great. Honore is just a list of things basically, actually, in python, they call them lists. They don't call them arrays. Thinking of Ruby, All right. Every other programming language in the world calls a raise a raise and python. They call them list. So we need to create a list. And that list has to be made up of different items and each item will be a tubal, and a temple is like a list. It's just slightly different. Uh, the difference. The main difference is that they're in these parentheses is two brackets, so we need to add as many of these as we want. So let's add, I don't know three or four. Now that's that for Let's get crazy. So inside here we just put just like we just did with John John It go to me and the age we just put whatever we want. So let's go, Tim. All right, Tim, Is that Tim at tim dot com? And Tim's age is 32. That's it, right? So if we want to add Mary Comma Mary at mary dot com are sophisticated. Mary is 21. Okay? And let's see who else. Steve, Come on, Steve at Steve email. Mix it up a little dot com, and Steve is 57. And finally, who do we want? Let's put Tina. Tina. Was Tina doing? Tina is at Tina at something else dot com and Tina is what, 19? You know, 29. All right, go ahead and save that Now, just like before. We need to commit this. So I'm just gonna copy this same thing here, and we also need our cursor. We need to execute the cursor. Now. In the last video, we added one record, right? We executed one in this video. We're adding many records, so we need to execute many. All right, so that's the first change. Next, Like in the last video, we start out by identifying our insert sequel command right here. And then we tell it what we want to add in. We're not adding records, ratting or not, adding record one ratting records. Right? So go and say that. And fingers crossed. It works. Okay, Nothing happened. So let's pull up our my sequel workbench, and come up here. And it just hit the little lightning bolt and see it updates. And boom, it does. Tim, Mary, Steve, Tina. And look at the user. I d. S their auto incriminating. 12345 Very, very cool Ages were listed there. Awesome. Now I'm gonna talk about how to change data from a python program you know, programmatically. But, you know, like I said earlier, if you see an error here like Steve is not 57 he's actually 56. You can change it from right in here. It's come up here, Change it. Come down here. Hit, apply, finish. Come up here and hit a little lightning bolt again to refresh it. And that change. Can't remember what it was before it was a 57. Let's put it 55 see again. I don't remember that one. Why? Why? But it refresh. Yep. 55 must have been 57 earlier. A long day. So, you know, just keep that in mind now. You're not gonna want to do that. I mean, it's kind of cheating, but you can in a pinch if you need to just fix something really quickly. And like I said, I'm gonna show you later on how to do this from, you know, an actual python program. Yeah, that's that's how we insert many things from the program. Now we can also let's pull this up again real quick. We can also import entire databases from this thing here, and we'll talk about that in a little bit, but this is how you just add, like your own information from a program. So that's all for this video. In the next video, we'll talk about data types, I promised. We talked about that earlier, but we really need to talk about it right quickly. It's not that tricky. I just need toe point you in the right direction and tell you a couple of things about it, and then we'll be good to go, and we'll get back into learning Cool Python database stuff so that'll be in the next video . 14. Python Mysql 14 Data Types: Okay. In the last video, we talked about inserting many things into the database table in this video. I want to talk very briefly about data types. So we've already touched on this in the past. Each column has to be of the same type here for name all of the things in here or text or in database terms of our cars here. Over here, we have a judge. Those air integers. Right? So what are the different data types that are available? What are they called? And how do you use them? Well, every database is a little bit different, right? And they all call data types different things. You know, Um, one of them might be var car. Another one might just call it car. You know, one of them might call it car field, you know, but they're all sort of the same. You just have to look them up basically, because there's so many databases, and likely you're going to use other databases. Besides, my sequel, Post Press is very popular on building a course on postcards. If you're interested in that, check back in a couple of weeks, Likely it will be out by the time you see this, but ah, all data bases have different types of data data types. So let's pull up a Web browser and you just have to go to Google and type in my sequel, um eight. Ah types. And there's a bunch of different now this is the my sequel, the actual docks. I don't recommend going there. It's confusing. It's a very silly thing on Let's See W three schools. Oh, that might work. Tutorials point. That's always a good one. We'll look at a couple of these and just see so sequel data types. My sequel data types. All right, this is what we want and you can see we have. Here's just a list of them car and then size of our car size and you could see holds a variable length string, uh, can contain letters, numbers, characters. Maxwell's maximum sizes, specified in parentheses can store up to 55 characters. If you put a greater value than 2 55 it will convert it to a text type. And that's down here, right? Um, car looks very similar of our car. I can't remember off the top of my head the difference between the two. Maybe the door appoint says through here you must define well, uh, right ahead of spaces. I don't know. I can't remember off hands. Not terribly important for cars when you're always gonna want to use. Just kind of trust me on that, uh, text lob these air for binary objects, images, maybe music files, things like that. Um, yeah. So then looking down here, we have different types of numbers. Tiny integer, small integers, medium integers into, uh, notice. I put integer that works too Orent you could put. And there's different types of things. Float is like a decimal number, right? So if you're going to save like, customer data, like if somebody bought something from you and it was $19.95 you wouldn't want to use into jerk is integers a whole number You would want to use float, you know, that sort of thing or decimal, right? Double started as a string. No, you would not not want that because it turns it into a string. So very interesting. You can come through here whatever you happen to need. And they're just millions of use cases, so I can't really go through all of them. But those were different things. And the main sort of categories are text and numbers. Those are the ones you're going to use the most. But you could also use date. So here you can see different date things. Date, date, time, time, year, time stamp. Oh, see, I think that's all they were getting into different types of data bases here. Yeah, Let's look at the editorial 00.1 real quick. Here we have same int medium begins data, types, time string types. So very interesting. And that's all there is to it. Really? So, like, don't try and memorize these cause it's stupid. You don't need to do that. Just whenever you're designing your database, spend a minute, Google it and go, Yeah, I need to use far car. Or if you have a specific use case that needs something, that bar car doesn't quite work well with, You know, just go through the string types and find the one that does work right, and they all work the same you type instead of our car. You would type, you know, blob and then the parentheses and then the the max length or whatever. Just like we did with our car. So, um yeah, I guess that's data types. Pretty simple. Three straight forward. Ah, let's see, in the next video, I think we're gonna start to look at pulling data out of the database and like, showing it on the screen, and that will be in the next video. 15. Python Mysql 15 Select Data From Table: Okay, so we've added a bunch of data to our database to our table. How do we get that information out and how do we put it on the screen? So that's what we're gonna talk about in this video. So let's see. First, we need to, like, always create our cursor. Let's just go my cursor dot execute as usual. And now to pull data from a database, we need to select it, right? We need to say, I want this specific data. So to do that, we run the Select Command sequel Command. Next. We need to tell it exactly what we want to select. So I just want to pull at this point everything. So let's select everything and this star means everything. And then we need to say from what table? Because we only have one table. But in the future, you might have dozens of tables. You need to say tables. So you say which table? Our table is called users. If I remember correctly, that's right. Right. Okay, so now we've selected it. Now let's put it into a variable that we can actually do stuff with. And let's call it our result call it whatever you want. And that equals my underscore cursor dot Fetch all. All right. So we're saying fetch it all from the selection that we've designated, right? So now we just need to print this under the screen, so there's gonna be a lot of different Rose. Probably so we need to loop. So let's go for And what do we want to call this? Um, just call it row in result, and then just print row. Now, this won't look pretty, and we'll make it look pretty in just a minute. But let's just run this to make sure it worked correctly. So it's run python database, Stop high and boom. We get a tubal with all of our different things. So John Jonah code me dot com 40 and one. So okay, that's cool. We can do the same trick we did before and call the zero with item. And to do that, we just add that on there like that, what we've done before and we get John, Tim, Mary and Steve. No, that doesn't just shows the zero with item if you want, Then the next item, say the email address. That would be the first item just in the way of truffles. The python thing? Yes, and we get all the the things. If you then want the age again, you go through here that it's again 40 32. And finally, if you want the I. D. S, that would be the third. I don't Right, So that's not that useful. We really need to sort of format this and we'll do that in a little bit. So for now, we'll just do it like this so that we could see all of the information. We've already done this once, but I'll do it again because this is fun. And when we get all this stuff, so that is how to pull everything out, right? So what if you only want the names? Well, we showed. We can just do you know, like this Well, the zeroth item. But that's not all that efficient. We don't necessarily want to pull everything from our database and then loop through and just print out the names. Instead, we could just pull out just the names, and that's much better from a sort of ah speed performance memory point of view so we can do that scared of that instead of select star instead of selecting everything we just select name, right? So let's save this. Run it, see what happens now. And boom, we get the same couple. But it only contains the name, right, And likewise, we can do our trick again. Riyadh deserve with item. Hurt this out. Make it look a little nicer. John, Tim, Mary, Steve and Tina So very, very cool. Um, yeah, Let's see what else you can instead of pulling every name you could, for instance, just grabbed the first name To do that, you would go instead of fetch all we want to fetch one. Right? So this will just do the very 1st 1 which I believe is John O. J O. Because we pulled the way called the zero a couple item. Let's get rid of that money. All right, John. Right, so that's kind of cool. I don't know how useful that is, but kind of interesting. So I think that's all for this video in the next video, I think we'll go through here and print out everything but kind of start to format it and make it look nice. More like a report form so that its actual actually readable. You know, right now we've got this. If we pull from star and then run this again, you know, we get this whole gobbledygook stuff. It's all kind of smushed together. You know, we can sort of figure out that this is the name. This is the email, but we don't know without sort of some sort of formatting or something. So I think we'll do that in the next video. 16. Python Mysql 16 Formatting Results: Okay, so we can print all our stuff out to the screen, but it's printing it out as a temple. It's kind of wonky. We want to be able to create cool little reports that are, you know, more readable. So how do we do that? Well, we already know if we do a little trick here and call the zero with item, save this and run it again when we get the first names, right? And if we do the first item, we get the emails. If we do the second item, we get the ages. If we do the third item, we get the user i d s. So what we can actually do is just come on here and create a big, long string with each of those things. Row one, row two or three, right. We just need to do a little bit of formatting using the same place holder things that we did up here when we inserted our stuff into the table way back win. Right? Remember, we had the name email age, and then we had these three placeholder percent s is we're gonna do the same thing here. So what, we're gonna do is at a plus and then I'm gonna put a space and then percent s and then another space. And then Teoh Inter Pilate the thing back into the string, we just type another percent side and then the thing that we want to put into the string in this case it's row one, right? Then we want to add another one, but space percent s and then percent wro what we on to, right? And then finally another one percent s the spaces so that there's a space between each one so that then all smushed together, which is something will tweak in a minute. I'll unseal it. We got percent row C three, so these percentage size don't get confused by them. This is really simple. It just means this is the place holder. It's inside of a string. We can't put variables inside of a string. It will just print out row three. It won't actually put what's in row three because it's in a string. Right? So to get around that we do these placeholders and then what this does is it creates a placeholder, and then it looks for the next percentage sign and then whatever is directly behind that, it puts that into the string. So if we save this and run it, hopefully it didn't mess. This up was clear the screen and run this inn boom that worked. Now this is not great, right? I mean, it's readable and it's better, but it's still not great. So what we can do here is we can use tabs. So come down here to our thing. You know what? A tabby hit the tab button and it bops it over a specific amount. We can do that here, too. So to do that, we just use an escape character. It's this slash SLASH says the thing directly behind this, we need to escape it out and do. It's a special thing, right? So we get rid of that space, so this slash t means a tab. So you do it another one right there and then another one right there. So let's save this and see how this looks better. Okay, so, um, a little bit better, But some of these email addresses air kind of long, so let's put two tabs for, um, the 01 to the ages, so that would be 012 right here. So let's just put another tab. Save this and run it. See how that looks. I'm not great. Some of these longer emails or throwing us off here. Well, just for the sake of formatting, what I'm gonna do is pull up our workbench. Where did that go? I'm just gonna change Steve's email from Steve email to Steve. Now, Obviously, in real life, this is not something you would do. But this is our world. We can do whatever we want, Tina. All right, so enter down here. Click apply while I finish. All right, let's run this thing again, see what happens. All right? Looking good there. So this is getting better. But we still would like some headers, right? Cause we can tell what this stuff is. But, like, where these is this age, we don't know. I mean, we know because we created it, but you know, somebody reading this wouldn't necessarily know what that is. So let's fix that real quick. So here, what we want to do is outside of the loop above the loop. Let's just print out some some header stuff. So let's see the first column is name. And then we want a tab. And then the next column is email, I think. Right. And then a tab. And after the email, we want to tabs, right? I think. Yeah. And then age tab. And then I d So we save this. Let's see how this looks. Just sort of have to play around with this. Get Tau. Look the way you want. Name email. Hope the age needs one more tab. See, that looks okay. That's pretty good. We can also then just sort of let's do some lines, right? Uh, 12 for let's do. Oh, so what? That looks like t and then e m a i l t two the a g e t i d three. I think he's the ideas Get big in the future. So it's clear the screen and try one more time. Ah, look here for that. Let's do regular dashes Instead, it underscores to try to get fancy on 234 I want you 345 123 123 Save this one more time. Screen first. Yeah, Okay. I started. Look pretty good, right? Is this just basic text formatting And this isn't really ah, sequel thing. It's more just a python were just kind of playing around, cause this is fun to me. But you conform at these things anyway, You wanted, you know, since we're emailer were, uh, printing this stuff out in the terminal. It's not gonna be real fancy at this point. Um, you know, you could I'll put this data to a mat plot lib craft or something. You know, you get fancy and do graphical stuff with Python. We're not really gonna get into that in this video. This is more of a nuts and bolts out to actually work with my my sequel and get date output data in update data do that sort of thing. This is just sort of ah, you know, fun little thing we're gonna do. So I think that's all for this video. In the next video, we're gonna dio put my Andy Dandy notes who the next video is the Wear command. What's phone? All right, so that'll be in the next video 17. Python Mysql 17 Where Clause: OK in this video, I want to talk about the where Clause and the where. Clauses, a sequel command a sequel clause that allows us to search for specific information. So in the last video or a couple of videos ago, you know, we searched for name right, and we just pulled everything from name. Well, that's fine, but you probably likely you want to get more specific. You want all the names where the first letter is J or something. That's a bad example. Let's say age. Maybe you want all the ages where of the ages over 30 or the ages under 20. Or you might want all the users where you know they live in New York. That's the where clause. So usually when you're searching, you're going to use the where clause and there's some variations on it will look at the next couple of videos, but the where clauses the main sort of workforce search thing that you're going to use. So it's pretty simple. We can just create my cursor like always, and then dot execute as always, and let's just create our select statement. Here we go select and we still want everything select everybody from users and to use the where clause we just type in aware and then some condition. So we might say where, um, age is greater than 30 right? And these air conditional statements greater than less than equal to those sorts of things will work there and in or will also work there. We'll talk about that next couple of videos. So pretty simple. Now we just need to put this into a result, as we always do. So let's go, my cursor dot fetch all. And then let's just print this out. So let's go. Ah, let's see. Four row and result. Let's just print through. Let's say this where age is greater than 30. Come back here and run our program and we get John, Tim and Steve, who is 40 32 55 years old. And if we pull up our work venture, we can just drive all this and see, you know, um, John is 40. Tim is 32 Mary's 21. That's less than 30 so she's not in there. Tina is 29. That's less than 30 so she is not in their Likewise, we could jest kind of play around with this a little bit and go where age is less than 30. Save this and run it. We're gonna get those other two Marianne Tina who are 2129 years old. So pretty cool if you know a specific thing you can do where age equals in this case 55. Let's go. We pull this up and run it, Steve. And in, like, always weaken. Do our little, uh, you know, a little trick here toe with zero with item. If we want Run this again and just get Steve So very cool. Pretty simple. And like I said, there are variations, um, that you can do, and we're gonna look at some of those in the next couple of videos. But, you know, here is an integer, integers or easy, because they're greater than less than or equal to that. You could do, um, you can do greater than or equal to greater than and equal to greater than or equal to like that or less center is equal to like that. But what about strings? Well, we can go where name equals, and then we need to put single quotes with whatever we're searching for. So let's go, John. We know that's in there and save that. And boom, we get John. Likewise. Weaken. Take away our little brackets here and run this again. Get the whole record. John Kotomi, 40 years old. So a pretty cool. Pretty simple. And I think that's all for the wear statement in the next video I want to talk about, like and some wildcard characters so that we can search things without knowing specifically what we're searching for using wild cards, so that'll be in the next video. 18. Python Mysql 18 Like and Wildcards: OK in this video, I want to talk about wildcards and like so in the last video, we did this. We're name equals John. And that's fine if we know specifically that there's a John. Right. But you likely when you're searching for something, you might not know exactly what's there. You may have some sort of idea, so we might want to search for, like, names that start with J or something s So how would we do that? Well, first, instead of using the equal to sign we use like So, if we run this, we're going to get the same thing we did last time. We're gonna get this John record here, right? But let's say we just want names that start with Jay. So if we save this and running, we're not gonna get anything because we need to use a wild card. We need to say names that are like J but then also have stuff behind them. And to do that, we've already sort of looked at wild cards in the past when we looked at our sort of placeholders, remember? We did that percent s earlier. Same thing here. We just don't need a nest. Now we just use the percent sign. So if we save this and run it, we get again. John. So very cool. Um, we could go t I names that start with T I. All right, So let's save this and run it. We get Tim and Tina will star with a T and an eye. So let's pull up our work eventually. Quick and just kind of look through here. Are there any other weird things we might want to look for? Well, let's see. There's ease. And there's a a Mm. Let's just go. Let's look for names that haven't I in the middle somewhere? How would we do that? Well, take a look at this. You know, we've got this wild card here. The end in the while occurred me and search from here on, right? So t i and then anything after that. That's what the wild card means. So we could do the same thing at the beginning. We could go. We want wildcard stuff in the beginning, has an eye somewhere in the middle. And then something else after. All, right. So we put two wild cards in there. If you save this and run it. We should get the same Tim and Tina, I hope Matina very cool. So play around with these wild cards. They're very interesting. They're very useful, especially if you sort of get the hang of them at the beginning. You know, it's a little weird. There's, you know, having them at the beginning versus at the end. But it's pretty simple and straightforward. I think so. That is wildcards that is equal to greater than less than, like those sort of things. Very interesting. Now, in the next video, we're gonna look it. And and or so basically, what we're doing here is doing one statement, and you could sort of string these together with ands and ors, and we'll look at that in the next video. 19. Python Mysql 19 And Or: OK in this video I want to talk about and in or so up until now, we've been just basically running one sort of big sequel statement and searching, for one thing. But you can search for many different things all at once, and that's often pretty useful to do. And you're always gonna want to do things like this. So to do that we just use and and or and and and or are useful but very different. We'll talk about those in this video. So do. And we could just use our same exact select statement here. But just that the end here, actually inside of the quotation marks. Still, we just type in. And so we could say we want to select all the users whose name has an eye in the middle and their age equals 29. All right, so if we save this and run it, we get Tina. So if we eyeball are ah, workbench here, we could see the only age here, you know, before it was Tim and Tina were the two things that it was returning. But Tim is 32 not 29 so he didn't get returned in this one and the important thing to know about And is all of the conditions in this statement have to be true in order for this to return anything. So, uh, you know, there has to be a name with an eye in it. And in age, if there's only a name with an eye in it and not an age, it won't return anything. So, for instance, if we go, you know, an age 100 if we run this, we're not gonna get anything because there's no records that has an eye in it and has the age 100. Both of the things have to be true. In order to return something, you know, um, weaken, do or and Or is the opposite. It will returned one or the other. So only one of those have to be true. So either they have to have an eye in the name or they have to be aged 100. So if we run this, we're gonna get to results Tim and Tina, because they both have eyes in their names now, neither one of these air 100 years old. But it doesn't matter because we used or and in or on Lee. One has to be true, one or the other. So pretty simple to keep straight. But it's just a basic sort of logic thing, and and Or And if you're familiar with python and other programming languages, you see and in or logic all the time. So you're probably already familiar with this concept of and and Or And Or or eso that's very cool. So that's really all there is to that. You just keep slapping things on our select statement here, you know. Ah, and you can you can, ADM. Or you can go and age equals 29 and I d equals What is Tina's? I d pull this up and look in his ideas. Five. Right? Her user I d changed that user underscore I d equals five. So if received this and run it, we lost our quotation marks accidentally. There we go. I'm always doing things like that when we get Tina. So you know you can keep stringing these on. You can string ands and ors together, though I don't think you really want Teoh. No, probably not. But you know whatever you want, however you want to do it. You have a hold on flexibility with this. So I think that's all for this video. In the next video, we're gonna look at updating records. 20. Python Mysql 20 Update Records: OK in this video. I want to talk about updating records, and we've already kind of played around with this bullet. The workbench just in here sort of manually updating things. You just click on the thing updated, come down here and click. Apply and you're good to go Course, that's no good for us. We want to learn how to do that with actual sequel, right? That's the point of this course. So how do we do that? Well, it's pretty straightforward. So let's just create some, um, my sequel stuff and said the sequel to now Let's just create a query here. Let's go update and then our table users and we want to set something. What do we want to do? Well, let's update an age. Let's update my age. Let's say I just had a birthday. So we want to go. Age equals 41 we use our wear clause where name equals John Capital. John. All right, so that's good. Now we need Teoh actually execute this thing. We do that using our cursor as always, my cursor dot execute. And then we just pass in this my underscore sequel that we just created now, like at the beginning, when we created our table and added stuff to the table. We need to commit this change so we can run this, and it will do it. But it won't save the change. We actually have to commit it. And we've done this before, so we just go my db dot commit and then that. And remember, the my i d be That is the name of our sequel connection here. My db From way back when, right? So if we save this, come back over here and run this guy, nothing happens on the screen. We didn't tell it to print out anything, but if we pull up our database here and refresh this with a little lightning bolt here we see, in fact, yes, John is 41. Now. That works. Obviously, we just did it. But what happens if we have another record of some other John John at other dot com And John is, you know, 12 years old and his user I d is Just leave that blank come down here and apply. Apply finish. You see, the auto implemented the i d. For us, of course. So now, if we've run this thing again. Let's change name, uh, to let's change the age from 41. Let's change it back to 40. I didn't actually have a birthday. What's going on here as an air? Let's let's fix that. So let's run this thing again. Changed to 40 her name is John. Well, this up and run it it happens. But let's check our work. Bench refresh. Yep, Here's my name, John on 40. But this John down here is now 42 he was like, 12 before and we just can't have that. So first off, I'm gonna change that. Well, no, let's not change it. Let's leave the way it waas. It's so this is a problem. And that's why you don't usually want to update records using non unique columns or whatever our fields or whatever. And so instead of name you're gonna want to use ah user, I D. And that's why one of the big reasons why we have this year's their I D column and why it's the primary key. Remember, Primary key means they're all unique. Every record every person in the database has their unique i D. So there's only one person with idea of one. There's only one person with an idea of six. Even though they're both named John, we can keep him separate with these righty. So let's go ahead and change this new John back to 12 years old. His I d is six. So or use your i d equals And this is not a string. It's a number. So we don't put quotation works. Headless changed this back to Let's make him 16. Let him drive. We're not heartless. Kids should be able to drive. There we go. So run. That was pulled back our workbench again. And boom. Now he's 16. So pretty simple. Updating records. Just use this update. Um, query commands the sequel command our you know. Then you put your table. Name was his users. We want to set H and likewise, you can do anything you could set on the name to. You know, maybe this was Johnny instead of John. Right? So if we save this and run it, just be sure to put single quotation marks in here, not double, because the doubles go on the outside. Right? So if you put him here, it's just gonna mess. Everything you see, we get this big red bar. What were you doing? Eso Let's come back here to our workbench. Refresh this. See if John is now Johnny and it is And ah, that's how that works. Eso Yeah, prequel In the next video, I think we'll talk about returning results with specific limits. So, you know, sometimes there's thousands of results. You might only want the top 10 or something. How do we do that? We'll look at that in the next video. 21. Python Mysql 21 Limit and Ordering: OK in this video. I want to talk about limiting results in ordering results. So we already know how to get things. We had to pull specific data out of our database, but we don't know how to limit those results. So let's do that in this video very quickly. So let's pull up a my cursor like always dot execute. And let's create a select statement, select everything from our users table. Um, now let's say we just want the top three records, right? We could do that by go limit three. Just as simple, right? Let's see. Now we want a result equals my cursor dot fetch. All has always And then we can just do a quick loop for road in result Print, bro. Okay, so let's just save this and run it and see what we got here. Or the screen. So, John, Tim and Mary and these are just in the order that they have been entered into the database . So our into the table. So you see here freshness to make sure this is current. The 1st 3 records we have is John, Tim and Mary, And so John, Tim and Mary of the first ones that come up now. You might want the 1st 3 but not the 1st 1 right? So you might want Tim, Mary and Steve. You might want to skip the 1st 1 for some reason. How do we do that? Well, that's very, very simple. We just limit three, but offset one. So we're saying Skipped the 1st 1 and then give us the next three. All right, so let's run this Tim, Mary, Steve, Tim, Mary and Steve. We skipped John. So very cool. Very, very simple. Um, as most things with this seemed to be so Okay, so let's say we can limit things, But now let's arrange them. Let's order them. So let's select all from users. And let's just print this out real quick. So John, Tim, Mary and Steve now weaken. This is sort of, um I guess this would be ascending order by default. Ascending meeting. The 1st 1 entered in the 2nd 1 entered then the 3rd 1/4 1 and so on. And so on. We can change this to descending. So instead, if we want Johnny to be the 1st 1 listed, and then Tina and Steve and then John to be the last one. Ah, we can do that. It's pretty simple to do that. We just go order by and then name. Um And then what do we want? Descending right. So there's a sending a S c. I think that's right. And descending DSC Let's save this and run it. If I've remember to do this correctly, I don't do this often. Uh, no, Tina. Oh, it's This is doing alphabetical order. Looks like Tina. Tim, Steve, Mary! So descending. Alphabetic order. Let's try a sending This would be alphabetical order, I suppose. S o h i j k l m n o p q r s t s. So now we're in a sending alphabetical order. Very interesting. So I mean, you know, you could play around with this. We can go age. We want to have them ordered by age, um, in a sending order. So that would be the lowest age to highest age? I guess So. Johnny gets to go first here. I believe Johnny 16 2129 32. I likewise, we could go the opposite descending, and we have one in there. That was, like, 55 years old is clear. The screen here. Yeah. 55 Steve 43 to 29 2116. So descending order. Uh oh, yeah, Pickle. Um and that's really all there is to it. This stuff is pretty straightforward. Once you just memorized these few basic sequel commands A Z we're seeing. So I think that's all for this video. In the next video, we'll look at deleting entries, deleting records and rose and things like that that'll be in the next video. 22. Python Mysql 22 Delete Records: OK in this video, I want to show you how to delete records. So deleting records is pretty straightforward. So we just create another my sequel, Little statement here, and it's pretty straightforward. We just want Teoh want to delete and then from and then name your table users where and then we just give this a condition just like any other wear clause. So we could say we're name equals or something like that. But let's go use your I d equals six that Johnny Pour Johnny wasn't here very long. User, I d equals six, right? So just like before we need to. You are my cursor dot execute and then pass in this my sequel thing we just created and we're making a change to our table. A permanent change. So we need to commit the change just like we have in the past. So it's my db dot commit and then that. So let's go ahead and save this and run it. It won't return anything cause we didn't tell it to you. But if 12 or workbench and refresh or Johnny has disappeared, gone on forever. Um, and here's something interesting. We can add a new. Remember Johnny's user? I d was six, right? So we can put, um, Tracy. Um no. At no dot com. Tracy is 19 and if we apply this, we could see her user ideas now seven, even though it goes from 45 Boom. Seven. Now we can manually changed. It's back for one. But since it's auto incriminating, it knows the next one is seven. Even though six is no longer with us. Just kind of a quirky thing. I just thought I sort of let you know about that so you don't freak out if you ever see that. No big deal. So let's look at this one more time here. Now again, We could do things like, you know, where name equals John or whatever, but I think we already learned that that's not a good idea, because there may be lots of different records with the name of John. And if you run this command ill, delete all of them, and that might not necessarily be what you want to do. So you is one of the sort of delete a unique field, right? The user i d. The primary key. Um, whenever you're deleting things because that's just just a smart thing to do. So, uh, that's all for this video deleting entries. Pretty simple. In the next video, I'll show you how to delete an entire table as fun stuff, so that will be the next video. 23. Python Mysql 23 Delete Drop Table and Backups: Okay. This video want to show you how to delete tables, but also how to back them up export than import them. I think the two go hand in hand. So before we do lead our table, we're gonna save it to make sure we have a copy in case we want it for later. In our case, we don't really care. There's only a few records in it. But you know, still eso Let's open up a workbench. And before we delete this thing, let me show you real quickly how to import and export tables. So we just come down here to our schema section and find our table that we want. Right? Click. Now you can drop table. This will delete the table. We're not going to do that here because we're gonna do this in Python. But when you delete a table in sequel terms, it's called dropping the table right? It's just the way it has always been. But that's not what we want here. We want to go to table Data Export Wizard. We want to make a backup copy of this before we delete it. Right. So click that and we get this screen here and it says, Which of the columns do you want to keep? We want to keep them all so they're all checked, Uh, and you can offset if you want. Ah, then it says, Where do you want to save this? And I'm just going to say this to my desktop in a file called Users, because it's a user's table that makes sense to me. Ah, and you could save a C via See SV File or Jason File. See SV stands for a comma. Separated values, I think, or something like that. And Jason is javascript object notation If you want it in Jason, that's really kind of useful. We're just gonna leave it. See SV for now, no big deal. And OK, click next, we're going to go boom, boom! And we're done Finish So I can confirm by checking my desktop on my other monitor that in fact, the users file is there. You can't see it, but trust me, it's there s so we've got it. So now let's learn how to delete the table from Python. So, uh, just like before we're gonna create a Let's give this Ah, comment, delete. Drop table. Okay, so we want to create some my sequel stuff and set the sequel to the sequel command of drop table users. All right, so we're dropping a table. Which table? User stable. And just like before we just go my underscore cursor dot Execute and then just pass in this my sequel thing, and that should do it. So let's save this. We don't really need to commit this, cause it's gonna do it for us. So, python Hey, stop high. Nothing returned. We don't expect it to, but now come back to our workbench And how head over to our scheme us, and just refresh this and boom are test db Database is still there, but there aren't any tables. Right? So if we refresh this, um, actually, this shouldn't work at all either. We're getting errors down here, so Yeah, it's gone. Right? So we've saved a copy so we can import this. So I'm just gonna hover over the tables thing and let's say table data import wizard, pull this over, and we just need to find the file that we just saved. Way called it users dot C s V open that next. Um create new table. Yeah, we want to do that. We don't want to import this into ah, uh, current table because we don't have any tables. We deleted her table, and we want to call it users. We'll just leave that as the default it already knows. And here's our columns we could see and all the stuff. So click next and then next. Boom, boom, That should be it finish. And now we come back to our schema and click refresh. Well, now we have our users table. We can select whatever, and we still have all of our records backed up. So, um, that's cool. Now one quick thing I want to show you before you go. Let's run this command again. So let's pull a poor guy here and run it again. And now it's looking a workbench. Refresh this thing to make sure it's gone. It is gone now. What if we run this command again? We're getting all kinds of errors. We can't delete something that's already been deleted. Right? Um so a better way to drop a table. You could do it this way. It works, obviously, but if you want to put in some error handling to make sure that it only deletes tables that exist. You can go drop table if exists, and then name your table users. So if we save this and run it close screen, nothing happens. We don't get all those angry errors, even though we just tried to delete a table that doesn't exist, right? So yeah, All right, So I put my notes here. I think that's about it. So in the next video, I'll talk about, ah, a few conclusion type things. I'll show you where you can get the code for all of this stuff that we've went over so you can download and keep a copy. Sort of a quick reference guide, if you want sort of useful. Um, you have to memorize all these sequel commands and things on then. Well, you know, rap. Something's up on DA se a few more things, and that'll be that. So that's in the next video 24. Python Mysql 24 Bonus Video: Okay, so I guess we're pretty much done here. So before we move forward here, I want to give you a chance to download all this code. We just did and have it sort of as a quick reference. So if you go to my get have account, get hub dot com forward slash flat planet. There should be a little button down here to follow me. If you want to do that, that's cool. But come over here to the repositories button. Click on this and look for this python. My secret course one. By the time you watch this, this might be further down the list, so you might have to search through here to find it. Or you could just go to Flat planet, forward slash python My secret course. All capitalize all with, you know, hyphens in between. And you see this database Stop. I file. This is the file we've been working on. And this is it. So you can copy this pace. This save this reference that however you want and then you see, has are comments for each little thing. We did, you know, insert one record. That's this stuff and start multiple records. That's this stuff. You know where and like and nor limit results. So it's a really handy little reference that you can look back on so you don't have to memorize this stuff. Nobody memorizes this stuff right away. You just always sort of reference things like this. So I hope that's useful. So that's the course we're done. Um, Hope you enjoyed this little intro to my sequel with Python. Uh, my secrets. A lot of fun. It's really easy to use. And these days, everything you know needs a database. I mean, websites need databases. If you're doing big data stuff, you're building some sort of app. You're always going to need a database, my Sequels free pythons free that you go together pretty good, eh? So I hope you enjoy this course. If you did like this course, do me a favor and leave a review at you to me or wherever he watched it. Those reviews really just make or break courses. A couple of bad reviews and it's all over a couple of good reviews, and it really turned things around and helps helps the course show up in the search results that you know me or wherever, which is really cool. So I would appreciate it if you took a couple minutes to do that. Uh, if you like this course, head over to my website Kotomi dot com. This is where have all my courses and there's, like 30 courses right now. If you like this sequel course, I've got other sequel courses you can check out Microsoft Sequel Building Outposts, CREss Sequel Course. Next, all kinds of stuff. We've got Python courses. There's that sequel course we've got Ruby courses. We got Django Ruby on rails. Some really cool Cryptocurrency stuff. Um, there's PHP. You've been to Lennox JavaScript, html WordPress, even like s CEO and email marketing. So, you know, we're computer people. We build stuff. You still have to kind of market those things when you build them. If you build a website, you still have to market it to get people to come to it. So we got the ECM on the email marketing course pretty cool. I looked through here. Anything else that's kind of cool jumps out Java script, and I have basically two sets of courses. We have, like programming language courses that teach like Python, ruby PHP html CSS Um, whatever. Other programming languages, then we also have, like project courses will rebuild cool projects like here. We build a crypto currency portfolio app with ruby on rails. I would do the same thing with Django up here. Build a copy of the website Pinterest with Ruby on rails. Just all kinds of cool stuff on it's really cool. Let's see. Normally it costs for total membership, which is all my courses on my future courses. One time fee. That's 49 bucks, which is pretty good. I mean, there's hundreds of videos. It's really cool, but just sort of as a thank you for watching this video. If you come over here to the check out and type in Super Coder as the discount code, which I know, it's a stupid code, but I can't get around to ever updating it. It'll knock another 22 bucks off, so you pay just $27 for all of my courses. Basically have to charge something just to cover band with for all the videos, because there's a lot of bandwidth involved in watching all these videos. 27 bucks. I mean, that's cheaper than a you know, large pizza these days. So I think it's a pretty good deal. I like I said, You get access to all my courses on my future courses. I'm adding new courses every week or two. It seems like whenever I do, you get those for free. They just pop up automatically. No future charges. One time fee. Pretty cool. You get access to me, you can ask me questions. You can access other students. You can discuss things with them. It just so it's a pretty cool little, um, community, I guess if you like watching my courses on you to me, head over to john elder dot org's ford slash you to me and this list all my courses, they're the same courses on as on my Kotomi website. They're just on you to me. So you know, if you have a bunch of free credits on you know me or something, that you want to take one of my courses there or if you just prefer that platform toe watch courses. This is a cool place to come and see all my courses. And each of these has a link to a coupon code they get you a big discounts. I think they're all at 11 11 99 each or something like that. So it's a lot more expensive than if you just get them all at Kotomi for the 27 bucks. But it's still a lot cheaper than taking them individually on you to me, So that's an option if you're if you're into that sort of thing, you can do that on that school. So anyway, my name is John Older, from Kotomi dot com had a good time teaching this course. Hope you learned a few things if he enjoyed it, and we'll see you next time.