PyQt5 QTableWidget tutorial: Load data from SQL table into Table Widget [Python, SQLite, PyQT5]

hi everyone welcome back to my channel so today i have another video for you and this video actually involves ipd5 again yet this time what we're going to do is we're going to be loading some data from an sql database so from an sql table you could say right into our pi qd5q table widget so if you don't know what a cue table widget is this is basically what it looks like now if you have been to my channel before you may have seen this table widget previously i use it in another tutorial where we actually got to know the actual q table widget and all those things now this that video is not necessarily a prerequisite for this one all you need to know is actually just to try to understand what a q table widget is which is something that i'm going to cover but basically it's just a fancy way for us to display tables in a pi qt5 or python graphical user interface and what we're going to attempt to do in today's video is load an sqlite table so i will be using sqlite just to keep keep things simple however you can use your favorite sql uh maybe mysql microsoft sql server or any of those things so here we can check out the sqlite table i have one table in this database and the table actually represents world cities so this is something i recently used in one of my projects it had like all the cities in the world so you can you know scroll down and see as a bunch of cities and some information about them so yeah that's one of the things that we're going to be doing so this is uh sorry exactly what we're going to be doing so how are we going to achieve this so let's see let's go back to our code and see what's going on so here in our code what we're going to do is we're going to set up a pique5 application now if you're not familiar with pyut5 i strongly encourage you to sort of maybe refresh your memory or learn a bit of basics before heading into this video mainly just this part about understanding how to launch a pique 5q application so even if you don't really know i'm just going to walk you through this existing code that i have here and then we're going to build on this code and see how we're going to add the sqlite data so now here we have some you know standard imports this is things that we actually need for us to be able to load our graphical user interface to actually launch it an application and all those things so the imports are pretty straightforward you need to import your sys you need to import um the pyqt5 qt widgets sorry about the noise you need to import the queue dialog the queue application and you need to import this line here so this load ui function which is something that we're going to use right here so you create a class for your current graphical user interface basically what really goes on when you use cutie designer so this is actually qt designer cutie designer is an external tool that you can use with pi kt5 although it's not mandatory so what do i mean by this i mean that you can actually design the tables the labels the buttons and all those things right in your python code without really needing to refer to this designer however this designer is a convenient way for us to drag and drop ui elements into our graphical user interface so you can see now i just added a button um i can add you know all sorts of things so you might already be familiar with this part but it doesn't hurt to really refresh our memories and what designer helps us do is that it saves these files as a table tutorial dot ui file so dot ui files are your products of the qt designer so just keep that in mind and these dot ui files are actually nothing but xml files so in the uh description i will have linked to the source code and that's where you can find more information about setting these things up you can also refer to my channel's pip85 tutorials playlist where you can see different sorts of things like setting up designer and actually using it for the first time if you're still a beginner but without further ado let's just go back to the tables and i feel like we've spent enough time on python so going back here what we actually did is um here we use the load ui function to actually load this table tutorial dot ui into our current class now why do we need a class the reason that is is because the class will actually contain the objects in our pyqt5 as actual variables so we will have a self.table widget which refers to this table widget right here whose object name is table widget now now that i have a self.table widget i can perform other things to it so one thing i actually went ahead and did is that i set the column width for the first second and third columns to be um let's see here so we have 250 and 100 and 350. so these are you know numbers that you set the way you like so these are just in pixels now this entire thing here so this piece of code will actually enable us to run the application and let's check it out and we get an empty table with these three columns and there's really nothing there either so this bar here on the left is actually just a styled uh part where it's just just for the looks of it there's really nothing here this is just a label as well so what else do we have so we have the other parts of the code right here so here what we have is the main part of the application we create a queue application which is what we are actually used to launch this application we create a main window which is an object for this class that we've defined before and then we add it to a stacked widget so the widget the stacked widget will actually contain all our screens you can refer to a video i have on going through multiple screens by through a click of a button or something like that and what we're going to do we add the main window to our stack we may set a fixed height and fixed width so i just did this here for aesthetics and then we just have to show it finally all we need to do is we need to execute this application so i hope this part is clear for more information on it you can refer to my more beginner videos so now that we've actually set the columns here so we've set the column widths we can also also do something that we actually want to set the headers so the headers are these things here so they are name age and address this is actually because of the older tutorial so you can find the older tutorial on my channel and you can also find the source code for it and the same github repository so i might want to change these things so let's see self.table widget dot set horizontal header labels and you just have to pass a list to it so here we have three columns and in the sqlite we actually have a name country and subcountry so this priority thing here is actually something i used for a project of mine you can just ignore it and yeah we're so we're going to get the city let's call it city instead of maine country and the subcountry so i hear some country refers to like state or something like that so we've set our header labels so now you can actually write it again if you're interested in seeing how that uh okay so horizontal i misspelled horizontal let's see okay so we can see city country and sub-country so we can actually you know what increase this and maybe here you know all right so now that we've done that what's our next step we actually want to load this the this data from the sql table so from our sqlite database into the pi 35 q table widget so how are we going to do that so let's create a function so let's say self upload data and then we'll define load data here so define load data okay so now this is the function that we need obviously here we're using sqlite so what we need to do is import sqlite 3. so sqli3 is not an external library that you need by any chance so it's actually built into python you can't use it directly so there's no need to install anything you just have to import it next we're actually going to connect to our sqli database so let's say connection is equal to sqlite3.connect and here we're just going to give it the path or the file name to the database so you can see here i have data.sqlite this is actually the file for this specific database that i have put in the pycharm project you can also find it in the source code as well so here what we're going to do is say data.sql lite so now i've successfully connected to this database then i need a cursor so the cursor will be like this um the cursor will go through the results of our query and it will execute the query then i'm going to create um sorry okay i'm going to create an sql query and what we're going to do for this query is let's say select star from world cities so the table name is actually sorry here it's actually world cities so i want to select all of them and there's a lot of cities so let's maybe limit this to 50. so now that we actually have our query all we need to do is actually execute it so let's see how are we going to execute this query what we need to do basically is curve dot execute sql query and this is how we would execute the query this would return a cursor as well that we can use to iterate over our results so what we're going to do is we're going to iterate over these results so for row in this so this is how we're gonna iterate and here is how we're going to put this stuff into the actual um table so this is how we're gonna put it into the q table widget before we do that it we have to make sure to do one thing let's first you know what let's try the print um row first of all so i just want to show you that this you know query works um okay low date i make typos sometimes so let's see you can see here that we have all of these uh you know cities the first 60 50 cities from the database in probably alphabetical order so yeah this is just one thing to keep in mind okay so the queries work now how are we going to translate this and actually store it here in this table so now i just printed it out into the command line how am i going to do it to the actual table so one thing we need to do always is we need to actually set a row count so i don't know if you notice but the table itself will have a row count of zero if i do not set a row count for the table that means the table will still be empty because the row count is zero so how do i really do this so let's say self dot table widget dot set row count and we set this row count to be 50 so in this case we have 50 results you can make this also dynamic through a variable it really doesn't matter now what else we're going to do is that we're going to create an index so we're going to say table index and we're going to say that it will be equal to 0. and you'll see what i mean by this in a second so now we're iterating over our results right so we're going for each row and our resulting cursor from the sql query so when we iterate what we need to do is we need to self.table widget dot set item so here we're setting a new item to the widget we want to set this item at table index so at the row or let's say maybe table row you can call this so table row we're saying that after a table row in the in the table we actually want to set this item to be zero um sorry we want to set this item in column zero and then what we want to do is qt widgets dot q table widget dot uh sorry q table widget item and now we're actually gonna give it a certain string so we can say row sub zero and what does this all translate to so what is happening here what's happening here is that we're going to our table widget we're setting an item at the row whose number is table row so obviously this should be incremented at the very end so let's say table row plus equal one why am i doing this because if you don't really do this there will be no way for the pyqt5 to know which row you're actually setting this information to so here we're saying you know at row 23 at row 50. so this is just this will keep on increasing until the results are done and at column zero means we want to do this at the very first column so here this is indexed by zero so this is column zero this is column one this is column two so then let's say i want to do the same thing for column one and then for column two so here i'm setting column one and then column two which means that this part so rho sub zero this is from our sqlite results right so row is from our results going back here we can see that you know let's say this is a row row sub 0 is the very first part the name of the city row sub 1 is the country and row sub 2 is actually the sub country so going back here we can just translate this to row sub 1 and row sub 2. so now that we have this let's see what would be the result if we were to actually run this piece of code and here we go so we have city country subcountry and we can see all of this information that is stored here obviously you can't change the amount of information so here we actually chose 50 and we can see that you know we didn't really cover a lot of space so just a couple countries but you know you can actually um you know get more information from the database so that's really it on how to load certain data from an sqlite database into a pique5 queue table widget now i can have more videos and please let me know in the comments if you're interested in the video like that where the actual query varies according to certain you know indicators from the pi 25 graphical user interface so what do i mean by this i mean that maybe here we could have a drop down and the drop down can't select a country and then from we only get all the results for this specific country so we can't do that um please let me know in the comments if you'd be interested in watching that and yeah that's really it for this video stay tuned for the next one thank you so much and bye bye

Channel: Code First with Hala

Views: 57,936

Keywords:

Id: YySB6tkjZ7Y

Length: 14min 50sec (890 seconds)

Published: Fri Dec 25 2020