Python Tutorial - How to create a college management system using python and MySQL - for beginners

[Music] right so hey guys and welcome back to yet 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 off what we want to do is make sure that you have um xampp installed so open browser and type in download so click on the first link and then it should take you to my um apache friends website so you want to click on the um most recent version of php and then download the version of sam 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 windows search bar and type in zam and then it will come up with a pop-up saying exam 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 taskbar 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 python 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 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 the up now in our college database we can have a couple of tables okay i'm opening the wrong 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 us 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 web um our college management system is going to allow an account for teachers and 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 tick that box to make sure this is automatically incremented so what this means is that it automatically increments the id by plus one each time a new record is added cool so i'm gonna go back that's my id done don't change anything else in there now in my users database i also wanna have a username so that we can refer to the user i'm gonna saving i'm gonna be saving that as a walker or a um 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 users 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 where 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 allow the admin to log in allowing the you 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 backend ish 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 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 ysql.connector which is the class that lets us connect to our mysql database 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 a mysql object that we just initialized 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 localhost 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 you go back to my phpmyadmin phpmyadmin 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 of database dot cursor so this cursor is pretty much going to allow us to run different queries such as delete um 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 101 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 and we're going to print a blank line i know i can do forward slash n but it's just easier for me to use speech marks for now i'm going to say option 1 is going to be login as a student i'm going to copy this line and paste it down here i'm gonna change the option one to option two and i'm gonna say login as teacher and then lastly we're gonna print that line again but the option is going to change to it and we're gonna 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 too or if they log out of an account so now we're gonna have to take the input so user option equals input string option or let's just 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 gonna 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 e-lift so e-lift user option equals 2 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 gonna print admin login here just so that we can see how this works and then lastly i'm going to print else print now 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 log in a 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 generation here it says no valid option was selected which means it's working fine cool let's close this off now insert 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 it'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 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 n 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 link 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 gotta match it so if username equals admin now by default this is 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 um 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 gonna 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 if it's the else for the other one we're gonna say print uh login details 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 def 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 logging in 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 gonna type in the username which is admin i'm gonna 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 gonna type in the gibberish username gibberish password and it says login 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 one 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 and let's copy this and then 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 a new student then it's going to be register new teacher then it's going to be delete existing student existing student oops oh what's on here okay delete let's just make this recaps 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 a logout 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 input string option cool and then we gotta do our if 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 new student and then here we need to grab the username that the admin wants to give the students so username equals input string command caller 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 this information that's getting here from the admin is going to be stored in our database in a second so once they're done we're going to 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 also incremented by 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 formatter so just percentage sign and s because we're going to be passing be passing them as the query values or query valves variable right here we're going to be passing them using that variable so just leave them as string formatters 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 speech mark 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 the 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 let's scroll this up um let's press on three because we want to log in as an admin i'm 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 123 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 state table and voila it has worked we have an id 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 gonna 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 e-lift here 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 the student oops 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 map123 and as you see matthew has been registered as a teacher now let's go ahead and open up my admin again and refresh this go ahead and see our users table and as you see the it worked it says id2 matthew and map123 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 gonna be doing that so first off we're gonna print a blank line like we always did then we're going to print delete um 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 it the account has been deleted for 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 variable again 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 um 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 values variable 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 oops where user name force 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 um 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 dot row counts which means um so what this does is it tells us how many rows were affected so if no rows were affected it will return 0 or anything less than 0. 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.rowcount is less than one that means it didn't exist we're just going to print out [Music] user not found 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 uh username plus and then has been deleted cool now let's quickly run this to see if it worked it should have worked um now we're going to be deleting a student account so let's log in as admin admin password um 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 matthew 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 from 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 e-lift 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 um 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 where username equals that and privilege equals that which is fine um 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 login this admin uh username is going to be admin password uh let's go on option number four because we want to do a teacher uh and let's okay completely 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 gonna 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 mysql returned an empty result zero rows so that's working perfectly let's just gonna i'm just gonna have to change teacher student to i mean teacher username so we're going to go and change that right now so 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 oops elif use a option equals five all we wanna do is just break so what happens here is since we're already in a loop and 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 logo logo 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 uh admin because that's the only feature working currently admin and password and we're gonna run the last option which is log out five and as you see right here we have logged out from our admin um because it no longer says admin menu it only says welcome to the college system which is the main menu for the system now if i want to log in as admin again i'd have to press 3 and then type in the username and password again because 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 mysql 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 patreon 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 um 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 2 for this series until we finish the college system peace

Channel: johan godinho

Views: 75,723

Keywords: Python Tutorial - How to create a college management system using python and MySQL - for beginners, create a college management system using python, python fun projects, python projects for beginners, how to use mysql and python, how to connect python to mysql, learn mysql, how to add records to mysql table, how to delete records from mysql table, mysql insert, mysql delete, johan godinho, findjesusgodinho.com, college system project in python, python college management system, joha

Id: sFzj6ZfUxus

Length: 31min 38sec (1898 seconds)

Published: Thu Aug 20 2020