In the previous post you learned about how to Display Records in a PostgreSQL Flask Web Application. In this post we will proceed further to modify or delete the selected record from the displayed list of records.
We will use the code discussed in the last post and add two links after each displayed record. If a user clicks the Modify link, the selected record will be displayed in another page for update. If a user clicks the Delete link the selected record is deleted from the table.
For these two operations in Flask Web Application you must know UPDATE and DELETE SQL statements. Follow the following steps to implement updation and deletion of stored records in a Flask PostgreSQL web application.
Display all Records
The first is to Display the book data in the web page(display.html). The code for this also remains the same with only change that Modify and Delete links are added in the HTML page displaying book list.
The modified code is
{% extends "layout.html" %}
{% block title %}
User Dashboard!
{% endblock %}
{% block booklist %}
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">ISBN</th>
<th scope="col">Title</th>
<th scope="col">Author</th>
<th scope="col">Year</th>
<th scope="col">Action</th>
</tr>
</thead>
{% for book in books %}
<tr>
<th scope="row">{{book.bookid}}</th>
<td>{{book.isbn}}
</td>
<td>{{book.title}}
</td>
<td>{{book.author}}
</td>
<td>{{book.year}}
</td>
<td><b><a href="{{url_for('bookModify', bookid=book.bookid)}}">Modify</a></b>
<b><a href="{{url_for('bookDelete', bookid=book.bookid)}}">Delete</a></b>
</td>
</tr>
{% endfor %}
</table>
{% endblock %}Add Routes in application.py of Flask Web Application
The route for fetching book details from booklist table is the same as discussed in the last post. Other routes are to be added for displaying selected book in another page (HTML) for modification and deleting the selected book. So, these are the routes you need to add
@app.route("/display")
def display():
#select all books from books
books=db.execute("SELECT * FROM booklist order by bookid")
# render template to display the all books
return render_template("display.html", books=books)
@app.route("/books/modify/<int:bookid>")
def bookModify(bookid):
# select row from booklist table for bookid passed from list page
book=db.execute("SELECT * FROM booklist where bookid=:book_id",{"book_id":bookid}).fetchall()
#display data in modify page passing the tuple as parameter in render_template method
return render_template("modifyBook.html",book=book )
@app.route("/bookUpdate", methods=["POST"])
def bookUpdate():
#store values recieved from HTML form in local variables
bookid=request.form.get("bookid")
isbn=request.form.get("isbn")
title=request.form.get("title")
author=request.form.get("author")
year=request.form.get("year")
#create string update query with the values from form
strSQl= "update booklist set isbn='"+isbn+"', title='"+title+"',author='"+author+"', year=" +str(year)+ " where bookid="+str(bookid)
#Execute update query
db.execute(strSQl)
#commit to database
db.commit()
return render_template("display.html")
@app.route("/bookDelete/<int:bookid>")
def bookDelete(bookid):
#create delete query as string
strSQL="delete from booklist where bookid="+str(bookid)
#execute delete query
db.execute(strSQL)
#commit to database
db.commit()
return render_template("display.html")Create the Page to Display Selected Book Details for Update
An HTML file named modifyBook.html is created and saved in the templates folder of your web application folder. This page displays details of selected book along with a submit button. When a user clicks this buttons bookUpdate route is invoked and updated values are passed through POST method. The UPDATE query is created with these values and the book details are updated in table.
{% extends "layout_reg.html" %}
{% block title %}
User Dashboard!
{% endblock %}
{% block body %}
<form action="{{ url_for('bookUpdate')}}" method="post">
<h5>Update Book Details</h5>
{% for bk in book %}
<div class="form-group">
<input class="form-control" type="hidden" name="bookid" placeholder="Book ID" value={{bk[0]}} >
</div>
<div class="form-group">
<input class="form-control" type="text" name="isbn" placeholder="ISBN" value={{bk[1]}}>
</div>
<div class="form-group">
<input class="form-control" type="text" name="title" placeholder="Title" value={{bk[2]}}>
</div>
<div class="form-group">
<input class="form-control" type="text" name="author" placeholder="Author" value={{bk[3]}}>
</div>
<div class="form-group">
<input class="form-control" type="text" name="year" placeholder="Year" value={{bk[4]}}>
</div>
<div class="form-group">
<button class="btn btn-primary">Submit</button>
</div>
{% endfor %}
</form>
{% endblock %}
</div>
</div>
We have used the raw SQL statements in Flask Web Application to perform the update and delete operations. This method of creating a web application has great possibility of insecure data. This method is discussed here to let you understand the basic operations. In the upcoming posts we will use Modeling and Migrations to utilize full benefit of creating secure web applications using FLASK and SQLAlchemy.
Be First to Comment