1
00:00:00,900 --> 00:00:08,430
Let me now show you how to create a python app that queries data from a mysql database.

2
00:00:08,540 --> 00:00:16,200
A remote mysql database. Your python script will connect to the database using the credentials I'll give

3
00:00:16,200 --> 00:00:24,120
to you now. First of all though you need a library that allows Python to interact with a mysql

4
00:00:24,120 --> 00:00:25,080
database.

5
00:00:25,080 --> 00:00:35,930
A good library is mysql connector. You can install that with pip install mysql-connector-python.

6
00:00:36,720 --> 00:00:46,150
I'm using Pip3 in my case. Use whatever you use in your system which could be Pip if you

7
00:00:46,150 --> 00:00:53,560
are on Windows and so successfully installed mysql connector.

8
00:00:53,830 --> 00:00:57,430
Once you do that, the installation you go ahead and import

9
00:00:57,520 --> 00:01:00,990
mysql.connecter.

10
00:01:01,660 --> 00:01:06,180
After that the first thing you want to do is you want to establish a connection.

11
00:01:06,340 --> 00:01:16,990
So use a variable to store that connection object, and then say mysql.connector.connect.

12
00:01:17,410 --> 00:01:25,300
And here in these parentheses will go the credentials of the database so you'll pass them as parameters.

13
00:01:25,570 --> 00:01:28,470
Since there are quite a few credentials,

14
00:01:28,540 --> 00:01:34,290
What I'm going to do is I'm just going to press enter so that I can write

15
00:01:34,300 --> 00:01:43,710
now all those parameters in here, so user equal to "ardit700_student".

16
00:01:44,170 --> 00:01:46,350
That's the user name of the database.

17
00:01:46,360 --> 00:01:47,700
Don't forget a comma.

18
00:01:47,710 --> 00:01:51,270
So just like you pass like multiple parameters here,

19
00:01:51,300 --> 00:01:57,570
but in this case we want to do that's one parameter in each line.

20
00:01:58,270 --> 00:02:00,080
Password equals to

21
00:02:01,250 --> 00:02:15,310
"ardit700_student", a comma again, host. This is the IP of the server that has the database, and that i

22
00:02:15,520 --> 00:02:24,820
"108.167.140.122", a coma.

23
00:02:24,820 --> 00:02:26,690
So everything comes as a string.

24
00:02:26,710 --> 00:02:29,170
You see with double quotes.

25
00:02:29,260 --> 00:02:40,450
Lastly, the database name, database is the parameter of the connect function that is ardit700_pm1

26
00:02:40,510 --> 00:02:44,590
database, and that's it.

27
00:02:44,590 --> 00:02:46,030
Let's test that.

28
00:02:46,120 --> 00:02:51,570
I'm going to execute my file. If you didn't get an error,

29
00:02:51,580 --> 00:02:58,980
that means the connection works but that's just a connection.
We want to find a way to query some data

30
00:02:58,990 --> 00:02:59,950
now.

31
00:03:00,040 --> 00:03:07,000
So after you establish the connection you want to create a cursor object that you were going to use to

32
00:03:07,690 --> 00:03:10,990
navigate through the table of the database.

33
00:03:11,050 --> 00:03:19,660
So con.cursor you use the con object you created in here. 

34
00:03:19,660 --> 00:03:24,520
And then let's do a query.

35
00:03:29,440 --> 00:03:37,710
Here goes the sql statement. You so that's in phpMyAdmin, the graphical interface actually executes these

36
00:03:37,840 --> 00:03:45,870
statements, these are Sql statements to show some data, for example, this query this sql query showed

37
00:03:45,870 --> 00:03:53,400
us all the data of the dictionary table, this is a dictionary table.
We're going to use a similar query

38
00:03:53,460 --> 00:03:55,920
like that.

39
00:03:55,920 --> 00:04:00,870
Select so this goes inside a string.

40
00:04:01,200 --> 00:04:10,680
This stands for all from dictionary. Dictionary is the name of the table so select all from the table

41
00:04:10,710 --> 00:04:14,030
dictionary, let's leave it like that.

42
00:04:14,080 --> 00:04:20,840
And lastly, you want to get the actual data.
Let's store them in the results variable.

43
00:04:20,850 --> 00:04:29,730
Cursor.fetchall, that's the method you want to use and print out the results.

44
00:04:29,730 --> 00:04:31,790
So now we are going to see

45
00:04:31,800 --> 00:04:34,220
if this script is actually working or not.

46
00:04:34,230 --> 00:04:35,870
Don't forget to save.

47
00:04:36,240 --> 00:04:37,490
Execute.

48
00:04:37,710 --> 00:04:45,030
It takes a while. We're querying a lot of data here, all the data of the dictionary which are the words in

49
00:04:45,090 --> 00:04:45,660
English.

50
00:04:46,860 --> 00:04:49,690
So it seems like it's working.

51
00:04:49,900 --> 00:04:51,410
That's a lot of them.

52
00:04:51,580 --> 00:04:56,050
So you can see that we got... It's actually a list.

53
00:04:56,140 --> 00:05:04,180
You see this closing square bracket here which indicates that this is a big list so results it's actually

54
00:05:04,180 --> 00:05:09,530
a list and this list is made of tuples.

55
00:05:09,580 --> 00:05:12,230
This is a last tuple of the list.

56
00:05:12,280 --> 00:05:18,380
Here is another tuple and so on. And so each tuple has the expression.

57
00:05:18,380 --> 00:05:26,060
So the words. You saw that. So remember these had the expression column and the definition column.

58
00:05:30,020 --> 00:05:33,340
This is the expression, the word and this is the definition of the expression.

59
00:05:33,410 --> 00:05:36,220
Now what if we want to return

60
00:05:36,650 --> 00:05:40,690
only one tuple of a certain expression?

61
00:05:40,740 --> 00:05:42,690
So for example in the inlay.

62
00:05:42,870 --> 00:05:44,470
Let's try this word.

63
00:05:44,790 --> 00:05:48,940
In that case you want to extend your query to make it more specific.

64
00:05:48,990 --> 00:05:57,070
You want to select all from dictionary where expression is equal to.

65
00:05:57,120 --> 00:06:03,000
And here you want to use single codes to enter the word 'inlay' allright.

66
00:06:03,120 --> 00:06:03,830
Execute.

67
00:06:07,700 --> 00:06:09,840
And this is what we get.

68
00:06:09,870 --> 00:06:19,100
So again we got a list, and that list this time contains only one tuple, the tuple of our interest.

69
00:06:19,100 --> 00:06:25,340
So if you want the actual tuple you may want to use zero here.

70
00:06:28,770 --> 00:06:36,790
You know to extract the first item of that list which is a tuple, so it's a list with a tuple inside.

71
00:06:36,830 --> 00:06:42,530
However some words let's say lining.

72
00:06:43,310 --> 00:06:48,270
Some words have more than one definition. For example lining has this...

73
00:06:48,270 --> 00:06:49,910
Oh no that was a bad example.

74
00:06:49,910 --> 00:06:53,720
So let's try another word with more than one definition.

75
00:06:53,930 --> 00:06:54,650
Maybe line.

76
00:06:57,610 --> 00:06:58,220
Yes, so

77
00:06:58,270 --> 00:07:04,570
line has this definition here, and then it has another definition.

78
00:07:04,580 --> 00:07:15,040
So we get a list of multiple tuples, therefore what you can do is you can construct a tuple here.

79
00:07:15,040 --> 00:07:24,900
You can construct a loop to extract all the tuples.
Let's say for result in results:

80
00:07:29,490 --> 00:07:31,760
print(results).

81
00:07:33,490 --> 00:07:35,230
Let's see what we're going to get this time.

82
00:07:38,080 --> 00:07:44,980
So you get the tuples one in each line, and if you get the, or if you want the definition only you want

83
00:07:44,980 --> 00:07:53,290
to pass one here so you'd get out of each tuple, you'd get the second item which is the definition.

84
00:07:53,780 --> 00:07:56,530
Let's see.

85
00:07:56,720 --> 00:08:02,030
And so these are all the definitions of line, the term, the expression line.

86
00:08:02,380 --> 00:08:10,980
Sometimes that are words that don't have a definition like that.
In that case, you don't get anything returned

87
00:08:11,190 --> 00:08:21,200
which means you might do something like If result, do this, else print

88
00:08:21,660 --> 00:08:23,790
("No word found").

89
00:08:28,290 --> 00:08:31,600
So in this case we're going to get no word found.

90
00:08:31,650 --> 00:08:33,780
So what does the do if result?

91
00:08:33,780 --> 00:08:40,440
Well if that word existed, we would get a list with tuples inside.

92
00:08:40,620 --> 00:08:44,010
If not what we get is an empty list.

93
00:08:44,040 --> 00:08:47,310
You can see here. Print (results).

94
00:08:52,080 --> 00:08:53,590
So it's an empty list.

95
00:08:53,670 --> 00:09:02,210
And in that case when you do if results and if that's results is an empty list it means is it's false.

96
00:09:02,250 --> 00:09:09,600
So this will not be executed if you get an empty list therefore else will be executed.

97
00:09:09,600 --> 00:09:10,890
This one here.

98
00:09:10,920 --> 00:09:16,890
So now we have something more left just before this query here.

99
00:09:16,890 --> 00:09:20,840
What we can do is we can get a word from the user.

100
00:09:24,640 --> 00:09:27,370
Enter word like, word

101
00:09:27,400 --> 00:09:38,050
and then here we replace this with the "%s" and outside of the string we say

102
00:09:38,260 --> 00:09:38,900
% word.

103
00:09:39,620 --> 00:09:46,400
So we're going to get the value that the user enters in word and put it in here.

104
00:09:46,840 --> 00:09:52,310
Save the script, execute enter a word rain.

105
00:09:53,680 --> 00:09:57,270
And these are the definitions for the word rain.

106
00:09:57,430 --> 00:10:04,420
So if you'd like to improve this program now as an exercise you could try to implement the techniques

107
00:10:04,420 --> 00:10:10,680
we used in application 1 where we were doing like when you executed that program.

108
00:10:10,740 --> 00:10:18,370
If the user made a typo like two Ns in rain, instead of getting "No word found", you could make the program

109
00:10:18,370 --> 00:10:22,990
more intelligent by looking for similar words in the database.

110
00:10:22,990 --> 00:10:29,770
Otherwise, this is still a good program and I think my sql connector is a very easy to use Python

111
00:10:29,770 --> 00:10:33,100
library too, to interact with my sql databases.

112
00:10:33,280 --> 00:10:36,760
I hope you like this and I hope you put this in good use.

113
00:10:36,760 --> 00:10:37,300
Thanks a lot.