How to implement a search function using a raw SQL query

Question

I am creating an app guided by CS50's web series, which requires me to ONLY use raw SQL queries not ORM.

I am trying to make a search function where a user can look up the list of books that's stored in the database. I want to able them to query ISBN, title, author column in the table called 'books'

Currently, it does shoot a 'GET' request with no problem but it is not returning any data and I think the problem is at the SQL line I've scripted.

Here's the route:

@app.route("/", methods=['GET','POST'])
def index():
    # search function for books
    if request.method == "GET":
        searchQuery = request.form.get("searchQuery")
        # return value from the search
        searchResult = db.execute("SELECT isbn, author, title FROM books WHERE isbn LIKE '%"+searchQuery+"%' OR author LIKE '%"+searchQuery+"%' OR title LIKE '%"+searchQuery+"%'").fetchall()
        # add search result to the list
        session["books"] = []
        # add the each result to the list
        for i in searchResult:
            session["books"].append(i)
        return render_template("index.html", books=session["books"])
    return render_template("index.html")

and here's my template.

    <form method="GET">
        <input type="text" name="searchQuery" class="searchTerm" placeholder="What are you looking for?">
        <button type="submit" class="searchButton">submit</button>
    </form>
    <div>
        <h3>
            {% for book in books %}
            {{ book }}
            {% endfor %}
        </h3>
    </div>

Can anyone spot the problem, please? Please note that I am supposed to utilize the raw SQL queries and session.

Are you using SQLite? where did you open db and cursor object? What happens when you print the searchResult ? — Vignesh SP
– Vignesh SP, Commented Jul 19, 2019 at 23:06
shorter session["books"] = searchResult. Or even shorter books=searchResult — furas
– furas, Commented Jul 19, 2019 at 23:14
print query on screen and use it directly in database (using any DB viewer/editor) to see if you get any results for this query. You can also check len(searchResult) — furas
– furas, Commented Jul 19, 2019 at 23:17
yes, it can be Adminer, or DBbeaver, HeidiSQL, etc. But every database have also preinstalled program to access it in console/terminal/cmd.exe. MySQL has program mysql, SQLite has program sqlite3, etc. so you don't have to install external programs but they work in text mode so they are not so user friendly like GUI programs. — furas
– furas, Commented Jul 20, 2019 at 0:17

Researcher · Accepted Answer · 2019-07-20 02:31:44Z

2

I created a github with full solution for you :)

https://github.com/researcher2/stackoverflow_57120430

A couple of things:

Avoiding SQL Injection

I recommend using bindings when doing raw sql statements, my code reflects that. I spent ages trying to get this working with your statement before stumbling upon this:

Python SQLite parameter substitution with wildcards in LIKE

Basically you can't put bindings inside the LIKE '%?%' because the quotes cause the replacement token to be ignored.

Instead you just have to do LIKE ? and build the replacement manually.

Using Session

All session information is JSON serialized and then sent to the client. In this case the row records weren't JSON serializable. This showed up as an error for me:

TypeError: Object of type 'RowProxy' is not JSON serializable

I probably wouldn't use the session here as there is no need for the client to be aware of this, as you're going to build them a nice html page with the information anyway. Just use a python dictionary and pass it to the template engine. My code did use the session because this is what you started with.

In case github ever goes down:

from flask import request, render_template, session
from app import app, db

@app.route("/", methods=['GET','POST'])
def index():
    if request.method == "POST":
        searchQuery = request.form.get("searchQuery")
        print(searchQuery)

        # Avoid SQL Injection Using Bindings
        sql = "SELECT isbn, author, title \
               FROM book \
               WHERE isbn LIKE :x \
               OR author LIKE :y \
               OR title LIKE :z"

        # I spent an hour wondering why I couldnt put the bindings inside the wildcard string...
        # https://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like
        matchString = "%{}%".format(searchQuery)

        stmt = db.text(sql).bindparams(x=matchString, y=matchString, z=matchString)

        results = db.session.execute(stmt).fetchall()
        print(results)

        session["books"] = []

        for row in results:
            # A row is not JSON serializable so we pull out the pieces
            book = dict()
            book["isbn"] = row[0]
            book["author"] = row[1]
            book["title"] = row[2]
            session["books"].append(book)
        return render_template("index.html", searchedFor=searchQuery, books=session["books"])

    return render_template("index.html")

answered Jul 20, 2019 at 2:31

Researcher

1,0468 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JasonTheMarketer Over a year ago

you went above and beyond for the question i had. thanks so much. I honestly do not understand %100 of it but it's going to be a fantastic studying material.

Researcher Over a year ago

You're welcome, would you believe I find this fun. Let me know if you have any questions about the repo. I would imagine you haven't seen requirements file and the sqlalchemy orm stuff yet?

Ilja Everilä Over a year ago

book = dict(row) would suffice for the serialization.

Ilja Everilä · Accepted Answer · 2019-11-22 07:24:02Z

1

Thanks for the chosen answer. The problem was solved with one simple change.

I needed to swap 'LIKE' to 'ILIKE' in the SQL query line.

searchResult = db.execute(
    """
    SELECT isbn, author, title
    FROM books
    WHERE isbn ILIKE :search OR author ILIKE :search OR title ILIKE :search
    """,
    {"search": "%"+searchQuery+"%"}
).fetchall()

This may not be an elegant solution but it did solve a temporary problem. Again, kudos for the guy who answered and create a github repo for everyone else who would encounter the problem while doing CS50's web series!

edited Nov 22, 2019 at 7:24

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

answered Jul 20, 2019 at 2:47

JasonTheMarketer

1972 gold badges8 silver badges16 bronze badges

2 Comments

Researcher Over a year ago

Nice work. Did I misread and you aren't using flask sessions, just a variable called session?

JasonTheMarketer Over a year ago

@Researcher I am actually using it. from flask import session

Collectives™ on Stack Overflow

How to implement a search function using a raw SQL query

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related