Using MySQL Databases With Python Course

what's going on John the elder here from Kona be calm into this video I'm going to teach you my sequel with Python all right in this video we're gonna learn my sequel with Python but before we get started if you like this video want to see more like it be sure to smash the like button below subscribe to the channel and be sure to check out Cottam e-comm where i have dozens of courses with hundreds of videos that teach you to code use coupon code youtube to get $22 off membership that's all my courses videos and books for a one-time fee just $27 which is insanely cheap alright so this video is actually the first half of my my sequel Python course that you can find at code on me calm or on udemy and it's you know I've taken all of the individual videos and edited them into this one big video so there's transitions between each one but it's free so it should be cool I hope you enjoy it and we'll talk more after the video alright welcome to the course my name is John elder from Cody me calm and I'll be your instructor today in this course I'm gonna teach you how to use my sequel the database with Python so my sequel is an 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 going to teach you all about my sequel the database itself but mostly we're gonna focus on how to connect Python 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 a 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 this will help you to so 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 Python is 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 and 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 Linux you should be able to follow along all the tools that we use are available on Mac and Linux 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 okay so in the next couple of videos we're gonna 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 what a database is just to give you sort of an idea of what we're talking about just a broad sort of overview in order to so you have sort of a solid picture in your mind of what a database is how it works I find that 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 the next video will be good for you but yeah so that's the course one more thing have questions along the way be sure and be sure and 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 also my name is John elder and let's get started 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 database is so you know there are these weird things they exist usually up in the cloud you put data in 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 are basically just big fancy spreadsheets they have columns these things that go up and down they have rows things that go across you can put you know things in and take things out you can put all kinds of different things in and we'll talk about that in just a minute but that's really pretty much all there is to it you know all databases pretty much work the same way they and they sort of all act like this so here let's talk about this a little bit you have going across these are called arose in sort of spreadsheet terms and database terms they're called records same thing record row everything in a row and a record goes together so you know you see here we have John elder Miette be calm my phone number and West Elm awesome Vegas Nevada eight nine one one one I don't know 40 so this is a row right all of this stuff goes with me right if we then have another entry we might have Mary Smith oops let's do Mary Smith weird formatting here only change that real quick you go let's go Mary at Mary calm her remember is two two two three two three four four four four she lives at eleven Maple Street I don't know Chicago Illinois six oh six one zero she is 23 right clean this up a bit okay so you know we have another record in another row and everything in this row 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 all right we'll talk about data types in a little bit but like for instance here age everything in this column is going to be an age you wouldn't put you know blue that doesn't make any sense blue is not an aged 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 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 we're really sort of gonna be interacting with and you can see down here at the bottom we have sheet 1 sheet 2 it's empty sheet 3 it's empty these are 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 can have a table for 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 it's all the table it's all the records and the rows within the tables so that's really sort of all I wanted to talk about I will talk about data types a little bit later 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 my career or my sequel's 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 going to start to download and install the tools that we're going to need and that'll be coming up in the next video okay 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 going to knock these out one at a time in this video we're gonna look at installing Python so just go to Python org 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 Linux 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 3 and above and Mac and Linux computers often come with Python too so we don't want that so it's always a good idea to install this if you already have python on your windows computer I'm gonna I hit just clicked on my Windows Start menu and click control panel and then just go down to uninstall a program and let's find Python and I'm just going to 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 gonna go ahead and pause this video while this goes and uninstalls and then we'll pick up right when it finishes okay so I went ahead and finished uninstalling so we can close that if you look there's also this Python launcher if you want to uninstall that too you can it's not probably a big deal I'll go ahead and do that just because you as you can see it isn't taking quite along and boom that's gone so once that's gone we can head over to Python org 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 Mac or Linux it'll give you the Mac or Linux version and you can see python 3.7.0 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 going to go ahead and click that now we can save this anywhere I'm just going to save it on my Windows desktop and it's not very large twenty four point three megabytes it downloads on my computer almost instantly so go ahead and click this and let's open this No okay so here it says install Python now this is the important part see down here it says add Python 3.7 to path and this is unchecked by default this is why I told you to uninstall Python if you already had it on your computer because chances are last time you installed it you didn't notice this little check box and you just click Next like people do but 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 and make sure that's checked and then click the Install Now button and initializing so this is gonna take just a second um I think while this is going oh no it's gonna last 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 at Google and you see this sublime text com this is the text editor I'm going to 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 coding text editor you know Adam is a popular one [Music] what are some other ones notepad plus plus as a popular one sublime is super super popular it's absolutely free and 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 it's just a basic text editor you just click the button and click the button again and it'll install for you but you can go ahead and do that yourself in the meantime let's check back and see how our Python is going up almost done so we'll go ahead and pause the video while this finishes up should be just a second and we will oh there goes almost done we'll pick up as soon as it finishes okay so setup was successful everything looks okay go ahead and click close and that's it we're done we're ready to use Python now 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 okay so we've installed Python now we need to install something called git bash and if you go to get - SCM comm 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 Mac or Linux but on Windows so you know in order to run Python you need a command line a terminal of some sort and Microsoft has the command prompt and PowerShell and those work but git bash is just much much better it allows us to do version control using git 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 bash thing and it's super free and super easy and very lightweight and very cool so we're just going to 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 two point one eight 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 go ahead and 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 thirty nine point two 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 to 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 correctly 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 next ok here's one weird thing it asks for the default editor to use and it's saying use vim now we're not ever going to 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 git from the windows command prompt if we want we probably won't do that we're going to use the little term role it comes with this instead but you know it's good to click that anyway so click Next now we want to make sure our open SSH is clicked and SSL library check out windows style with commits UNIX style line endings that's good ey that's good too all of these can be the default and that's it so this should install pretty quickly but I'll go ahead and well nope I was gonna pause until it's finished but it's already halfway done so I think we could just chat for another couple of seconds until it finishes and that's cool so it really doesn't matter like I said earlier what terminal you use for this course because all the commands are going to be running in our git bash terminal you can run in Windows PowerShell or the Windows command prompt that come installed with Windows but for people that are on Mac or Linux um I can use Mac or Linux type commands in this get bash terminal which is really really cool so everyone should be able to follow along and that's cool so we can uncheck this view release notes so let's go ahead and launch get bash finish in the future if you want to turn this thing on just go to your Windows Start menu and search for get GI T and that should work so that's it we can type in sort of commands and this is cool so we'll use this going forward I'll tell you how to use this and what it can do and all kinds of cool stuff but I think that's all for this video in the next video we'll jump in and start to download actual sequel my sequel and look at the different tools we're going to need for that and that's coming up in the next video 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 sourced 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 or the server and we also need something called the my sequel workbench and the workbench is sort of a GUI a graphical user interface that lets us access our my sequel databases add information add data remove data do all kinds of cool stuff now we're going to be doing most of our stuff in this course with Python so we're going to be writing Python code to do this but it's still nice to have the workbench to check and see that things are correct and it's just a cool tool to have so we'll download and install both of those you can install them separately or you can install them together we're going to go ahead and download them and install them together in this video so go to my sequel comm you could search for my sequel at Google or just go to my sequel comm 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 commercial you have to pay for them so the my sequel cloud service commercial Enterprise Edition commercial cluster commercial down here at the bottom is the my sequel Community Edition 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 going to download in just a second there's all kinds of things here our connectors repositories we don't really need any of those things we're 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 it's 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 can go ahead and click this otherwise you're gonna have to download a zipped archive and it's all kinds of hassle and we don't want to deal with any of that so I click 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 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 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 buggy I found it not work very well it'll start to download 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 point 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 and click this no thanks just start my download link do that pops up install this anywhere you want I'm going to install it on the desktop now you see I've already installed it on my computer so I don't have to do it again but you go ahead and do that it is two hundred and seventy three megabytes it's gonna take a while I'm gonna go ahead and pause this because like I said I've already downloaded it Kancil but you go ahead and do that and in the next video I think we'll go ahead and actually install this thing we've downloaded the thing now we can go ahead and install it so you want to click this thing right here on the bottom once it's finished downloading myself I've already downloaded it so I'm going to go over to my desktop where I downloaded it and double click it there either one works pull this over okay so we get this my sequel installer now 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 do 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 its own video but we'll walk through it and see what we can see and should be fun okay my sequel installer one point for 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 click okay and then next okay so there's a bunch of different things we can install we can go full we can go custom we probably want this developer default and it souls with all the products that we need for development purposes so go ahead and click that already it's already clicked you just go ahead and click Next you want to go back and look through here these are the type of things that comes with it the sequel server itself a shell router the mini sequel workbench which we're definitely going to use my sequel for excel I don't know what that is some other things so I'll go ahead and just click Next now let's see some products have path conflicts let's see because I've installed us in the past you probably won't get this selected path already exists yeah so we just leave this the way it is you won't get this Erika's like I said I'm only getting this because I installed this previously I'll click Next sure I want to continue yes 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 uhh manually you can but I think you could just ignore this I always have in the past click Next to continue yes okay 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 can be very speedy just sort of depends on how your computer how fast it is and on rate as 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 and 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 down here at the bottom samples and examples I will 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 can play with and we'll play out play around with that a little bit but it's not terribly important that you uh you know install all that stuff my sequel 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 but 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 and go ahead and click Next okay so now these things need to be configured click Next here we just want this standalone my sequel server thing at the top the default that works fine click Next all of these things we can keep as the default the default recognized here this port 3306 sort of important don't really need to make a note of it but just keep that in mind tcp/ip all these things we don't really care about any of this stuff just click Next ok so it's going to 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 it has a user and then that user has a password and in order to connect to the database especially from my Python you need to know password and the username and most of the time the username is just root 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 one two three let me do that again password one two three repeat it password one two three again I'm not suggesting you do that because you can see right here it's very weak but just for our purposes of learning this thing it doesn't really matter so that's fine now you don't need to add a user because root already is a user so we're going to be the root user in computer science root is like the all-powerful super user it's called the root user so just for your information so go ahead and click Next we want to configure my sequel servers windows service that's fine leave all these things as the default 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 can 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 now 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 have 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 as soon as it's finished okay so all the things have finished and we're pretty much done so we can click this finish thing oh no we need to configure some other things so go ahead and click Next we don't want to click this this you know DB cluster thing is not something we want any part of click finish next again we want this standalone server thing yes okay so it wants us to type in our password remember that's just pass password one two three check all connections succeeded and this just runs a little test to make sure it's running that we were able to connect to it that our password works and all that stuff so that's good next um one last execute I think ok so we're done with that it's like start my sequel workbench after setup surely let's not do either of those you can if you want you go ahead and click this moment I'm 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 are 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 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 and start using them so first let's head over to our git bash terminal if you've already closed this go just go back to your Windows Start menu and typing git GI 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 PW PWD I'm in this see users flat planet directory this is just my default directory and it's flat planet because that's the name of my computer because I think it's hilarious some people still think the world is flat it's going to be whatever your computer user name is that's the directory you're going to be in so let's go ahead and new directory to store our Python files that we're gonna be creating throughout this course so just type in mkdir that stands for make directory that works on Mac or Linux too and let's go /c and let's put let's call let's create a directory called my sequel okay so now if we go see D which stands for change directory we can change into my sequel clear the screen so I could see yes I'm in my sequel type in LS there's not actually anything in there at the moment and that's fine we haven't created anything yet but before we move forward we need to install one more thing in the background here so in order for to connect to my sequel we need a 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 to 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 pip install and the thing we're going to 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 antivirus 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 okay yes it's okay 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 yours will go through a little thing and it will 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 - Python you see that the first one was my sequel connector now is my sequel connect your dot or - python same basic things slightly different this one might work for you you can see I've already installed it so I'm getting the same thing finally there's a third one and clear the screen here and it is just let's see paste a pip install my super connector - python - RF right so we're just adding things on to the end to make them slightly different but if the first two don't work go ahead and try this one and I'm not going to solve this one because the second one worked for me just fine I know it's kind of you know kind of wonky but it'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 okay so we've installed everything we need to install and 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 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 was in our C my sequel folder and let's just call this I don't know a database that's fine and come down here to all files and programs or whatever and look for Python right here select that Python and then click Save another way you might do it hit cancel here come up here to view and then syntax and then scroll down to Python you can see now the colors change a little bit so now sublime knows this is Python so now when we come up here and 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 our 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 installed 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 - Python or pimp install my sequel connector - Python - RF you can still get away with just importing my sequel dot connector you don't have to import my sequel dot connector dot Python for instance I believe that's the case if if you run it it still doesn't work try doing that you never know okay so 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 excuse 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 point three variables the first one is host and then comma the next one is user comma and then finally password PA sswd to quotation marks and then I don't think you have to put a comma at the end but I always do just in case because we're gonna be adding things in the future 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 URL 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 URL for that database like 192.168 for 0.9 or something right we're running our database on our local computer on our own computer so we can just type in localhost here right now remember when we set up the database our user was root so that's root and that password we picked when we set up the database was password one two three 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 C my sequel directory LS to list the stuff we can see hope there's that database dot PI file so now let's just run this thing so python database dot py and this is how you run Python programs you type in Python and then the name of the program so when we get this my sequel dot connector dot connection that my sequel connection dot object so this is a we've created an object here and this is just a URL not a URL 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 were 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 as easy as 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 username 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 then to retrieve data from that table order it in different ways things like that all the basic database stuff you want to do and that's what we're going to 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 a sort of an 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 well I think we'll start to look at that in the next video okay 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 GUI for our my sequel database so now throughout the rest of the course we're gonna be doing mostly all of this stuff in Python but I just want to spend a few minutes kind of looking at this software cuz it's really cool and you'll find it useful 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 head 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 is version 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 instance of my sequel that's running there's root localhost I don't know why it's called Lampe my sequel but 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 schemas when you create database tables a schema is 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 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 a city country country language and some other things so let's click on city and we have even more here we have columns ID name country code so let's go a name so if we double click this right up here just says name so see this little lightening bolt up here this allows us to run sequel queries and we'll talk about sequel queries coming up and you know some further videos from now but basically whenever you do anything to the database whenever you pull data out take push data in do anything at all to a database you're running a query and the query language that we use a sequel that's what we call this my sequel so this is just a very basic sequel command I guess if we hit this little thing it's error code yeah it's not a good sequel command I'm gonna go ahead and close this or delete it okay instead let's right click here and what we want to do is select rows limit 1000 if we hit this boom we get this actual graphical user interface representation of all the names in this specific column in this specific table I guess so we seek a bowl Brida you know what some of these are Hira San Fernando and we can expand this make it bigger Sydney Perth it's just a list of it looks like city names around the world and here's the actual sequel statement of the sequel query select name from world dot city and we'll talk about sequel queries and things like that later on not that important um let's see let's go back we can get a better view I'm just gonna click this city table here let's click the entire thing so we have name country code district and population cool interesting data here and we don't care about this data this is just example text that came with this but kind of interesting let's go country so it's in countries so right click select rows basically what we're doing here is we're selecting the first thousand rows in this table right all kinds of stuff in this one so we have the name Aruba continent North America region Caribbean surface area population life expectancy all kinds of neat information here we might play around with this a bit government form Republic non-metropolitan territory of the something [Music] Netherlands it looks like fun head of state you bush [Laughter] so I guess the head of state of the of America Samoa and Oceania 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 to you know it's kind of useful from time to time so you know if you're doing like web development you're not going to probably use this workbench but if you're doing something locally and you just want to visualize your data you 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 rubas if we want right now we just come down here well actually we tab away and we come down here to apply click that it creates the sequel code for us update world country or we want to set the name equal Aruba's we're the country code is a BW so if we apply that done if we come up here and run this again aruba's it was disappeared completely let's run it again ere we go aruba's very cool if we doubled 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 you know teach you a few things here and there that are interesting excuse me but yeah very cool comes free with my sequel that's the my sequel workbench and 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 add our data and do all the fun stuff that'll be starting in the next video okay 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 new database here if we wanted to we'll look at doing that maybe a little bit later but we want to do this in Python this is a course on 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 I click this little thing if I want to 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 going to do that we're actually writing Python code but we're still gonna create sort of a instance of a cursor it's called a cursor and that cursor can sort of execute commands and our sequel database 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 because 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 going to call this my underscore cursor SOR okay you can call it anything you want but it's a cursor so I'm going to call it my cursor now we need to create an instance of this in our my DB connection right so we just go my DB dot cursor and then put 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 do well let's create our own database we've got the database running but we don't have our own database that we've created yet you know we did this let me 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 cursor thing right so we always start out with naming it 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 at the table yet we'll do that little a little bit later we just want to create the database so we pass in some quotation marks and we just type in create database and then we need to name our database and let's just call this test DB right you and that's it so notice these are capital letters these are this is our sequel commands right so we're passing a sequel command into my sequel and the sequel command in this case is create database misspelled 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 up our or get bash and we could just run Python database py nothing happens on the screen right because we didn't actually tell it to show anything but we can pull up our my sequel workbench here and come over here to schemas and click this little refresh button and then BOOM see we have this testdb now there's nothing really in it 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 to start working so very very cool and pretty simple just one little well two little lines really but this doesn't count cuz we're just always going to do this to sort of initialize our cursor one little command hit to execute it 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 commands here and inside 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 off our workbench we see we have one two three four right we can just run this command see clothes back up we could go something like print my cursor right but this is gonna show an object like we did earlier if 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 save this real quick and come up here and run it and you can see my sequel cursors 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 for loop that allows us to look through all of the databases that are there and print them on to 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 DB right let's save this and over here and run it boom we get a list a nice little sort of Tuffle list of each of these databases in our my sequel database so we have information schema we have a my sequel performer schema at the Sekulow test DB and world and if we open this up we had the same Sakina assist test DB 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 pretty simple so you see when we printed this out it printed it out as a sort of a big tupple and that's what this is it's a tubble so if we want to you know be a little nicer about this we could do something like call the zeroeth item of the tupple something like that it might work let's see what that looks like yeah so 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 we'll show you how to do that and that'll be cool so I think that's all for this video in 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 okay in this video I'm going to show you how to create a table so up until now we just created this testdb 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 know 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 then 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 going to go ahead and save that now you can see here I've started to 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 can have all this as sort of a quick reference guide I think that'll probably be 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 I 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 and the sequel command for creating a table is create table you're gonna find that sequel commands are 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 you 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 datatypes 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 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 going to go through here and type some of these out and then we'll talk about them afterwards so let's say we want a name field now you can have first-name lastname I'm just going to keep it simple for this so we want name and we want the datatype for that name to be VAR car and then inside of that we want to say 2 55 now 255 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 going to put 255 so for instance you know if somebody types in John elder right that is 1 1 2 3 4 5 6 7 8 9 10 characters long right as long as as long as it's less than 255 we can put that in there in that column so it's just sort of a you know tells you the max amount of characters that you can add so that's of our car and we'll talk about VAR car and some of these other data types you know moving forward so ah 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 255 now back in the day when databases were new you really wanted to be careful and put like just the number of characters that you needed because memory and space computer space was very expensive back in the day and there wasn't much memory available to add these things there wasn't much you know disk space to hold this stuff these days you know computers are huge web servers are huge it really doesn't matter how many characters are 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 255 you might put 50 or something you know you might scale it down just to get that one hundred thousandth of a percentage point speed you know thing we don't care about that right so okay varkari male comma let's say we want the person's age right so age is going to be an integer and I don't know 10 10 characters I mean really we need three characters at the most nobody's more than 900 years old right 999 so but I'll just put 10 just because ok so and then finally we want to put a user ID so I'll just go user ID 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 that you don't know what any of this is yet but we want some number that's unique for each row right so if we want to look up John elder there might be 10 John odors and our you know people have the same name you know how do we designate which ones which well they need a user ID and those numbers need to be unique right so that's what this does it creates a unique number it assigns it automatically so it just says who was the last one oh the last one was number 8 this guy's number 9 the next one will do me number 10 you know it does that automatically auto increments for us and primary key actually we need to make that capitalized primary key means that this is the main key that we're gonna use to keep things you know keep track of things you can have lots of different IEDs this is going to 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 can go my cursor dot 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 table in my cursor and then go just print table okay 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 tubful we can also do it like this at a zero the zero with item in our tuple you can't run this guy again oops what do we do Oh I ran this again it tried to create another table oops we need to comment that out since we've already created the table alright run this again boom users and a nicely better formatted and you notice if we pull off our workbench err we have in this world thing we have lots of tables right city country country language etc none of those things are showing up on our git bash when we were on this command the at 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 testdb so it's going into the test DP it's calling all of the the tables in that and in this case there's only one table the see what do we call it users table and likewise we can pull up the workbench again come over to our schemas hit refresh and then come down to test DB tables and then boom we see this users select rows 1000 there's none left there's none yet we haven't added any people into our database but you can see here the different columns name email age and user ID 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 that should be a lot of fun and that'll be in the next video okay in this video I want to talk about inserting data into our new table so unless what do we add at a table we've got this users table now how do we put stuff actually in it well there's a couple of different ways and I think in the first video we're gonna look at the first one 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 gonna do one I think 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 variable and let's just call this I don't know sequel stuff does it really matter and set this equal to now we need to create a sequel command and when we add things into a table we are 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 what we're going to be putting in so we had a name an email and an age we don't need to put the user ID because the database will add that automatically since we put this Auto increment thing so we don't have to deal with that so let's go name comma age now let's go a name comma email comment age it doesn't really matter the the well yeah it probably doesn't matter the where you put them so we probably shouldn't do a Gmail we'll do email age because that's the way they look in the actual table itself so then we go values and here we're not going to put the actual values right here we're gonna put placeholders and for our placeholder we put a percentage sign s and that's sort of like when you interpolate in a string with Python you'll do the same sort of kind of thing so that's what that is so comma and then we'll do it again and then we'll do it again it's yes I go and okay so we do this three times because we we have three things we're putting in here right make 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 to let's just call it record one and let's go let's add John and my email is John at Kota me.com and my age is 40 and we need to put this all in brackets I 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 we'll 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 are surrounded by quotation marks and the numbers are not generally speaking when you do numbers in 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 them in quotation marks so no quotation marks okay so whoops 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 oops underscore cursor dot execute cute and then we need to pass in two things first this equal stuff whoops ago and then this record one pretty 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 that's calling it's called commit you know 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 to show anything on the screen so if we run this nothing will happen on the screen hopefully unless we get an error nothing happened but now we can pull up our workbench here this is why this is kind of handy we can just eyeball this head over to schemas and then click on test DB tables users and now let's right-click and do this select rows limit 1000 and boom John John echo to me age 40 and look at this it has a user ID of one very cool huh so that's how you add one record pretty simple pretty straightforward yeah and you see up here this is the sequel right select the star stands for everything from test DB users and then this 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 and 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 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'll be in the next couple of videos all right so I hope you enjoyed this first half of the course if you'd like to see the other half the second half of the course you can check it out on my website go to me.com or you can check it out on you to me and now you to me I'll have a coupon code below in the description so you get like 97 percent off I think it's $9.99 for the whole course or you can check out code me comm where I have the rest of this course and over 40 other courses and membership total membership is usually $49 but there's coupon code below its youtube you can use that at checkout to get another $22 off membership so I think you paid $27 for all my courses that's unlimited access you can log in watch them as often as you want forever there's no time limit you also get all my future courses for free every time I release a new course and I release a new course fairly often it just shows up in your account absolutely free so if you enjoyed this course you want to see the rest of it that's a great option for $27 using coupon code youtube if you just want the rest of this course alone check out the coupon code bloat for you to me and it's like $9.99 can't really beat that and so yeah so my name is John elder from khou-tv comm we'll see in the next video

Channel: Codemy.com

Views: 109,704

Keywords: Python, MySQL, Using MySQL with Python, Using MySQL Databases With Python, Python MySQL, MySQL python, Python Databases, How To Use MySQL With Python, Can You use MySQL with Python, MysQL For Python

Id: yOmxJbZjTnU

Length: 70min 9sec (4209 seconds)

Published: Thu May 23 2019