Flask blog app tutorial 4 : Update / Delete
In the previous part of this series, we implemented the feature of adding blog posts.
In this part of the series, we'll let user edit or delete a blog post.
Here are the files we'll be using in this tutorial part-4:
They are available from FlaskApp/p4
We did bind the received data using jQuery to our HTML. Now, we're going to modify the code and use jQuery templates to make it easier to bind data.
Additionally, we'll add an edit icon to update the post.
Let's include a reference to jQuery templates in userHome.html:
<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.templates/beta1/jquery.tmpl.js"></script>
Add the following line in the body of the HTML. We'll be binding our data inside the ul with class list-group:
<div class="row"> <div class="col-md-12"> <div class="panel-body"> <ul id="ulist" class="list-group"> </ul> </div> </div> </div>
Let's define a listTemplate in the body of the userHome.html:
<script id="listTemplate" type="text/x-jQuery-tmpl"> <li class="list-group-item"> <div class="checkbox"> <label> ${Title} </label> </div> <div class="pull-right action-buttons"> <a data-toggle="modal" data-target="#editModal"><span class="glyphicon glyphicon-pencil"></span></a> </div> </li> </script>
Let's modify the jQuery AJAX success callback to bind the data to the listTemplate:
<script> $(function() { $.ajax({ url: '/getBlog', type: 'GET', success: function(res) { // Parse the JSON response var blogObj = JSON.parse(res); // Append to the template $('#listTemplate').tmpl(blogObj).appendTo('#ulist'); }, error: function(error) { console.log(error); } }); }); </script>
Also, we want to include styles at the end of the within 'head' block in userHome.html as shown below:
<style> .trash { color: rgb(209, 91, 71); } .panel-body .checkbox { display: inline-block; margin: 0px; } .list-group { margin-bottom: 0px; } </style>
In order to show a popup to the user who wants to edit the posts, we're going to use Bootstrap. SO, let's include a reference to it in userHome.html:
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
Now we need to work on userHome.html to serve a popup which should appear as the user clicks the edit icon. Let's add the following line of code to the file:
<div class="modal fade" id="editModal" tabindex="-1" role="dialog" aria-labelledby="editModalLabel" aria-hidden="true"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button> <h4 class="modal-title" id="editModalLabel">Update Blog</h4> </div> <div class="modal-body"> <form role="form"> <div class="form-group"> <label for="recipient-name" class="control-label">Title:</label> <input type="text" class="form-control" id="editTitle"> </div> <div class="form-group"> <label for="message-text" class="control-label">Description:</label> <textarea class="form-control" id="editDescription"></textarea> </div> </form> </div> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">Close</button> <button id="btnUpdate" type="button" class="btn btn-primary">Update</button> </div> </div> </div> </div>
Note that in our JQuery templates we have already added the attributes data-target and data-toggle which will trigger the modal popup:
<a data-toggle="modal" data-target="#editModal"><span class="glyphicon glyphicon-pencil"></span></a>
After signed in, click on the edit icon and we should be able to see the popup:
When the user clicks the edit icon, the user may want to update popup with the title and description.
To implement the feature, we need the blog ID to fetch the particular post details once the user clicks the edit icon.
So, let's modify the jQuery template code to include an extra attribute data-id on the edit anchor element. We're attaching an onclick event to call the method Edit:
<script id="listTemplate" type="text/x-jQuery-tmpl"> <li class="list-group-item"> <div class="checkbox"> <label> ${Title} </label> </div> <div class="pull-right action-buttons"> <!-- <a data-toggle="modal" data-target="#editModal"><span class="glyphicon glyphicon-pencil"></span></a> --> <a data-id=${Id} onclick="Edit(this)" ><span class="glyphicon glyphicon-pencil"></span></a> </div> </li> </script>
In the code below, inside the Edit() function, we make an AJAX call to a python method called getBlogById which returns the blog details:
function Edit(elm) { $.ajax({ url: '/getBlogById', data: { id: $(elm).attr('data-id') }, type: 'POST', success: function(res) { console.log(res); }, error: function(error) { console.log(error); } }); }
Now we want to get the particular blog post details from the database. So, let's create a method called getBlogById() in app.py:
@app.route('/getBlogById',methods=['POST']) def getBlogById(): try: if session.get('user'): _id = request.form['id'] _user = session.get('user') conn = mysql.connect() cursor = conn.cursor() cursor.callproc('sp_GetBlogById',(_id,_user)) result = cursor.fetchall() blog = [] blog.append({'Id':result[0][0],'Title':result[0][1],'Description':result[0][2]}) return json.dumps(blog) else: return render_template('error.html', error = 'Unauthorized Access') except Exception as e: return render_template('error.html',error = str(e))
Note that in the code above we pass in the blog ID to this method and it gets the data from the database using the user ID and blog ID. Once the data has been fetched, it converts that data into a list and then returns it as JSON data.
Let's create the required MySQL stored procedure to fetch data from the database:
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetBlogById`( IN p_blog_id bigint, In p_user_id bigint ) BEGIN select * from tbl_blog where blog_id = p_blog_id and blog_user_id = p_user_id; END$$ DELIMITER ;
Let's check the procedures:
mysql> SHOW PROCEDURE STATUS WHERE db = 'FlaskBlogApp'; +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | FlaskBlogApp | sp_addBlog | PROCEDURE | root@localhost | 2016-12-03 17:00:03 | 2016-12-03 17:00:03 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_createUser | PROCEDURE | root@localhost | 2016-12-02 21:50:34 | 2016-12-02 21:50:34 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogById | PROCEDURE | root@localhost | 2016-12-04 13:45:30 | 2016-12-04 13:45:30 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogByUser | PROCEDURE | root@localhost | 2016-12-03 23:47:43 | 2016-12-03 23:47:43 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_validateLogin | PROCEDURE | root@localhost | 2016-12-03 11:09:38 | 2016-12-03 11:09:38 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 5 rows in set (0.66 sec)
Let's restart the app, once logged in, if we press the "Edit" icon, we can see the received data on the console of the browser:
$ python app.py * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit) "GET / HTTP/1.1" 200 - "GET /showSignin HTTP/1.1" 200 - "GET /getBlog HTTP/1.1" 200 - "GET /logout HTTP/1.1" 302 - "GET / HTTP/1.1" 200 - "GET /showSignin HTTP/1.1" 200 - "POST /validateLogin HTTP/1.1" 302 - "GET /userHome HTTP/1.1" 200 - "GET /getBlog HTTP/1.1" 200 - result= ((4, u'Curiosity has its own reason for existing', u'The important thing is not to stop questioning. Curiosity has its own reason for existing.', 1, datetime.datetime(2016, 12, 4, 11, 37, 19)),) 127.0.0.1 - - [04/Dec/2016 14:24:15] "POST /getBlogById HTTP/1.1" 200 -
To bind the received data to the Edit popup, we need to remove the data-target and data-toggle attributes from the edit icon anchor tag:
<div class="pull-right action-buttons"> <!--<a data-toggle="modal" data-target="#editModal"><span class="glyphicon glyphicon-pencil"></span></a>--> <a data-id=${Id} onclick="Edit(this)"><span class="glyphicon glyphicon-pencil"></span></a> </div>
Then, we should add the following code to the Edit() JavaScript function success callback to populate the popup and trigger it:
function Edit(elm) { $.ajax({ url: '/getBlogById', data: { id: $(elm).attr('data-id') }, type: 'POST', success: function(res) { // Parse the received JSON string var data = JSON.parse(res); //Populate the Pop up $('#editTitle').val(data[0]['Title']); $('#editDescription').val(data[0]['Description']); // Trigger the Pop Up $('#editModal').modal(); }, error: function(error) { console.log(error); } }); }
Once signed in, if we click the Edit icon, we should have the popup with the title and description:
To implement the update functionality, once again we need to create a MySQL stored procedure, sp_updateBlog which passes in the modified title and description along with the IDs of the blog and the user to update the details in the database:
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_updateBlog`( IN p_title varchar(45), IN p_description varchar(1000), IN p_blog_id bigint, In p_user_id bigint ) BEGIN update tbl_blog set blog_title = p_title,blog_description = p_description where blog_id = p_blog_id and blog_user_id = p_user_id; END$$ DELIMITER ;
mysql> SHOW PROCEDURE STATUS WHERE db = 'FlaskBlogApp'; +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | FlaskBlogApp | sp_addBlog | PROCEDURE | root@localhost | 2016-12-03 17:00:03 | 2016-12-03 17:00:03 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_createUser | PROCEDURE | root@localhost | 2016-12-02 21:50:34 | 2016-12-02 21:50:34 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogById | PROCEDURE | root@localhost | 2016-12-04 13:45:30 | 2016-12-04 13:45:30 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogByUser | PROCEDURE | root@localhost | 2016-12-03 23:47:43 | 2016-12-03 23:47:43 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_updateBlog | PROCEDURE | root@localhost | 2016-12-04 15:58:41 | 2016-12-04 15:58:41 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_validateLogin | PROCEDURE | root@localhost | 2016-12-03 11:09:38 | 2016-12-03 11:09:38 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 6 rows in set (0.01 sec)
We're almost there!
Let's create a new method called updateBlog() in app.py to update the details:
@app.route('/updateBlog', methods=['POST']) def updateBlog(): try: if session.get('user'): _user = session.get('user') _title = request.form['title'] _description = request.form['description'] _blog_id = request.form['id'] conn = mysql.connect() cursor = conn.cursor() cursor.callproc('sp_updateBlog',(_title,_description,_blog_id,_user)) data = cursor.fetchall() if len(data) is 0: conn.commit() return json.dumps({'status':'OK'}) else: return json.dumps({'status':'ERROR'}) except Exception as e: return json.dumps({'status':'Unauthorized access'}) finally: cursor.close() conn.close()
As we can see from the code above, after validating for a valid session, we collect the posted data and call the stored procedure sp_updateBlog to update the details.
In order to call the updateBlog() method, we need to attach an event on the Update button click. So, name the update button btnUpdate and attach an click() event as shown below:
$(function(){ $('#btnUpdate').click(function(){ $.ajax({ url : '/updateBlog', data : {title:$('#editTitle').val(),description:$('#editDescription').val(),id:localStorage.getItem('editId')}, type : 'POST', success: function(res){ $('#editModal').modal('hide'); GetBlogs(); }, error: function(error){ console.log(error); } }); }); });
Note that we call the GetBlogs() function in the success callback of the update AJAX call.
As we can see from the code above, we have collected the editId from localStorage. So, inside the Edit() function save the ID into localStorage:
function Edit(elm) { localStorage.setItem('editId',$(elm).attr('data-id')); $.ajax({ url: '/getBlogById', data: { id: $(elm).attr('data-id') }, type: 'POST', success: function(res) { // Parse the received JSON string var data = JSON.parse(res); //Populate the Pop up $('#editTitle').val(data[0]['Title']); $('#editDescription').val(data[0]['Description']); // Trigger the Pop Up $('#editModal').modal(); }, error: function(error) { console.log(error); } }); }
We can make an AJAX call getBlog() again once the data has been updated:
function GetBlogs(){ $.ajax({ url : '/getBlog', type : 'GET', success: function(res){ var blogObj = JSON.parse(res); $('#ulist').empty(); $('#listTemplate').tmpl(blogObj).appendTo('#ulist'); }, error: function(error){ console.log(error); } }); }
Updating a blog post:
Updated blog post:
Let's add the following HTML code to userHome.htm:
<div class="modal fade" id="deleteModal" tabindex="-1" role="dialog" aria-labelledby="deleteModalLabel" aria-hidden="true"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header" style="text-align:center;"> <h4 class="modal-title" style="color:red;" id="deleteModalLabel">You are going to Delete this forever !!</h4> </div> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button> <button type="button" class="btn btn-primary" onclick="Delete()">Delete</button> </div> </div> </div> </div>
Also we need to add a "delete icon" inside the listTemplate:
<script id="listTemplate" type="text/x-jQuery-tmpl"> <li class="list-group-item"> <div class="checkbox"> <label> ${Title} </label> </div> <div class="pull-right action-buttons"> <a data-id=${Id} onclick="Edit(this)"><span class="glyphicon glyphicon-pencil"></span></a> <a data-id=${Id} onclick="ConfirmDelete(this)" ><span class="glyphicon glyphicon-trash"></span></a> </div> </li> </script>
On clicking on the above "delete icon", we call a JavaScript function, ConfirmDelete(), where we'll trigger the confirmation popup:
function ConfirmDelete(elm){ localStorage.setItem('deleteId',$(elm).attr('data-id')); $('#deleteModal').modal(); }
To implement the Delete blog functionality, we need to create the MySQL stored procedure to delete:
DELIMITER $$ USE `FlaskBlogApp`$$ CREATE PROCEDURE `sp_deleteBlog` ( IN p_blog_id bigint, IN p_user_id bigint ) BEGIN delete from tbl_blog where blog_id = p_blog_id and blog_user_id = p_user_id; END$$ DELIMITER ;
mysql> SHOW PROCEDURE STATUS WHERE db = 'FlaskBlogApp'; +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation | +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | FlaskBlogApp | sp_addBlog | PROCEDURE | root@localhost | 2016-12-03 17:00:03 | 2016-12-03 17:00:03 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_createUser | PROCEDURE | root@localhost | 2016-12-02 21:50:34 | 2016-12-02 21:50:34 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_deleteBlog | PROCEDURE | root@localhost | 2016-12-04 23:28:27 | 2016-12-04 23:28:27 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogById | PROCEDURE | root@localhost | 2016-12-04 13:45:30 | 2016-12-04 13:45:30 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_GetBlogByUser | PROCEDURE | root@localhost | 2016-12-03 23:47:43 | 2016-12-03 23:47:43 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_updateBlog | PROCEDURE | root@localhost | 2016-12-04 15:58:41 | 2016-12-04 15:58:41 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | | FlaskBlogApp | sp_validateLogin | PROCEDURE | root@localhost | 2016-12-03 11:09:38 | 2016-12-03 11:09:38 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci | +--------------+------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 7 rows in set (0.02 sec)
The sp_deleteBlog takes in the blog ID and user ID and deletes the corresponding wish from the database.
Let's create a method called deleteWish() inside app.py to call the procedure sp_deleteBlog:
@app.route('/deleteBlog',methods=['POST']) def deleteBlog(): try: if session.get('user'): _id = request.form['id'] _user = session.get('user') conn = mysql.connect() cursor = conn.cursor() cursor.callproc('sp_deleteBlog',(_id,_user)) result = cursor.fetchall() if len(result) is 0: conn.commit() return json.dumps({'status':'OK'}) else: return json.dumps({'status':'An Error occured'}) else: return render_template('error.html',error = 'Unauthorized Access') except Exception as e: return json.dumps({'status':str(e)}) finally: cursor.close() conn.close()
In the deleteBlog() method, we first validate the session. Once the user session is validated using the blog ID and the user ID, we call the stored procedure sp_deleteBlog.
To call the deleteBlog() method, add an onclick event to the "Delete" button in the delete confirmation popup:
<div class="modal fade" id="deleteModal" tabindex="-1" role="dialog" aria-labelledby="deleteModalLabel" aria-hidden="true"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header" style="text-align:center;"> <h4 class="modal-title" style="color:red;" id="deleteModalLabel">You are going to Delete this forever !!</h4> </div> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button> <button type="button" class="btn btn-primary" onclick="Delete()">Delete</button> </div> </div> </div> </div>
Let's create a JavaScript function called Delete() in userHome.html, and inside Delete() make an AJAX call to the python method deleteBlog():
function Delete(){ $.ajax({ url : '/deleteBlog', data : {id:localStorage.getItem('deleteId')}, type : 'POST', success: function(res){ var result = JSON.parse(res); if(result.status == 'OK'){ $('#deleteModal').modal('hide'); GetBlogs(); } else{ alert(result.status); } }, error: function(error){ console.log(error); } }); }
After signed in, click on the "delete icon" in the blog list and we should be able to see the confirmation popup:
As we can see we deleted one out of four blog post:
Ph.D. / Golden Gate Ave, San Francisco / Seoul National Univ / Carnegie Mellon / UC Berkeley / DevOps / Deep Learning / Visualization