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.
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 @7model/Movie.py
, the query had to be changedmain.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.