如何使用原始SQL查询实现搜索功能 [英] How to implement a search function using a raw SQL query

问题描述

我正在创建一个由CS50的网络系列指导的应用程序，它要求我仅使用原始SQL查询而不是ORM.

我正在尝试提供一种搜索功能，用户可以在其中查找存储在数据库中的书籍列表.我想让他们查询名为"books"的表中的ISBN，标题，作者列

当前，它确实拍摄了"GET"请求，没有问题，但是它没有返回任何数据，我认为问题出在我编写的SQL行中.

这是路线:

 @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")
 

这是我的模板.

     <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>
 

有人可以发现问题吗?请注意，我应该利用原始SQL查询和会话.

解决方案

我创建了一个为您提供完整解决方案的github :)

https://github.com/researcher2/stackoverflow_57120430

几件事:

避免SQL注入

我建议在执行原始sql语句时使用绑定，我的代码反映了这一点.我花了很长时间尝试使它与您的陈述相符，然后才绊倒:

>用LIKE中的通配符替换Python SQLite参数

基本上，您不能将绑定放在LIKE'％?％'内，因为引号会导致替换令牌被忽略.

相反，您只需要执行LIKE?并手动构建替代产品.

使用会话

所有会话信息都是JSON序列化的，然后发送到客户端.在这种情况下，行记录不是JSON可序列化的.这对我来说是一个错误:

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

我可能不会在这里使用会话，因为客户端不需要知道这一点，因为无论如何您将使用信息构建一个漂亮的html页面.只需使用python字典并将其传递给模板引擎即可.我的代码确实使用了会话，因为这是您开始的.

如果github掉线了:

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")

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.

解决方案

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")

这篇关于如何使用原始SQL查询实现搜索功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！