使用SQLite在 flask 中搜索功能

我的搜索功能出现问题。搜索结果没有显示在我的Web应用程序中。我尝试根据品牌或型号搜索字词。我不知道我犯了什么错误。

这是www.example.com中的后端 app.py

@app.route('/search',methods = ['GET','POST'])
def search():
    result = request.form.get('search')
    conn = get_db_connection()
    searchsmartphones = conn.execute("SELECT * FROM Smartphone WHERE brand OR model = ?",(result,))
    conn.commit()
    return render_template('smartphone.html',searchsmartphones = searchsmartphones)

这是搜索表单

<form action="/search" method="GET">
            <div class="mb-5 d-flex position-relative">
                <!-- Search -->
                <div class="input-group w-50 mx-auto">
                    <input class="form-control py-2" placeholder="Search for smartphone" name="search" value="" />
                    <button type="submit" class="btn btn-secondary text-muted">Search</button>
                </div>
            </div>
</form>

这是输出结果

第一次
使用原始代码时为空

这个错误表明我们没有传递足够的参数给选择查询。我发现我们不能直接使用%?%。

使用Flask和Sqlite3进行搜索查询的示例

我用虚拟数据和虚拟SQL模式重现了这个场景。SQL模式和虚拟查询插入发生在根/路径的第一个视图中。搜索路径定义在/search route中。
文件结构：

.
├── app.py
├── mobile_devices.db
├── schema.sql
├── templates
│   ├── home.html
│   └── search.html

schema.sql：

CREATE TABLE IF NOT EXISTS Smartphone (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    brand TEXT NOT NULL,
    model TEXT NOT NULL,
    lowprice DOUBLE NOT NULL
);

app.py：

from flask import Flask, render_template, g, request
import sqlite3

DATABASE = 'mobile_devices.db'
app = Flask(__name__)
app.config['SECRET_KEY'] = 'your secret key'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv

def insert_db(query, args=()):
    msg = ""
    with app.app_context():
        try:
            db = get_db()
            cur = db.cursor()
            cur.execute(query, args)
            db.commit()
            msg = "Insertion successful"
        except Exception as ex:
            msg = f"Insertion failed: {str(ex)}"
        finally:
            print(msg)

def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()

init_db()

def insert_dummy_values():
    insert_db(
        "INSERT INTO Smartphone (brand, model, lowprice) VALUES (?,?,?)",
        ("Nokia", "C6", 150))
    insert_db(
        "INSERT INTO Smartphone (brand, model, lowprice) VALUES (?,?,?)",
        ("Samsung", "Fold", 250))
    insert_db(
        "INSERT INTO Smartphone (brand, model, lowprice) VALUES (?,?,?)",
        ("Nokia", "N95", 300))
    insert_db(
        "INSERT INTO Smartphone (brand, model, lowprice) VALUES (?,?,?)",
        ("Sony", "Samsung", 1250))

@app.route('/')
def show_home():
    smart_phones = query_db('select brand, model, lowprice from Smartphone')
    if len(smart_phones) == 0:
        insert_dummy_values()
    smart_phones = query_db('select brand, model, lowprice from Smartphone')
    return render_template('home.html', smart_phones=smart_phones)

@app.route('/search', methods=['GET', 'POST'])
def search():
    if request.method == "POST":
        search_value = request.form.get('search_brand_model')
        print(search_value)
        smart_phones = query_db(
            "SELECT * FROM Smartphone WHERE brand LIKE ? OR model LIKE ?",
            ('%' + search_value + '%', '%' + search_value + '%'))
        return render_template('search.html', searchsmartphones=smart_phones)
    else:
        return render_template('search.html')

templates/home.html：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Home</title>
</head>
<body>
{% if smart_phones%}
Total smartphones in DB: {{ smart_phones | length }}
<ul>
    {% for smartphone in smart_phones %}

    <li> {{ smartphone['brand'] }} {{ smartphone['model'] }} :
        ${{ smartphone['lowprice'] }}
    </li>
    {% endfor %}
</ul>
{% else %}
<p>No smartphone in db</p>
{% endif %}
</body>
</html>

templates/search.html：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Search</title>
</head>
<body>
<form action="/search" method="POST">
    <div class="mb-5 d-flex position-relative">
        <!-- Search -->
        <div class="input-group w-50 mx-auto">
            <input class="form-control py-2"
                   placeholder="Search for smartphone" name="search_brand_model" value=""/>
            <button type="submit" class="btn btn-secondary text-muted">Search
            </button>
        </div>
    </div>
</form>
{% if searchsmartphones %}
<div class="d-flex">
    {% for smartphone in searchsmartphones %}
    <div class="row row-cols-4 mb-5">
        <div class="card mr-5" style="width: 20rem;">
            <div class="card-body">
                <a href="#">
                    <h5 class="card-title">{{ smartphone['model'] }}</h5>
                </a>
                <div class="mt-5 d-flex justify-content-between">
                    <p class="text-muted">{{ smartphone['brand'] }}</p>
                    <p class="fw-bold">{{ smartphone['lowprice'] }}</p>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-auto">
    </div>
    {% endfor %}
</div>
{% endif %}
</body>
</html>

截图：

初始数据库行：

使用品牌Nokia进行搜索：

使用型号n95搜索：

使用SQLite在 flask 中搜索功能

1条答案

使用Flask和Sqlite3进行搜索查询的示例

相关问题

热门标签

最新问答