Export CRM Database To Excel File - Python Tkinter GUI Tutorial #32

what's going on guys John elder here from Konami common in this video I'm going to show you how to save the output of your database to an excel file with Python and Kenter alright guys we were gonna move on to searching the database for specific records but I thought before we did that we should take a minute to talk about exporting our database to an excel file outside of our app if we ever wanted to do that so we're gonna do that in this video 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 be sure to check out coding Meachem where i've dozens of courses with hundreds of videos that teach you to code use coupon code youtube to get $20 off membership it's all my courses videos and books for a one-time fee just $27 which is insanely cheap I should mention that price is going up probably in the new year so if you want to lock in that $27 price I jump on that right away so okay let's go ahead and run our app you go python CRM pi we pull this over this is where we left off if we click this list customers button this is what we get we've only got two records in our database and we're just throwing this up on the screen of the this is a very fancy and we're gonna make this look nicer later on but for now this is what we have so what I want to do now is just add a little button down here that allows us to export this to an excel file you know a CSV file a comma separated file so you know you've got all this stuff in a database in your GUI there's chances chances are you're gonna want to export this at some point so let's do that real quick in this video so the first thing we we have to do is come up here to the very top of our program we want to import something called CSV this cut this stands for comma separated values it's just what Excel spreadsheets are CSV files usually databases when you export them they're comma separated values so each row is going to be separated by a comma and it's just gonna output to a txt file so we want to do that go ahead and save that now let's head down to our list customers function here this is the function that actually looks up the stuff in our database and puts it out onto the screen we did all this in the last video now inside of this function you can see we're tabbing over from our list customers so one tab over we want to just create a button so let's go let's call a CSV comma separated value button and this is gonna be a button and it's going to be in our list underscore customer underscore query window right and we want the text to say what save to CSV or save to excel use excel as your spreadsheet a thing you can open a CSV file in Excel you can also open it in it just a notepad and we'll look at both of those so all right next we want to give this a command and we want this to be a lambda because we need to pass something in now look at this this looks like a capital L it is not this is a lowercase L for some reason sublime makes this lowercase L look like capital LS so make sure that that is a lowercase L and we want a colon and then let's create a function let's call it write to CSV and what we want to pass in here is our result okay so save this now I'm just gonna copy this while we're here now what is this result well if you remember in the last video that's this result right here which is this result right here so we're querying the database we're saying hey grab everything in our database and pull it out and slap it in this result variable so a result holds all of our database data at this time after we've you know gone through here and done all these things so okay we've created our button now on the next line we need to actually you know throw it up on the screen so let's go CSV underscore button dot grid and we want this to be row equals something and column equals something and we want this in the first column so that's column zero and what row do we want this in well let's see we've been doing rows stuff here and we created a for that so we could just take the last index number and use that so let's go index plus 1 so index the last number of index is the last row we have we want to go one more down so we just go plus one alright so that should work so now we need to create this function so when we click the button this function gets called so we can put this anywhere I'm just gonna put it right above here and so let's go I don't know write to CSV Excel function I don't know call it whatever you want now we want to go define and then I'm just gonna paste that in and we want to pass in the result : and again this is a lowercase L and why are we doing that because down here and our button we passed end result so we want to accept that into our function up here right so okay so now we need to do some Python csv code so I'm gonna go with open now let's just name what do we want to call the the excel file that we're gonna save I'm just going to call it customers dot CSV right and now we need to tell it the reader right method or function right so whenever you're reading or writing to files in Python you have to tell it how to do that and there's like ten different ways you can do it you can read the file only you can write to the file only you can read and write to the file you can read you can write to the beginning of a file you can write to the end of a file that's just a basic Python thing I'm gonna let you Google all those things just Google Python readwrite methods or something and you'll see a big list of them I'm gonna use a and a stands for append so this will open the file and it'll go down to the last line and then add stuff from there so if we change our database later on and we want to save it again it will it will save it and we've already saved it before it will go down to the last line of that file and then save the whole database again so keep that in mind that may not be what you want so but just for our purposes I think this is gonna be fine so you know if we click the button three times it's going to add our database three times to the CSV file which is probably something you may not want but I'm just gonna do a for now so we want to open this file and we want to call it as F and this is just a variable that we can use to do stuff with as you'll see so I'm gonna go W equals and you can use any variable and I'm gonna call the CSV writer which comes in that CSV package we installed earlier or we imported earlier and I'm gonna call F F which is what we just called this thing right here and what we want to do is called dialect equals Excel and there's a bunch of ways you can do this as well let you Google different methods if you don't want to save it as a excel thing you can look in you can learn all about that CSV program we just imported I'm not gonna get into the details in this video so okay so now we can go W dot write row and then pass in whatever we want to pass in what do we want to save to this file we want to save the result and the result is just whatever was in our database right so we're creating this W now we're using this right now method I definitely did not spell that right right now there we go seeing that the stuff from our database into it so that should be though all there is to it let's fire this up and see if it worked almost certainly mess this up so let's close a program and run it again and when we do we can click list customers the button is not showing up all right what did we do let's take a look Oh list Custer query he misspelled that all right so what was that yeah list customer query there we go I mean you tell me I did that all right run the singing again pull this over click this okay so we've got this thing here we've got this button safe to Excel we can click it nothing appears to happen but if you then go in now this is gonna save this file in whatever director you're in so we're in the C GUI directory so I can just open up my GUI directory right here and I'll see you inside of here this customer CSV why is that well that's because where did we save this we saved it we called it customers CSV so that's the file you want to look for and there it is and now if I double click this Excel opens and nothing has happened all right so what did I do wrong let's look through here w dot right oh man I misspelled this twice it's right row okay right row alright so go ahead and save this let's try it one more time close our old program open terminal run it again customers click save to excel pull up our thing here double-click this and boom here it is now you'll notice it's all fished together and what this is doing is it's in each column here it's putting one record and it's just putting them this way instead of this way so if you go to this one you can control see and then paste it here if you want this maybe it looks a little better right but if you have a thousand records you're not gonna want to do that for everything but you can still it all the data is here right you can still do Excel II type things now you'll notice that each one of these items is in one row instead of having them all in their own column earnst or something like that so if you want to tweak this you can get into the the CSV coding out the wazoo and do whatever you want I'm just showing you a very basic way to do this and so I'm gonna go ahead and delete this and let's modify this a little bit to make it a little bit more readable let's pull back our code and in the rights of CSV section here we're just writing reason the right row function and we have a lot of different rows and we maybe don't want to put them all on the same column we can we can change that around by just kind of let's create a for loop let's go for and what do we want to call this records or for record in result and then we could say this o instead of printing the whole result we just want to pick print a single record so this might work I think so let's pull this up close it and run it again when we do we can click list customers and we can click the save to excel button and when we do here's our customers s CSV and now they're sort of on their own line and there these things are in their own columns which may be more like what you want to do right there's a space between each one I don't know what that's all about but you can delete those you can run a little macro or something to delete those if you want to no big deal so I'm not gonna try and fix that and again you can learn all about the CSV program that we just imported earlier if you want it's way too much stuff to get into in just one little video like this I just want to show you a very basic way that you can do this and this is looking pretty good now we open this in Excel you can open this in like I said a notepad you just right click and click open with notepad and when you do you see the same sort of thing there comma separated values right CSV and that's cool so that's a real quick and easy way you can save to an excel file or a comma separated value file like I said there's lots of other ways you can do it there maybe even better I'll let you get into that on your own if that's something you really want to do but this is just a quick way to do it that works good enough for me and so I'll leave it at that so that's all for this video if you liked it be sure to smash the like button below subscribe to the channel give a thumbs up and check out co2 Meachem or you can use coupon code youtube to get $22 out membership you pages $27 for now to access all my courses hundreds of videos and the PDFs long best-selling coding books join over 60,000 students learning to code just like you my name is John elder from coding me calm and we'll see you in the next video

Channel: Codemy.com

Views: 17,723

Keywords: export mysql to excel, export mysql to csv, export database to csv, export mysql to csv in tkinter, export mysql database to excel, export mysql database to excel using python, 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

Id: 2MMwfNKN1_s

Length: 13min 27sec (807 seconds)

Published: Thu Dec 12 2019