Creating a Web App From Scratch Using Python Flask and MySQL: Part 4

In the previous part of this tutorial series, we implemented the required functionality for a logged-in user to add a wish. We also saw how to display the wishes entered by a user on the user home page.

In this part, we'll implement the functionality for editing and deleting the wishes entered by a user.

Getting Started

Let's start by cloning the previous part of the tutorial from GitHub.

1	git clone https://github.com/jay3dec/PythonFlaskMySQLApp_Part3.git

Once the source code has been cloned, navigate to the project directory and start the web server.

1	cd PythonFlaskMySQLApp_Part3
2	python app.py

Point your browser to http://localhost:5002/ and you should have the application running.

Editing the Wish List

Step 1: Display the Edit Icon

We are already binding the received data using jQuery to our HTML. We'll modify that code and use jQuery templates to make it easier to bind data. We'll also add an edit icon to our HTML to provide a way to update the wish. Open userHome.html and include a reference to jQuery templates.

1	<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.templates/beta1/jquery.tmpl.js"></script>

Remove the existing list-group div and replace it with the following HTML code:

<div class="row">
    <div class="col-md-12">

        <div class="panel-body">

            <ul id="ulist" class="list-group">



            </ul>
        </div>

    </div>
</div>

Inside the UL with class list-group we'll be binding our data. Define a listTemplate as shown in the body of the 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>

Modify the jQuery AJAX success callback to bind the data to the listTemplate.

<script>
    $(function() {
        $.ajax({
            url: '/getWish',
            type: 'GET',
            success: function(res) {

                // Parse the JSON response
                var wishObj = JSON.parse(res);
                
                // Append to the template
                $('#listTemplate').tmpl(wishObj).appendTo('#ulist');


            },
            error: function(error) {
                console.log(error);
            }
        });
    });
</script>

Also, include some styles in userHome.html:

<style>
    .trash {
        color: rgb(209, 91, 71);
    }
    .panel-body .checkbox {
        display: inline-block;
        margin: 0px;
    }
    .list-group {
        margin-bottom: 0px;
    }
</style>

Save all the changes and restart the server. Point your browser to http://localhost:5002 and sign in using a valid email address and password. Once logged in, you should be able to see the wishes created by the user.

Step 2: Display the Edit Popup

We'll be using Bootstrap to show a popup to provide an interface to edit the wishes. Include a reference to Bootstrap in userHome.html.

1	<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>

Once the reference has been included, add the following HTML to userHome.html.

<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">&times;</span><span class="sr-only">Close</span>
                </button>
                <h4 class="modal-title" id="editModalLabel">Update Wish</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 type="button" id="btnUpdate" class="btn btn-primary">Update</button>
            </div>
        </div>
    </div>
</div>

The above HTML will serve as the popup. When the user clicks the edit icon the popup will show. We have already added the attributes data-target and data-toggle which will trigger the modal popup.

1	<a data-toggle="modal" data-target="#editModal"><span class="glyphicon glyphicon-pencil"></span></a>

Save the above changes and restart the app. Once signed in to the application, click on the edit icon and you should be able to view the popup.

Step 3: Populate the Edit Popup

When the user clicks the edit icon, we'll show the update popup with the title and description to update. In order to get started, first we need the wish ID to fetch the particular wish details once the user clicks the edit icon. So modify the jQuery template code to include an extra attribute data-id on the edit anchor element.

1	<a data-id=${Id} onclick="Edit(this)" ><span class="glyphicon glyphicon-pencil"></span></a>

We have also attached an onclick event to call the method Edit. Inside the Edit function, we'll make an AJAX call to a python method called getWishById which will return the wish details.

function Edit(elm) {
    $.ajax({
        url: '/getWishById',
        data: {
            id: $(elm).attr('data-id')
        },
        type: 'POST',
        success: function(res) {
            console.log(res);
        },
        error: function(error) {
            console.log(error);
        }
    });
}

Next, open up app.py and create a method called getWishById. Using this method, we'll get the particular wish details from the database.

@app.route('/getWishById',methods=['POST'])
def getWishById():
    try:
        if session.get('user'):

            _id = request.form['id']
            _user = session.get('user')

            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.callproc('sp_GetWishById',(_id,_user))
            result = cursor.fetchall()

            wish = []
            wish.append({'Id':result[0][0],'Title':result[0][1],'Description':result[0][2]})

            return json.dumps(wish)
        else:
            return render_template('error.html', error = 'Unauthorized Access')
    except Exception as e:
        return render_template('error.html',error = str(e))

As you can see in the above method, we have passed in the wish ID to this method and it gets the data from the database using the user ID and wish ID. Once the data has been fetched, it converts that data into a list and then returns it as JSON data.

Next, let's create the required MySQL stored procedure to fetch data from the database.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetWishById`(
IN p_wish_id bigint,
In p_user_id bigint
)
BEGIN
select * from tbl_wish where wish_id = p_wish_id and wish_user_id = p_user_id;
END

The code shown above is the stored procedure to get particular wish details using the wish ID and user ID.

Save the changes and restart the server. Once signed in to the application, click on the edit icon and you should have the details logged in your browser console.

To bind the received data to the HTML popup, first remove the data-target and data-toggle attributes from the edit icon anchor tag. Then add the following code to the Edit JavaScript function success callback to populate the popup and trigger it.

// 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();

Save the changes and restart the server. Once signed in to the application, try to click the edit icon and you should have the popup with the title and description.

Step 4: Update Wish Details

To implement the update functionality, let's first create a MySQL stored procedure.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_updateWish`(
IN p_title varchar(45),
IN p_description varchar(1000),
IN p_wish_id bigint,
In p_user_id bigint
)
BEGIN
update tbl_wish set wish_title = p_title,wish_description = p_description
    where wish_id = p_wish_id and wish_user_id = p_user_id;
END$$
DELIMITER ;

As seen in the stored procedure above, we'll be passing in the modified title and description along with the ID of the wish and the user to update the details in the database.

Next, let's create a new method called updateWish to update the details. Here is the updateWish method:

@app.route('/updateWish', methods=['POST'])
def updateWish():
    try:
        if session.get('user'):
            _user = session.get('user')
            _title = request.form['title']
            _description = request.form['description']
            _wish_id = request.form['id']



            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.callproc('sp_updateWish',(_title,_description,_wish_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 seen in the above code, after validating for a valid session, we have collected the posted data and called the stored procedure sp_updateWish to update the details.

In order to call the updateWish method, we need to attach an event on the Update button click. So, name the update button btnUpdate and attach an onclick event as shown:

$('#btnUpdate').click(function() {
    $.ajax({
        url: '/updateWish',
        data: {
            title: $('#editTitle').val(),
            description: $('#editDescription').val(),
            id: localStorage.getItem('editId')
        },
        type: 'POST',
        success: function(res) {
            $('#editModal').modal('hide');
            // Re populate the grid
        },
        error: function(error) {
            console.log(error);
        }
    })
});

As seen in the above code, we have collected the editId from localStorage, so inside the Edit function save the ID into localStorage.

1	localStorage.setItem('editId',$(elm).attr('data-id'));

Wrap up the getWish AJAX call into a function, so that we can call it again once the data has been updated.

function GetWishes() {
    $.ajax({
        url: '/getWish',
        type: 'GET',
        success: function(res) {
            var wishObj = JSON.parse(res);
            $('#ulist').empty();
            $('#listTemplate').tmpl(wishObj).appendTo('#ulist');
        },
        error: function(error) {
            console.log(error);
        }
    });
}

Call the GetWishes function in the success callback of the update AJAX call.

$('#btnUpdate').click(function() {
    $.ajax({
        url: '/updateWish',
        data: {
            title: $('#editTitle').val(),
            description: $('#editDescription').val(),
            id: localStorage.getItem('editId')
        },
        type: 'POST',
        success: function(res) {
            $('#editModal').modal('hide');
            
            // Re populate the grid
            GetWishes();
        },
        error: function(error) {
            console.log(error);
        }
    })
});

Save all the changes and restart the server. Once signed in to the application, try to edit the available wishes created by the user.

Deleting a Wish

Step 1: Show a Confirmation Popup

Add the following HTML code to userHome.html.

<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">Delete</button>
            </div>
        </div>
    </div>
</div>

Add a delete icon inside the listTemplate by adding the following HTML:

1	<a data-id=${Id} onclick="ConfirmDelete(this)" ><span class="glyphicon glyphicon-trash"></span></a>

On clicking on the above delete icon, we'll call a JavaScript function called ConfirmDelete where we'll trigger the confirmation popup.

1	function ConfirmDelete(elem) {
2	localStorage.setItem('deleteId', $(elem).attr('data-id'));
3	$('#deleteModal').modal();
4	}

Save the changes and restart the server. Once signed in, click on the delete icon in the wish list and you should be able to see the confirmation popup.

Step 2: Delete a Wish

To implement the Delete wish functionality, first let's create the MySQL stored procedure to delete.

DELIMITER $$
USE `BucketList`$$
CREATE PROCEDURE `sp_deleteWish` (
IN p_wish_id bigint,
IN p_user_id bigint
)
BEGIN
delete from tbl_wish where wish_id = p_wish_id and wish_user_id = p_user_id;
END$$

DELIMITER ;

The above procedure takes in the wish ID and user ID and deletes the corresponding wish from the database.

Next, let's create a method inside app.py to call the procedure sp_deleteWish.

We'll create a method called deleteWish for wish deletion.

@app.route('/deleteWish',methods=['POST'])
def deleteWish():
    try:
        if session.get('user'):
            _id = request.form['id']
            _user = session.get('user')

            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.callproc('sp_deleteWish',(_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 above method, we have first validated the session. Once we have validated the user session, using the wish ID and the user ID we have called the stored procedure sp_deleteWish.

To call the above method deleteWish, add an onclick event to the Delete button in the delete confirmation popup.

1	<button type="button" class="btn btn-primary" onclick="Delete()">Delete</button>

Create a JavaScript function called Delete, and inside Delete make an AJAX call to the python method deleteWish.

function Delete() {
    $.ajax({
        url: '/deleteWish',
        data: {
            id: localStorage.getItem('deleteId')
        },
        type: 'POST',
        success: function(res) {
            var result = JSON.parse(res);
            if (result.status == 'OK') {
                $('#deleteModal').modal('hide');
                GetWishes();
            } else {
                alert(result.status);
            }
        },
        error: function(error) {
            console.log(error);
        }
    });
}

On the success callback of the above Delete function, we'll check for the returned status, and if it's OK we'll hide the modal popup and reload the wishes.

Save the changes and restart the server. Once logged in to the application, try to delete a wish from the user home page.

Conclusion

In this part of the series, we saw how to implement the Edit and Delete wish functionality for our Bucket List Application. In the next part of this series, we'll implement pagination for our user home list and also implement a few more features.

Source code from this tutorial is available on GitHub.

Do let us know your thoughts in the comments below!