Deleting records from database using flask/mysqldb

Issue


I am trying to make a view, insert, update and delete on a single page. As for now, I am able to do a view and insert. However, when I am trying to delete records based on the id, however I cant seem to capture the id and thus my delete function was unable to run. When I tried to press the delete for a record, it just shows that url cant be found.

app.py:

from flask import Flask, render_template, request, redirect, url_for
from flask_mysqldb import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'myproject'


mysql = MySQL(app)

@app.route('/', methods=['GET', 'POST'])
def select():
   cur = mysql.connection.cursor()
   cur.execute("SELECT * FROM people")
   selectall = cur.fetchall()
   cur.close()
   return render_template('index.html', dbhtml = selectall)

@app.route('/insert', methods = ['GET','POST'])
def insert():
   name = request.form['name']
   email = request.form['email']

   cur = mysql.connection.cursor()
   cur.execute("INSERT INTO people (name, email) VALUES (%s, %s)", (name, email))

   mysql.connection.commit()
   return redirect('/')

@app.route('/delete/<int:id>', methods = ['GET','POST','DELETE'])
def delete(id):
   cur = mysql.connection.cursor()
   cur.execute("DELETE FROM people WHERE id=%s", (id))

   mysql.connection.commit()
   return redirect('/')

if __name__ == '__main__':
   app.run(debug=True)

index.html:

<form action="{{url_for('insert')}}" method="POST">

  <div class="form group">

    <label>Name:</label>
    <input type="text" class="form-control" name="name" required="1">

  </div>

  <div class="form group">

    <label>Email:</label>
    <input type="text" class="form-control" name="email" required="1">

  </div>

  <div class="form group">

    <button class="btn btn-primary" type="submit">Insert Data</button>

  </div>

</form>

<br><br><br>

  <table align="center">
    <tr align="center">
      <td>id:</td>
      <td>name:</td>
      <td>email:</td>
    </tr>
    {% for row in dbhtml %}
    <tr>
      <td>{{row[0]}}</td>
      <td>{{row[1]}}</td>
      <td>{{row[2]}}</td>
    </tr>
    <tr>
      <td><a href="/delete/{{id}}">Delete</a></td>
      <td><a href="#">Update</a></td>
    </tr>
    {% endfor %}
  </table>

Solution

you have to send the id, as you can see the id is row[0] not "id"

<td><a href="/delete/{{row[0]}}">Delete</a></td>

i dont know if this correct or not, i think delete method must be wrapped in a form

<form method="POST" action=..url_delete>
   <td><buttin type=submit>Delete</button></td>
<form>

and change the cur.execute to

 cur.execute("DELETE FROM people WHERE id={}".format(id))

Answered By – Eric Kong

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published