Lookup All Customers CRM - Python Tkinter GUI Tutorial #31

what's going on guys John elder here from Konami comm and in this video we're gonna query our database for our CRM tool with kinter and Python all right so we've been building out this CRM tool for our customer database in this video we're going to query the database and put the results up on the screen in the actual graphical user interface itself not on the command line like we've been doing but before we get started if you like this video I want to see more like it be sure to smash the like button below subscribe to the channel and check out cody me comm where i've dozens of courses with hundreds of videos that teach you to code use coupon code youtube to get twenty two hours off membership that's all my courses videos and books for a one-time fee adjust twenty seven dollars which is insanely cheap okay so this is what we got so far I went ahead and put another record in it just made up some data and click Add to customer to database and so now when we exit this out we look at our terminal we can see it prints onto the screen these two records this is the the one we had earlier and this is the new one that I put in just now off camera so this is not great we don't want this graphical or our terminal we don't want this terminal thing showing up we want this information to be in our actual program to show graphically on the screen so we're gonna start to work on undoing that in this video so let's see let's pull up our code up until now and let's come down here to the button section and let's create a new button and where's our buttons all the way down here create great buttons so let's say list customers button I don't know and let's go list underscore customers underscore button and that's going to equal a button and this we want to point this or we want this to be in our route we want the text to be list customers I don't know right now we're gonna have a command for this in just a bit but let's just go ahead and actually create this button first so let's go list underscore our customers underscore button dot red and we want this to be and let's see your row looks like a row fifth 14 is where we're at now so let's go ahead and go a row equals 15 and we want this to be column equals zero and let's see what else do we want we'd probably want to sticky this to the west side and let's give this a pad X of 10 so it lines up with our other buttons let's go ahead and save this and just run it real quick to make sure this is looking good okay so this customer we'd click it nothing happens but we've got the button so okay so now I think when we click this button we want a whole other window to pop up we don't want this is getting kind of crowded in here let's just build another window so that's what we'll do now so let's pull up our code and let's give this a command let's go command equal what we want to call this list underscore customers okay so now we need to actually create this function this list customers function so let's go ahead and do that and I'm going to come up to the top here I usually like to put my functions you know towards the top of program so where we at let's do it right here let's go list customers [Music] yeah that'll work so we want to define less customers okay so now the first thing we want to do is create a new window right and we've done this in the past a couple months ago when we showed you how to you know create other windows and to do that let's go a list underscore customer query I guess we're gonna query this the database right so this is gonna be capital T underscore K we want to create a new TK sort of instance right so now we can go list customer query dot title and let's call this list all customers and you'll notice I'm just pulling these things all of this stuff we're about to do from up here we're just sort of redoing this stuff in fact we can we can just copy both of these bring them down here and paste them in instead of root of course we want this to be list customer query same thing here list customer query and 400 by 600 we need this to be bigger because we're putting all our data on the screen so let's call this 800 of sneek with a shoe marks 800 by 600 okay so that should work so let's just save this and run it real quick to make sure that that worked so let's see if we click the button this other window pops up and it's pretty good size and it says list customers at the top and it has our little icon here okay so far so good we need to query the database essentially just do this stuff right here all right so we've already got code for that we can just come down to the bottom of our program and just right here this this my cursor executes stuff that we did probably in the last video or the video before that this is where we're getting this stuff right here right so let's just look at this real quick what we're doing here is we're creating a cursor and we're executing a sequel command and the command is select everything from our customers table and then we want to slap that into a variable called result and to do that we just go my cursor fetch all we're fetching all of the stuff out of our database right and then we just want to loop through all that stuff and print it all onto the screen so for X and results print X so I'm just going to copy all of this and we can get rid of that there and I just come back up to our new little function we just built and create a comment that says query a database let's just paste all this in tab okay now obviously we can't just print X we need to create a label right so let's call this I don't know look up their score label and that's a label and where do we want this well normally we put it in rude but we're not working with route right now we're working with the list customer query window right so that's where we want this and we want the text to equal X now we need to put this on the screen so instead of print X we can just look up label let's just pack this onto the screen for now just to see what we got okay so let's save this and run it and see indentation error let's see what did I do there we go yeah all right so that's right now all right so let's try it again run it so here's our main window click this boom it this pops up and here we have our two records so John elder and you'll notice how this is laid out John elder zipcode price this is the ID this is the email address why is it in this order it's kind of a weird order well if we look at our original or created the database that's the order we did so we started with first name last name zip code price paid user ID so it's just using that order right now it's kind of a weird order we should have probably put it in a better order but it doesn't really matter we can spit this stuff out on the screen in any way we want this is just the default it's just putting in the default order that your database is built as right so we'll look at that in just a minute but just kind of look at this here and this is kind of weird this looks like a Python dictionary I'm not sure why there's squiggly brackets on some things and some things don't we'll see how to get rid of those in just a second this is just a very generic way to throw stuff up on the screen and this is obviously not how we want this to and you know this is not the final output we want here this is just a very quick way to show you how to spit the stuff out onto the onto the screen so okay we're starting to see some stuff but it's not that interesting so let's close this now let's play around with this a little bit let's head back down here where'd we go so we're just printing out X and each row in our GUI here is just a row in our database but you can access each individual fields in our database just by using a Python lists and lists start at 0 so the 0th item in our database is the first name so if we save this and run it it's kind of interesting and how we just get John and Tim right so you can go through here and do however you know lay this out any way you want and by that I mean you know you can come here and you can go text and then you can concatenate a field here and then you can go x1 and then Khan can't what we need to come Canton eight concatenate again first name last name and then you could just come through here and for each one of these fields you know so this is the zeroth field first second third fourth so if we want the email that would be zero one two three four five it looks like the fifth one so let's put that into first name last name and email plus a space plus X where we say five all right so let's save this and run see what this looks like this up with button and we get John elder and then the thing now obviously you could create a label on top of this for like headers you could put you know first name email whatever whatever you wanted to put on the screen you could do it that way so that's one way you could do it let's do another way real quick we're using pack here let's do some grid stuff cuz this can get a little bit interesting so instead of let's get rid of all this just go back to spinning at all out onto the screen and instead of packing let's go grid and we want row to be something and column to be something but what do we want it to be well let's change our for loop here a little bit so instead of just looping through our results let's get an index number for each item we pull out so let's go index so we want to pull out index and X from results but we don't want to pull it from results we want to pull it from a numerate result what this will do is it will create a number enumerate it and put that number in the index so the first item it pulls out will be the 0th item and the first item economic item or maybe it will pull out one first I'm not sure it doesn't matter it will number them right so inside of here then we can use this number so we can pull out you know we want to put it in row index and column what do we want we have to actually create a column so let's create a number let's just call it num set it equal to zero and here let's just go numb and then inside of here we want to go num plus equals one so every time it loops through here it will add one to num and then the next time the column number will be one bigger so we'll put each item over well okay we're getting there now what this will do though is this is going to put each robe from our database up on the screen and we don't want to do each row we want to do each column of each row so we can do that by creating another another for loop inside of this for loop so let's go for we're gonna call it let's call it Y in X so now we're looping through X right so our original thing is result we're looping through result and we're creating X now we're looping through X which is each row of our data and we're pulling out Y which is going to be each column of our data so for y in X let's print out y and let's see I think the rest works yeah I think that'll work so let's save this and we might look at this code again because I went over that kind of fast Oh got our program list customers boom now it's putting them one after another so we don't necessarily want that what did we do wrong there look at our code I think I know the num is in the wrong spot yeah we need to put this num inside of this first loop like that all right that'll fix it I think yeah it should all right so let's take a look at this one more time customers there we go and now we have you can see each item is in a very specific column right so first name last name zip code price paid user ID email address 1 address to notice this record doesn't have an address - because I didn't have one city state country phone number payment method coupon code right and again this is just spitting them out just in the way that they're in the database in the order that they're in so first name last name zip code because again that's how we stupid lead in our database first name last name zip code right price paid user ID price paid user ID and we drop down email address 1 address 2 email address 1 address 2 etc etc so again you could loop through here in another way and like we did just a second ago where we listed each item the 0th item the first item etc yeah becomes a little bit more complicated but you can play around with this there just does iliyan different ways to do this so again let's look through this real quick we're first we're creating an enumerated for loop and that just pulls our database results and for each row in our database it enumerates it gives it an index number and we use that number to tell it which row so every so the first row is the first item in our database then the next row down will be the next item in our database as enumerated by this little index guy so that's that then we're creating a number setting it equal to 0 and then every time we loop through we just add 1 to that number and that becomes the next column over right and so that's how that works and then the rest of this we're just spitting out the text of Y which we created here and then gridding it up onto the screen so fairly not that complicated a little bit complicated but not too bad and yeah that's all there is to it so ok let's do this again look at it this is kind of very sloppy and maybe not that useful but this video is getting pretty long already so I think we'll stop it right here in the next video we'll kind of start to think through how we want this to actually look and here we're just listing all customers maybe that's not that useful maybe we want to be able to look up specific customers based on their name or their ID or their email address or whatever so we'll start to look at that and things like that in the next couple of videos or so and that should be a lot of fun so that's all for this video if you liked it be sure to smash the like button below subscribe to the channel give it a thumbs up and check out Kota Meachem or you can use coupon code youtube you get $20 off membership you pay just $27 to access all my courses hundreds of videos in the PDFs of all my best slang coding books I joined over 60,000 students learning to code just like you my name is John elder from coding me calm and we'll see in the next video

Channel: Codemy.com

Views: 13,346

Keywords: tkinter query database, query database tkinter, look up database tkinter, crm, python crm, python tkinter crm, crm with tkinter, kinter, python, tkinter, python tkinter, tkinter python, python gui, tkinter gui, gui python, how to python tkinter, tkinter mysql, mysql tkinter, using mysql with tkinter, mysql python tkinter, tkinter mysql python, tkinter database, alter table, mysql alter table, mysql python alter table, python tutorial

Id: Q2uRvncE_gM

Length: 16min 35sec (995 seconds)

Published: Tue Dec 03 2019