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.
