-: Hi guys and welcome back.
In this video we're going to learn about
the LIKE comparison keyword
and how we can search by using that.
So you can use wildcards when you search with LIKE
and there's two main wildcards.
The percent symbol means any number of characters.
The underscore means one character.
So, for example, Do% would match Doyle, Dot, Douglas
because the percent symbol means
any number of characters after D-O,
so it will match all of them.
Here, we've got an example.
We've got John Doyle, Bob Smith, Jen Dot and Sam Douglas.
We're gonna perform a search like that,
SELECT star FROM users WHERE surname LIKE
and then as a string, Do%.
Important we don't have an equal sign here anymore,
we just have the LIKE keyword instead.
So LIKE replaces the equal sign
or the greater than or less than et cetera.
Also important to notice, LIKE can be used with strings,
you're not going to be using it with numbers.
The end result is that you get all of the people
whose surname starts with Do.
Note that the capital letters are important,
although you can set it up
so that it doesn't care about case sensitivity.
Now, let's have a look at another example.
Same table
but we do WHERE surname LIKE Do_.
Now we only get Jen Dot back
because the underscore matches a single character.
A few things in here,
%th would match anything that ends with T-H
because it has any number of characters at the start
and it must have T-H at the end.
Do__S would match anything
that starts with D-O and ends with S
and has two characters in between.
Bo%b matches anything starting with B-O
and ending with B and any number of characters in between.
And %sens% percent would match anything containing sens
like sensibility or insensible.
All of these things you should try out
as that's really gonna let you experience it yourself
but for now let's go and add search to our app.
So we're gonna allow users to search for movies,
we're gonna need a new query, new menu item
and some new functions as well.
This is the query you are going to use.
So let's start from movies where title LIKE
and then the argument,
and we're gonna put into there, using SQLite3
the argument which is gonna allow us to search for movies.
So there's, gonna be a couple of percentage signs in there
as well as whatever the user types.
Let's go and do this right now.
So we're here in the code editor,
we're going to add movie searching to our application.
So we'll add the new query
and new function in database.py,
and then a new function in app.py
and an option in the menu as well.
Let's do it.
Here we've got the queries,
we're gonna add one for search movies
and that is gonna be SELECT star FROM movies
WHERE title LIKE
?
And then we're going to put our semicolon there as well.
Now you may be tempted to put the percent signs in there
but actually the percent symbols are used
for other things in sequel,
so that's not really going to work.
We're going to pass them in as the search term.
Then we will add a search movies function.
I'm gonna add that, maybe
maybe down here underneath get movies,
I think that's a good place for it.
So we'll do search_movies
and we're gonna get in a search term, of course.
Then, we're just gonna do something
like getting movies with connection,
we're gonna get a cursor, which is connection.cursor,
then we're gonna do cursor.execute search movies
and we're gonna pass in the F string
that contains the percent sign, the search term
and the other percent sign.
And, of course, remember to make it a tuple
so that that can get passed into there.
So here, you can see I'm putting the percent signs in there
but you could potentially expand this
to search for movies in different ways,
or maybe tell the user what symbols they can use
to search in different ways.
It's all up to you but here we're gonna pass
the percent signs in there,
so anything they search for
will get, essentially, searched for in the database
if they enter mat, we will show "The Matrix", for example.
Now, we have to go and return cursor.fetchall
and we can go and make use of this function
over in app.py .
So let's go and do that.
The first thing to do is to go up to the menu.
We're going to change seven to search for a movie
and eight
to exit.
So we go down to the menu
and here we change this to eight for exiting
and we add the new branch here if user input
is equal to seven.
And we can do something like prompt_search_movies.
So let's go and create that function,
prompt_search_movies,
and that is going to ask for the search term
so we'll do search_term = input
enter the partial movie title,
and then we will grab the movies from the database,
database.search_movies with that search term.
And then we're gonna do something like what we had above
if there's any movies we will print the movie list,
something like movies found and the movies that we found.
Otherwise, we can print something like,
found no movies for that search term,
or something like that.
As you can see, we've gotten very good use
out of our print movie list function.
That's a sign that we've done something right here.
Okay, we can now run this.
Let me just bring it up here.
We can do something like add a new movie.
We are gonna add something like "The Incredibles"
that was released in the first of January 2006.
I don't think so but I can't really remember the exact date.
Then we can view movies.
The only upcoming movie is "Mulan".
We can view all the movies,
which as you can see we've got "The Matrix", "Gone Girl",
"Eternal Sunshine of the Spotless Mind", "Mulan"
and "The Incredibles".
We can mark a movie as watched if we wanted to,
view watched movies, add users to the app
and we can also search for a movie.
So if we put lan we get Mulan.
If we say something like
the
then we get
"The Matrix",
"Eternal Sunshine of the Spotless Mind"
and "The Incredibles".
Notice though, that the search with LIKE
is by default,
not case sensitive.
So we've got the matching the exact case
and then we've got the down here
not matching the exact case.
That is because in SQLite,
we've got case sensitivity disabled
but you can enable it if you want.
It's pretty easy, it's just a configuration change.
All right, thank you for watching this video.
I hope you've enjoyed it
and I'll see you in the next one.