 Right, so hey guys and welcome back to another Python tutorial So today's video is going to be the start of a very interesting series Which is going to be creating a college management system using Python So just to let you guys know this system is going to be a command line based application Which means it's not going to have a graphical user interface. We might do a series on that in the future So this series is going to actually be using a MySQL database to store all information Retrieve information, edit information and delete information related to the college system Cool. So this is based on what we learned in my previous tutorials about how to use MySQL and manipulate a database using Python So I'm going to be linking a couple of tutorials in the description if you are not familiar with MySQL So go ahead and watch those first and if you have a bit of an idea of how MySQL works, you can carry on with me now So first up what we want to do is make sure that you have ZAMP installed. So open browser and type in ZAMP download So click on the first link and then it should take you to Apache friends website So you want to click on the most recent version of PHP and then download the Version of ZAMP that is compatible with your system So ZAMP is going to let us pretty much host the MySQL database and let us interact with our database Cool. So once you've got that installed you can go in your window search bar and type in ZAMP And then it will come up with a pop-up saying ZAMP control. So that's what you want to open up So I'm going to go ahead and open mine up, which is already in my Um task bar down here So I'm going to click that and this is what it looks like So once you have it open what you want to do is click on start the Apache server Because that's going to let us have the GUI version of MySQL And then click on start for the MySQL so that we can interact with our database Cool. So the second requirement for this is to actually have our hyphen module for this installed as well So what you want to do is open up cmd or command prompt Make sure you have pip installed and then you type in pip install um MySQL a hyphen connector hyphen hyphen and press enter Now mine already says requirement already satisfied because I've already got this installed So if you guys haven't got this installed already make sure you run this command and have it installed successfully So once you're done with that our setup is then complete So what I'm going to do first of all is go ahead and open up MySQL admin so that I can Create the database for our system. So we're going to be working backwards for our system cool So let's go ahead and take a look at how this works So this is the user interface for MySQL if you don't want to write queries and you want to do everything using the GUI So in the last tutorial, I did show how to use queries, but in this tutorial just to save time We're going to be using GUI instead of the queries. So I'm going to click on new and open a new database We're going to call this database college because it's a college management system So database is going to be called college. Let's create that up Now in our college database, we can have a couple of tables. Okay, I'm opening the wrong one So I click on college and it says no tables found because quite rightfully we have no tables in here So we're going to have a couple of tables in here. Our first table is going to be called users So this table is pretty much going to store all the users that are That exist on our server So it will store the username It will store the password and it will also store the privilege So whether it's a student account or a teacher account because our Our college management system is going to allow an account for teachers and an an account for students Cool. So let's open this up and click on go So once you click on go, it will take a second to load and it will let you create fields For your table. So I'm going to create an id field, which is going to be an automatically incremented field So if I go to my right side, there is an ai field up here I want to take that box to make sure this is automatically incremented So what this means is that it's automatically increments the id by plus one each time a new record is added Cool. So I'm going to go back. That's my id done. Don't change anything else in there Now in my users database, I also want to have a username so that we can refer to the user I'm going to saving I'm going to be saving that as a rocker or a variable character And then 255 because that's just the max for it. If you want to go with good practice, you would obviously go with Something about I would say 25 to 30 because that's the max limit for a username and that should be okay But we're just going to be creating this for fun as like a beginners project. So that's fine I'm going to create a password field And I'm going to be saving this as a variable character as well Again, I'm going to go with the max so that we don't run into any errors for now And lastly as I said, we're going to have a privilege Field which is going to be a variable character as well because we're going to be storing strings in there And this variable privilege field would either store a teacher or a student string Which will signify what type of an account it is Let's save this up And once I've saved if I open up my college database now I have a user's table in there which is where my user accounts for all the people are going to be stored Now we're going to have an admin account on this console application. That's going to be able to register student or teacher accounts So essentially this admin account would be the technician Who has all the privileges and is able to register anyone? Now our privilege will Specify whether the account has more privilege than the other because students are going to have less privileges than the teachers Obviously teachers will be able to take registers and all that stuff whereas students will be able to do not a lot of admin stuff anyway So that's our basic stuff done, which is enough for this tutorial So we're only going to be doing up to allowing the user to allowing the admin to log in allowing the Admin to create a teacher account allowing the admin to create a student account Um and allowing the admin to delete any of those accounts So that's what we're going to be covering in today's tutorial So let's go ahead and create our file now because we've done the back end ish of our Of our tutorial today. So I'm going to go ahead and create a new text document and call this college System and then I'm going to change the extension to pi because we're coding in python obviously Now let's open this up in visual studio. So let's open up visual studio code I forgot I could do that in there. Um, and then let's just drag this in here. Cool So we have that done now So we can actually start coding our application now. So first off what we want to do is actually import what we need So we need only the mysql module in this. So I'm going to do import mysql.connector which is the class that lets us connect to our mysql database Um as mysql so that we can refer to it easily Now we're going to initialize our database in here. So database equals so db stands for database It's just a variable and we're going to use our mysql object that we just initialize dot connect Which is one of the methods it has and then we need to provide it with our host Which is going to be local host as default your mysql Database will be hosted on these parameters user is going to be root unless you've changed it Password is going to be blank by default and my database as I know is called college Because if we go back to my phb my admin phb my admin the database name is college because we called it that Cool. So once that's done that just means my database has connected successfully Now i'm not going to be adding any try and accept in here But if you want to you can do it for good practice So i'm going to also initialize a command handler variable in here, which is going to be an object um of database Dot cursor So this cursor is pretty much going to allow us to run different queries such as delete insert edit or so on So i'm also going to pass in buffered equals true so that we can run multiple queries on this without having any errors Cool. Now the first function that we actually want to program in here is going to be called our main function So our main function is going to be holding the main menu for our program. So main And then this is going to be an infinite loop. So while one We're going to print out a menu for the user. So we're going to say a nice message welcome to the college system We're going to print a blank line I know I can do forward slash n, but it's just easier for me to speech mark for now Now we're going to say option one is going to be login as a student I'm going to copy this line and paste it down here. I'm going to change the option one to option two and I'm going to say login as teacher And then lastly we're going to print that line again But the option is going to change to three and we're going to say login as admin So these are the three options that our main menu is going to have so every time the user logs in into or opens up Our application. This is the main menu is going to be greeted to or if they log out open account So now we're going to have to take the input. So user option equals input string Option or let's say Yeah, let's just say option So the user will enter the number Corresponding to the option they want to select. So if they wanted to log in as teacher, they would type in two and so forth So now we're going to have to write an if conditional for this. So if user option equals one That means they want to do a student login. Okay, let's just scroll down a bit here That means they want to do a student login. So I'm just going to type student login over here Now I'm going to also do an elif. So elif user option equals two that means the user wants to do a teacher based login so teacher login And we need to do elif user option equals three that means they want to do an admin login Now I'm going to print admin login here just so that we can see how this works and then lastly I'm going to print else print No valid Option was selected So let's go ahead and run this main function down here because it's already stored as a function. We've got to run it as well main And then let's run this to see if it actually works Cool. So we have a nice little command line application that says welcome to the college system login as student login as teacher or login as admin Let's press on one and it says student login Now let's press on Let's see. Let's press on two And it says teacher login And let's press on three And it says admin login. So it is recognizing the options we're selecting and we have if I type in some jibberish in here It says no valid option was selected, which means it's working fine. Cool. Let's close this off Now we're only going to be programming the admin login for today because the user should be able to register Students or teacher accounts and be able to delete them. That's what the admin is able to do for the current moment in this tutorial So i'm going to get rid of admin login in here and we're actually going to assign this to a authentication function. So authenticate admin so off admin which stands for authenticate admin So we're going to create a function that's going to grab the username and password from the Person trying to log in and verify whether they are the actual admin to the system or not Now if the username and password is correct, they will open a session for the admin If not, they will just let the admin know that Hey, this user information is wrong and the admin will be redirected back to this menu Cool. So let's go ahead and code that function now. Uh, let's put this above main so def Because we're creating a new function and we're going to call this off admin authorized admin So in my authorized admin what we're going to do first is we're going to print a blank line Once again, as I said, I could do forward slash in but I just can't be asked for today So I'm going to just do that admin login is going to be the title Blins another blank line and then we're going to do a username input. So username equals input string username so pretty so far we're just doing a lot of basic stuff and just um trying to practice the initial stuff And then for the password, we're taking a string input as well of password Cool. So now that we've got the input from the user, we've got to match it. So if username equals admin Now by default, it's not good practice But by default I'm making sure that the admin password is saved and embedded in our program And not on our server because if it was on our server, anyone would be able to change it But if it's embedded in our actual program, no one would be actually able to change it It would just be stuck there unless it's like reverse compiled, which is not the case in most Most places. So anyway, if the username equals admin, we want to authenticate the password. So we say if password equals Let's just say the admin had a stupid password called password Password that means the username and password is correct. So we're going to do admin Okay, let's just do admin session, which is going to be a function we create in a moment else we say print incorrect password over here And if it's the else for the other one, we're going to say print log in Retails not recognized And then that should be it Cool. So now it's going to complain that the admin session function doesn't exist So I'm going to quickly go ahead and create a new function called admin session deaf admin session Now this function is only going to be run if the username and password provided by the admin is correct so print login success Welcome Oops, welcome admin Cool, let's run this up to just test run this whole thing Now we're obviously just log in and as an admin because we only have that function So three now it says admin login and we're being asked for a username So I'm going to type in the username which is admin I'm going to type in the password as password And as you see it says login success welcome admin. So it's running the function That is um admin session Which is fine. That's what we want it to do now Let's log in again, but we'll log in using the wrong credentials So I'm going to have an algebraic username, a jibberish password and it says Log in details are not recognized. So as you see it's working fine Cool. So now let's actually program this admin session so that admin can have a menu once he or she has logged in So we're going to print the menu for the admin. Let's take a look So for the menu what we're going to do is actually create a violent loop here Because the admin should be able to pretty much be able to access this menu on and on again When they've logged in unless they log out. So unless they log out, this menu will be recurring as a loop. So print admin menu And then we're going to let's just copy this off paste it right here And then let's give it some options. So option one. Oh my god option one is going to be register New student Let's copy this Um, I'm going to paste it here Paste and paste and paste cool. So copying and pasting is really great because it saves me a lot of time So I'm going to change the numbers to one two three four five. So there's five options Now it's going to be register new student. Then it's going to be register new teacher Then it's going to be delete existing student existing student Oh, what's wrong here? Okay delete. Let's just make this recap Delete existing student and we will do delete existing teacher So these two options will allow the user admin to either delete An existing student or an existing teacher account information Cool And then lastly we want to log out button so that the user can go back to our other menu Just in case he doesn't want to be stuck in this menu for the whole time Cool So what we need to do first is create a user option variable again So that we grab the user option and put string option Cool And then we got to do our F statements to check what the user is selecting. So let's do if user option Equals one. This means that they want to register a new student. So what we want to do first is print um A blank line, then we're going to print again register a new student And then here we need to grab the username that the admin wants to give the student. So username equals Input string Come on go Input string and then student username Okay, let's do you username So we need to grab the username that the admin wants to give the New student and the password that the admin wants to give the new student so they can log in later password equals input string and student password Now these disinformation that's gathered here from the admin is going to be stored in our database in a second So once they're done, we're going to just save these values username and password into a variable called query values So we're going to type in username in there and password in there So this um variable right here called query values is going to be passed in later into our query So that the query can know what the value of username and password is to be stored in our database Cool. So what we're going to do now is go on and run our command handler Which is the object that's going to help us run pretty much any command or queries On our sql database. So command handler.execute insert into Users which was the table that we created Now we need to mention what fields we're inserting We don't need to insert id because that's auto incremented for inserting username password and privilege Cool, and then since we've mentioned what fields will be entering We need to type in values and then we need to specify what values we're going to be passing through So the first two values which are username and password are going to be passed in as a string format So just um percentage sign and s because we're going to be passing be passing them as the query Values or query vals variable right here. We're going to be passing them using that variable So just leave them as string formaters And then the last value is going to be student So this account privilege is going to be set to student because it's a student account And lastly once the speechmark is done. You want to do a comma and pass in the query values, which is going to be Replacing this s with the actual username the user insert and replacing this second s with the actual password the user insert Now if everything was fine, we're going to do a database.commit in here to save all the changes to database and then print username plus username plus a space Has been registered as a student Cool, let's run this up and see if this actually works. So scroll this up Let's press on three because we want to log in as an admin We're going to type in the admin username, which is admin and the password is password Now we have our admin menu, which is nicely showing up. Obviously, we only have um Register as a register new student option showing up only that at the moment. So let's press one So as we programmed it to is asking us for a student username Let's just call him mark And student password is going to be mark one two three Cool, let's press enter and then it took a second and it says mark has been registered as a student And then we have our admin menu again because it's looping through and it will keep doing that until we Click on logout, which is going to be programmed later Let's actually verify if this worked by refreshing our admin phpmyadmin page going to our user's table And voila, it has worked. We have an idea of one username mark password mark one two three And privilege has been set to student because it's a student account Now we're going to be doing um pretty much the same for the teacher account So we can actually copy and paste which is the wonderful bit about this So the option one was creating a student account now option two is creating a teacher account For which what i'm going to do is type in an elif yes, so elif user option equals two What we're going to be doing is literally copying and pasting this whole lot Because it's literally the same thing the only thing that will change is the account privilege And then these little words right here register new teacher instead and then student username will change to teacher username and teacher password Then the the rest remains the same I believe and the only bit that will change in here Is where you run the query using command handler you would change um the student What did I do there you change the student to teacher now because this is a teacher account And that should be stored like that in the database and then lastly username has been registered as a teacher not as a student Let's do that run this and see if that works Okay, go up. Let's log in as admin using option three admin and password let's register a teacher teacher username is going to be Matthew and it's going to be map one two three And as you see Matthew has been registered as a teacher now. Let's go ahead and open up our My admin again and refresh this go ahead and see our users table And as you see the it worked it says id to Matthew and map one two three And the difference is that this account has been privileged to a teacher account So we know how to make the distinguish between them using the privilege tab Cool, so that's working flawlessly. Let's go ahead and program the delete functions as well And then the logout button. So elif Let's go ahead and do it over here elif user option equals three That would be for deleting an existing account, which is for a student So let's go ahead and see how we're going to be doing that So first off we're going to print A blank like like we always did then we're going to print delete Existing student account And then what we're going to be doing next is actually gathering the username So that we can delete that actual user from our database. So username equals input string Student username So we need the student's username so that we can look up the student in our users table And then delete that specific user mentioned in here From the table so that the account has been deleted forever And this student will no longer be able to pretty much log into that account Cool. So once that's done, we want to run in our query values very broken username Is going to be stored in there as well as we're going to be storing student as the second parenthesis the second parenthesis is stored as student because The account privilege we're going to be looking for is a student account So we don't want to delete any teacher that has the same username We only want to delete any student that has the same username Cool. So once our query very values very boy is done. We're going to do command handler.execute and then let's write our query The query is going to be delete from our users table where where username Of course Now we're going to use our string formatter percentage s because we're going to be providing the values in a moment and privilege equals percentage s because we're going to be providing that as a value as well Cool. Now I'm going to go ahead and put a comma in here and pass in the query values Now once that's done. I'm going to print. I'm going to do a database.commit to save all the changes of the deletion That's done. Now. We need a bit of validation here. So if command handler.row calves Which means So what this does is it tells us how many rows were affected So if no rows were affected, it will return zero or anything less than zero So if no rows were affected, that means there was no user with the username provided So that means there was no records that were deleted and no user with that username existed So that's what we want to find out whether the user with that username actually existed or not So if the user did exist So if the handler.row count is less than one, that means it didn't exist. We're just going to print out user not count Because that user does not exist. That's why no rows were affected Else we're going to print out we're going to print out username plus and then has been deleted Cool. Now let's quickly run this to see if it worked. It should have worked Now we're going to be deleting a student account. So let's log in as admin admin password option number three student username, let me see what student I had saved up. It was mark. Okay, so let's type in mark And it says mark has been deleted. Let's go ahead and check if he has actually been deleted or not refresh And if you see right here, we only have mafiu, which was a teacher account. So Mark, which was a student account has been deleted through our program Now let's try and see if our validation is working. So let's go through our admin menu again click on three And then provide a student name that doesn't exist some gibberish And it says user not found so it is working. Perfect Now as you may have already guessed we can copy and paste this function for our teacher function too Because there's only going to be one value that changes, which is the account privilege So I'm going to copy this Then we're going to go ahead and do the elif statement. So Elif user option equals four, I believe. Yep four Now we paste all of this down here. Then we change these little words to from student to teacher So it's pretty simple what we're doing right here. We're just playing with queries. It's just a little practice Kind of tutorial for you guys to like brush up your skills with this Now you're going to change the query values to username and teacher because it's a teacher account privilege needs to be equal to teacher Um where username equals that and privilege equals that which is fine User not found has been deleted. So cool. That should have worked. Let's go ahead and see if this works So I'm going to log in as admin again. Let's just Put this all the way up log in as admin username is going to be admin password Uh, let's go on option number four because we want to do a teacher and let's Okay Existing teacher account, but it's saying student teacher. Okay made a mistake there, but I'll fix that in a moment So I'm going to type in gibberish and it says user not found. Let's press on option four again Let's actually type in Matthew And it says Matthew has been deleted. Let's verify this Refresh and as you see the table is actually empty my skill return an empty result zero rows So that's working perfectly Let's just gonna I'm just going to have to change teacher student to I mean Teacher username. So we're going to go and change that right now. It's a teacher username Cool. So that's that sorted and now the last and last but not the least function for this tutorial right here For the admin panel to be finished is the logout button, which is the easiest one So we're going to do an elif in here, which is going to be for option five. So Elif Elif user option equals five. What we want to do is just break So what happens here is since we're already in a loop in our if I go to my main function We're already in a loop and when we run the other function, which is admin session We're in a second loop. So when I break from my second loop, I am returned back to my first loop So we'll be returned to this main program right here as soon as we break out So that will be like a little log on log out thing going on So that should work just fine. And then lastly, I'm going to do an else just for validation So if the user types in any gibberish, you would just say print no valid No valid option selected. Cool. Let's run this up to see if this worked And I'm going to log in as admin because that's the only feature working currently admin and password And we're going to run the last option, which is log out five And as you see right here, we have logged out from our admin Because it no longer says admin menu. It only says welcome to the college system Which is the main menu for the system And if I want to log in as admin again I'd have to press three and then type in the username and password again as we've already logged out So that was it for today's tutorial guys. Hope you have enjoyed this amazing tutorial and brushed up your skills on my SQL If you guys have any future ideas for videos drop them in the community tab under my post ideas for new videos And if you guys would like to support the channel directly You can do so by signing up as a patron using the patreon link in the description Do consider joining the discord channel for a bit of fun and programming ideas Also follow up my socials Thank you guys once again for all the support you've been showing. I am very grateful for it If you guys would like to see my channel grow I would really appreciate you for if you could share it and ask your friends and family to subscribe And guys, I will see your beautiful faces in the next tutorial Which is part two for this series until we finish the college system. Peace