[Flask] Unexpected result when viewing results from a sql query

[Søren Pilgård]

On Tue, Jun 25, 2019 at 4:06 PM Cravan <savageapple850 at gmail.com> wrote:
>
> Hi all,
>
>                 I’m working on the same movie review project as before, but I recently tried to create a new search module for my search function, which is to search for the movie’s imdbid. Each movie has its own unique imdbid, but when I search for a single unique imdbid, somehow all the movies pop out. Can someone point out the source of the problem? On a side note, I tried to include a print statement in each condition statement, but nothing is printed, and I tried fetchone instead of fetchall, but it didn’t work either. Thanks in advance!
>
> Here is my results function:
>
> ````
>
> @app.route("/movie_results")
>
> def movie_results():
>
>     name = request.args.get("movie.title")
>
>     year = request.args.get("movie.year")
>
>     imdbid = request.args.get("movie.imdbid")
>
>     name_pattern = "%" + name + "%"
>
>     if year == '' or None and imdbid == '' or None:
>
>         search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE title ILIKE :movie_title')
>
>         movie_specific = engine.execute(search_movie_statement, movie_title=name_pattern).fetchall()
>
>         if len(movie_specific) != 0 and movie_specific is not None:
>
>             return render_template("movie_specific.html", movie_specific=movie_specific)
>
>         if len(movie_specific) == 0:
>
>             return render_template("error2.html", message="No such movie.")
>
>     elif name == '' or None and imdbid == '' or None:
>
>         search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE year = :year')
>
>         movie_specific = engine.execute(search_movie_statement, year=year).fetchall()
>
>         if len(movie_specific) != 0 and movie_specific is not None:
>
>             return render_template("movie_specific.html", movie_specific=movie_specific)
>
>         if len(movie_specific) == 0:
>
>             return render_template("error2.html", message="No such movie.")
>
>
>
>     elif name == '' or None and year == '' or None:
>
>         search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE "imdbID" = :imdbId')
>
>         movie_specific = engine.execute(search_movie_statement, imdbId=imdbid).fetchall()
>
>         if len(movie_specific) != 0 and movie_specific is not None:
>
>             return render_template("movie_specific.html", movie_specific=movie_specific)
>
>         if len(movie_specific) == 0:
>
>             return render_template("error2.html", message="No such movie.")
>
>
>
>     elif name == '' or None:
>
>         print(name)
>
>         search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE "imdbID" = :imdbid AND year = :year')
>
>         movie_specific = engine.execute(search_movie_statement, imdbid=imdbid, year=year).fetchall()
>
>         if len(movie_specific) != 0 and movie_specific is not None:
>
>             return render_template("movie_specific.html", movie_specific=movie_specific)
>
>         if len(movie_specific) == 0:
>
>             return render_template("error2.html", message="No such movie.")
>
>
>
>     elif year == '' or None:
>
>         search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE "imdbID" = :imdbid and title ILIKE :movie_title')
>
>         movie_specific = engine.execute(search_movie_statement, imdbid=imdbid, movie_title=name_pattern).fetchall()
>
>         if len(movie_specific) != 0 and movie_specific is not None:
>
>             return render_template("movie_specific.html", movie_specific=movie_specific)
>
>         if len(movie_specific) == 0:
>
>             return render_template("error2.html", message="No such movie.")
>
>
>
>     elif imdbid == '' or None:
>
>         search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE year = :year and title ILIKE :movie_title')
>
>         movie_specific = engine.execute(search_movie_statement, year=year, movie_title=name_pattern).fetchall()
>
>         if len(movie_specific) != 0 and movie_specific is not None:
>
>             return render_template("movie_specific.html", movie_specific=movie_specific)
>
>         if len(movie_specific) == 0:
>
>             return render_template("error2.html", message="No such movie.")
>
>
>
>     elif name != None and name != '' and year != '' and year != None and imdbid != '' and imdbid != None:
>
>         search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE year = :year and title ILIKE :movie_title and imdbID = :imdbid')
>
>         movie_specific = engine.execute(search_movie_statement, year=year, movie_title=name_pattern, imdbid=imdbid).fetchall()
>
>         if len(movie_specific) != 0 and movie_specific is not None:
>
>             return render_template("movie_specific.html", movie_specific=movie_specific)
>
>         if len(movie_specific) == 0:
>
>             return render_template("error2.html", message="No such movie.")
>
> ````
>
> Here is my movie table:
>
> ````
>
> CREATE TABLE movies (
>
>       "title" TEXT UNIQUE NOT NULL,
>
>       "year" INTEGER NOT NULL,
>
>       "runtime" INTEGER NOT NULL,
>
>       "imdbID" VARCHAR NOT NULL,
>
>       "imdbRating" NUMERIC NOT NULL
>
>   );
>
> ````
>
> Thanks,
>
> Cravan
>
>
>
> _______________________________________________
> Flask mailing list
> Flask at python.org
> https://mail.python.org/mailman/listinfo/flask



"""
Beaware that you are using `or` wrong
x == y or z
is equivalent to (x == y) or (z)
So you have a lot of conditions depending on None to be true, which
will always fail
If you just want to test if something is falsey use `not x`
so instead of if `if x == "" or x is None` you could just write `if not x`

Also you have a lot of repetition in your code making it very hard to
reason about. I made a cleaner version.
In general this way of building search queries often explodes in
cases. You will end up with 2^n cases where n is the number of
arguments that can either be there or not. In your case there should
be 2^3=8 cases but you only handle 7.
When doing long sequences of complicated if statements it is advisable
to also have an else clause even if it theoretically should never be
hit. Make that throw an error. I marked that with ???

"""

@app.route("/movie_results")
def movie_results():
    name = request.args.get("movie.title")
    year = request.args.get("movie.year")
    imdbid = request.args.get("movie.imdbid")
    name_pattern = "%" + name + "%"
    if not year and not imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE title ILIKE :movie_title')
        movie_specific = engine.execute(search_movie_statement,
movie_title=name_pattern).fetchall()
    elif not name and not imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE year = :year')
        movie_specific = engine.execute(search_movie_statement,
year=year).fetchall()
    elif not name and not year:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE "imdbID" = :imdbId')
        movie_specific = engine.execute(search_movie_statement,
imdbId=imdbid).fetchall()
    elif not name:
        print(name)
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE "imdbID" = :imdbid AND year = :year')
        movie_specific = engine.execute(search_movie_statement,
imdbid=imdbid, year=year).fetchall()
    elif not year:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE "imdbID" = :imdbid and title ILIKE :movie_title')
        movie_specific = engine.execute(search_movie_statement,
imdbid=imdbid, movie_title=name_pattern).fetchall()
    elif not imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE year = :year and title ILIKE :movie_title')
        movie_specific = engine.execute(search_movie_statement,
year=year, movie_title=name_pattern).fetchall()
    elif name and year and imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE year = :year and title ILIKE :movie_title and imdbID = :imdbid')
        movie_specific = engine.execute(search_movie_statement,
year=year, movie_title=name_pattern, imdbid=imdbid).fetchall()
    else:
        ??? # Return some kind of error

    if len(movie_specific) != 0 and movie_specific is not None:
        return render_template("movie_specific.html",
movie_specific=movie_specific)
    if len(movie_specific) == 0:
        return render_template("error2.html", message="No such movie.")


"""
Another thing that can be done to clear the logic is to ensure that
the if statement and the used variables matches, so use the fact that
it has a value instead of it not having a value.
So you could simplify your code even further
I haven't used sqlalchemy for a while so I can't remember if you are
allowed to send unused parameters to engine.execute.
"""

@app.route("/movie_results")
def movie_results():
    title = request.args.get("movie.title")
    year = request.args.get("movie.year")
    imdbid = request.args.get("movie.imdbid")
    title_pattern = "%{title}%".format(title)
    if title and year and imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE year = :year and title ILIKE :movie_title and imdbID = :imdbid')
    elif title and year and not imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE year = :year and title ILIKE :movie_title')
    elif title and not year and imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE "imdbID" = :imdbid and title ILIKE :movie_title')
    elif not title and year and imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE "imdbID" = :imdbid AND year = :year')
    elif title and not year and not imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE title ILIKE :movie_title')
    elif not title and year and not imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE year = :year')
    elif not title and not year and imdbid:
        search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE "imdbID" = :imdbId')
    elif not title and not year and not imdbid:
        # Empty search, what to do here?
        ???
    else:
        assert False, "Should never happen, a case was not handled correctly"
    movie_specific = engine.execute(search_movie_statement, year=year,
movie_title=title_pattern, imdbid=imdbid).fetchall()

    if movie_specific:
        return render_template("movie_specific.html",
movie_specific=movie_specific)
    else:
        return render_template("error2.html", message="No such movie.")

"""
I find this much cleaner to reason about
"""