Colon notation and wildcard conflict

[Cravan]

Hi all,

                I recently tried to create a search function which is supposed to display all possible results in a table even if the title is incomplete. However, I am using python variables and hence have to use colon notation to sub it into the sql command, however I also learnt about wildcards. How then do I use both of them without resulting in a conflict or error? My code goes something like this:

 

````

@app.route("/movie_results") #im using flask

def movie_results():

    name = request.args.get("movie.title")

    search_movie_statement = sqlalchemy.text('SELECT * FROM movies WHERE title LIKE :movie_title')

    movie_specific = engine.execute(search_movie_statement, movie_title=name).fetchall()

    if movie_specific is not None:

        print(name)

        return render_template("movie_specific.html", movie_specific=movie_specific, name=name)

    if movie_specific is None:

        return render_template("error2.html", message="No such movie.")

````

 

Thanks,

Cravan

 

[Simon King]

Are you saying that you want to surround the name that the user tried
to search for with wildcards? If so, how about this:

name = request.args.get("movie.title")

name_pattern = "%" + name + "%"

search_movie_statement = sqlalchemy.text('SELECT * FROM movies
WHERE title LIKE :movie_title')
movie_specific = engine.execute(search_movie_statement,

movie_title=name_pattern).fetchall()

Hope that helps,

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5D3E94C9-50D3-4B5C-B722-B9C3FFE85293%40gmail.com.
> For more options, visit https://groups.google.com/d/optout.

[Cravan]

Thanks Simon, this works for most cases. However, it still does not work in some cases for example if I want Avengers:Endgame and Avengers: Infinity War to come out if I type avengers(lower_cased). How should I rectify this?
Cheers,
Cravan

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexd-5KgwfbQhDpoomUo%2BGXD62BuueVQW%3DwGYTn94jO1g%3DQ%40mail.gmail.com.

[Simon King]

I think you're using postgresql, so you can use ILIKE instead of LIKE
to do a case-insensitive search.

Simon

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/16787B03-1BF0-44D4-9EB0-E2CDEF77943B%40gmail.com.

[Cravan]

Dear Simon,
Thanks for your quick reply and help! It works now.
Cravan

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcJjUy0f6UAk7YWUu_1Riswfo7bnyz0DHjMxTaLPGM59Q%40mail.gmail.com.