Grocery Store Application - 3. Products Backend | Python project tutorial

Key Takeaways

now we have three layers in our application ui back-end and front-end but if you think about modules we have two major models products and orders now when the project is managed in a corporate setting they often do vertical slice development what i mean by that is they take products as a one feature so you can see this vertical slice they develop that feature fully and then release it then they pick up orders and they develop the whole vertical slice and then they release it the other approach would be you do database development for all the modules then you do back-end development for all the modules and then you do ui development for all the models the disadvantage of that approach is that if you keep your ui towards the end then it will take long time before your customer sees anything but in this case you can just build products module give it to your grocery store owner ask him to start using it so you get feedback or layer now for software project development companies use a process called scrum scrum allows you to break down and prioritize your task and it is a very very popular technique that is being used in majority of the software companies and the tool that allows you to do scrum is called jira jira is a very popular tool from atlasian that is being used in majority of the companies who are doing a scrum based project management now uh for if you want to know more about scrum then there is a there is this website called scrum training series dot com it has little videos 10 minutes 15 minutes videos these videos are awesome so you should see these videos these videos will be very helpful when you join any company as a software developer as a data scientist even in non-tech companies they use chrome for doing a project development okay now what we have we have done here is i created an empty folder called grocery app where i have created two sub folders backend and ui and these folders are empty right now this is empty i don't know why the arrow is showing up back-end will contain our python flash code ui will contain our html code so the first thing i'm going to do is i'm building a products page okay so our goal is to build this product page where you can do add new product you can list all the products so i'm going to write a file called products dao what the heck is dao it is data access object it's a common terminology that is used in the industry and let me go to my meditation my zen mode all right now i will uh i just googled it and find out uh found this uh driver for mysql so driver is basically a python module that allows you to connect to a database so what we're going to do is see in this query if you write this query it returns all the products so in my table i have inserted all these products you can just you know you can just double click here type in products click on apply it will insert the record i'm going to execute same query from python and retrieve all these products in my python code and i need this mysql connector module and to install it you can see by the way i have a github page so let me just show you so i have a github page for the grocery store app and on that i'll give an installation instruction so peep install mysql connector python that will install that module for you now first thing is how do you connect to a database so let's just copy paste this code so what we're doing is we're connecting to our database and username and password is root root what is the database name well it's grocery underscore store see whatever you have here you should type that here grocery when i run it nothing happens which means the database connection worked okay i want to now query the products okay so for that again i go back to documentation there is a code snippet for querying products so for querying products the first thing you need is cursor okay so cursor allows you to execute the query so you can say cursor.execute and query all right what is my query query is something what the heck is my query well i can copy this okay and once query is executed the results are within a cursor and you can i trade through those results so you can say for a tuple some tuple in cursor uh do something okay what is this tuple well this tuple is exactly your column name so column names are uh product id name uh let's see yeah product id name um id price per unit so i am going to do some copy paste you need to know copy paste skill in order to become a better or an effective uh software developer okay when you execute this not enough values to unpack okay i don't have a unit name actually see i got all these values i got these values but my um is still id see i don't have like a kg or each so i need that and for that you need to do a join so you need to do a join on a uom id so our um table knows see um table knows one is kg so i want to do that here so what we have to do is i will um instead of just saying star i will explicitly say what are my columns that i want so i want products of product id product or display whatever right and i'm doing inner join so from this uh and then inner join inner join uom so you are inner joining products with um table on on means which column you are in doing in a join well products dot uom id is equal to um dot um id if you do ctrl enter it will execute the query but i still don't see um because i have to specify that in select so i will say dot um name because um name is in uom table okay so i'm saying um table dot u m name okay and when i do ctrl enter see i got this perfect so all i need to do is now copy this query to my python code and so my query will be this and uh here of course in the end you have to say uom name um name so now i get ur name kg each whatever but my code is not modular i want to make it modular you know so i want to have if name is equal to mean then call some function called get all products which just retrieves me all the products okay so how about i make that one and get all products and so by the way you need to store all this in a array and return it so i will create a list and insert uh dictionary objects okay so copy paste copy paste is the mantra of my success okay and you of course need to return and right click run see my products are coming here you see all the products every product is a dictionary object so they are coming up along with uom name very good now i want to make my code even more modular see i have this fascination for modularity so i want to move my sql connection code into a separate file you know such that connection is passed as and a variable into this function okay so and connection will be passed here so this connection should be something what is that something well i will just create a file called sql connection and in this i will just say get sql connection that will be my function and i return it now if i call this function multiple times it is going to create multiple connections for that reason i will just store this in a global variable and in python if there is a global variable use underscore underscore convention and so what i'm saying is now if this is none then only create it you see if this is none then only create that variable return it so now oh you have to specify this person here and get a sql so from sql connection import okay again copy paste so this is returning your sql connection so i'll just say get sql connection here right click run works like a charm so now our code is modular it is creating sql connection in a different file and whenever you use it we need it you just pass it in a function argument okay so my all products is done so when i have this web page i will call that function on this webpage and it will list all my products how about add new product well you need to write a function to add new product okay i can write it so let's see okay so right now i have this many products okay if i want to insert a new product all i do is say insert into products okay values so here you specify column name and so column name is this and here you specify values so what are the values so say by the way i'm not specifying product id because it's an auto increment column so it's gonna increment on its own so i want to insert a new product uh let's say spinach and spinach is like a bunch so it's each so um id will be one and spinach less is 34 rupees 34.5 by the way you have to put single quote around all these values otherwise it will scream and when you control enter execute it see it inserted a product see here you see one row affected green green so when you click here i see a product so i have this query all i need to do is put this query into my python code okay so i'm gonna design a new function called this okay and in this function what is my query well my query is this so this is how you specify your value so it's called parameterized query where when you do see normally you do cursor. execute and pass query right but you need to also pass the data and data will be uh these three parameters so i learned all of this from here only saying querying the data and here's the percentage as percentage s so that's what we have here so let's say let's let's think about it like what do we want to pass as a product here well it will be a dictionary okay and the dictionary is let's say this okay cabbage i'm putting cabbage yeah uh so it's a dictionary so this product will be dictionary and here in the values you need to specify name um id price per unit so you will say product name here or rather product name um id price per unit and once it is executing uh you need to do connection dot commit and then you can return cursor dot last row id so it will return you the row id of the last row that has been inserted so if you run it so it's returning me 13 which means it inserted this row fine okay what was that okay okay cabbage see i don't have a cabbage i have only 12 rows now when i execute this query again i got my cabbage yeah hooray it's working hard to believe it's working otherwise that code first time usually doesn't work okay now let's write our last function which is a delete row and it is so simple i'm going to just copy paste so delete query is delete from products where product id is equal to whatever okay so we will delete uh cabbage so i said delete product row id for cabbage rather product id for cabbage is 13 see 13 is the product id that's what i'm specifying so product id will go here it will come here and the query will be delete from products where product id is equal to one three all right and now execute again see cabbage is gone so my delete function is also working whenever you have a question on sql query just ask google okay so you say okay sql delete query most of the time w3schools.com website will pop up they will have nice simple example with the sample query you can just copy paste it's so easy to learn this sql i hope you like this session in this session we wrote python back-end code for products in the next session we are going to write the ui code for product and will connect it with this backend code so that our product page will be fully functional i hope you're liking these videos so far if you do give it a thumbs up and share it with your friends thanks for watching