Search function in Flask with SQLite-CodePudding

I have a problem in my search function. The search result was not displayed in my web application. I'm trying to search words based on the brand or model. I have no idea what did I make mistake.

This is the backend in 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)

This is the search form

<form action="/search" method="GET">
            <div >
                <!-- Search -->
                <div >
                    <input  placeholder="Search for smartphone" name="search" value="" />
                    <button type="submit" >Search</button>
                </div>
            </div>
</form>

This is the output result

<div >
            {% for smartphone in searchsmartphones %}
            <div >
                <div  style="width: 20rem;">
                    <a href="#">
                        <img src="{{ url_for('static',filename = smartphone['image_URL']) }}"  style="height: 250px;">
                    </a>
                    <div >
                        <a href="#">
                            <h5 >{{ smartphone['model'] }}</h5>
                        </a>
                        <div >
                            <p >{{ smartphone['brand'] }}</p>
                            <p >{{ smartphone['lowprice'] }} - {{ smartphone['highprice'] }}</p>
                        </div>
                    </div>
                </div>
            </div>
            <div >
            </div>
            {% endfor %}
        </div>

Traceback (most recent call last):
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 2525, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 1822, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 1820, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\user\desktop\phonebuddy\.venv\lib\site-packages\flask\app.py", line 1796, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "C:\Users\user\Desktop\PhoneBuddy\app.py", line 94, in search
    searchsmartphones = conn.execute("SELECT * FROM Smartphone WHERE brand LIKE '%?%' OR model LIKE '%?%'",(result,result))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 2 supplied.
127.0.0.1 - - [27/Nov/2022 22:15:14] "GET /search?search=Apple&sort=low HTTP/1.1" 500 -

Empty when using my original code

CodePudding user response：

The error indicates that we did not pass enough parameters to the select query. I found that we cannot use %?% directly.

Example of using Flask and Sqlite3 for search query

I reproduced the scenario with dummy data and dummy SQL schema. The SQL schema and dummy query insertion happens at the first view of the root / path. The search path is defined in /search route.

File structure:

.
├── 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 >
        <!-- Search -->
        <div >
            <input 
                   placeholder="Search for smartphone" name="search_brand_model" value=""/>
            <button type="submit" >Search
            </button>
        </div>
    </div>
</form>
{% if searchsmartphones %}
<div >
    {% for smartphone in searchsmartphones %}
    <div >
        <div  style="width: 20rem;">
            <div >
                <a href="#">
                    <h5 >{{ smartphone['model'] }}</h5>
                </a>
                <div >
                    <p >{{ smartphone['brand'] }}</p>
                    <p >{{ smartphone['lowprice'] }}</p>
                </div>
            </div>
        </div>
    </div>
    <div >
    </div>
    {% endfor %}
</div>
{% endif %}
</body>
</html>

Screenshots:

Initial database rows:

Search using Brand Nokia:

Search using model n95: