Select one record from database Flask/Sqlite

Question

i'm using a function in python/flask to delete some records on my database. the only problem i have is that i only can delete records with an id from 1 to 9. If i try to delete a record with an id higher than 9 i get the error:

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

Flask code:

@app.route('/change-teacher', methods = ['GET', 'POST'])
def changeTeacher():
    teacherId   = request.form['id']
    teachers    = selectFromDatabaseWithVar("SELECT * FROM leraren WHERE id = ?", teacherId)
    teacherData = [dict(id = row[0], naam = row[1], voornaam = row[2], foto = row[3], email = row[4]) for row in teachers]
    return render_template("leraarAanpassen.html", teacherData = teacherData)

@app.route('/change-teacher/action', methods = ['GET', 'POST'])
def changeTeacherAction():
    teacherData = (request.form['name'], request.form['firstName'], request.form['email'], request.form['id'])
    insertAndUpdateDatabase("UPDATE leraren SET naam = ?, voornaam = ?, email = ? WHERE id = ?", teacherData)
    return redirect(url_for("teachers"))

@app.route('/delete-teacher', methods = ['GET', 'POST'])
def deleteTeacher():
    teacherId = request.form['id']
    insertAndUpdateDatabase("DELETE FROM leraren WHERE id = ?", teacherId)
    return redirect(url_for("teachers"))

template:

{% include "dashboard.html" %}
{% block content %}

<table>
<tr>
    <th>ID</th>
    <th>NAAM</th>
    <th>VOORNAAM</th>
    <th>FOTO</th>
    <th>EMAIL</th>
    <th>EDIT</th>
    <th>DELETE</th>
</tr>

{% for leraar in leraren %}
<tr>
    <td>{{ leraar.id }}</td>
    <td>{{ leraar.naam }}</td>
    <td>{{ leraar.voornaam }}</td>
    <td>{{ leraar.foto }}</td>
    <td>{{ leraar.email }}</td>
    <td>
        <form method="POST" action="/change-teacher">
            <button type="submit" name="id" value="{{ leraar.id }}">
                <img src="{{ url_for('static', filename='img/settings.png') }}">
            </button>
        </form>
    </td>
    <td>
        <form method="POST" action="/delete-teacher">
            <button type="submit" name="id" value="{{ leraar.id }}">
                <img src="{{ url_for('static', filename='img/trash.png') }}">
            </button>
        </form>
    </td>
</tr>
{% endfor %}
</table>

<a href="/leraartoevoegen"><input type="button" name="addRecord" class="newRecord" value="Nieuwe record toevoegen"></a>

{% endblock %}

my delete function:

def insertAndUpdateDatabase(query, data):
    db  = sqlite3.connect('schooldatabase.db')
    cur = db.cursor()
    cur.execute(query, data)
    db.commit()

anserk · Accepted Answer · 2017-01-09 20:26:30Z

2

I think the execute method is expecting a tuple or a dictionary. If you change your call to execute in the insertAndUpdateDatabase from

cur.execute(query, data)

to

cur.execute(query, (data,))

your code should work.

answered Jan 9, 2017 at 20:26

anserk

1,3381 gold badge9 silver badges16 bronze badges

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

Comments

Lian · Accepted Answer · 2017-01-10 06:52:29Z

0

For manage the database I use the flask extension: flask_sqlalchemy, it makes very to insert, modify and delete records in a database. For Example:

Supose you a table Teacher define in a models module with name and age as columns. To delete a record you simply have to do the next:

to_delete = Teacher.query.filter_by(name="<the-one-you-want-delete>").first()
db.session.delete(to_delete)
db.session.commit()

And that'll be all, db is an instance of SQLAlchemy, you can create it like this:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)

For more detail I recommend the book "Flask Web Development" of Miguel Grinberg

answered Jan 10, 2017 at 6:52

Lian

11 bronze badge

Collectives™ on Stack Overflow

Select one record from database Flask/Sqlite

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related