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):

from mysql.connector import pooling

class DatabasePool:
    connection_pool = pooling.MySQLConnectionPool(
                               pool_name="ws_pool",
                               pool_size=5,
                               host='localhost'
                               database='assignment',
                               user='root',
                               password='abc123')

    @classmethod
    def getConnection(cls):
        dbConn = cls.connection_pool.get_connection()
        return dbConn

This is my model file (shown below):

from model.DatabasePool import DatabasePool

class Movie:
    @classmethod
    def getRelevantMovies(cls,name):
        try:
            dbConn = DatabasePool.getConnection()
            cursor = dbConn.cursor(dictionary=True)
            sql = "select * from movie where name like % + %s + %"
            cursor.execute(sql,(name,))
            results = cursor.fetchall()
            return results
        finally:
            dbConn.close()

and this is my controller file (shown below):

from flask import Flask,jsonify,request
from model.Movie import Movie

app = Flask(__name__)
@app.route('/search/movies', methods=['GET'])

def getMoviesBySubstring(name):
    try:
        movieList = Movie.getRelevantMovies(name)

        if len(movieList)>0:
            foundData = {"Movies":movieList}
            return jsonify(foundData),200

        else:
            return jsonify({}),400

    except Exception as err:
        print(err)
        return jsonify({}),500

if __name__=="__main__":
    app.run(debug=True)

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.

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

from mysql.connector import pooling

class DatabasePool:
    connection_pool = pooling.MySQLConnectionPool(
                               pool_name="ws_pool",
                               pool_size=5,
                               host='localhost',
                               database='assignment',
                               user='root', 
                               password='abc123')

    @classmethod
    def getConnection(cls):
        dbConn = cls.connection_pool.get_connection()
        return dbConn

model/Movie.py

class Movie:
    @classmethod
    def getRelevantMovies(cls,name):
        try:
            dbConn = DatabasePool.getConnection()
            cursor = dbConn.cursor(dictionary=True)
            sql = "select * from movie where name like %s"
            cursor.execute(sql, (f"%{name}%", ))
            results = cursor.fetchall()
            return results
        finally:
            dbConn.close()

main.py

from flask import Flask,jsonify,request
from model.Movie import Movie

app = Flask(__name__)

# https://stackoverflow.com/a/35189294/206413
# https://stackoverflow.com/a/51385027/206413
@app.route('/search/movies', methods=['GET'])
def getMoviesBySubstring():
    name = request.args['name']

    try:
        movieList = Movie.getRelevantMovies(name)

        if len(movieList)>0:
            foundData = {"Movies":movieList}
            return jsonify(foundData),200

        else:
            return jsonify({}),400

    except Exception as err:
        print(err)
        return jsonify({}),500

if __name__=="__main__":
    app.run(debug=True)

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?

sql = "select * from movie where name like % + %s + %"
cursor.execute(sql,(name,))

Change them to:

sql = "select * from movie where name like %s"
cursor.execute(sql, (f"%{name}%", ))

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