flask search bar using postgres

Question

I'm doing a flask project and I want to enable a a search bar. Usually I would create a route but the search bar takes the url to ?query= and bypasses the route that I created. I tried to make a route similar to the one created by the search bar but didn't have success. I'm trying to implement this with postgresql using psycopg2.

@newsbeta.route("/newsbeta/<query>")
def get_query(query):
    cur.execute(f"SELECT * FROM test WHERE to_tsvector('english',title) @@ to_tsquery('english','{query}');")
    searchquery = []
    for i in range(10):
        searchquery.append(cur.fetchone())
        return render_template('search.html', title='News', searchquery=searchquery)

expected code is that when I write a query in the search bar it would fetch it from the postgres database and return a render template with the information. this works when I query something in the search bar then if I go to /newsbeta/query manually.

user @roy said something that could be the answer toward other question but I don't know how to change this said patter if someone else could enlighten me please. 'You'll have to change the pattern so that it redirects to a different view i.e. def show_results():. It's also the reason you aren't able to share a url i.e. example.com/search?query='some text' by @roy

This might not resolve your issue, but you probably want to take a look at using [parameterized queries](https://stackoverflow.com/questions/1466741/parameterized-queries-with-psycopg2-python-db-api-and-postgresql) instead of using formatted strings. — Scratch'N'Purr, May 31 '19 at 01:35
@Scratch'N'Purr thanks. any reason why should I prefer parameteried queries over formatted strings? is it better practice? — , May 31 '19 at 01:38
You don't run into the risk of someone hacking into your database with SQL injection — Scratch'N'Purr, May 31 '19 at 01:40

score 0 · Answer 1 · answered May 31 '19 at 17:33

@newsbeta.route("/search")
def get_query():
    query = request.args.get("search")
    cur.execute(f"SELECT * FROM test WHERE to_tsvector('english',title) @@ to_tsquery('english','{query}');")
    searchquery = []
    for i in range(10):
        searchquery.append(cur.fetchone())
    return render_template('search.html', title='News', searchquery=searchquery)

#set html 
form action="/search" methods=['GET'] name= "search" id="query"

flask search bar using postgres

1 Answers1