 All right, so hey guys and welcome back to another Python tutorial. So in this video, we're going to be doing something different We're actually going to be stepping foot into first steps of learning how to actually deal with a proper database So some of you may have or may have not already heard about SQL which stands for structured query language So what this does is it helps us make different queries or requests to a database that we have going So we're going to actually learn how to make requests Insert information create databases and do do different functions to do with databases So pretty much interaction with databases and all that using Python we're going to be using this information to later on create a Good project like a big massive project that we haven't done on this channel before So I hope you guys are going to enjoy this series because this video is going to be split in a few Tutorials because there's a lot of things that you can do with SQL. So I'm going to be splitting that up So without further ado, let's begin first of what you actually want to do is open up Chrome Okay, Chrome and then you want to type in in your tab exam Download now what this is is it's going to allow you to have PHP my admin and you could also going to have your my SQL So my SQL was pretty much an interface that allows you to manage your databases with a GUI interface, so go ahead and Go on the website for downloading XAMP and then download the version that suits it for your system And then once you're done installing come back to me So I'm going to close this off because I've already got mine installed So I don't need to once you're done installing what you'll have is something like a XAMP control panel So something like this. So when you type XAMP And you have XAMP control. That's what you want to click on So I've already got a shortcut for this. I'm just going to click on that. My shortcut is down here So I'm going to click on that once you click on your XAMP control You should have a control panel like this opening up for you So what you want to do is click on start Apache service I'm not sure if we're going to need that but just in case and you want to also click on Start MySQL service because that's the service We're going to be using to interact with our database and do all the stuff we want to do. Cool So once that's done What you want to do is actually open up CnB so we can do our install for the PIP So what you want to type in here is we're going to be typing in PIP install Let's type that again PIP install MySQL, which is the name of the module we're needing Connector which is the class we're needing and then type in Python press enter and then it will take a few seconds to install So mine already says already Requirement already satisfied. That's because I've already got it installed make sure that yours installs successfully and once you're done Let's start coding then close this off now. So we pretty much Have downloaded whatever we need we've got a Python module and we've also got my SQL server running so that we can actually interact with our database in real time Cool, let's open up visual studio code and let's get going So create a new file and then save and say my whatever you call this file guys Make sure you don't call it my SQL.py because if you do so you're gonna have errors with your import So make sure you don't do that. So I'm gonna call this learning SQL.py.py because it's a Python file. So that's the Python extension Let's save that up and now what we're gonna do first off is actually import our SQL module So import SQL dot connector. So we're gonna import the Connector in SQL that lets us make connections to our database as My SQL. So what we do when we say as my SQL is that we no longer need to do SQL dot connector when we want to refer to this We can just type in We can just type in my SQL. So we could just do my SQL dot whatever. We don't need to type SQL dot connector the next time we need to refer to this. So that just makes it easier. Cool. So for making First of all whenever you're using SQL the first thing you want to do is actually connect your code to your database So to connect this we're gonna need a few variables. So let's assign host So that we actually know where the host is going to be connected to So by default your host is going to be hosted on your local host. So just type in local host as your host as a string Then you also need to provide what user you're logging in using By default, this is going to be root. So type in user equals root and speech marks And then lastly you need the password to your database So your password is just going to be a blank string unless you've already set it up So if you've set it up type in your password in here if it's your first time installing this XAMP you do not need to type in a password because it's blank by default. Cool So we've literally got all the variables we need to log in to our SQL So what we're gonna do first is actually try and connect to our SQL database. So Connecting to my SQL. So this is pretty much logging into my SQL So try and catch we're gonna be doing now. Let's create a new variable called database equals my SQL Dot connect. So we're using the my SQL module and then we're using the dot connect Class in it or the method of it and then in there you need to specify a few arguments So the first argument is the host which equals to host which is right here Second argument is user and third is password. So we're gonna type all of those up. So host equals host user equals user and then password equals password. So we've already have saved these Values into variables, which is a lot easier now since we can just change it whenever we need to So once we're done with that we need to print Connected Successfully so if this message brings out it just means we've connected successfully without any errors And then we also need to do an exception. So accept Exception as eep and then print eep print failed To connect hope. So that's that done Now if everything went fine, you should have a message that says connected successfully Now bear in mind guys for this to work You actually need to make sure that you have your zam control and this needs to be running So it needs to be on whatever and the stop button needs to be on if it's if it's not running Then it's not this is not gonna work. So make sure you have a party and my SQL running So I'm gonna run this now and moments of truth What does it say? No module named SQL. Okay, so What I did wrong here is I need to type in my SQL. So I got a bit excited there I forgot to type in my so it's import my SQL connector because the name of the module is my SQL I forgot about that Let's run this now and hopefully we should have no errors. So as you see right here We have a message that says connected successfully. That's because we have Provided the right house the right user and the right password. Now, let's go ahead and mess this up Let's type in something like root e Not when I run this it should return an error saying it couldn't so it said failed to connect and the error was Access denied for root e at local host using password. No, which pretty much means there has been an error in the authentication for the user so our catch and Accept statement is working fine. So let's save this up as it was before so that it works fine Now that's one bit. This is always how you're gonna be connected to your SQL database in the start Now let's talk about how we could actually create a database. So Create Creating a database So what we need to do to create a database is another try and accept because just in case it's a good practice as well Now we need a command handler object that we're going to be assigning a cursor to so command handler equals Database which is our SQL object that has already been connected and authenticated and everything So this object right here. We're going to be using that and then we're going to be using dot cursor Now we have Used command handler as a variable right here to make it easier since we don't have to keep typing database Dot cursor on and on again. Now, it's all saved in this command handler variable right here So what we're going to do with that is type in command handler dot execute And this is where you're going to be typing a query. So this obviously is SQL Which means it's a query language. We need to type a query to the database. So our query is going to be create database and The name of the database. So this is going to be about cars. I'm going to type in cars So we're telling the SQL that we want to create a database called cars Cool, and then we are executing that command using the cursor that we assigned before and obviously This is all happening because our connection has been successful if the connection wasn't successful This will this would error as well because there's no login session initialized Cool. So once that's done, we'll say cars database has been created and then we're going to do accept Exception as E print Could not create Database and then we're going to print Y. So the exception. Cool. So let's go ahead and actually run this and As you see right here, it says connected successfully Which was the first lines of code we learn how to connect to my SQL and then it says cars database has been created So the database has been created. Now you might say, oh, how do I know if it's actually created now? Thankfully ZAMP has a GUI version of this so that we can actually check what's going on So if you go back to your ZAMP control panel click on Go down to my SQL and click on the admin tab for my SQL So when you click on that, it's going to open a browser window and it's going to take a few moments to load So let's close this off. Once it's done loading. It should show us all the available database All the available databases that we have now if you see right here, we have a database Let me zoom in a bit. So if you see right here, we have a database called cars Which we literally just created using our Python script So we're using Python to interact with this PHP my admin or my SQL interface right here to do stuff with our databases So we've just created a cars database right here We can go ahead and add tables to it in a moment as well So what happens if I try to run this command again, you might say so let's try running it again It would say could not create database can't create database cars because database already exists which makes sense because we can't recreate the same database that already exists It's just against the rules. So we have now learned also how to create a database. Let's move forward So you might want to also view all the databases that already exist on your MySQL So this is pretty much command line and how you should learn to do stuff instead of having to do it through the GUI So try and catch again. So try command handler Dot execute. Okay Come on handler dot execute and we're going to execute a query to actually show databases. So show Databases so this right here is the language that SQL understands It's a query that we're making to the database. So it should understand that and return the relevant results Which is going to be stored in command handler cool Now that we're done with that What we're going to be doing is we're going to need a for loop to actually view all the databases because we can't just view in one Go so we're going to do for Database in Come on handler so for database in command handler print Database so for each database that is returned and stored in command handler We're going to print that database out and then we're going to say appear print These are the available databases. Cool. So we're going to use an exception statement down here just in case except exception as E and then print For not show all Basis and then we're going to print E which is going to be the error that just happened. Cool. Let's go ahead and try this out So when I run this and pretty neatly, it's done it right here It says these are the available databases cause ePause information schema Inventory my SQL performance scam on page be my bin and blah blah blah So it's pretty much shown us all the databases that exists now We can confirm this by going into the GUI version that was opened in my browser right here And as you see it's literally shown us all the databases that are right here. So it's working fine Now, let's move on. Let's close this off These are now three things that you've learned how to make a connection how to create a database and how to view databases That already exist now what we're going to try to do is Instead of connecting to my SQL We're going to try and connect to a database that already exists because right now the actions that we're Doing are based on my SQL We're not actually connected to any database if you see because we're only connected to the my SQL through here We could also connect to a database like a specific database and then perform actions to that So let me try and show you what I mean. So to connect to an existing database. Let's just go down here Connecting to an existing database now if you remember the database we created was called cars So we're going to try and connect to cars now So we've got to do the same thing again database one this time because it's a new database equals mysql connect host equals host because that doesn't change user equals user because that doesn't change either so Does the password doesn't change either now only we need to add an extra Argument in here called database We're just going to be equals to cars because we want to connect to our database called cars using instead of just connecting to the Mysql general Interface so that should do that and then if it connects successfully. Let's do a try and catch here as well because why not print connected to cars database and then accept exception as print could not Connect to cars database and then print exception Cool So the only reason the exception should happen is either because we have entered the wrong login details or that this Database clearly doesn't exist, but we shouldn't have an exception anyway Let's try running this to see if we can actually connect to an existing database on it Let's go up and as you see we have a message saying connected to cars database Now as I said the exception should occur if we type typing in a database that doesn't exist or a wrong password So if I type in an extra s in here and then run it it shouldn't allow me to connect It should say could not connect to cars database and we have an error unknown database cause because that doesn't exist Cool, so let's revert that back to the right version of it And that's how you connect to an existing database Cool, so we're learning a lot now. So try and catch up with me and I would recommend practicing this as soon as you're done We're also going to be using all of this to actually create a big project as I said in the start So don't worry about that if you could practice this that would be amazing for yourself though So now that we've learned how to actually log in into an existing database We can go ahead and learn how to create tables in a database. So creating tables in a In a database So at the moment the database one variable is looked into our database called cars So we're going to be using that to create a table in there So table pretty much stores information in in the form of different fields. Let me show you an example So I'm going to go to my GUI version right here And I'm going to go to an ePOS database which is an a restaurant system that I created a while ago So in here when I click on it, it shows me the available tables So I have a table for menu. I have a table for orders and I have a table for menu categories So the table for menu stores the different items that I'm selling. So if I click on it I have different so this is my table for menu and this is all stored in my ePOS database. So in my menu table, I've stored a column for ID I've got a column for category I've got a column for item name and I've got a column for price and item type So this is the way we're going to be going about our cars Database as well. So if I click on cars right now, obviously, we haven't added any tables to it So it's empty. So it says no tables found in database. So that's exactly what we're about to do So we're going to be adding a table in there for based on models So we're going to create a table for the car Ford and then in there We're going to specify the different versions of Ford that exists and the different engine sizes and all that So let's go ahead and do that. So what we need to do now is do-do-do-do-do Try and then we need to assign command handler again because now we're using database one Remember the command handler we have previously assigned is stuck on database, which is only my SQL But now we've logged in into our actual database called cars. So we use database one dot cursor To literally go ahead and update the command handler to be using the right cursor for the right database now Cool. So now what we want to do is go ahead and execute a query. So command handler Dot execute and this is going to be a query So we need to type in create table This is the SQL language and then the name of the table as I said the name of my table is going to be Ford because it's Different models of Ford and then we need brackets in here So in the brackets you're going to specify what fields you want So the first field you always want to add in every table is an ID which pretty much auto increments So every time a new record is added this ID should automatically increment by one So you do this by typing an ID then you type in int because that's the variable type So the file type is integer and then you want it to auto increment So these are all keywords that are understood by SQL And you need to say that this is going to be our primary key, which means the identifier for all our information So unique identifier now you can use a comma to specify other fields that you want to have in this table, too So I've got an ID field I want a name field for the name of the car and the type for this name field is going to be a very Variable character, which is fine and then the length for this variable character is just like a String so we're going to use that and we need to specify How many the length of the string so 255 characters, which is the max you can use I'm going to go with that and I'm going to use another comma because I want another Column in there. So I'm going to have another one called engine size Try not to leave spaces just use underscores if you can and then I'm going to use rocker Variable character Rackets 255 just so that I can go with the max size again. Cool. So once that's completed make sure you have your Syntax like mine. You should have a speech mark and a closing bracket and all of this as well So what you want to do next is actually print Okay, so you put a caps table Created Successfully Cool. So if the table has actually created successfully that will come up and we need to accept Exception as E and then do the rest just in case an error happens print Table could not be Created and then we print the exception that's gonna happen so print exception Cool. So fingers crossed this should work in the first time. So let's go ahead and run this Let's see. I don't think we have any errors and it says table created successfully now if it has created successfully when we open the my SQL When we open my SQL the graphic user interface on Google or Whatever you've got it opened on and when we select the cars database We should have a table for Ford where we have an ID Column a name column and an engine size column. So let's take a look So I'm gonna go into a pair because that's where I've got php my admin or my SQL opened up Refresh this page because you need to refresh and as you see right here marvelously We click on cars and then out of nowhere This Ford table has shown up because we've just run the query using Python I want to click on this for table. Guess what just like I asked it to it's created an ID Column which is going to automatically increment by one each time It's got a name column where we can type in the name of the car and then it's got an engine size column Just like I asked it to so it's all working flawlessly now We've also learned how to create a table and add fields to it. So perfect. Let's move on So now you might be saying hey, what if I don't want to use the GUI to actually check how many or what tables I've got going in my database so you can do that by typing in showing. Okay. I'm gonna do showing tables in the database selected Now bear in mind. We're in our cars database. So it's only going to show us the tables in our car database Unless you want to swap it to another one. So we just do come on handler dot execute There's a query called show tables, which is pretty much going to show you all the tables in the database You're logged in which is cars for us and then we're going to do print I print showing all tables in the database and Then we need a for loop like I said before because you can't just show it all together Whatever tables there are are going to be returned to this object right here called command handler So we're going to do a for loop so for table and come on Handler and I can call the table anything. It's just like a variable. So I'm just going with the generic name We need to print each table. That's in the command handler. That's returned. So let's run this up quickly and Okay, let's go. Yeah, as you see right here showing all tables in the Database and we have four because that's just the one table that we have in our database currently Which is the cars database if you go ahead and add another one like I don't know a different brand like wall swag You know something like that. It should show up there as well Cool. So that's working fine as well Now, let's go ahead and do the rest of the commands So you might be saying hey, we've not got a table in our cars database But how do we actually add data to it or like a row of information? So that's exactly what we're going to be doing next. So adding data into the table. So They turn to the table Now what we need to do for this is actually first of all create a query So we're going to have to do this separately instead of just typing in command handler execute. So insert Into which means we're going to insert some information then we specify what Database you want to insert. I mean what table of the database you want to insert this information into which is Ford Then you need to specify what fields you want to fill in we want to fill in name and Engine size. That's the fields that we're going to be adding information to and then we type in the key word values, which is part of SQL and then you in this bracket Essentially, you would add the values for name and engine size using a comma separator But the issue is we can't really type the names of the variables in here So we can't just go like, I don't know hello comma hello because that's just bad practice. We need to assign these as variables So that's why we're going to be using a string converter you need to type in Percentage sign s and percentage sign s So let me explain what that's going to do. It's going to format the variable that we pass into this later Into these fields right here. So I'm going to I'm going to explain that in a bit So we've got a query ready now. We need to assign the query values. So query values equals Actually, we need this. Yeah, so query values equals You're going to have two brackets and then we're going to pass in the values in here So the first value that's going to be stored as a name for me is going to be let's say Focus because that's one of the versions that board has I assume and then we're going to say engine size is going to be 1.8 liters So essentially what's going to happen is these values are going to be placed here And this value is going to be placed here, which is how we want it to be So this is not yet happening. It's going to happen when you do command handler Execute and then we pass in first argument the query This is usually where we'd like type in this whole line But we need to type it in separately so that we can actually use variables to pass them through and then we Repass in the query and then we also need to pass in the query values So whatever values have to be run into the query So these values right here query values are going to be merged into these so this Percentage s is going to be replaced with Ford Focus and this percentage s is going to be replaced with 1.8 liters So that all makes sense cool Finally you need to take the name of the database or the connection that you have which is Database one for me and then type in dot commit which means dot save the changes and then print command Handler dot row count So what the row count does is it returns how many rows were inserted in the last query and then you need to say record or record Inserted so for now, it's going to say one recorded inserted because we're only inserting one record right here, which is this Ford Focus and 1.8 liter Cool. So that's that for that. Let's run this and hopefully it should work in the first go So run it up Let's pull this up and it says one record inserted now We can verify this by going to PHP my admin right here in which I have open in opera Let's refresh my page and if I go to Ford right here as you see my ID is 1 because I don't need to enter anything in there I can leave it empty or auto increment for each Each column its row of data that's entered then I have name which is Ford Focus Which I entered using my Python script and then I have engine size 1.8 Liter which I've also entered using my Python script now you might say hey This is a bit jarring to enter each field of information using one query each. That's a bit long So I'm gonna say there's a better method of doing it So let's go ahead and learn how to do that so we can actually using this method We can add multiple fields of data into the table So let's go ahead and do that. I'm just gonna scroll down a bit and This is actually going to be how to Adding how to be adding multiple fields of data. So let's do that adding multiple fields of data So we need to list that query again. So insert Okay, actually, I'm just gonna copy and paste because it's literally the same query. So let's just copy and paste this Paste a query and then the query values this time are going to be a bit different So I'm gonna copy this and I'm gonna paste it down here So the query values for this are going to be multiple values because as I said We're gonna be inserting multiple values this time. So we're gonna need an array So put square brackets at the end of this So now Ford Focus comma 1.8 liter is the first item in our array Then we need to use a comma and then we need to have another one. So I'm gonna do another one for Ford Fiesta and Then I'm going to do it with a 2.0 liter engine and let's change this Ford Focus from before to like a Mustang or something I don't know so that it doesn't overwrite. So I'm not sure if that's Ford But hey, whatever doesn't matter for now. So array has now got two Two sets of data Which are going to be replaced in here So whenever we want this query it should insert these two into our database together instead of inserting one at a time Not like before command handler execute But this time we're gonna type in many because we're executing many queries at one go and then we tap in the query and the query value is just like last time and Lastly, we need to do database 1.commit so that we save all changes and then we print command Handler dot row count comma Record Insert it Cool, let's run this up and see if it actually works And it says one record inserted for the last one that we had which was the Ford one And then the last test that we done was to record inserted which means it should have worked Let's go ahead and check So if I run this again as you see right here We have Ford Focus repeating once because obviously that that code is still in our program So it's still running But we have Mustang and Ford fiesta and 1.8 liter and 2.0 liter showing up all together So that query worked. We were able to add multiple values in one go Cool, so that's how you do that. Let's close this off Now we still have a few more things to go through before we actually end this tutorial So there's quite a bit you're learning from this. So I hope you find value in this tutorial So let's say you wanted to find out what records are there in your table without actually having to open up your Jewel gooey each time. This is going to be how to display all records from a selected table so you can use a MySQL query actually to do this so command handler execute and the query for this is select star From whatever table you want to select it from so select star means select everything from I'm going to go with Ford because that's the only table we have in our cars database at the moment So select star from Ford and then I'm going to do records equals command handler dot fetch all so we're telling the Command handler, which is this query right here when it's executed We want to fetch all the results that we get from this forward table and then save that into a variable called records Now we need to loop through this to actually find out what records are in there. So just playing records Spazzed out and then for record reports We're going to print report cool Let's run this up quickly to see if that works and as you see it flawlessly works displaying records And it has all the records that are currently stored in our Ford table of our cars database So this bit right here Cool, so we have successfully managed to pull out data from our table that we've selected Now what if you wanted to pick only specific columns of this table? So what if you wanted to find out only the name or only the engine size from this table and not everything? You can still do that which is going to be the last command for this tutorial. Hey, finally comes to an end, right? so displaying specific columns From the table select So we need to command handler again because command handler is what we use to make any queries Don't execute and then in there we type in the query, which is going to be select name From Ford so now if you remember in the last query we said select star which means select everything from Ford But this time we're being more specific We're saying select only the column called name from the table Ford Now this is only going to return the names of the different cars that we have under the Ford table, which is what we want so records again Records is going to be overwritten by command handler fetch all because we need to better all the records display names from table Ford and Then we're going to do a for loop again for record and Reports now as I said before the record variable could be changed to whatever you like I'm just calling it record to keep it specific and Lastly we print the report for each record we have So let's run this up and if I see right here as I see as you see It says displaying names from table Ford and we only have the names of the cars We have no longer got the engine size Whereas in the previous query where we said baby displaying everything from a table It literally says the ID the name and the engine size But now we've specifically said we only want the name now likewise if you wanted to find out just the Engine size you could change select a name to select Engine size and if I run that it should only show me engine sizes now Right like it's showing me right here So it's pretty simple how we work with this my skewer library It might sound a bit hard in the start But trust me guys you can rewatch this tutorial or go through the source code and keep practicing and you get used to it Like it's nothing so sorry that the tutorial is so long But I'm pretty sure that you've learned a lot through this and you might find value through it Thank you guys for all the support that you guys have been showing me recently I am very grateful to you guys and if you like if you would like to support my channel You can do so by becoming a patron and signing up using my patron page Which is going to be on my description on all my channel tags You can also consider joining my discord channel where we discuss different ideas for videos meet new people and just have a Lot of fun also consider following my suit following up my socials and sharing this video to try to help my Channel grow because I'm trying to do that at the moment if you could share it would be amazing And guys I will see a beautiful faces in the part two of this tutorial Peace