LIKE: flexible searching

Using LIKE instead of WHERE we can perform flexible searching.

For example, we might search for people with a surname like 'Do%', and that would match Dot, Doyle, or Douglas.

The percent symbol, %, means here "any character, any length".

Let's say we have this table, users:

first_name	surname
John	Doyle
Bob	Smith
Jen	Dot
Sam	Douglas

Here's how to use LIKE:

SELECT * FROM users
WHERE surname LIKE 'Do%';

You would get back:

first_name	surname
John	Doyle
Jen	Dot
Sam	Douglas

Matching single characters

If you wanted to match only against "Dot", you can use the underscore character, _.

By doing this:

SELECT * FROM users
WHERE surname LIKE 'Do_';

Only people with three-letter surnames starting with 'Do' would match:

first_name	surname
Jen	Dot

Matching within text

You can use the percent symbol and the underscore anywhere within text, and not only at the end. These are perfectly valid:

• %th to match text ending with 'th'.
• Do__s to match text starting with 'Do', ending with 's', and with two characters in between.
• Bo%b to match text starting with 'Bo', ending with 'b' ,and with any number of characters in between.

Adding searching to our app

We can add searching for movies to our app!

First of all, we'd write the query in database.py:

SEARCH_MOVIE = """SELECT * FROM movies WHERE title LIKE ?;"""

This query will allow us to pass in any search term, such as "%Mat%", and it will return rows that contain that string in the movie title. For example, The Matrix.

We should create a function that allows us to use that query as well. In it, we'll accept a search term and we'll add the % at the start and the end:

def search_movies(search_term):
    with connection:
        cursor = connection.cursor()
        cursor.execute(SEARCH_MOVIE, (f"%{search_term}%",))
        return cursor.fetchall()

Then we can make use of it in app.py!

First, let's modify the user menu:

menu = """Please select one of the following options:
1) Add new movie.
2) View upcoming movies.
3) View all movies
4) Add watched movie
5) View watched movies.
6) Add user to the app.
7) Search for a movie.
8) Exit.

Your selection: """

Then we'll need to modify the main loop. At the moment it looks like this:

while (user_input := input(menu)) != "7":
    if user_input == "1":
        prompt_add_movie()
    elif user_input == "2":
        movies = database.get_movies(upcoming=True)
        print_movie_list("Upcoming", movies)
    elif user_input == "3":
        movies = database.get_movies()
        print_movie_list("All", movies)
    elif user_input == "4":
        prompt_watch_movie()
    elif user_input == "5":
        movies = prompt_get_watched_movies()
        if movies:
            print_movie_list("Watched", movies)
        else:
            print("That user has watched no movies yet!")
    elif user_input == "6":
        prompt_add_user()
    else:
        print("Invalid input, please try again!")

We can add another branch (make sure to change the exit condition of the loop as well!):

while (user_input := input(menu)) != "8":
    if user_input == "1":
        prompt_add_movie()
    elif user_input == "2":
        movies = database.get_movies(upcoming=True)
        print_movie_list("Upcoming", movies)
    elif user_input == "3":
        movies = database.get_movies()
        print_movie_list("All", movies)
    elif user_input == "4":
        prompt_watch_movie()
    elif user_input == "5":
        movies = prompt_get_watched_movies()
        if movies:
            print_movie_list("Watched", movies)
        else:
            print("That user has watched no movies yet!")
    elif user_input == "6":
        prompt_add_user()
    elif user_input == "7":
        movies = prompt_search_movies()
        if movies:
            print_movie_list("Movies found", movies)
        else:
            print("Found no movies for that search term!")
    else:
        print("Invalid input, please try again!")

And of course, define the prompt_search_movies() function:

def prompt_search_movies():
    search_term = input("Enter partial movie title: ")
    return database.search_movies(search_term)

We've got very good use out of our print_movie_list() function! That's a good sign that we've done something right.

Excellent job!