I have been banging my head for a week trying to solve this and desperately need help for my assignment. Downvote all you want, just save me from this hell... Basically I have a table like this:

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.

As you can tell, my understanding of programming is very basic so any simple explanation is greatly appreciated. It is likely that I have made further mistakes elsewhere. Please point them out for me, I have really tried all I can and need more guidance...

Thank you so much

CodePudding user response：

Use the below SQL statement:

sql = "select * from movie where name like %%{0}%%".format(name)
cursor.execute(sql)

CodePudding user response：

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.