1
00:00:00,580 --> 00:00:01,390
Welcome back.

2
00:00:01,420 --> 00:00:07,000
Previously, we added this table with four columns to our interface.

3
00:00:07,030 --> 00:00:08,920
Now we're going to add data here.

4
00:00:08,920 --> 00:00:13,750
So by the end of this video, we're going to have some rows with actual data.

5
00:00:13,780 --> 00:00:14,680
Let's begin.

6
00:00:15,430 --> 00:00:18,730
Now, to add rows, we're going to use SQL.

7
00:00:18,820 --> 00:00:26,380
So we're going to use an skew L library called a skew light three.

8
00:00:26,860 --> 00:00:28,860
This is a standard library.

9
00:00:28,870 --> 00:00:33,370
And this library has some functions which allow you to interact with the database.

10
00:00:33,400 --> 00:00:43,300
Now, since these are functions of the SQLite library, then it makes sense that we call those functions

11
00:00:43,300 --> 00:00:47,800
in a separate methods such as inside load data.

12
00:00:47,920 --> 00:00:54,730
Instead of writing them in here, this could also be the case.

13
00:00:54,730 --> 00:00:56,910
You could also do this if you like.

14
00:00:56,920 --> 00:01:01,120
You can write those functions in here instead of in here.

15
00:01:01,570 --> 00:01:08,110
But keeping those functions inside this method makes things more organized.

16
00:01:08,110 --> 00:01:10,870
So that's what we're going to do now.

17
00:01:11,110 --> 00:01:16,780
Go inside, load data and connect to a database.

18
00:01:17,660 --> 00:01:25,760
Now I do have some data already in a database file, so please don't load the database file from the

19
00:01:25,760 --> 00:01:30,110
lecture resources and then paste it in your project directory.

20
00:01:30,110 --> 00:01:31,460
So this is what I'm doing.

21
00:01:33,080 --> 00:01:33,950
Add to it?

22
00:01:33,950 --> 00:01:34,700
Perhaps.

23
00:01:34,700 --> 00:01:38,480
So that's my database, the DB file.

24
00:01:39,560 --> 00:01:46,010
Just to quickly show you beforehand what this database has inside, I can use the DB browser for SQLite

25
00:01:46,010 --> 00:01:52,310
program, which you can find online, download and install it, but you don't have to install this program.

26
00:01:52,700 --> 00:01:54,260
You can just look here.

27
00:01:55,220 --> 00:02:04,760
So if I go to browse data and I go to students table, so this database has a student table, right?

28
00:02:04,760 --> 00:02:08,690
A database is made of one or more tables.

29
00:02:08,690 --> 00:02:13,010
So this has an ID column name course and mobile.

30
00:02:13,700 --> 00:02:19,580
So these are the data that we want to put on our pie sheet program.

31
00:02:20,840 --> 00:02:23,030
These data are inside database DB.

32
00:02:23,060 --> 00:02:25,160
Therefore, what we need to do is.

33
00:02:27,960 --> 00:02:31,050
Create a connection variable.

34
00:02:33,100 --> 00:02:41,590
And use the XQ three Connect method and provide the database DB file.

35
00:02:41,740 --> 00:02:48,340
So make sure that database DB is located in the same directory with your main p file.

36
00:02:48,460 --> 00:02:49,000
Right.

37
00:02:49,000 --> 00:02:54,040
That allows you to simply provide the name of the DB file in here.

38
00:02:56,290 --> 00:03:00,100
Then we want to extract the data out of that database.

39
00:03:00,100 --> 00:03:05,170
To do that you need to refer to connection, the connection object.

40
00:03:05,170 --> 00:03:11,380
So that's a specific escalate three object which contains an execute method.

41
00:03:11,470 --> 00:03:20,470
And inside the execute methods you write an SQL query such as select all from students.

42
00:03:20,470 --> 00:03:22,900
Students is the name of the table.

43
00:03:22,900 --> 00:03:28,270
Inside that database, I mentioned that a database can have multiple tables.

44
00:03:28,420 --> 00:03:33,460
So the table which have the data is named students.

45
00:03:36,080 --> 00:03:40,910
The next, we're going to insert the results into the table.

46
00:03:40,910 --> 00:03:46,340
But before we do that, let's check what result looks like in the terminal.

47
00:03:47,000 --> 00:03:48,440
So run the program.

48
00:03:49,250 --> 00:03:56,630
Expand the run tab and check if you got.

49
00:04:02,280 --> 00:04:03,900
Anything printed out.

50
00:04:06,210 --> 00:04:11,760
There's nothing in my case because that's because I haven't called the load methods yet.

51
00:04:11,760 --> 00:04:15,830
So we defined it here, but we're not calling it anywhere.

52
00:04:15,840 --> 00:04:19,589
So where should we call that method?

53
00:04:20,700 --> 00:04:22,860
Well, there are two ways to do that.

54
00:04:22,890 --> 00:04:28,650
You either call it insights here with cells that load data.

55
00:04:30,470 --> 00:04:38,080
Or you can also call it somewhere in here after you have created the main window.

56
00:04:38,090 --> 00:04:48,050
So age calculator, age calculator show, then you do age calculator that load data.

57
00:04:49,620 --> 00:04:51,570
So let's try this version.

58
00:04:52,110 --> 00:04:53,550
Either way, it's fine.

59
00:04:55,230 --> 00:04:57,270
Let's rerun this.

60
00:04:59,410 --> 00:05:03,600
And yeah, so that's the results variable, right?

61
00:05:03,610 --> 00:05:06,280
It doesn't look anything useful for.

62
00:05:06,280 --> 00:05:09,490
No, because it's a cursor object.

63
00:05:09,490 --> 00:05:16,990
So you need to convert it into for example, into a list just like this and rerun.

64
00:05:20,850 --> 00:05:22,960
And now we see some actual data.

65
00:05:22,980 --> 00:05:25,110
So that's a list of tuples.

66
00:05:25,260 --> 00:05:30,210
The first tuple represents the first row of the table.

67
00:05:30,210 --> 00:05:33,270
So the student with ID won.

68
00:05:33,270 --> 00:05:35,670
The name of the student is John Smith.

69
00:05:36,180 --> 00:05:42,240
Math is the course The student has a role to have as a phone number of a student.

70
00:05:42,240 --> 00:05:45,960
And of course, in the interface we will add more students.

71
00:05:47,650 --> 00:05:51,760
To the table and these students will be added to the database as well.

72
00:05:51,760 --> 00:05:52,120
Right.

73
00:05:52,120 --> 00:05:53,800
So we can delete these students.

74
00:05:53,800 --> 00:06:01,000
But for now, we have some initial data and these are those let's add those data to the table.

75
00:06:01,240 --> 00:06:03,760
So remember the structure of this.

76
00:06:03,760 --> 00:06:06,190
This is a list of tuples, right?

77
00:06:06,340 --> 00:06:16,330
So let's go here, delete that print function and let's add the actual code which populates the table

78
00:06:16,330 --> 00:06:17,200
with data.

79
00:06:18,220 --> 00:06:19,960
This code will look like this.

80
00:06:19,960 --> 00:06:22,240
Let me show you that to you graphically.

81
00:06:22,240 --> 00:06:29,920
So what we'll do is we're going to iterate for this to every row.

82
00:06:29,920 --> 00:06:34,600
So we're going to go to every row with the first full loop.

83
00:06:34,600 --> 00:06:43,930
Then inside this full loop, we're going to have another for loop, a nested for loop for cell in row.

84
00:06:43,930 --> 00:06:48,700
So basically the first loop will go to the first row.

85
00:06:48,700 --> 00:06:55,090
So it's going to get the first row and the second for loop is going to go to each of the cells.

86
00:06:55,090 --> 00:06:59,020
So it's going to iterate over the cells of the first row.

87
00:06:59,200 --> 00:07:02,950
Then the main loop will go to the second row.

88
00:07:02,980 --> 00:07:10,150
The second loop will go to the cells of the second row and so on, until we populate all the rows with

89
00:07:10,150 --> 00:07:11,500
all the cells.

90
00:07:11,650 --> 00:07:12,850
Yeah, let's do that.

91
00:07:13,830 --> 00:07:14,310
Four.

92
00:07:14,460 --> 00:07:21,990
Row number row data in enumerates results.

93
00:07:24,800 --> 00:07:25,680
Enumerate.

94
00:07:26,180 --> 00:07:26,960
Results.

95
00:07:27,380 --> 00:07:31,040
So row number is zero one, two, three, four.

96
00:07:31,040 --> 00:07:37,410
Depending on the row and row data is going to be the tuple with the data.

97
00:07:37,460 --> 00:07:37,900
Right.

98
00:07:37,910 --> 00:07:45,920
Such as one join math the number and then we say self that table.

99
00:07:45,920 --> 00:07:52,940
So basically this that we're keeping here self the table that insert row.

100
00:07:54,520 --> 00:07:55,180
Row.

101
00:07:57,490 --> 00:07:58,390
Number.

102
00:07:58,480 --> 00:08:06,640
So what we're doing here is we are inserting an empty row in a particular index in the table.

103
00:08:06,640 --> 00:08:09,940
So we're starting with the row with index zero.

104
00:08:10,330 --> 00:08:17,730
So we insert the first row, which has an index of zero, and then we say full column.

105
00:08:17,740 --> 00:08:19,530
So that's the second loop.

106
00:08:19,540 --> 00:08:25,570
Now number data in enumerate.

107
00:08:25,600 --> 00:08:29,860
This time we have row data, this data rate.

108
00:08:30,670 --> 00:08:32,710
So we're working on that tuple.

109
00:08:34,950 --> 00:08:39,299
And we tried to insert that table into the first row.

110
00:08:39,390 --> 00:08:45,460
So we do self dot, table, dot set item.

111
00:08:45,480 --> 00:08:50,650
So insert row was used to insert a row, an empty row.

112
00:08:50,670 --> 00:08:57,040
Now set item is used to populate cells of that row with actual data.

113
00:08:57,060 --> 00:09:00,840
So set item at row number.

114
00:09:00,870 --> 00:09:08,760
So we want to specify which row and which column using column number.

115
00:09:09,120 --> 00:09:14,340
So we specify the coordinates of the cell in the table.

116
00:09:15,080 --> 00:09:19,140
And then here comes a third argument, which is queue.

117
00:09:19,160 --> 00:09:23,540
Table widget item.

118
00:09:24,260 --> 00:09:27,020
This is a class we need to import.

119
00:09:28,190 --> 00:09:32,480
So next to queue table widget, we import queue table, widget item.

120
00:09:33,480 --> 00:09:38,690
And then we use parenthesis to call that class.

121
00:09:39,050 --> 00:09:42,140
And inside we say string data.

122
00:09:42,800 --> 00:09:46,400
And there's one last thing we need to perform before the loop.

123
00:09:46,400 --> 00:09:52,970
So between the result variable and the for loop here in the same indentation level, we do solve the

124
00:09:53,000 --> 00:09:57,380
table that set row count.

125
00:09:58,550 --> 00:09:59,470
Zero.

126
00:09:59,480 --> 00:10:06,830
This will make sure that whenever you load the program or do something, these data will not be added

127
00:10:06,830 --> 00:10:08,630
on top of the existing data.

128
00:10:08,630 --> 00:10:12,200
So you'll get duplicate data if you don't have this line.

129
00:10:12,200 --> 00:10:18,560
So this sort of resets the table and loads the data as fresh.

130
00:10:20,080 --> 00:10:27,760
And just before we execute this in the same indentation level with the main code, we say connection

131
00:10:27,910 --> 00:10:34,000
that close to close the connection and then rerun the program.

132
00:10:35,520 --> 00:10:37,680
So, yeah, we've got something here.

133
00:10:38,430 --> 00:10:40,050
So these are the data.

134
00:10:44,320 --> 00:10:45,670
And that's how it works.

135
00:10:45,670 --> 00:10:50,770
So if you want to understand what's going on here, perhaps you want to insert some print functions

136
00:10:50,770 --> 00:10:53,890
such as print road data.

137
00:10:56,560 --> 00:10:58,930
And execute.

138
00:11:00,450 --> 00:11:03,100
So see what you've got here.

139
00:11:03,120 --> 00:11:04,160
It's raw data.

140
00:11:04,170 --> 00:11:08,030
So these are the raw data that are being printed out.

141
00:11:08,040 --> 00:11:09,240
So tuples.

142
00:11:11,440 --> 00:11:16,150
So that's the code to add data to the table.

143
00:11:17,350 --> 00:11:21,130
We could do a small improvement just before we close this video.

144
00:11:22,060 --> 00:11:25,810
This index column here is unnecessary.

145
00:11:26,410 --> 00:11:32,020
You notice the first column, which is part of the table, not part of the data.

146
00:11:32,140 --> 00:11:37,480
We can disable that by going to the init method.

147
00:11:37,930 --> 00:11:43,830
So you know that we set a header for the horizontal space.

148
00:11:43,840 --> 00:11:46,120
So these were the column names.

149
00:11:47,110 --> 00:11:54,130
But we can do the same by doing self that table dot vertical header.

150
00:11:54,520 --> 00:11:56,830
You call that method.

151
00:11:57,070 --> 00:12:06,010
So this has to be a small v lowercase v, So you call the method and after the parentheses you do set.

152
00:12:07,970 --> 00:12:10,850
Visible and set it to false.

153
00:12:10,850 --> 00:12:14,600
So that's who will hide that column.

154
00:12:14,600 --> 00:12:16,820
So let's execute it again.

155
00:12:18,350 --> 00:12:18,980
And there we go.

156
00:12:18,980 --> 00:12:25,700
So now we don't have that duplicate index column there and the table is cleaner.

157
00:12:26,150 --> 00:12:27,290
So there we go.

158
00:12:27,710 --> 00:12:33,860
Next, we need to add some action to this so that we're able to add more data.

159
00:12:33,890 --> 00:12:39,950
So how do you insert data into this table as a user from the interface?

160
00:12:39,980 --> 00:12:41,690
Let's do that in the next videos.

161
00:12:41,720 --> 00:12:42,440
I'll see you there.