WEBVTT 1 00:00:00.000 --> 00:00:01.450 Hi guys and welcome back. 2 00:00:01.450 --> 00:00:02.313 In this video we're going to learn about 3 00:00:02.313 --> 00:00:05.330 the LIKE comparison keyword 4 00:00:05.330 --> 00:00:07.760 and how we can search by using that. 5 00:00:07.760 --> 00:00:10.560 So you can use wildcards when you search with LIKE 6 00:00:10.560 --> 00:00:12.510 and there's two main wildcards. 7 00:00:12.510 --> 00:00:15.610 The percent symbol means any number of characters. 8 00:00:15.610 --> 00:00:18.130 The underscore means one character. 9 00:00:18.130 --> 00:00:22.570 So, for example, Do% would match Doyle, Dot, Douglas 10 00:00:22.570 --> 00:00:24.130 because the percent symbol means 11 00:00:24.130 --> 00:00:26.960 any number of characters after D-O, 12 00:00:26.960 --> 00:00:28.720 so it will match all of them. 13 00:00:28.720 --> 00:00:30.310 Here, we've got an example. 14 00:00:30.310 --> 00:00:33.830 We've got John Doyle, Bob Smith, Jen Dot and Sam Douglas. 15 00:00:33.830 --> 00:00:36.020 We're gonna perform a search like that, 16 00:00:36.020 --> 00:00:39.920 SELECT star FROM users WHERE surname LIKE 17 00:00:39.920 --> 00:00:42.700 and then as a string, Do%. 18 00:00:42.700 --> 00:00:45.560 Important we don't have an equal sign here anymore, 19 00:00:45.560 --> 00:00:47.770 we just have the LIKE keyword instead. 20 00:00:47.770 --> 00:00:49.780 So LIKE replaces the equal sign 21 00:00:49.780 --> 00:00:52.130 or the greater than or less than et cetera. 22 00:00:52.130 --> 00:00:55.090 Also important to notice, LIKE can be used with strings, 23 00:00:55.090 --> 00:00:57.270 you're not going to be using it with numbers. 24 00:00:57.270 --> 00:01:00.370 The end result is that you get all of the people 25 00:01:00.370 --> 00:01:02.423 whose surname starts with Do. 26 00:01:03.360 --> 00:01:05.680 Note that the capital letters are important, 27 00:01:05.680 --> 00:01:07.360 although you can set it up 28 00:01:07.360 --> 00:01:10.740 so that it doesn't care about case sensitivity. 29 00:01:10.740 --> 00:01:12.180 Now, let's have a look at another example. 30 00:01:12.180 --> 00:01:13.013 Same table 31 00:01:13.890 --> 00:01:16.283 but we do WHERE surname LIKE Do_. 32 00:01:17.250 --> 00:01:19.870 Now we only get Jen Dot back 33 00:01:19.870 --> 00:01:22.800 because the underscore matches a single character. 34 00:01:22.800 --> 00:01:24.615 A few things in here, 35 00:01:24.615 --> 00:01:28.070 %th would match anything that ends with T-H 36 00:01:28.070 --> 00:01:30.530 because it has any number of characters at the start 37 00:01:30.530 --> 00:01:32.363 and it must have T-H at the end. 38 00:01:33.313 --> 00:01:35.920 Do__S would match anything 39 00:01:35.920 --> 00:01:37.440 that starts with D-O and ends with S 40 00:01:37.440 --> 00:01:39.140 and has two characters in between. 41 00:01:40.350 --> 00:01:44.070 Bo%b matches anything starting with B-O 42 00:01:44.070 --> 00:01:47.270 and ending with B and any number of characters in between. 43 00:01:47.270 --> 00:01:50.780 And %sens% percent would match anything containing sens 44 00:01:50.780 --> 00:01:53.030 like sensibility or insensible. 45 00:01:53.030 --> 00:01:54.400 All of these things you should try out 46 00:01:54.400 --> 00:01:57.490 as that's really gonna let you experience it yourself 47 00:01:57.490 --> 00:02:00.550 but for now let's go and add search to our app. 48 00:02:00.550 --> 00:02:03.540 So we're gonna allow users to search for movies, 49 00:02:03.540 --> 00:02:05.540 we're gonna need a new query, new menu item 50 00:02:05.540 --> 00:02:07.120 and some new functions as well. 51 00:02:07.120 --> 00:02:10.040 This is the query you are going to use. 52 00:02:10.040 --> 00:02:13.790 So let's start from movies where title LIKE 53 00:02:13.790 --> 00:02:15.570 and then the argument, 54 00:02:15.570 --> 00:02:19.350 and we're gonna put into there, using SQLite3 55 00:02:19.350 --> 00:02:22.010 the argument which is gonna allow us to search for movies. 56 00:02:22.010 --> 00:02:24.070 So there's, gonna be a couple of percentage signs in there 57 00:02:24.070 --> 00:02:26.250 as well as whatever the user types. 58 00:02:26.250 --> 00:02:28.430 Let's go and do this right now. 59 00:02:28.430 --> 00:02:29.920 So we're here in the code editor, 60 00:02:29.920 --> 00:02:33.000 we're going to add movie searching to our application. 61 00:02:33.000 --> 00:02:34.400 So we'll add the new query 62 00:02:34.400 --> 00:02:36.280 and new function in database.py, 63 00:02:36.280 --> 00:02:38.710 and then a new function in app.py 64 00:02:38.710 --> 00:02:41.020 and an option in the menu as well. 65 00:02:41.020 --> 00:02:41.853 Let's do it. 66 00:02:43.306 --> 00:02:44.480 Here we've got the queries, 67 00:02:44.480 --> 00:02:47.190 we're gonna add one for search movies 68 00:02:47.190 --> 00:02:50.920 and that is gonna be SELECT star FROM movies 69 00:02:50.920 --> 00:02:53.350 WHERE title LIKE 70 00:02:54.566 --> 00:02:55.560 ? 71 00:02:55.560 --> 00:02:58.670 And then we're going to put our semicolon there as well. 72 00:02:58.670 --> 00:03:01.370 Now you may be tempted to put the percent signs in there 73 00:03:01.370 --> 00:03:03.020 but actually the percent symbols are used 74 00:03:03.020 --> 00:03:04.980 for other things in sequel, 75 00:03:04.980 --> 00:03:07.160 so that's not really going to work. 76 00:03:07.160 --> 00:03:10.000 We're going to pass them in as the search term. 77 00:03:10.000 --> 00:03:12.460 Then we will add a search movies function. 78 00:03:12.460 --> 00:03:14.480 I'm gonna add that, maybe 79 00:03:15.400 --> 00:03:17.400 maybe down here underneath get movies, 80 00:03:17.400 --> 00:03:18.750 I think that's a good place for it. 81 00:03:18.750 --> 00:03:20.267 So we'll do search_movies 82 00:03:21.790 --> 00:03:24.600 and we're gonna get in a search term, of course. 83 00:03:24.600 --> 00:03:26.080 Then, we're just gonna do something 84 00:03:26.080 --> 00:03:28.300 like getting movies with connection, 85 00:03:28.300 --> 00:03:31.320 we're gonna get a cursor, which is connection.cursor, 86 00:03:31.320 --> 00:03:35.670 then we're gonna do cursor.execute search movies 87 00:03:35.670 --> 00:03:38.960 and we're gonna pass in the F string 88 00:03:38.960 --> 00:03:41.600 that contains the percent sign, the search term 89 00:03:42.740 --> 00:03:44.040 and the other percent sign. 90 00:03:44.040 --> 00:03:46.300 And, of course, remember to make it a tuple 91 00:03:46.300 --> 00:03:48.930 so that that can get passed into there. 92 00:03:48.930 --> 00:03:51.430 So here, you can see I'm putting the percent signs in there 93 00:03:51.430 --> 00:03:53.470 but you could potentially expand this 94 00:03:53.470 --> 00:03:55.920 to search for movies in different ways, 95 00:03:55.920 --> 00:03:58.460 or maybe tell the user what symbols they can use 96 00:03:58.460 --> 00:04:00.130 to search in different ways. 97 00:04:00.130 --> 00:04:01.770 It's all up to you but here we're gonna pass 98 00:04:01.770 --> 00:04:02.990 the percent signs in there, 99 00:04:02.990 --> 00:04:04.710 so anything they search for 100 00:04:04.710 --> 00:04:07.230 will get, essentially, searched for in the database 101 00:04:07.230 --> 00:04:11.500 if they enter mat, we will show "The Matrix", for example. 102 00:04:11.500 --> 00:04:14.590 Now, we have to go and return cursor.fetchall 103 00:04:15.560 --> 00:04:17.580 and we can go and make use of this function 104 00:04:17.580 --> 00:04:19.420 over in app.py . 105 00:04:19.420 --> 00:04:21.030 So let's go and do that. 106 00:04:21.030 --> 00:04:23.700 The first thing to do is to go up to the menu. 107 00:04:23.700 --> 00:04:28.150 We're going to change seven to search for a movie 108 00:04:28.150 --> 00:04:29.630 and eight 109 00:04:29.630 --> 00:04:31.010 to exit. 110 00:04:31.010 --> 00:04:32.680 So we go down to the menu 111 00:04:33.580 --> 00:04:37.190 and here we change this to eight for exiting 112 00:04:37.190 --> 00:04:40.720 and we add the new branch here if user input 113 00:04:40.720 --> 00:04:42.973 is equal to seven. 114 00:04:43.880 --> 00:04:46.203 And we can do something like prompt_search_movies. 115 00:04:47.480 --> 00:04:50.048 So let's go and create that function, 116 00:04:50.048 --> 00:04:51.798 prompt_search_movies, 117 00:04:52.860 --> 00:04:55.010 and that is going to ask for the search term 118 00:04:55.010 --> 00:04:57.980 so we'll do search_term = input 119 00:04:57.980 --> 00:05:01.053 enter the partial movie title, 120 00:05:02.180 --> 00:05:05.510 and then we will grab the movies from the database, 121 00:05:05.510 --> 00:05:08.893 database.search_movies with that search term. 122 00:05:09.910 --> 00:05:11.760 And then we're gonna do something like what we had above 123 00:05:11.760 --> 00:05:14.450 if there's any movies we will print the movie list, 124 00:05:14.450 --> 00:05:18.060 something like movies found and the movies that we found. 125 00:05:18.060 --> 00:05:20.300 Otherwise, we can print something like, 126 00:05:20.300 --> 00:05:24.930 found no movies for that search term, 127 00:05:24.930 --> 00:05:26.870 or something like that. 128 00:05:26.870 --> 00:05:28.520 As you can see, we've gotten very good use 129 00:05:28.520 --> 00:05:30.357 out of our print movie list function. 130 00:05:30.357 --> 00:05:33.500 That's a sign that we've done something right here. 131 00:05:33.500 --> 00:05:35.570 Okay, we can now run this. 132 00:05:35.570 --> 00:05:37.563 Let me just bring it up here. 133 00:05:38.410 --> 00:05:40.770 We can do something like add a new movie. 134 00:05:40.770 --> 00:05:44.650 We are gonna add something like "The Incredibles" 135 00:05:44.650 --> 00:05:49.230 that was released in the first of January 2006. 136 00:05:49.230 --> 00:05:51.880 I don't think so but I can't really remember the exact date. 137 00:05:51.880 --> 00:05:53.820 Then we can view movies. 138 00:05:53.820 --> 00:05:55.530 The only upcoming movie is "Mulan". 139 00:05:55.530 --> 00:05:57.070 We can view all the movies, 140 00:05:57.070 --> 00:05:59.747 which as you can see we've got "The Matrix", "Gone Girl", 141 00:05:59.747 --> 00:06:01.820 "Eternal Sunshine of the Spotless Mind", "Mulan" 142 00:06:01.820 --> 00:06:03.490 and "The Incredibles". 143 00:06:03.490 --> 00:06:05.490 We can mark a movie as watched if we wanted to, 144 00:06:05.490 --> 00:06:07.720 view watched movies, add users to the app 145 00:06:07.720 --> 00:06:10.140 and we can also search for a movie. 146 00:06:10.140 --> 00:06:13.330 So if we put lan we get Mulan. 147 00:06:13.330 --> 00:06:15.410 If we say something like 148 00:06:16.370 --> 00:06:18.970 the 149 00:06:18.970 --> 00:06:20.020 then we get 150 00:06:20.857 --> 00:06:21.690 "The Matrix", 151 00:06:21.690 --> 00:06:22.850 "Eternal Sunshine of the Spotless Mind" 152 00:06:22.850 --> 00:06:24.200 and "The Incredibles". 153 00:06:24.200 --> 00:06:27.690 Notice though, that the search with LIKE 154 00:06:27.690 --> 00:06:29.700 is by default, 155 00:06:29.700 --> 00:06:31.090 not case sensitive. 156 00:06:31.090 --> 00:06:33.320 So we've got the matching the exact case 157 00:06:33.320 --> 00:06:34.830 and then we've got the down here 158 00:06:34.830 --> 00:06:36.780 not matching the exact case. 159 00:06:36.780 --> 00:06:38.010 That is because in SQLite, 160 00:06:38.010 --> 00:06:40.450 we've got case sensitivity disabled 161 00:06:40.450 --> 00:06:42.320 but you can enable it if you want. 162 00:06:42.320 --> 00:06:45.560 It's pretty easy, it's just a configuration change. 163 00:06:45.560 --> 00:06:47.040 All right, thank you for watching this video. 164 00:06:47.040 --> 00:06:48.060 I hope you've enjoyed it 165 00:06:48.060 --> 00:06:49.713 and I'll see you in the next one.