Python MySQL Integration Tutorial: Build a Task Manager Application

[Music] hello everyone today we'll be building a tax manager application using flax flax is a python framework that could be used for web development or web application in general so we're going to be using flax skl Alchemy and mycale in this application users will be able to add View and delete TX we will also explore how to set up mySQL database and connect it to our flax application first let's set up our development environment ensure you have python installed on your computer if you don't have python installed you can simply open your web browser and then search for Python and then I'm going to click on the first link and I will hover on the download tab and then click on python to begin the installation so I'm I'm just going to start the download I'm going to start the download and the download will begin so I'm going to click on open folder and right here I'm going to double click to install python so you can see python is already installed and I'm asked to upgrade now if you don't have python installed and is your first time installing python make sure you select add python to part so you're going to see a box you're going to check that box before you hit on the install so right now I'm not going to do any upgrade I'm just going to go straight to the application development so I'm just going to cancel this next you want to make sure that you also have MySQL installed on your system now I have a workbench installed on my computer so I'm just going to open it so it's right here on my desktop now there are other tools you can use to install my scale so workbench is one of of them so you can use any tool of your choice so now I already have a local instance so I'm just going to click to start a connection and I'm going to enter my password and hit okay and right here I have my connection established with a graphical user interface so I'm just going to minimize this and I will come back to it so we have the necessary software installed which is my Square workbench which equally installs a my Square server and then we have python installed so right now I'm going to create a directory from my application on my desktop so I'm going to right click then new folder so I'm going to call this tax uncore application just like this so taxor application that's my working directory so I'm going to right click on this and then open in Windows terminal so I'll open this folder or our working directory in Windows terminal so here we have it next I'm going to create a virtual environment so I'm going to say python hyphen M then virtual environment V EnV oops once you install python python comes with a virtual environment and then I want to call my folder which is going to house my virtual environment my en EnV so I'm going to call it my EnV so I'm going to hit enter and then we have to wait until the command executes and here we go it has created it so now we are going to activate the virtual environment in case you don't know what virtual environment is so what I'm going to do is I'm going to right click on our folder and I'm going to open this with or I can simply open my favorite text editor or code editor so my I'm using visual studio code so then I'm just going to drag this to the visual studio code and then I have my folder here so you can see the my virtual environment that was created and stored in my EMV okay so I'm going to minimize that and then continue executing other command so the next thing is I'm going to activate my virtual environment so to activate your virtual environment you have to call your folder my e EnV which is the folder I installed my virtual environment then I'm going to say script so make sure the first X is capu letterer uppercase script and then I'm going to say activate then hit enter and here we go our virtual environment is activated so next we are going to install some packages so we are going to use peip to install fla so I'm going to say peip install and the first package you are going to install is flax like I said earlier flax is a python framework for web application so flax and then I also want to install flax SQ alemy flax SQL Alchemy and then P my SQL and then hit enter and this will begin installing flags on our virtual environment so we have successfully installed flax and SQL Alchemy SQL Alchemy is an object relational mapper is actually a tool that we can use to interact with our my scale and then we also have we also installed Pi MySQL package to connect to our mySQL database so now I'm going to minimize this and then go back to my code editor now we are going to set up our project structure we're going to create a directory and some files so I'm going to right click and then create first of all let me create a directory so we can right click new folder and this folder I'm going to call it template and with this template folder I'm going to create two files so the first one is going to be Bas do HTML and then I'm going to create the next one and this is going to be index. HTML all right next I'm going to create another folder and I'm going to call this static now these folders are required in flax so the template folders are where your front end codes are stored and then we have the static for for your Styles your assets and all of that so on the start static I'm going to create a new file so let me create a folder first I'm going to call that folder CSS right and then within the folder CSS I'm going to create a new file I'm going to call that file style. CSS all right so we have um static and within the static we have this so if we go to the folder itself so you can see static and we have CSS and inside CSS we have style. CSS all right then next we are going to create some files on the root directory so here is a root directory so we are not creating any file inside a folder so on the root directory so I'm going to call this file app.py so this is going to be our main file and I'm going to create another one I'm going to call this config config.py and this is going to store our configuration codes and this is going to be model models.py just like this all right now we're going to start with the configuration our database configuration files so the configuration file will allow us to set up our database connection details kindly note that the purpose of this lecture is not to teach you python is to demonstrate how to interact with my scale using python so if this is your first time writing a python code I encourage you to check out my other courses on introdu lessons on python so I'm going to provide the link in this session of this video so you could take the courses but I'll do my best to make this as simple as possible so on this session of this video I have provided a link to all the code that will be needing for this application so first you can see start config.py and then we have config.py so I'm just going to copy all of this CR C and I'm going to paste that in the config so if I paste that and here we go so here on this code we have imported OS which is operating system is actually a module in Python and then we created a class called config and the class config is holding the configuration settings of our flag application so here we have SQL aremy database URL which is an attribute and this is going to connect to our my scale so right here we are going to create a database so I'm going to remove all this I'm I'm going to create a database and then come back to fill the name of the database right here so let's go to our workbench and on our workbench I'm going to create a database so I'm going to say create database and I'm going to call this database tax uncore DB and I'm going to execute this so if I refresh so you can see we have tax DB so that is all we can do for now then we have to go back to our configuration settings and make sure that we rename the database as taxor DB and then here you're going to specify the password of your database so my password is this and then the user is root so if you have specified a different thing for your user when you're setting up your my Square workbench you have to specify the user right here so my user name is root and then this is my password the host is Local Host and then the database name is tax DB once you have this set so I'm going to save that file so the next thing is model now you may be asking why do we need a model the model defines the structure of our database table and relationship between them so I'm going to go over to the code and here I have the model so I'm going to copy everything on the model CR C and then I'll go back and then I'm going to paste that right here now let's go through the model.py now first we imported the FLA SQL Alchemy and we have the class so this is like the class imported from the flax SQL Alchemy package so this is the class so if I Hoover my mouse on it so you can see is a class so like I said earlier scale Alchemy is an o that means object relational mapper that allows you to interact with the database in an objectoriented way so so on the next line we created an instance of our SQL Alchemy and we assign that to a variable DB so the essence of this is that the instance is going to be used to manage our database so here you can see db. Model D db. colon and all of that so we defined a class called tax and the tax is going to inherit from the db. model so our new class that we defined is going to inherit from the db. model Now take notes that the class tax represents the table in the database where each instance of the class corresponds to a rule in the table so on the first one we defined a column named ID and we also have the next column named title description and then created at so we have four colons that will be created when we run our application it's not yet created so even if we go back to our workbench so you can see tax DB if we go to tables you can see we have no table right here so when the application runs it's going to create all of these and then we Define a data type for each of the columns so you can see we have the integer strange and then the the number of uh acceptable characters strange and date time and then we Define this as a primary key so which is going to be unique and these are nullable nullable and all of that so if you have done this so you can hit on save or crl S on your keyboard so the next thing we are going to do is to get our base. HTML so I'm going to copy everything on the Bas HTML then I will go to my template then on the Bas HTML which is going to be our front end I'm going to paste that code so these are just HTML codes and then most importantly on the body we have a bootstrap class now basic Ally we're using bootstrap and not a regular HTML bootstrap is a front end framework for HTML CSS and JavaScript so it makes things easy and and responsive so most importantly on the body we have a container that holds what we call Ginger 2 template engine so fla uses Ginger 2 template engine for rendering python on HTML pages so we have a block content which defines a starting point and then we have a end blog who defines an end point so for our good we can save and then let's go to the next file the next is index.html so I'm going to copy everything on the index okay here we go so contrl C and on the index I'm going to paste this now on the index we have extend base. HTML and then we have the block content which defines the starting point and then we have the end block now what it means is that we have the base of HTML that means everything inside the index. HTML goes in here so that is why we have the block content and the end block so everything goes in between so the index. HTML can be seen right here in between the starting and the ending so you have to extend it and then you start the blog content and then you end the blog content so if you are not yet comfortable with python or want to strengthen your skills I highly recommend enrolling in one of my python courses this will provide you with the necessary background to make the most out of this course next we are going to do some styling so we have our style. CSS I'm just going to copy everything contrl C and then in the static I'm just going to save this so we have the body which defines everything on the body so it's going to we have a background color of this we have H1 the BTN primary and the BTN danger so I'm going to save this and then finally we have our main file so the first thing we are going to do is we are going to first so I'm going to copy everything from here so I can explain it bit by bit and then I'm going to paste so the first thing we did was to import the necessary modules from the flax package so we say from flax import flax so flax is the main class for creating our flax application and then we have the render template the render template is used to render HTML templates so for this templates to be rendered we need this module or package called render templates then we also have request request is used to handle incoming request data and then we have redirect redirect is used to redirect users to a different URL and equally used for URL route so we have URL for for URL routes so if we go to the base HTML sorry the index. HTML so we can see URL for so the URL for is used to generate URLs for routes next we imported our config file so remember we have defined a configuration file so we need to import it so from config import config and then from Models import DB and tax so from the models we have the model and we have to import the DB and tax class so next we created an instance of the flax application and then we assigned it to a variable called app and then this allows us to determine the root part of our application so next this line loads the configuration settings from the config class into our fla application so like I said we have the app.py as the main flx application so the line loads the configuration settings for us into the app and then we initializes the SQL Alchemy database so next we create a database so we just printed um print creating tables so if this runs successfully we're going to have this being printed in our command line or command prompt so we're going to have it printed right here just to show that everything is working correctly and then once this run creates all table for us and it's going to say table created so next we have the route now the route defines the root URL so this is like the first page or the index now in order to explain this I'm just going to copy this we have the last if uncore name so I'm going to copy this and then have it pasted right here now this line or blog checks if the script is being run directly as opposed to importing you know we imported modules but we want to make sure or check that the script is running directly so if it is the flax application is started and the bugging is enabled so you can see the bug is through so if we are running straight from this then the BG equal to true so if I save this file so I'm going to run this file which is going to create our table and equally demonstrate what this route means so now I will go back to my command line and then I'm going to say python space app.py so our main app is app.py if I hit enter and here we go so you can see creating tables so that means our table was created successfully table created and our flax app is running so let's find out if our table is created so let's go to so let's refresh first then tax the BB then tables and here we go so you can see our table has been created with ID title description and created ad we we can say use use taxor DB and then I want to select select star select star from tax so tax is our table name just like this so the star means all so you're going to select every record in the star so if I run this and here we go so you can see basically we have no record inserted so our application is working correctly and then here we have been given a URL or a link to access or to view our application on the browser so you can see the bug mode is on and this is a development server so once you are ready to push or deploy this online you have to change this to false you have to change this to false so when you are developing it has to be true so we can debug our application or code so right here I'm going to click or just simply copy or I'll hold control my keyboard or command if you're using Mac then I'm just going to click and this will open in one of the browsers and here we go oh build error could not build URL for endpoint add TXS did you me static instead all right so what we are going to do is we need some of this code for everything to work correctly so what endpoint means is that right here the want 127 7 0.0.1 5000 is our default route now this is the same thing as what we are seeing right here now let's say I have something like about so that means for this code to be accessed I need to I need to navigate to that route about and hit enter so but this is not going to work because we need the ad stxs end point so so let's go back to our code and then I'm going to copy this and then the add tax end point contrl C and go back to our code just below this I'm going to add this so now we have I'm going to modify this so this is how it is so we have the first route then we have another route which is going to add records to the database and then we have another route for deleting now like I said if this is your first time going through a python code or programming with python I encourage you to check out the link in the course material or in the description box below so you could take an introductory lessons on python now if we are good then we can save our file and let's go back to application and refresh and here we go so we have successfully created our app so let's add some TX so let's say uh complete the card de so I just want to write a TX and then uh description I'm going to complete the tax deal today so this is just for demonstration purposes and then I'm going to click on ADD tax so once you click on ADD tax you are basically assessing the URL ad STX you are assessing the URL ad tax so it's going to request the title which is going to come from right here and then is going to also request the description from right here and then it's going to assign that to a variable so it's going to get the title and description assign that to a variable and this will be added to our database and once it's added to our database the next thing is going to redirect to our index so that is why when a tax is being add added automatically it goes back to the index so if I add another one complete the integration I have completed the in gration completed integration so add tax so if we look at it so that's added and we can equally delete our tax so this is a logic be the deletion it's going to take uh the ID every item right here has an ID so you can see delete then we have an id2 and then this is ID one so delete slash what the ID and the ID is actually coming from the database so from the database we've just added two tags so let's go to our workbench and uh we are going to rerun this and here we go so we can see the title we see the description and then we can see the Tim stamp so you can see the ID I talked about the ID one and ID 2 so if I click on this is accessing the ID to so I can delete and it's gone so if I go go back to our database refresh and you can see it's gone so in this session we have successfully built a simple tax manager application using flax SQL Alchemy and my scale we've covered setting up the environment defining database models creating routes and templates and managing our database with MyCare workbench now I want to believe that this foundational knowledge will help you build more complex application in future if you haven't subscribed to this Channel please do well to subscribe and turn on the notification Bell so you never miss a video thank you for joining me and happy [Music] coding for

Channel: NextTechGem

Views: 87

Keywords: Mysql workbench, Python flask mysql, Python mysql, learn sql, learn to code, mysql, mysql database with python, mysql python connector, python, python database connection, python database connection mysql, python flask mysql example, python for everybody, python full course, python programming language, python projects examples, python tutorial, python tutorial for beginners, python tutorials, sql, sql tutorial

Id: GSGHIT6WLvE

Length: 32min 42sec (1962 seconds)

Published: Sun Jul 14 2024