Python MySQL Tutorial - Setup & Basic Queries (w/ MySQL Connector)

hello everyone and welcome back to a brand new tutorial series on MySQL SQL and Python now the goal of this series is to get you familiar with SQL which is a structured query language that is used in all different kinds of databases and then to apply that knowledge to MySQL which is a popular database that's used by a lot of companies and a lot of web applications and things of that nature so essentially before I go too far I just want to tell you why you might actually want to learn this especially if you're just kind of clicking to see what this is MySQL is usually actually required or a MySQL but SQL is usually a required skill for most programming jobs even entry-level positions so recently I've actually been looking at a few different positions for programming and almost all of them require that you know SQL or you have some kind of experience with relational database systems like SQLite MySQL and the other ones that exist out there so anyways if you're looking for a job I'd recommend that you guys follow along with this tutorial or at least learn SQL on your own time because it is something that you may be tested on and be expected to know or be familiar with for a certain position so anyways with that being said let's get started what we're gonna do in this video is be downloading the tools that we need to actually work with MySQL now an important thing about MySQL is that it's different from some other database systems like SQLite and this typically is designed to run on a server so we're gonna be doing this on our local machine for this specific video and in later videos we'll actually deploy this out to a server and see how to work with it there but just so you know this typically runs on its own computer its own machine typically a Linux machine and what happens is a client can connect to that server and use the database like that whereas a database like SQLite just runs locally on whatever it's using so this is typically in my opinion more powerful and more used in terms of like large applications you typically have a dedicated server for your database but anyways let's go ahead and get started and download things we need to so we're gonna head over to this web link right here this should be able to bring it bring you guys right to it'll leave it in the description now if at any point you're asked to make an account you don't need to do that you can go through this without making one just so you're aware of that so anyways once we get to this page the first thing we're gonna load is this installer right here so there's two options you can choose either one you want I've downloaded the larger one because that means I don't have to wait for this web installer to install the rest of it once it's downloaded and once we have that downloaded we're gonna run through the installer which I'll do right now ok so I've opened up the Installer here for my downloads folder and now it's bringing me to a page that looks like this now here you can choose which one you want from this list now I'm gonna recommend we just go developer default we need at least the server and we need some of the client stuff as well because well we're gonna be connecting to it from this specific machine but developer deep default is fine so we'll click Next let me go here and this is MySQL for Excel we don't need that unless I mean you want that so I'm just gonna click yes ok that's fine and now we have all these things that need to be installed so let's go ahead and click execute now if for some reason any of these don't work you can always reinstall things after I'm with like a previous version of MySQL anything like that so don't be worried if these fail for me the first time doing it some of them did fail and you can go back and reinstall them and I'll show you how to do that so once this is done I'll be right back and we'll continue alright so we've gone through here and noticed that this connector for python failed now we do need to install this but I'm gonna do this a different way so don't worry about that and let's go ahead and continue for now this next button works ok so now we're here and it says do you want an eye and a node database cluster or standalone MySQL server again this is not super important we're just gonna go standalone server as the default here now we're gonna have this that comes up we can just leave this alone we don't need to touch this for now because this is just gonna be on our development computer as noted here you can see that if you're gonna run this on a server you could choose server computer dedicated computer whatever it is so anyways let's go ahead and continue here now it's gonna ask for all this let's just go the recommended use strong password encryption we'll click Next and now it's gonna ask us for a root password now it's important that you remember this password so make it something you're gonna remember now there we go I'm gonna type mine and I've just made mine root so that I remember it you guys can make it whatever you want and now we need to create a user account so I'm gonna start by doing this I'm gonna make the username Tim for host will leave like that and for password I'm gonna make the Timm as well uh what does the password does not mean okay so I'll make it reach it's the password all right there we go and continue on now we have a database user we have our root password and we can continue okay so now this is where we might want to look at some things here so what this is saying is this is gonna run this as a Windows service which essentially means it's gonna run in the background continuously now I want this to happen because I'm gonna be working with MySQL but some of you may not want this so just keep that in mind when it says you know configure MySQL as a Windows service you don't need to do this but it's gonna be easier in the future if you just leave it running in the background now for the service name you can change this if you want I'm just gonna leave this the same and move next all right so now we have these that we need to do so let's just click execute wait for that to finish up and then we'll be good to go okay so that's finished so I'm gonna go ahead and click finish here and then we'll go through and click Next it's gonna say bootstrap MySQL router we don't need to do that so we'll just hit finish okay now we'll go next again and now it's gonna ask us for this one more time so the password for root is gonna be root so we'll do that when it says show my SQL server instance that may be running in read-only mode we don't need to do that I will check that that's works correctly with the password for root which my password was root and then we'll hit next okay now finally we'll click execute we'll go through this and the server should hopefully didn't be installed on our machine we have a few more steps to go through and then we'll actually be ready to start writing some code here okay so next thing here we're gonna hit next again and so start MySQL workbench after setup start shell after setup I don't actually need either of these so I'm gonna click finish but you can launch these if you want okay so now we have successfully installed MySQL now if we want to actually like kind of mess with this and see what it is I mean we can type MySQL first of all there's a MySQL installer which means we can install other things that we need to and we have a workbench and a shell so let's open up the workbench and just kind of explore what this is we're not really gonna use it but I just want to show you that we do have a tool that's able to kind of navigate our database so we can see we have one connection here I gotta type in my password so let's type in root and there we go so we had one instance there now I'm inside here we this is where we can actually type SQL queries directly and then we can kind of mess with the database look at it see all this stuff that's going on in here I'm not really gonna explain this but I mean if you get more advanced than you can mess with this graphical interface we're not gonna deal with that for now though okay so now we need to install the connector for Python know what the connector allows us to do is actually make SQL queries from Python code which is kind of the whole point of this tutorial series all right so some of you guys might have seen that when you installed that your Python connector failed now if it worked properly chances are that you don't need to do this next step so I'm gonna show you how to check so what we're gonna do is just run a Python command inside of like our command prompt that's what I'm gonna do at least you guys can do this from like ideally like wherever you write your Python code is where you're gonna do this anyways what I'm gonna do is I'm just activating my base directory because this is anaconda that I'm using it's not really relevant just gonna type Python you can see that my Python version is three point seven point two it shows right there that's how you kind of check if you're in the right version and then what I'm gonna do is just try to import my SQL now if that import works correctly and there's no error then you have everything installed and you don't need to do anything else but if for some reason it fails we need to type in another command now what we're gonna do is we can do this in two ways for this MySQL Python connector we can download it from the download link that I have in the description and you can pick which version of Python it is that you need so in this case I'd like three seven two seven three five all of that so you can download the correct one and run through that installer and it should install it for you or what you can do is do a pimp command which will install this now I'm gonna use the pimp command and show you how this works it's pretty much just pip install and then in this case we go MySQL like that - connected now wait for this to run and once this runs and installs you're good to go and we actually start writing the SQL queries so again just to recap here you're gonna test to see if you can import MySQL if you can do that properly in Python wherever you're writing the Python code you're good to go to this next step otherwise you need to run pip install MySQL connector or download the file from the link the description run through that installer and it should install that for you all right so now that we've installed everything what we're gonna do is test to make sure that everything's working now I'm gonna provide a few solutions if some stuff doesn't work so don't freak out immediately if something's not going correctly now what we're gonna start by doing is importing MySQL connector now just do this in any new Python file so I'm just doing this in sublight X it doesn't really matter just run this in any Python file you want and make sure that this command works we've already tested this but just you know run this make sure you don't get an error next what we're going to do is try to connect to our database so what I'm gonna do is say DB equals MySQL dot connector dot connect now before I even bother typing the rest of this stuff what I want you to do is run this so I'm running this by just hitting ctrl B in sublime text and make sure you don't have any errors with this because if you have an error we need to fix that so if you run this and you get something saying like there's no attribute connect don't freak out what you need to do is go to the website and install that connector file that I talked about before so I think I have it actually I don't still have the window open otherwise I would show you guys but essentially what I showed before when I said you know this is the Python connector download for whatever version of Python you have you need to install that and installing that should hopefully fix your problem now that's because sometimes the pimp command doesn't work properly when you do that pip install MySQL connector whatever it is so just run the actual file and hopefully that should work and be proper for you guys okay so now that this command is working what we need to do is actually connect to our database now remember I was saying before what you typically end up doing is you're gonna connect to an actual server so different computer all alone now since this is running on the same computer as the one that we're actually writing this code on what we're gonna type here for the host attribute is the localhost so I'm gonna say don't connect host localhost next I'm gonna put the user now again this user can be whatever you want I'm gonna put it as root but you could make it whatever user you created so in that case I made a user called Tim I could connect with that but let's just do root and then for passwd which is just your password I'm gonna type root as well now that was what I select as my password you guys might have a different one that you're gonna put this just in plain text whatever the password was now let's run this again and see if that's working okay that's working we're good we can move to the next step so the next thing that we're gonna do is create a cursor object using this database and execute one SQL query to create a new database so what I'm gonna do is I'm gonna say my cursor and you I mean you can call us whatever you want equals DB cursor like that now that we have this what we can do is kind of curse through the database and run SQL queries and get information that we want so the first query I'm gonna make is gonna be create a new database now if you're not familiar with SQL queries don't worry we're gonna go into detail but exactly how they work but the way that this one works is we do my cursor dot execute and inside here we're gonna put a string which is going to be the query that we want to execute so this is kind of the standard way to do this you say my cursor dot execute and then your query goes inside of a string so in this case what I'm gonna do is I'm gonna say create database and this isn't all capitals and I'm gonna call this one test database like that now notice sub-line text is noticing that this is a query so it's highlighting it for me but your editor might not do that so don't worry anyways we have that so what I'm gonna do now is go ahead and hit control B we see there's no issues everything worked and now we've officially created a new database now what I'm gonna do is change my connection option here at the top when I connect to connect to this specific database that I just created so now what I'm gonna do is put data base here is equal to and we're gonna put the string test data base because that's when we already made write so we'll do that here test data base and then we can actually get rid of this execute command like that we can run this and we can see that we don't get any errors so I know I haven't showed you guys much we've just got everything set up but hopefully everything's working here if you have questions or concerns leave a comment in the next video we'll get into actually working with the database committing some stuff adding some things understanding how these queries work and all of that but as always if you guys enjoyed make sure you leave a like and let me know what else you want to see from the series in the comments down below