1
00:00:02,220 --> 00:00:09,750
There are many, many data storage formats out there to store data we can mention see as we excel,

2
00:00:10,350 --> 00:00:20,340
Jason Ex-model Askew L now askew URL is considered to be one of the best ways to store data.

3
00:00:21,240 --> 00:00:30,300
So in this section, we're going to work on retrieving and inserting and changing data from obscure

4
00:00:30,310 --> 00:00:35,070
databases via Python for this particular lecture.

5
00:00:35,340 --> 00:00:41,640
We're going to work on retrieving data, so getting that data from a nice Google database and printing

6
00:00:41,640 --> 00:00:42,930
them out with Python.

7
00:00:42,930 --> 00:00:45,990
So we access those data with Python.

8
00:00:47,070 --> 00:00:51,720
Now there are different skill databases out there.

9
00:00:52,140 --> 00:00:57,330
You have Oracle, you have MySchool, PostgreSQL, ask your lines.

10
00:00:57,870 --> 00:00:59,490
These are all similar.

11
00:00:59,490 --> 00:01:02,760
So if you learn one, you basically know the other.

12
00:01:02,760 --> 00:01:06,090
There are small differences how to access the data.

13
00:01:07,540 --> 00:01:09,730
But all you need to learn is one of them.

14
00:01:09,910 --> 00:01:13,150
So the one we're going to work on is askew Lloyd.

15
00:01:14,470 --> 00:01:16,660
This is how this is going to work.

16
00:01:17,380 --> 00:01:19,870
I will give you a database file.

17
00:01:20,380 --> 00:01:23,350
The one I have in here uploaded in my arrival.

18
00:01:23,500 --> 00:01:29,680
You can download this from the lecture resources and place it in your project directory.

19
00:01:30,310 --> 00:01:38,410
If I click this, we're not going to see anything because this is not a text file, so it's a binary

20
00:01:38,410 --> 00:01:42,160
file and you need a specific program to read it.

21
00:01:42,580 --> 00:01:49,870
So you either read it with Python or with another program before we read it with Python.

22
00:01:50,170 --> 00:01:53,830
Let's read it with another third party program.

23
00:01:54,160 --> 00:01:55,060
I would suggest you.

24
00:01:55,330 --> 00:02:06,970
This web app in lieu adults GitHub dot com slash ASCII light dash viewer or just Google this light viewer.

25
00:02:07,600 --> 00:02:15,940
And then all you need to do is upload the database, the DB file that you got from the lecture resources.

26
00:02:16,300 --> 00:02:18,310
And so you should see the dots over.

27
00:02:18,340 --> 00:02:23,990
This database file has a database can have zero or more tables.

28
00:02:24,340 --> 00:02:28,990
This database here has a one table we can see it's in the drop zone lists year.

29
00:02:29,320 --> 00:02:37,570
The table name is AI PS, and this table has one to three fields or columns.

30
00:02:37,600 --> 00:02:45,970
In other words, and we have nine rows, so these are IP dots on the IP address, the domain of the

31
00:02:45,970 --> 00:02:52,370
IP and the Ascend number, which is an identifier for the piece.

32
00:02:52,870 --> 00:03:00,700
Now, just like we opened this database file in this program, we can do the same in Python.

33
00:03:01,180 --> 00:03:07,660
So the difference is that we don't use a graphical user interface to drag and drop it, but we use Python

34
00:03:07,660 --> 00:03:10,960
codes and we need a library for that.

35
00:03:11,000 --> 00:03:11,860
Ask you like three.

36
00:03:12,040 --> 00:03:19,630
It's a standard Python library, so it doesn't need to be installed now with databases, you have to

37
00:03:19,630 --> 00:03:21,130
establish a connection.

38
00:03:21,820 --> 00:03:27,700
So for that, you needs to see escalate three Dot Connect.

39
00:03:28,120 --> 00:03:33,220
That is the methods which gets as inputs the database path.

40
00:03:34,990 --> 00:03:37,090
Data base the deep.

41
00:03:38,640 --> 00:03:45,150
So that file goes there, and then once you establish your connection so you have that variable, then

42
00:03:45,150 --> 00:03:52,410
you create what is referred to as a cursed object that is basically an object which is going to look

43
00:03:52,410 --> 00:03:57,450
at the rows and give you data depending on what you want.

44
00:03:59,010 --> 00:04:05,040
So you point to that connection object and then to cursor to create that cursor variable.

45
00:04:05,520 --> 00:04:06,840
So this will stay here.

46
00:04:07,260 --> 00:04:13,740
Basically, you establish a connection and create cursor.

47
00:04:15,720 --> 00:04:16,860
Then we were done with that.

48
00:04:17,790 --> 00:04:20,519
Next, all we need to do is run.

49
00:04:20,519 --> 00:04:21,899
Ask you all queries.

50
00:04:22,470 --> 00:04:27,690
What is the MySQL query and ask you a query is ask you a language.

51
00:04:28,140 --> 00:04:32,520
In fact, ask que ele means structured query language.

52
00:04:33,330 --> 00:04:36,670
So it is a language like this one in here.

53
00:04:36,690 --> 00:04:37,590
This is a skill.

54
00:04:37,740 --> 00:04:44,690
So when we drag and drop this file in this app, this query was executed.

55
00:04:44,700 --> 00:04:47,280
This Eskimo language was executed.

56
00:04:47,550 --> 00:04:53,220
And what that did is it selected all the rows of the eyepiece table.

57
00:04:53,280 --> 00:04:57,780
And there's also this limits here, so it limits up to 30 rows.

58
00:04:58,080 --> 00:05:05,310
So if this table has eight zeros, we would only see three of them if you wanted to see overall.

59
00:05:05,400 --> 00:05:08,760
You can delete that and just say executes again.

60
00:05:08,760 --> 00:05:15,060
We would get the same output because this table has only nine rows, so that didn't affect the results.

61
00:05:15,600 --> 00:05:20,640
But you can do other stuff such as order by a as.

62
00:05:20,640 --> 00:05:26,970
And if you execute that you you'll see that the order of the rows will change.

63
00:05:27,480 --> 00:05:34,320
So we displayed all everything, every every row ordered by Ascend.

64
00:05:34,320 --> 00:05:39,840
So you have this one on one here, one to five and so on.

65
00:05:39,840 --> 00:05:41,460
The ascent increases.

66
00:05:42,150 --> 00:05:43,350
So this is what this does.

67
00:05:43,360 --> 00:05:47,570
Select everything from table like peace and order it by ason.

68
00:05:47,860 --> 00:05:49,230
Now we copy that.

69
00:05:50,550 --> 00:05:57,300
We go back to Python and gets ready to execute a school.

70
00:05:59,850 --> 00:06:04,650
Which goes like this, Kerr, you point to the cursed object that executes.

71
00:06:05,790 --> 00:06:11,880
And then here goes in quotes, in double quotes, it's good to use double quotes because you might have

72
00:06:11,880 --> 00:06:16,890
some single quotes names in the school, so use double quotes for that.

73
00:06:17,510 --> 00:06:20,460
In there goes the query.

74
00:06:21,210 --> 00:06:27,030
Once you do that, you need to do one more thing you need to print Kerr fetch.

75
00:06:27,030 --> 00:06:34,470
All that is a method, so I'm calling that method and run off as the output.

76
00:06:35,930 --> 00:06:40,060
So the outputs is a list of tables.

77
00:06:40,700 --> 00:06:47,240
The list starts in here and it ends in here, so it's one list, but multiple multiple tables.

78
00:06:47,510 --> 00:06:50,210
Each table represents a row.

79
00:06:50,420 --> 00:06:59,030
So that is the first IP address with its domain, and it's a as and then the second row with its IP

80
00:06:59,030 --> 00:07:01,520
address with the domain and ascend.

81
00:07:01,910 --> 00:07:07,850
And they are also ordered by the as soon as you can see the number in here.

82
00:07:08,780 --> 00:07:13,310
So that's how you retrieve Dot with Python.

83
00:07:13,610 --> 00:07:14,740
That's the syntax.

84
00:07:14,780 --> 00:07:20,180
No, it's all a matter of learning the esker, a language which is pretty simple.

85
00:07:20,330 --> 00:07:23,300
And I'm going to teach you school now in this lecture.

86
00:07:23,510 --> 00:07:27,140
So that was how to execute a skill.

87
00:07:27,350 --> 00:07:32,750
And more specifically, this was how to get all.

88
00:07:33,870 --> 00:07:37,650
Rows and all columns.

89
00:07:41,670 --> 00:07:46,830
By order, how do you get certain columns only?

90
00:07:47,040 --> 00:07:53,130
So let's say we don't want the domain, we only want the IP and the ason to do that.

91
00:07:54,300 --> 00:07:56,640
Let's copy that pasted in here.

92
00:07:57,210 --> 00:08:07,320
Let's insert a comment gets all rows and certain columns instead of everything here instead of the asterisk

93
00:08:07,710 --> 00:08:10,920
you place address.

94
00:08:11,490 --> 00:08:22,230
So address is the name of that column, and as the name of that honor column, comma a space and ason

95
00:08:23,370 --> 00:08:24,780
and execute.

96
00:08:25,740 --> 00:08:26,610
And here we go.

97
00:08:27,480 --> 00:08:30,630
So here is where the first output ends.

98
00:08:31,260 --> 00:08:39,480
So that's the first output up there from this print function, and that is the second output.

99
00:08:40,140 --> 00:08:46,230
So that is the address and the asset and no domain this time.

100
00:08:47,940 --> 00:08:48,960
Let's do some more.

101
00:08:48,990 --> 00:08:52,500
Ask you how to apply conditions.

102
00:08:52,950 --> 00:09:04,770
So, for example, you won't only the rules of the table which have an alias and less than hundreds.

103
00:09:05,040 --> 00:09:08,430
So basically, we want to extract.

104
00:09:09,570 --> 00:09:12,240
That portion only those Dutton.

105
00:09:14,250 --> 00:09:21,120
To do that, you need to use a aware statement which goes like this.

106
00:09:21,540 --> 00:09:29,700
So get all rows were ace and is less than 300.

107
00:09:30,540 --> 00:09:35,010
To do that, you say select everything or certain columns or whatever you want.

108
00:09:35,370 --> 00:09:36,360
Let's say everything.

109
00:09:36,360 --> 00:09:40,350
So all the columns from my piece, that's the table name.

110
00:09:40,860 --> 00:09:43,920
And then we see where.

111
00:09:45,540 --> 00:09:52,830
HSN is smaller than 300 now execute.

112
00:09:55,470 --> 00:09:57,210
And that's the output.

113
00:09:58,740 --> 00:10:04,830
So we get to one to five and then one oh one.

114
00:10:05,340 --> 00:10:14,040
They are not ordered this time because we will remove the order by statement one, four, four and one

115
00:10:14,040 --> 00:10:14,850
nine eight.

116
00:10:15,120 --> 00:10:20,040
So there are no rules with ason or greater than 300.

117
00:10:20,820 --> 00:10:33,000
Similarly, you can apply a condition to get, for example, where a person is 144, so a specific value

118
00:10:33,180 --> 00:10:37,470
that would be assigned equal to one hundred forty four.

119
00:10:40,020 --> 00:10:42,660
Of as the outward, that's only one rule.

120
00:10:44,070 --> 00:10:52,320
Be aware, though, that this rule is still a tipple inside a list, so the output is always a least

121
00:10:52,320 --> 00:10:56,010
of at least one tipple, or maybe zero two.

122
00:10:56,220 --> 00:11:04,920
Sometimes when the school doesn't show any results as in the case, for example, if this is a number

123
00:11:04,920 --> 00:11:08,490
that doesn't exist and you run it, you gets an activist.

124
00:11:09,030 --> 00:11:09,300
So.

125
00:11:12,900 --> 00:11:13,570
What's more.

126
00:11:13,620 --> 00:11:18,100
Well, you can have multiple conditions, for example.

127
00:11:18,270 --> 00:11:28,980
Let me copy that piece that's in here and we want this time where ascent is less than 300, but also

128
00:11:28,980 --> 00:11:41,880
we want another condition which we can answer with the and operator and domain like single quotes.

129
00:11:42,210 --> 00:11:52,830
So open single courts, close single quotes and in between we say percentage is a let's see what this

130
00:11:52,830 --> 00:11:53,670
will give us.

131
00:11:55,190 --> 00:11:55,460
Hmm.

132
00:11:55,550 --> 00:12:00,440
So that's the output of that last print function.

133
00:12:01,430 --> 00:12:08,330
What we got is all the rules which have a niacin of greater than three onwards, but also they have

134
00:12:08,750 --> 00:12:11,040
the words as a at the end.

135
00:12:11,060 --> 00:12:18,920
So you see at the end of the domain as a in here and as a for this second rule.

136
00:12:20,790 --> 00:12:21,050
Sorry.

137
00:12:21,390 --> 00:12:23,040
So this is the ultimate.

138
00:12:24,630 --> 00:12:25,950
Of that information.

139
00:12:26,400 --> 00:12:29,730
So we have two roles, actually, that was from the previous brain function.

140
00:12:30,540 --> 00:12:34,680
So we have assay for that and assay for that.

141
00:12:35,640 --> 00:12:41,310
So what this does this ports in here and here is that.

142
00:12:43,540 --> 00:12:51,910
The domain has everything before you see the percentage operator, the percentage symbol here means

143
00:12:52,240 --> 00:12:56,860
every character followed by Assane.

144
00:12:57,460 --> 00:12:59,530
So that's the pattern here.

145
00:12:59,950 --> 00:13:04,090
We have multiple characters and then in the end, we have as a.

146
00:13:05,230 --> 00:13:12,460
So that's how you apply to conditions, and you can add more, of course, by adding more and more operators,

147
00:13:12,460 --> 00:13:13,780
followed by conditions.

148
00:13:15,460 --> 00:13:18,970
Now there's something you should know about the fetch all methods in here.

149
00:13:19,480 --> 00:13:22,060
See what's happened, what happens if you.

150
00:13:25,280 --> 00:13:30,590
Assign a variable to this method like that and then you print out.

151
00:13:33,410 --> 00:13:39,380
The variable of when you create another variable.

152
00:13:41,560 --> 00:13:43,150
Let's see results, too.

153
00:13:43,930 --> 00:13:45,070
And you printout

154
00:13:47,410 --> 00:13:53,290
results, too, you'd probably expect to get the results two times.

155
00:13:53,500 --> 00:13:54,610
But see what happens?

156
00:13:56,350 --> 00:14:01,030
So what you get is the results in here from the results of one variable.

157
00:14:01,360 --> 00:14:02,950
But then you get an empty list.

158
00:14:03,610 --> 00:14:13,660
That is because this method should only be executed as what's in the codes for a certain SQL query.

159
00:14:14,320 --> 00:14:19,690
So once you run the scroll query and you execute that method once, then.

160
00:14:20,920 --> 00:14:27,790
The least is exhausted because you used it once in here, so you can not use it again because you get

161
00:14:27,790 --> 00:14:28,480
an empty list.

162
00:14:28,720 --> 00:14:32,440
So for the second print function, you get this empty list.

163
00:14:34,400 --> 00:14:42,230
That's something to keep in mind, and of course, you can always iterate over the results.

164
00:14:42,650 --> 00:14:47,450
So for results or for real in results.

165
00:14:49,940 --> 00:14:51,440
Print roll.

166
00:14:55,540 --> 00:14:57,880
And you will see this afterwards.

167
00:14:59,560 --> 00:15:02,080
So this time we don't get the least.

168
00:15:03,760 --> 00:15:08,200
Because we are iterating over that list, so we see the US directly.

169
00:15:08,830 --> 00:15:14,770
So if you want to do something for each table, that is an option, what you can do, you can iterate

170
00:15:14,770 --> 00:15:16,240
over those totals.

171
00:15:17,990 --> 00:15:19,250
And that's about this video.

172
00:15:19,460 --> 00:15:23,030
This cold should be enough to get you started with a school.

173
00:15:23,330 --> 00:15:27,200
So now you know how to query data from school databases.

174
00:15:27,680 --> 00:15:28,880
I'll talk to you in the next video.