Skip to content
Advertisement

Check if string contains user-defined substring using SQL LIKE operator

Basically I have a table like this:

movieId name
1 White chicks
2 Inception
3 The Avengers: Endgame
4 Avatar: The Last Air Bender

My job is to retrieve all relevant movies based on a substring of the movie name. For example, if a user enters a substring such as “av” (e.g., they enter “http://localhost:5000/search/movies?name=av”), they will find movies like The Avengers and Avatar.

Because the assignment requires the use of a Model-View-Controller(MVC) design with a pool of open connections, I have created 3 separate python files (a model, a controller and a database). For full information, this is my database file (shown below):

This is my model file (shown below):

and this is my controller file (shown below):

I am struggling to figure out how exactly can I modify the sql code (found in the model file) to take in whatever substring input entered by the user (e.g., http://localhost:5000/search/movies?name=av), select the relevant movies in the database (Avatar and The Avengers) and pass it back to the controller. I am only allowed to use the like operator. Most online guides (e.g., Check if a string contains a substring in SQL Server 2005, using a stored procedure) on the LIKE operator have given examples that require pre-specified queries (e.g., using %a% to find values that contain an “a”), which is not appropriate for my case.

Advertisement

Answer

I made it! You owe me a drink… really! 🙂

Beforehand, I named main.py because I don’t do Flask (but, yes, I tested it!).

In:

  • model/DatabasePool.py, you had a missing comma @7
  • model/Movie.py, the query had to be changed
  • main.py, Flask controllers do not expect as call parameters those from URLs as ?parm=val

model/DatabasePool.py

model/Movie.py

main.py

Oh. One more thing! Man, Python is awesome, but it’s not built for web apps. React, Vue, .Net, Ruby/Rails are perfect alternatives.


Depricated


You are almost there.

I’m pretty sure these lines below yield an exception, right?

Change them to:

I don’t have a working environment here to check it. So… my apologies if it’s syntactic wrong.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement