How to fix error message (sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.)

Question

I'm trying to create a website that filters out suburbs using data from 4 different SQL tables (dictionary is attached)

However, this error message keeps popping up;

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.)

How can I fix this?

Python code:

from flask import Flask, render_template, request
import sqlite3

app = Flask(__name__)

@app.route('/')
def home():
    return render_template('home.html')

@app.route('/result', methods=['POST'])
def result():
    
    parks = request.form.get('parks')
    wifi = request.form.get('wifi')
    libraries = request.form.get('libraries')
    schools = request.form.get('schools')

    conn = sqlite3.connect('suburb.db')
    c = conn.cursor()

    c.execute("SELECT suburb FROM park WHERE EXISTS (SELECT suburb FROM library WHERE library.suburb =park.SUBURB ) =?", (parks,))
    park_subs = [row[0] for row in c.fetchall()]

    c.execute("SELECT suburb FROM wifi WHERE EXISTS (SELECT suburb FROM library WHERE library.suburb =wifi.suburb)", (wifi,))
    wifi_subs = [row[0] for row in c.fetchall()]

    c.execute("SELECT suburb FROM library", (libraries,))
    library_subs = [row[0] for row in c.fetchall()]

    c.execute("SELECT suburb FROM school WHERE EXISTS (SELECT suburb FROM library WHERE library.suburb =school.suburb)?", (schools,))
    school_subs = [row[0] for row in c.fetchall()]

    conn.close()
    
    
    
    result_subs = set(park_subs) & set(wifi_subs) & set(library_subs) & set(school_subs)
    return render_template('result.html', subs=result_subs)

if __name__ == '__main__':
    app.run

HTML:

<!DOCTYPE html>
<html>
<head>
    <title>Real Estate Filtering Website</title>
    <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
    <h1>Real Estate Filtering Website</h1>
    <form method="POST" action="{{ url_for('result') }}">
        <h2>Filter by:</h2>
        <div class="filter">
            <input type="checkbox" name="parks" id="parks" value="yes">
            <label for="parks">Parks</label>
        </div>
        <div class="filter">
            <input type="checkbox" name="wifi" id="wifi" value="yes">
            <label for="wifi">Wifi Hotspots</label>
        </div>
        <div class="filter">
            <input type="checkbox" name="libraries" id="libraries" value="yes">
            <label for="libraries">Libraries</label>
        </div>
        <div class="filter">
            <input type="checkbox" name="schools" id="schools" value="yes">
            <label for="schools">Schools</label>
        </div>
        <input type="submit" value="Filter">
    </form>

    <div style="overflow-x:auto;">
        <table>
          <tr>
            <th>Suburb ID</th>
            <th>Suburb</th>
            <th> 
              {% for record in records %}  <!-- record is the list created from the python code. The for loop iterates through each record (row) -->
              <tr>
              {% for value in record %}    <!-- loop iterates through each value in the record list and generates a table cell for each peice of data -->
                  <td>{{ value }}</td>
              {% endfor %}
              </tr>
              {% endfor %}
              </table>
              </div>
</body>
</html>

I've tried completely reprogramming it but, have had difficulties making an alternative system

score 0 · Answer 1 · answered Mar 02 '23 at 10:14

In this line

c.execute(
    "SELECT suburb FROM wifi WHERE EXISTS (SELECT suburb FROM library WHERE library.suburb =wifi.suburb)",
    (wifi,)
)

you are passing a value (wifi,), but there is no placeholder (?) in the query text. The error message is alerting you to the mismatch. To fix the error, remove the value:

c.execute(
    "SELECT suburb FROM wifi WHERE EXISTS (SELECT suburb FROM library WHERE library.suburb =wifi.suburb)"
)

score -2 · Answer 2 · answered Mar 01 '23 at 09:57

The error in the above code is that the parameters of the SQL queries are not passed correctly. Instead of passing parameters as the second argument of the execute() method, they should be passed as the first argument and the query should be passed as the second argument.

For example, the first query should be changed from:

c.execute("SELECT suburb FROM park WHERE EXISTS (SELECT suburb FROM library WHERE library.suburb =park.SUBURB ) =?", (parks,))

To:

c.execute(query="SELECT suburb FROM park WHERE EXISTS (SELECT suburb FROM library WHERE library.suburb =park.SUBURB ) =?", params=(parks,))

How to fix error message (sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.)

2 Answers2