Creating a Web App From Scratch Using Python Flask and MySQL


Part 1

by 
Difficulty:IntermediateLength:LongLanguages:
English
বাংলা
Español
Hrvatski
Bahasa Indonesia
Italiano
한국어
Polski
Pусский
Wikang Tagalog
Türkçe
Tiếng Việt
This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.
Creating a Web App From Scratch Using Python Flask and MySQL: Part 2

In this series, we’ll be using PythonFlask and MySQL to create a simple web application from scratch. It will be a simple bucket list application where users can register, sign in and create their bucket list.

This tutorial assumes that you have some basic knowledge of the Python programming language. We’ll be using Flask, a Python web application framework, to create our application, with MySQL as the back end.

If you need to brush up on your Python skills, try the Introduction to Python course, which gives you a solid foundation in the language for just $5.

Flask is a Python framework for creating web applications. From the official site,

Flask is a microframework for Python based on Werkzeug, Jinja 2 and good intentions.

When we think about Python, the de facto framework that comes to our mind is the Django framework. But from a Python beginner’s perspective, Flask is easier to get started with, when compared to Django.

Setting up Flask is pretty simple and quick. With pip package manager, all we need to do is:

1
pip install flask

Once you’re done with installing Flask, create a folder called FlaskApp. Navigate to the FlaskApp folder and create a file called app.py. Import the flask module and create an app using Flask as shown:

1
2
from flask import Flask
app = Flask(__name__)

Now define the basic route / and its corresponding request handler:

1
2
3
@app.route("/")
def main():
return "Welcome!"

Next, check if the executed file is the main program and run the app:

1
2
if __name__ == "__main__":
app.run()

Save the changes and execute app.py:

1
python app.py

Point your browser to http://localhost:5000/ and you should have the welcome message.

First, when the application runs we should show a home page with the latest bucket list items added by users. So let’s add our home page to our application folder.

Flask looks for template files inside the templates folder. So navigate to the PythonApp folder and create a folder called templates. Inside templates, create a file called index.html. Open up index.html and add the following HTML:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
<!DOCTYPE html>
<html lang="en">
<head>
<title>Python Flask Bucket List App</title>
</head>
<body>
<div class="container">
<div class="header">
<nav>
<ul class="nav nav-pills pull-right">
<li role="presentation" class="active"><a href="#">Home</a>
</li>
<li role="presentation"><a href="#">Sign In</a>
</li>
<li role="presentation"><a href="showSignUp">Sign Up</a>
</li>
</ul>
</nav>
<h3 class="text-muted">Python Flask App</h3>
</div>
<div class="jumbotron">
<h1>Bucket List App</h1>
<p class="lead"></p>
<p><a class="btn btn-lg btn-success" href="showSignUp" role="button">Sign up today</a>
</p>
</div>
<div class="row marketing">
<div class="col-lg-6">
<h4>Bucket List</h4>
<p>Donec id elit non mi porta gravida at eget metus. Maecenas faucibus mollis interdum.</p>
<h4>Bucket List</h4>
<p>Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Cras mattis consectetur purus sit amet fermentum.</p>
<h4>Bucket List</h4>
<p>Maecenas sed diam eget risus varius blandit sit amet non magna.</p>
</div>
<div class="col-lg-6">
<h4>Bucket List</h4>
<p>Donec id elit non mi porta gravida at eget metus. Maecenas faucibus mollis interdum.</p>
<h4>Bucket List</h4>
<p>Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Cras mattis consectetur purus sit amet fermentum.</p>
<h4>Bucket List</h4>
<p>Maecenas sed diam eget risus varius blandit sit amet non magna.</p>
</div>
</div>
<footer class="footer">
<p>&copy; Company 2015</p>
</footer>
</div>
</body>
</html>

Open up app.py and import render_template, which we’ll use to render the template files.

1
from flask import Flask, render_template

Modify the main method to return the rendered template file.

1
2
def main():
return render_template('index.html')

Save the changes and restart the server. Point your browser to http://localhost:5000/ and you should have the below screen:

Bucket List App home page

We’ll be using MySQL as the back end. So log into MySQL from the command line, or if you prefer a GUI like MySQL work bench, you can use that too. First, create a database called BucketList. From the command line:

1
mysql -u <username> -p

Enter the required password and when logged in, execute the following command to create the database:

1
CREATE DATABASE BucketList;

Once the database has been created, create  a table called tbl_user as shown:

1
2
3
4
5
6
CREATE TABLE `BucketList`.`tbl_user` (
`user_id` BIGINT NULL AUTO_INCREMENT,
`user_name` VARCHAR(45) NULL,
`user_username` VARCHAR(45) NULL,
`user_password` VARCHAR(45) NULL,
PRIMARY KEY (`user_id`));

We’ll be using Stored procedures for our Python application to interact with the MySQL database. So, once the table tbl_user has been created, create a stored procedure called sp_createUser to sign up a user.

When creating a stored procedure to create a user in the tbl_user table, first we need to check if a user with the same username already exists. If it exists we need to throw an error to the user, otherwise we’ll create the user in the user table. Here is how the stored procedure sp_createUser would look:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_createUser`(
IN p_name VARCHAR(20),
IN p_username VARCHAR(20),
IN p_password VARCHAR(20)
)
BEGIN
if ( select exists (select 1 from tbl_user where user_username = p_username) ) THEN
select 'Username Exists !!';
ELSE
insert into tbl_user
(
user_name,
user_username,
user_password
)
values
(
p_name,
p_username,
p_password
);
END IF;
END$$
DELIMITER ;

Navigate to the PythonApp/templates directory and create an HTML file called signup.html. Add the following HTML code to signup.html:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
<!DOCTYPE html>
<html lang="en">
<head>
<title>Python Flask Bucket List App</title>
<link href="../static/signup.css" rel="stylesheet">
</head>
<body>
<div class="container">
<div class="header">
<nav>
<ul class="nav nav-pills pull-right">
<li role="presentation" ><a href="main">Home</a></li>
<li role="presentation"><a href="#">Sign In</a></li>
<li role="presentation" class="active"><a href="#">Sign Up</a></li>
</ul>
</nav>
<h3 class="text-muted">Python Flask App</h3>
</div>
<div class="jumbotron">
<h1>Bucket List App</h1>
<form class="form-signin">
<label for="inputName" class="sr-only">Name</label>
<input type="name" name="inputName" id="inputName" class="form-control" placeholder="Name" required autofocus>
<label for="inputEmail" class="sr-only">Email address</label>
<input type="email" name="inputEmail" id="inputEmail" class="form-control" placeholder="Email address" required autofocus>
<label for="inputPassword" class="sr-only">Password</label>
<input type="password" name="inputPassword" id="inputPassword" class="form-control" placeholder="Password" required>
<button id="btnSignUp" class="btn btn-lg btn-primary btn-block" type="button">Sign up</button>
</form>
</div>
<footer class="footer">
<p>&copy; Company 2015</p>
</footer>
</div>
</body>
</html>

Also add the following CSS as signup.css to the static folder inside PythonApp.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
body {
padding-top: 40px;
padding-bottom: 40px;
}
.form-signin {
max-width: 330px;
padding: 15px;
margin: 0 auto;
}
.form-signin .form-signin-heading,
.form-signin .checkbox {
margin-bottom: 10px;
}
.form-signin .checkbox {
font-weight: normal;
}
.form-signin .form-control {
position: relative;
height: auto;
-webkit-box-sizing: border-box;
-moz-box-sizing: border-box;
box-sizing: border-box;
padding: 10px;
font-size: 16px;
}
.form-signin .form-control:focus {
z-index: 2;
}
.form-signin input[type="email"] {
margin-bottom: -1px;
border-bottom-right-radius: 0;
border-bottom-left-radius: 0;
}
.form-signin input[type="password"] {
margin-bottom: 10px;
border-top-left-radius: 0;
border-top-right-radius: 0;
}

In app.py add another method called showSignUp to render the signup page once a request comes to /showSignUp:

1
2
3
@app.route('/showSignUp')
def showSignUp():
return render_template('signup.html')

Save the changes and restart the server. Click on the Sign Up button on the home page and you should have the signup page as shown:

Sign Up user page

Next, we need a server-side method for the UI to interact with the MySQL database. So navigate to PythonApp and open app.py. Create a new method called signUp and also add a route /signUp. Here is how it looks:

1
2
3
@app.route('/signUp')
def signUp():
# create user code will be here !!

We’ll be using jQuery AJAX to post our signup data to the signUp method, so we’ll specify the method in the route definition.

1
2
3
@app.route('/signUp',methods=['POST'])
def signUp():
# create user code will be here !!

In order to read the posted values we need to import request from Flask.

1
from flask import Flask, render_template, request

Using request we can read the posted values as shown below:

1
2
3
4
5
6
7
@app.route('/signUp',methods=['POST'])
def signUp():
# read the posted values from the UI
_name = request.form['inputName']
_email = request.form['inputEmail']
_password = request.form['inputPassword']

Once the values are read, we’ll simply check if they are valid and for the time being let’s just return a simple message:

01
02
03
04
05
06
07
08
09
10
11
12
13
@app.route('/signUp',methods=['POST'])
def signUp():
# read the posted values from the UI
_name = request.form['inputName']
_email = request.form['inputEmail']
_password = request.form['inputPassword']
# validate the received values
if _name and _email and _password:
return json.dumps({'html':'<span>All fields good !!</span>'})
else:
return json.dumps({'html':'<span>Enter the required fields</span>'})

Also import json from Flask, since we are using it in the above code to return json data.

1
from flask import Flask, render_template, json, request

We’ll be using jQuery AJAX to send the signup request to the Python method. Download and place jQuery inside PythonApp/static/js and add a link to it from the signup page. Once jQuery has been included, we’ll add a jQuery POST request when the user clicks the Sign Up button.

So, let’s attach the signup button click event as shown:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
$(function() {
$('#btnSignUp').click(function() {
$.ajax({
url: '/signUp',
data: $('form').serialize(),
type: 'POST',
success: function(response) {
console.log(response);
},
error: function(error) {
console.log(error);
}
});
});
});

Save all the changes and restart the server. From the Sign Up page, fill in the details and click Sign Up. Check the browser console and you should have the below message:

1
{"html": "<span>All fields good !!</span>"}

Once we have the nameemail address and password, we can simply call the MySQL stored procedure to create the new user.

To connect with MySQL, we’ll be using Flask-MySQL, which is a Flask extension. In order to get started with Flask-MySQL, install it using pip package manager:

1
pip install flask-mysql

Import MySQL inside app.py:

1
from flask.ext.mysql import MySQL

Earlier we defined our app as shown:

1
app = Flask(__name__)

Along with that include the following MySQL configurations:

1
2
3
4
5
6
7
8
mysql = MySQL()
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'jay'
app.config['MYSQL_DATABASE_PASSWORD'] = 'jay'
app.config['MYSQL_DATABASE_DB'] = 'BucketList'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

First, let’s create the MySQL connection:

1
conn = mysql.connect()

Once the connection is created, we’ll require a cursor to query our stored procedure. So, using conn connection, create a cursor.

1
cursor = conn.cursor()

Before calling the create user stored procedure, let’s make our password salted using a helper provided by Werkzeug. Import the module into app.py:

1
from werkzeug import generate_password_hash, check_password_hash

Use the salting module to create the hashed password.

1
_hashed_password = generate_password_hash(_password)

Now, let’s call the procedure sp_createUser:

1
cursor.callproc('sp_createUser',(_name,_email,_hashed_password))

If the procedure is executed successfully, then we’ll commit the changes and return the success message.

1
2
3
4
5
6
7
data = cursor.fetchall()
if len(data) is 0:
conn.commit()
return json.dumps({'message':'User created successfully !'})
else:
return json.dumps({'error':str(data[0])})

Save the changes and restart the server. Go to the signup page and enter the nameemail address and password and click the Sign Up button. On successful user creation, you’ll be able to see a message in your browser console.

1
{"message": "User created successfully !"}

In this tutorial, we saw how to get started with creating a web application using Python FlaskMySQL and the Flask-MySQL extension. We created and designed the database tables and stored procedure, and implemented the signup functionality. In the next tutorial, we’ll take this series to the next level by implementing sign-in functionality and some other features.

Source code from this tutorial is available on GitHub.

Do let us know your thoughts in the comments below!

Learn Python with our complete python tutorial guide, whether you’re just getting started or you’re a seasoned coder looking to learn new skills.

This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.
Creating a Web App From Scratch Using Python Flask and MySQL
Creating a Web App From Scratch Using Python Flask and MySQL: Part 3

In the previous part of this series, we saw how to get started with Python Flask and MySQL and implemented the user registration portion of our application. In this tutorial, we’ll take this to the next level by implementing the sign-in and logout functionality for our application.

First clone the source code of the previous tutorial from GitHub.

1
git clone https://github.com/jay3dec/PythonFlaskMySQLApp---Part-1.git

Once the source code has been cloned, navigate to the PythonFlaskMySQLApp---Part-1 directory and start the server.

1
python app.py

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

Navigate to PythonFlaskMySQLApp---Part-1/templates and create a new file called signin.html. Open signin.html and add the following HTML code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Python Flask Bucket List App</title>
   
    <link href="http://getbootstrap.com/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="../static/css/signup.css" rel="stylesheet">
    <script src="../static/js/jquery-1.11.2.js"></script>
   
  </head>
  <body>
    <div class="container">
      <div class="header">
        <nav>
          <ul class="nav nav-pills pull-right">
            <li role="presentation" ><a href="/">Home</a></li>
            <li role="presentation" class="active"><a href="#">Sign In</a></li>
            <li role="presentation" ><a href="/showSignUp">Sign Up</a></li>
          </ul>
        </nav>
        <h3 class="text-muted">Python Flask App</h3>
      </div>
      <div class="jumbotron">
        <h1>Bucket List App</h1>
        <form class="form-signin" action="/validateLogin" method="post">
        <label for="inputEmail" class="sr-only">Email address</label>
        <input type="email" name="inputEmail" id="inputEmail" class="form-control" placeholder="Email address" required autofocus>
        <label for="inputPassword" class="sr-only">Password</label>
        <input type="password" name="inputPassword" id="inputPassword" class="form-control" placeholder="Password" required>
        
        <button id="btnSignIn" class="btn btn-lg btn-primary btn-block" type="submit">Sign in</button>
      </form>
      </div>
      
      <footer class="footer">
        <p>&copy; Company 2015</p>
      </footer>
    </div>
  </body>
</html>

Open app.py and add a new route for the sign-in interface.

1
2
3
@app.route('/showSignin')
def showSignin():
    return render_template('signin.html')

Next, open up index.html and signup.html, and add the href link for sign-in on both the pages as /showSignin. Save all the changes and restart the server.

1
python app.py

Point your browser to http://localhost:5002 and click on the Sign In link, and you should be able to see the sign-in page.

Sign In page
Advertisement

Now, we need to create function to validate the user login. On clicking Sign In we’ll post the entered email address and password to the validate user function.

To validate a user, we’ll need a MySQL stored procedure. So create a MySQL stored procedure as shown:

1
2
3
4
5
6
7
8
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_validateLogin`(
IN p_username VARCHAR(20)
)
BEGIN
    select * from tbl_user where user_username = p_username;
END$$
DELIMITER ;

We’ll get the user details based on the username from the MySQL database using sp_validateLogin. Once we have the hashed password we’ll validate it against the password entered by the user.

Create a method to validate the user which we’ll call when the user submits the form:

1
2
3
4
5
6
7
8
@app.route('/validateLogin',methods=['POST'])
def validateLogin():
    try:
        _username = request.form['inputEmail']
        _password = request.form['inputPassword']
    except Exception as e:
        return render_template('error.html',error = str(e))

As seen in the above code, we have read the posted email address and password into _username and _password. Now we’ll call the sp_validateLogin procedure with the parameter _username. So create a MySQL connection inside the validateLogin method:

1
con = mysql.connect()

Once the connection has been created, create a cursor using the con connection.

1
cursor = con.cursor()

Using the cursor, call the MySQL stored procedure as shown:

1
cursor.callproc('sp_validateLogin',(_username,))

Get the fetched records from the cursor as shown:

1
data = cursor.fetchall()

If the data has some records, we’ll match the retrieved password against the password entered by the user.

1
2
3
4
5
6
7
if len(data) > 0:
    if check_password_hash(str(data[0][3]),_password):
        return redirect('/userHome')
    else:
        return render_template('error.html',error = 'Wrong Email address or Password.')
else:
    return render_template('error.html',error = 'Wrong Email address or Password.')

As seen in the above code, we have used a method called check_password_hash to check if the returned hash password matches the password entered by the user. If all is good then we’ll redirect the user to userHome.html. And if there is any error, we’ll display error.html with the error message.

Here is the complete validateLogin code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@app.route('/validateLogin',methods=['POST'])
def validateLogin():
    try:
        _username = request.form['inputEmail']
        _password = request.form['inputPassword']
        # connect to mysql
        con = mysql.connect()
        cursor = con.cursor()
        cursor.callproc('sp_validateLogin',(_username,))
        data = cursor.fetchall()
        if len(data) > 0:
            if check_password_hash(str(data[0][3]),_password):
                session['user'] = data[0][0]
                return redirect('/userHome')
            else:
                return render_template('error.html',error = 'Wrong Email address or Password.')
        else:
            return render_template('error.html',error = 'Wrong Email address or Password.')
    except Exception as e:
        return render_template('error.html',error = str(e))
    finally:
        cursor.close()
        con.close()

Create a page called userHome.html inside the templates folder and add the following HTML code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Python Flask Bucket List App</title>
    <link href="http://getbootstrap.com/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="../static/css/signup.css" rel="stylesheet">
</head>
<body>
    <div class="container">
        <div class="header">
            <nav>
                <ul class="nav nav-pills pull-right">
                    <li role="presentation" class="active"><a href="/logout">Logout</a>
                    </li>
                </ul>
            </nav>
            <h3 class="text-muted">Python Flask App</h3>
        </div>
        <div class="jumbotron">
            <h1>Welcome Home !!</h1>
        </div>
        <footer class="footer">
            <p>&copy; Company 2015</p>
        </footer>
    </div>
</body>
</html>

Also create an error page called error.html in templates folder and add the following HTML code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Unauthorized Access:: Python Flask Bucket List App</title>
    <link href="http://getbootstrap.com/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container">
        <div class="header">
            <nav>
                <ul class="nav nav-pills pull-right">
                    <li role="presentation" class="active"><a href="#">Home</a>
                    </li>
                    <li role="presentation"><a href="/showSignin">Sign In</a>
                    </li>
                    <li role="presentation"><a href="/showSignUp">Sign Up</a>
                    </li>
                </ul>
            </nav>
            <h3 class="text-muted">Python Flask App</h3>
        </div>
        <div class="jumbotron">
            <h1>{{error}}</h1>
        </div>
        <footer class="footer">
            <p>&copy; Company 2015</p>
        </footer>
    </div>
</body>
</html>

Inside error.html we have an element as shown:

1
<h1>{{error}}</h1>

The value for the variable can be passed from the render_template function and can be set dynamically.

On successful sign-in we are redirecting the user to the user home page, so we need to create a route called /userHome as shown:

1
2
3
@app.route('/userHome')
def userHome():
    return render_template('userHome.html')

Save all the changes and restart the server. Click on the Sign In link in the home page and try to sign in using a valid email address and password. On successful user validation, you should have a page as shown below:

User home on successful user sign in

On an unsuccessful user validation the user will be redirected to an error page as shown below:

Error message on unsuccessful user sign in

Here we have used a separate error page to display the error. It’s also fine if you want to use the same page to display the error message.

On successful user validation a user is redirected to the user home page. But right now even an unauthorized user can view the home page by simply browsing the URL http://localhost:5002/userHome.

To restrict unauthorized user access, we’ll check for a session variable which we’ll set on successful user login. So import session from flask:

1
from flask import session

We also need to set a secret key for the session. So in app.py, after the app as been initialized, set the secret key as shown :

1
app.secret_key = 'why would I tell you my secret key?'

Now, inside the validateLogin method, before redirecting the user to /userHome on successful sign-in, set the session variable as shown:

1
session['user'] = data[0][0]

Next, inside the userHome method, check for the session variable before rendering userHome.html. If the session variable is not found, redirect to the error page.

1
2
3
4
5
6
@app.route('/userHome')
def userHome():
    if session.get('user'):
        return render_template('userHome.html')
    else:
        return render_template('error.html',error = 'Unauthorized Access')

Save all the changes and restart the server. Without signing in, try to navigate to http://localhost:5002/userHome and since you haven’t logged in yet, you should be redirected to the error page.

Unauthorized access error

Implementing the logout functionality is the simplest. All we need to do is make the session variable user null and redirect the user to the main page.

Inside app.py, create a new route and method for logout as shown:

1
2
3
4
@app.route('/logout')
def logout():
    session.pop('user',None)
    return redirect('/')

We have already set the href for the log out button to /logout. So save all the changes and restart the server. From the home page, click on Sign In and try to log in using a valid email address and password. Once signed in, click on the Logout button in user home and you should be successfully logged out from the application.

In this part of the tutorial, we saw how to implement the user login and logout functionality. We also saw how to restrict unauthorized access to application pages. In the next part of this tutorial, we’ll implement the functionality for the logged-in user to add and edit a blog post in the application.

Source code from this tutorial is available on GitHub.

Do let us know your thoughts in the comments below!

Advertisement
This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.
Creating a Web App From Scratch Using Python Flask and MySQL: Part 2
Creating a Web App From Scratch Using Python Flask and MySQL: Part 4

In the previous part of this tutorial series, we implemented the sign-in and logout functionality for our Bucket List application. In this part of the series, we’ll implement the back end and front end required for a user to add and display bucket list items.

Let’s start by cloning the previous part for the tutorial from GitHub.

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

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

1
2
cd PythonFlaskMySQLApp_Part2
python app.py

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

Bucket List App Home Page
Advertisement

We’ll start by creating an interface for the logged-in user to add bucket list items. Navigate to the templates folder inside the project directory, and create a file called addWish.html. Open addWish.html and add the following HTML code:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
<!DOCTYPE html>
<html lang="en">
<head>
<title>Python Flask Bucket List App</title>
<script src="../static/js/jquery-1.11.2.js"></script>
</head>
<body>
<div class="container">
<div class="header">
<nav>
<ul class="nav nav-pills pull-right">
<li role="presentation" class="active"><a href="#">Add Item</a>
</li>
<li role="presentation"><a href="/logout">Logout</a>
</li>
</ul>
</nav>
<h3 class="text-muted">Python Flask App</h3>
</div>
<section>
<form class="form-horizontal" method="post" action="/addWish">
<fieldset>
<!-- Form Name -->
<legend>Create Your Wish</legend>
<!-- Text input-->
<div class="form-group">
<label class="col-md-4 control-label" for="txtTitle">Title</label>
<div class="col-md-4">
<input id="txtTitle" name="inputTitle" type="text" placeholder="placeholder" class="form-control input-md">
</div>
</div>
<!-- Textarea -->
<div class="form-group">
<label class="col-md-4 control-label" for="txtPost">Post</label>
<div class="col-md-4">
<textarea class="form-control" id="txtPost" name="inputDescription"></textarea>
</div>
</div>
<!-- Button -->
<div class="form-group">
<label class="col-md-4 control-label" for="singlebutton"></label>
<div class="col-md-4">
<input id="singlebutton" name="singlebutton" class="btn btn-primary" type="submit" value="Publish" />
</div>
</div>
</fieldset>
</form>
</section>
<footer class="footer">
<p>&copy; Company 2015</p>
</footer>
</div>
</body>
</html>

Open app.py and add a new route and method to display the Add Wish page.

1
2
3
@app.route('/showAddWish')
def showAddWish():
return render_template('addWish.html')

Open userHome.html and add a new menu item to link to the Add Wish page.

1
<li role="presentation"><a href="/showAddWish">Add Wish</a></li>

Save 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, click on the Add Wish link and you should have the Add Wish page displayed.

Add Bucket List Item

To add items to the bucket list, we need to create a table called tbl_wish.

1
2
3
4
5
6
7
8
CREATE TABLE `tbl_wish` (
`wish_id` int(11) NOT NULL AUTO_INCREMENT,
`wish_title` varchar(45) DEFAULT NULL,
`wish_description` varchar(5000) DEFAULT NULL,
`wish_user_id` int(11) DEFAULT NULL,
`wish_date` datetime DEFAULT NULL,
PRIMARY KEY (`wish_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

tbl_wish will have titledescription and the ID of the user who created the wish.

Next, we need to create a MySQL stored procedure to add items to the tbl_wish table.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
USE `BucketList`;
DROP procedure IF EXISTS `BucketList`.`sp_addWish`;
DELIMITER $$
USE `BucketList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_addWish`(
IN p_title varchar(45),
IN p_description varchar(1000),
IN p_user_id bigint
)
BEGIN
insert into tbl_wish(
wish_title,
wish_description,
wish_user_id,
wish_date
)
values
(
p_title,
p_description,
p_user_id,
NOW()
);
END$$
DELIMITER ;
;

Create a method called addWish in app.py.

1
2
3
@app.route('/addWish',methods=['POST'])
def addWish():
# Code will be here

Since we’ll be posting data to this method, we have explicitly declared it in the defined route.

When a call is made to the addWish method, we need to validate if it’s an authentic call by checking if the session variable user exists. Once we have validated the session, we’ll read the posted title and description.

1
2
3
_title = request.form['inputTitle']
_description = request.form['inputDescription']
_user = session.get('user')

Once we have the required input values, we’ll open a MySQL connection and call the stored procedure sp_addWish.

1
2
3
4
conn = mysql.connect()
cursor = conn.cursor()
cursor.callproc('sp_addWish',(_title,_description,_user))
data = cursor.fetchall()

After we have executed the stored procedure, we need to commit the changes to the database.

1
2
3
4
5
if len(data) is 0:
conn.commit()
return redirect('/userHome')
else:
return render_template('error.html',error = 'An error occurred!')

Here is the complete addWish method.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@app.route('/addWish',methods=['POST'])
def addWish():
try:
if session.get('user'):
_title = request.form['inputTitle']
_description = request.form['inputDescription']
_user = session.get('user')
conn = mysql.connect()
cursor = conn.cursor()
cursor.callproc('sp_addWish',(_title,_description,_user))
data = cursor.fetchall()
if len(data) is 0:
conn.commit()
return redirect('/userHome')
else:
return render_template('error.html',error = 'An error occurred!')
else:
return render_template('error.html',error = 'Unauthorized Access')
except Exception as e:
return render_template('error.html',error = str(e))
finally:
cursor.close()
conn.close()

Save all the source code and restart the server. Point your browser to http://localhost:5002 and sign in using a valid email address and password. Once signed in, click on the Add Wish link. Enter the title and description for your wish and click Publish. On successfully adding the wish, it should redirect to the user home page. Log in to the MySQL database and you should have the wish in your tbl_wish table.

Let’s create a MySQL stored procedure which will get the wishes created by a user. It will take the user ID as a parameter and return a data set of wishes created by the particular user ID.

01
02
03
04
05
06
07
08
09
10
11
12
13
USE `BucketList`;
DROP procedure IF EXISTS `sp_GetWishByUser`;
DELIMITER $$
USE `BucketList`$$
CREATE PROCEDURE `sp_GetWishByUser` (
IN p_user_id bigint
)
BEGIN
select * from tbl_wish where wish_user_id = p_user_id;
END$$
DELIMITER ;

Next, let’s create a Python method which will call the sp_GetWishByUser stored procedure to get the wishes created by a user. Add a method called getWish in app.py.

1
2
3
4
5
6
7
8
9
@app.route('/getWish')
def getWish():
try:
if session.get('user'):
_user = session.get('user')
else:
return render_template('error.html', error = 'Unauthorized Access')
except Exception as e:
return render_template('error.html', error = str(e))

As seen in the above code, this method can only be called with valid user session. Once we have validated for a valid user session, we’ll create a connection to the MySQL database and call the stored procedure sp_GetWishByUser.

1
2
3
4
5
6
7
_user = session.get('user')
# Connect to MySQL and fetch data
con = mysql.connect()
cursor = con.cursor()
cursor.callproc('sp_GetWishByUser',(_user,))
wishes = cursor.fetchall()

Once we have fetched data from MySQL, we’ll parse the data and convert it into a dictionary so that it’s easy to return as JSON.

1
2
3
4
5
6
7
8
wishes_dict = []
for wish in wishes:
wish_dict = {
'Id': wish[0],
'Title': wish[1],
'Description': wish[2],
'Date': wish[4]}
wishes_dict.append(wish_dict)

After converting the data into a dictionary we’ll convert the data into JSON and return.

1
return json.dumps(wishes_dict)

Here is the full getWish method.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@app.route('/getWish')
def getWish():
try:
if session.get('user'):
_user = session.get('user')
con = mysql.connect()
cursor = con.cursor()
cursor.callproc('sp_GetWishByUser',(_user,))
wishes = cursor.fetchall()
wishes_dict = []
for wish in wishes:
wish_dict = {
'Id': wish[0],
'Title': wish[1],
'Description': wish[2],
'Date': wish[4]}
wishes_dict.append(wish_dict)
return json.dumps(wishes_dict)
else:
return render_template('error.html', error = 'Unauthorized Access')
except Exception as e:
return render_template('error.html', error = str(e))

When the user home page is loaded, we’ll call the getWish method using jQuery AJAX and bind the received data into our HTML. In userHome.html add the following jQuery AJAX script:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
<script>
$(function() {
$.ajax({
url: '/getWish',
type: 'GET',
success: function(res) {
console.log(res);
},
error: function(error) {
console.log(error);
}
});
});
</script>

Save the above changes and restart the server. Once logged in with a valid email address and password, check your browser console and you should have the wish list retrieved from the database as shown:

01
02
03
04
05
06
07
08
09
10
11
[{
"Date": "Fri, 23 Jan 2015 23:26:05 GMT",
"Description": "I want to climb Mount Everest",
"Id": 1,
"Title": "Climb Everest"
}, {
"Date": "Fri, 23 Jan 2015 23:27:05 GMT",
"Description": "I want to jump from top of a mountain",
"Id": 2,
"Title": "Bungee Jump"
}]

Now, we need to iterate over the JSON data and bind it into the HTML. We’ll be using bootstrap list-group to display our wish list items. Here is the basic template for list-group:

1
2
3
4
5
6
<div class="list-group">
<a href="#" class="list-group-item active">
<h4 class="list-group-item-heading">Wish Title</h4>
<p class="list-group-item-text">Wish Description</p>
</a>
</div>

Add the above HTML code to the jumbotron div in userHome.html. Here is how it looks:

list-group in User Home

Now, what we’ll do is create the above shown list-group div dynamically for each wish list entry and append it to the jumbotron div. Inside the success callback of the getWish function call, create a div as shown:

1
2
3
4
5
6
7
8
var div = $('<div>')
.attr('class', 'list-group')
.append($('<a>')
.attr('class', 'list-group-item active')
.append($('<h4>')
.attr('class', 'list-group-item-heading'),
$('<p>')
.attr('class', 'list-group-item-text')));

We’ll be cloning the above div to create the list-group div for each wish list item. Next, parse the returned JSON string into a JavaScript object.

1
var wishObj = JSON.parse(res);

Now, iterate over wishObj and for each wish item, clone a new div and append it to the jumbotron div.

1
2
3
4
5
6
7
8
var wish = '';
$.each(wishObj, function(index, value) {
wish = $(div).clone();
$(wish).find('h4').text(value.Title);
$(wish).find('p').text(value.Description);
$('.jumbotron').append(wish);
});

Save the above changes and restart the server. Log in using a valid email address and password and you should be able to see the list of wishes created by the particular user.

User Home Page Populated with Wishes

In this tutorial, we implemented an interface for a logged-in user to create a wish. We also implemented the required methods and database stored procedure to fetch and display the created wishes in the user home page.

In the next part of this series, we’ll see how to implement the Edit and Delete functionality for the wish list shown in the user home page.

Source code from this tutorial is available on GitHub.

Do let us know your thoughts in the comments below!

This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.
Creating a Web App From Scratch Using Python Flask and MySQL: Part 3
Creating a Web App From Scratch Using Python Flask and MySQL: Part 5

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.

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
2
cd PythonFlaskMySQLApp_Part3
python app.py

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

Advertisement

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:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
<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:

01
02
03
04
05
06
07
08
09
10
11
12
13
<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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
<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:

01
02
03
04
05
06
07
08
09
10
11
12
<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.

User Home with Edit Icon

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.

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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<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.

Edit Wish 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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
@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.

1
2
3
4
5
6
7
8
9
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.

1
2
3
4
5
6
7
8
9
// 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.

Populated Edit Pop Up

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

01
02
03
04
05
06
07
08
09
10
11
12
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:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@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:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
$('#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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
$('#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.

Add the following HTML code to userHome.html.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
<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
2
3
4
function ConfirmDelete(elem) {
    localStorage.setItem('deleteId', $(elem).attr('data-id'));
    $('#deleteModal').modal();
}

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.

Delete Confirmation Popup

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

01
02
03
04
05
06
07
08
09
10
11
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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
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.

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.

This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.
Creating a Web App From Scratch Using Python Flask and MySQL: Part 4
Creating a Web App From Scratch Using Python Flask and MySQL: Part 6

In the previous part of this series, we saw how to implement the Edit and Delete wish functionality for our Bucket List Application. In this part we’ll implement the paging functionality for our user home list.

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

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

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

1
2
cd PythonFlaskMySQLApp_Part4
python app.py

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

As the list of wishes on the user home page increases, it gets scrolled down the page. So it’s important to implement pagination. We’ll be limiting the number of items shown on a page to a certain number.

Advertisement

We’ll start by modifying the sp_GetWishByUser procedure to return results based on a limit and offset value. This time we’ll be creating our stored procedure statement dynamically to return the result set based on the limit and offset value. Here is the modified sp_GetWishByUser MySQL stored procedure.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
USE `BucketList`;
DROP procedure IF EXISTS `sp_GetWishByUser`;
DELIMITER $$
USE `BucketList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetWishByUser`(
IN p_user_id bigint,
IN p_limit int,
IN p_offset int
)
BEGIN
    SET @t1 = CONCAT( 'select * from tbl_wish where wish_user_id = ', p_user_id, ' order by wish_date desc limit ',p_limit,' offset ',p_offset);
    PREPARE stmt FROM @t1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

As seen in the above stored procedure, we created our dynamic SQL query and executed it to get the wish list based on the offset and limit parameters.

First, let’s define a few default settings. In app.py add a variable for page limit.

1
2
# Default setting
pageLimit = 2

Make the getWish python method accept POST requests.

1
@app.route('/getWish',methods=['POST'])

Read the offset and limit inside the getWish method and pass it on while calling the MySQL stored procedure sp_GetWishByUser.

1
2
3
4
5
6
7
8
_limit = pageLimit
 _offset = request.form['offset']
con = mysql.connect()
cursor = con.cursor()
cursor.callproc('sp_GetWishByUser',(_user,_limit,_offset))
wishes = cursor.fetchall()

Modify the GetWishes JavaScript function in userHome.html to make it a POST request and pass the offset value.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
function GetWishes() {
    $.ajax({
        url: '/getWish',
        type: 'POST',
        data: {
            offset: 0
        },
        success: function(res) {
            var wishObj = JSON.parse(res);
            $('#ulist').empty();
            $('#listTemplate').tmpl(wishObj).appendTo('#ulist');
        },
        error: function(error) {
            console.log(error);
        }
    });
}

Save all the changes and restart the server. Sign in using a valid email address and password and you should have only two records displayed on the screen.

User Home with Limited records

So the database portion is working well. Next, we need to add the pagination UI to the user home page, which will enable the user to navigate across the data.

We’ll use the Bootstrap pagination component. Open up userHome.html and add the following HTML code after the #ulist UL.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<nav>
    <ul class="pagination">
        <li>
            <a href="#" aria-label="Previous">
                <span aria-hidden="true">&laquo;</span>
            </a>
        </li>
        <li><a href="#">1</a>
        </li>
        <li><a href="#">2</a>
        </li>
        <li><a href="#">3</a>
        </li>
        <li><a href="#">4</a>
        </li>
        <li><a href="#">5</a>
        </li>
        <li>
            <a href="#" aria-label="Next">
                <span aria-hidden="true">&raquo;</span>
            </a>
        </li>
    </ul>
</nav>

Save the changes and restart the server. After successfully signing in, you should be able to see the pagination under the wish list.

Pagination in User Home Page

The above pagination is how our pagination will look. But to make it functional, we need to create our pagination dynamically based on the number of records in the database.

To create our pagination, we’ll need the total number of records available in the database. So let’s modify the MySQL stored procedure sp_GetWishByUser to return the total number of records available as an out parameter.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
USE `BucketList`;
DROP procedure IF EXISTS `sp_GetWishByUser`;
DELIMITER $$
USE `BucketList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetWishByUser`(
IN p_user_id bigint,
IN p_limit int,
IN p_offset int,
out p_total bigint
)
BEGIN
    
    select count(*) into p_total from tbl_wish where wish_user_id = p_user_id;
    SET @t1 = CONCAT( 'select * from tbl_wish where wish_user_id = ', p_user_id, ' order by wish_date desc limit ',p_limit,' offset ',p_offset);
    PREPARE stmt FROM @t1;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

As seen in the above modified stored procedure, we added a new output parameter called p_total and selected the total count of the wishes based on the user id.

Also modify the getWish python method to pass an output parameter.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
_limit = pageLimit
 _offset = request.form['offset']
 _total_records = 0
con = mysql.connect()
cursor = con.cursor()
cursor.callproc('sp_GetWishByUser',(_user,_limit,_offset,_total_records))
wishes = cursor.fetchall()
cursor.close()
cursor = con.cursor()
cursor.execute('SELECT @_sp_GetWishByUser_3');
outParam = cursor.fetchall()

As you can see in the above code, once we’ve called the stored procedure we close the cursor and open a new cursor to select the returned out parameter.

Earlier, we were returning a list of wishes from the Python method. Now, we also need to include the total records count in the returned JSON. So we’ll make the wish list dictionary into another list and then add the wish list and record count to the main list. Here is the modified code of the getWish python method.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
response = []
wishes_dict = []
for wish in wishes:
    wish_dict = {
        'Id': wish[0],
        'Title': wish[1],
        'Description': wish[2],
        'Date': wish[4]}
    wishes_dict.append(wish_dict)
    
response.append(wishes_dict)
response.append({'total':outParam[0][0]})
return json.dumps(response)

In the GetWishes JavaScript function, inside the success callback add a console log.

1
console.log(res);

Save all the above changes and restart the server. Sign in using a valid email address and password and when on the user home page, check the browser console. You should be able to see a response similar to the one shown below:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
[
    [{
        "Date": "Sun, 15 Feb 2015 15:10:45 GMT",
        "Description": "wwe",
        "Id": 5,
        "Title": "wwe"
    }, {
        "Date": "Sat, 24 Jan 2015 00:13:50 GMT",
        "Description": "Travel to Spain",
        "Id": 4,
        "Title": "Spain"
    }], {
        "total": 5
    }
]

Using the total count received from the response, we can get the total number of pages.

1
2
var total = wishObj[1]['total'];
var pageCount = total/itemsPerPage;

Dividing the total items count from itemsPerPage count gives us the number of pages required. But this holds true only when the total is a multiple of itemsPerPage. If that’s not the case, we’ll have to check for that and handle the page count accordingly.

1
2
3
4
var pageRem = total%itemsPerPage;
if(pageRem !=0 ){
    pageCount = Math.floor(pageCount)+1;
}

So that will give us the correct page count.

Now since we have the total number of pages, we’ll create the pagination HTML dynamically. Remove the LI element from the pagination HTML we added earlier.

1
2
3
4
5
<nav>
    <ul class="pagination">
        // li we'll create dynamically
    </ul>
</nav>

In the GetWishes success callback, let’s create the previous link dynamically using jQuery.

1
2
3
4
5
6
7
8
var prevLink = $('<li/>').append($('<a/>').attr({
        'href': '#'
    }, {
        'aria-label': 'Previous'
    })
    .append($('<span/>').attr('aria-hidden', 'true').html('&laquo;')));
$('.pagination').append(prevLink);

In the above code, we just created the previous button link and appended it to the pagination UL.

Save the above changes and restart the server. On successful sign-in you should be able to see the previous link under the list.

Previous link in the Pagination

Similarly, let’s add the pages in the pagination based on the page count.

1
2
3
4
for (var i = 0; i < pageCount; i++) {
    var page = $('<li/>').append($('<a/>').attr('href', '#').text(i + 1));
    $('.pagination').append(page);
}

Let’s also add the Next link after the pages link have been added.

1
2
3
4
5
6
7
8
var nextLink = $('<li/>').append($('<a/>').attr({
        'href': '#'
    }, {
        'aria-label': 'Next'
    })
    .append($('<span/>').attr('aria-hidden', 'true').html('&raquo;')));
$('.pagination').append(nextLink);

Save the changes and restart the server. Sign in using a valid email address and password, and once on the user home page you should be able to see the pagination.

Pagination in User Home Page

Now comes the main logic that will make our pagination functional. What we’re going to do is attach a click event call on each page index to call the GetWishes JavaScript function. Let’s first attach a click event to the anchor element displaying the page number.

01
02
03
04
05
06
07
08
09
10
11
12
for (var i = 0; i < pageCount; i++) {
    var aPage = $('<a/>').attr('href', '#').text(i + 1);
  
    $(aPage).click(function() {
        
    });
  
    var page = $('<li/>').append(aPage);
    $('.pagination').append(page);
}

So we just attached an onclick event to the page anchor. On each click we’ll call the GetWishes function and pass the offset. So declare the offset outside the for loop.

1
var offset = 0;

Call the GetWishes function inside the click event call.

1
GetWishes(offset);

Also increment the offset based on the number of records shown.

1
offset = offset + 2;

But each time the GetWishes function is called, the value of offset will always be the last one set. So we’ll make use of JavaScript Closures to pass the correct offset to the GetWishes function.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
var offset = 0;
for (var i = 0; i < pageCount; i++) {
    var aPage = $('<a/>').attr('href', '#').text(i + 1);
  
    $(aPage).click(function(offset) {
        return function() {
            GetWishes(offset);
        }
    }(offset));
  
    var page = $('<li/>').append(aPage);
    $('.pagination').append(page);
    offset = offset + itemsPerPage;
}

Save all the above changes and restart the server. Sign in using valid credentials and once on the user home page, try clicking the pages in the pagination UL.

Next, we’ll implement the previous and next page links. It may seem a bit complicated, so let me explain it a bit before we start with the implementation.

We’ll be displaying five pages at a time. Using the next and previous link the user can navigate to the next five and previous five pages respectively. We’ll store the values of the start page and end page and keep updating both on the next and previous button click. So let’s start by adding two hidden fields to the userHome.html page.

1
2
<input type="hidden" id="hdnStart" value="1" />
<input type="hidden" id="hdnEnd" value="5"/>

In the GetWishes success callback, after we have emptied the .pagination UL, add the following line of code to get the latest start page and end page.

1
2
3
4
$('.pagination').empty();
var pageStart = $('#hdnStart').val();
var pageEnd = $('#hdnEnd').val();

No previous button link will be shown when displaying pages 1 to 5. If the pages displayed are greater than 5 then we’ll display the previous button link.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
if (pageStart > 5) {
    var aPrev = $('<a/>').attr({
            'href': '#'
        }, {
            'aria-label': 'Previous'
        })
        .append($('<span/>').attr('aria-hidden', 'true').html('&laquo;'));
    $(aPrev).click(function() {
        // Previous button logic
    });
    var prevLink = $('<li/>').append(aPrev);
    $('.pagination').append(prevLink);
}

When the user clicks the previous button, we’ll reset the hdnStart and hdnEnd values and call the GetWishes JavaScript function.

1
2
3
4
5
$(aPrev).click(function() {
    $('#hdnStart').val(Number(pageStart) - 5);
    $('#hdnEnd').val(Number(pageStart) - 5 + 4);
    GetWishes(Number(pageStart) - 5);
});

Next, based on the start page and the end page we’ll loop and create the page links and append the .pagination UL.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
for (var i = Number(pageStart); i <= Number(pageEnd); i++) {
    if (i > pageCount) {
        break;
    }
    var aPage = $('<a/>').attr('href', '#').text(i);
    
    // Attach the page click event
    $(aPage).click(function(i) {
        return function() {
            GetWishes(i);
        }
    }(i));
    
    var page = $('<li/>').append(aPage);
    // Attach the active page class
    if ((_page) == i) {
        $(page).attr('class', 'active');
    }
    $('.pagination').append(page);
}

By comparing the total page count and the page start value, we’ll decide the display of the next button link.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
if ((Number(pageStart) + 5) <= pageCount) {
    var nextLink = $('<li/>').append($('<a/>').attr({
            'href': '#'
        }, {
            'aria-label': 'Next'
        })
        .append($('<span/>').attr('aria-hidden', 'true').html('&raquo;').click(function() {
            $('#hdnStart').val(Number(pageStart) + 5);
            $('#hdnEnd').val(Number(pageStart) + 5 + 4);
            GetWishes(Number(pageStart) + 5);
        })));
    $('.pagination').append(nextLink);
}

As seen in the above code, on the next button click we are resetting the hdnStart and hdnEnd button values and calling the GetWishes JavaScript function.

So here is the final GetWishes JavaScript function.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
function GetWishes(_page) {
    var _offset = (_page - 1) * 2;
  
    $.ajax({
        url: '/getWish',
        type: 'POST',
        data: {
            offset: _offset
        },
        success: function(res) {
            var itemsPerPage = 2;
            var wishObj = JSON.parse(res);
            $('#ulist').empty();
            $('#listTemplate').tmpl(wishObj[0]).appendTo('#ulist');
            var total = wishObj[1]['total'];
            var pageCount = total / itemsPerPage;
            var pageRem = total % itemsPerPage;
            if (pageRem != 0) {
                pageCount = Math.floor(pageCount) + 1;
            }
            $('.pagination').empty();
            var pageStart = $('#hdnStart').val();
            var pageEnd = $('#hdnEnd').val();
            if (pageStart > 5) {
                var aPrev = $('<a/>').attr({
                        'href': '#'
                    }, {
                        'aria-label': 'Previous'
                    })
                    .append($('<span/>').attr('aria-hidden', 'true').html('&laquo;'));
                $(aPrev).click(function() {
                    $('#hdnStart').val(Number(pageStart) - 5);
                    $('#hdnEnd').val(Number(pageStart) - 5 + 4);
                    GetWishes(Number(pageStart) - 5);
                });
                var prevLink = $('<li/>').append(aPrev);
                $('.pagination').append(prevLink);
            }
            for (var i = Number(pageStart); i <= Number(pageEnd); i++) {
                if (i > pageCount) {
                    break;
                }
                var aPage = $('<a/>').attr('href', '#').text(i);
                $(aPage).click(function(i) {
                    return function() {
                        GetWishes(i);
                    }
                }(i));
                var page = $('<li/>').append(aPage);
                if ((_page) == i) {
                    $(page).attr('class', 'active');
                }
                $('.pagination').append(page);
            }
            if ((Number(pageStart) + 5) <= pageCount) {
                var nextLink = $('<li/>').append($('<a/>').attr({
                        'href': '#'
                    }, {
                        'aria-label': 'Next'
                    })
                    .append($('<span/>').attr('aria-hidden', 'true').html('&raquo;').click(function() {
                        $('#hdnStart').val(Number(pageStart) + 5);
                        $('#hdnEnd').val(Number(pageStart) + 5 + 4);
                        GetWishes(Number(pageStart) + 5);
                    })));
                $('.pagination').append(nextLink);
            }
        },
        error: function(error) {
            console.log(error);
        }
    });
}

Save all the above changes and restart the server. Sign in using a valid email address and password. You should be able to see the fully functional pagination for the user wish list.

In this part of series, we implemented the pagination functionality for the wish list on the user home page. We saw how to retrieve data using a MySQL stored procedure and create pagination using that data, jQuery and Bootstrap.

In the next part of this tutorial series, we’ll implement the file upload functionality into our application.

Source code from this tutorial is available on GitHub.

Do let us know your thoughts in the comments below!

This post is part of a series called Creating a Web App From Scratch Using Python Flask and MySQL.
Creating a Web App From Scratch Using Python Flask and MySQL: Part 5
Creating a Web App From Scratch Using Python Flask and MySQL: Part 7

In the previous part of this series, we implemented paging for the wish list on the user home page. In this part of the series, we’ll implement an option for the user to upload an image representing a wish, an option to mark the wish as accomplished, and an option to set privacy.

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

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

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

1
2
cd PythonFlaskMySQLApp_Part5
python app.py

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

Let’s start by modifying our “add wish” page to include an option to upload an image. Navigate to templates/addWish.html. Our form in addWish.html looks quite small, so let’s modify the bootstrap HTML code to make the form vertical.

First, we’ll modify the form-horizontal to a vertical form, so remove the class form-horizontal from the form. We’ll also add three new controls: a file upload control to upload photos, a check box to mark the wish as private, and another check box to mark the wish as completed. Here is the modified addWish.html.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Python Flask Bucket List App</title>
    <script src="../static/js/jquery-1.11.2.js"></script>
    <style>
        .btn-file {
            position: relative;
            overflow: hidden;
        }
        
        .btn-file input[type=file] {
            position: absolute;
            top: 0;
            right: 0;
            min-width: 100%;
            min-height: 100%;
            font-size: 100px;
            text-align: right;
            filter: alpha(opacity=0);
            opacity: 0;
            outline: none;
            background: white;
            cursor: inherit;
            display: block;
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <nav>
                <ul class="nav nav-pills pull-right">
                    <li role="presentation" class="active"><a href="#">Add Item</a>
                    </li>
                    <li role="presentation"><a href="/logout">Logout</a>
                    </li>
                </ul>
            </nav>
            <h3 class="text-muted">Python Flask App</h3>
        </div>
        <form role="form" method="post" action="/addWish">
            <!-- Form Name -->
            <legend>Create Your Wish</legend>
            <!-- Text input-->
            <div class="form-group">
                <label for="txtTitle">Title</label>
                <input id="txtTitle" name="inputTitle" type="text" placeholder="placeholder" class="form-control input-md">
            </div>
            <!-- Textarea -->
            <div class="form-group">
                <label for="txtPost">Description</label>
                <textarea class="form-control" id="txtPost" name="inputDescription"></textarea>
            </div>
            <div class="form-group">
                <label for="txtPost">Photos</label>
                <div class="input-group">
                    <span class="input-group-btn">
                    <span class="btn btn-primary btn-file">
                        Browse&hellip; <input type="file" id="fileupload" name="file" multiple>
                    </span>
                    </span>
                    <input type="text" class="form-control" readonly>
                </div>
            </div>
            <div class="form-group">
                <label>Mark this as private and not visible to others.</label>
                <br/>
                <input type="checkbox"> Mark as Private <span class="glyphicon glyphicon-lock" aria-hidden="true"></span>
            </div>
            <div class="form-group">
                <label>Have you already accomplished this?</label>
                <br/>
                <input type="checkbox"> Mark as Done <span class="glyphicon glyphicon-ok" aria-hidden="true"></span>
            </div>
            <!-- Button -->
            <div class="form-group">
                <p class="text-center">
                    <input id="singlebutton" name="singlebutton" class="btn btn-primary" type="submit" value="Publish" />
                </p>
            </div>
        </form>
        <footer class="footer">
            <p>&copy; Company 2015</p>
        </footer>
    </div>
</body>
</html>

Save the above change and restart the server. After signing in successfully, click on the Add Wish link and you should be able to see the modified add wish page.

Add Wish Page with Image Upload
Advertisement

We’ll be using blueimp jQuery-File-Upload to implement the file upload functionality. Download the required the files from GitHub. Extract the source and add the following script references to addWish.html.

1
2
3
4
5
6
7
8
9
<script src="../static/js/jquery-1.11.2.js"></script>
<script src="../static/js/jquery.ui.widget.js"></script>
<script type="text/javascript" src="../static/js/jquery.fileupload.js"></script>
<script type="text/javascript" src="../static/js/jquery.fileupload-process.js"></script>
<script type="text/javascript" src="../static/js/jquery.fileupload-ui.js"></script>

On addWish.html page load, add the plugin initiation code to the file upload button click.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
$(function() {
    $('#fileupload').fileupload({
        url: 'upload',
        dataType: 'json',
        add: function(e, data) {
            data.submit();
        },
        success: function(response, status) {
            console.log(response);
        },
        error: function(error) {
            console.log(error);
        }
    });
})

As seen in the above code, we have attached the file upload plugin to the #fileupload button. The file upload plugin posts the file to the /upload request handler, which we’ll define in our Python code. We have also defined an add function to submit the data, and defined success and failure callbacks to handle the upload success and failures.

Next, let’s define the upload Python file upload handler in app.py. Define a route /upload as shown:

1
2
3
@app.route('/upload', methods=['GET', 'POST'])
def upload():
    # file upload handler code will be here

Check if the request is a POST request, and if so read the file from request.

1
2
if request.method == 'POST':
        file = request.files['file']

We’ll also need to get the image file extension to save the file. So import os and then split the extension name from the file name.

1
extension = os.path.splitext(file.filename)[1]

Once we have the file extension, we’ll create a new unique file name using uuid. Import uuid and create the file name.

1
f_name = str(uuid.uuid4()) + extension

Create a folder called Uploads in the static folder. This is where we’ll keep the uploaded images. Add the path to the Upload folder in the app configuration.

1
app.config['UPLOAD_FOLDER'] = 'static/Uploads'

Now save the posted file into the UPLOAD_FOLDER location and return the file name as a response.

1
2
file.save(os.path.join(app.config['UPLOAD_FOLDER'], f_name))
return json.dumps({'filename':f_name})

Save the above changes and restart the server. Point your browser to the http://localhost:5002 and sign in using valid credentials. Try to upload an image using the browse button, and when done, check your browser console. You should be able to see the returned uploaded file name.

Instead of the read-only input text field, let’s add an image element to display the uploaded image. So replace the read-only input text field with the following HTML code.

1
2
3
<div class="pull-right">
    <img id="imgUpload" style="width: 140px; height: 140px;" class="img-thumbnail">
</div>

In the file upload success callback, update #imgUpload‘s src to the uploaded image.

1
$('#imgUpload').attr('src','static/Uploads/'+response.filename);

Save the above changes and restart the server. Sign in to the application and try to upload a new image file, and you should be able to see the uploaded image.

Add Wish Page With Upload

We’ll need to modify our tbl_wish table structure to include three new fields. Alter the tbl_wish as shown below:

1
2
3
4
ALTER TABLE `BucketList`.`tbl_wish`
ADD COLUMN `wish_file_path` VARCHAR(200) NULL AFTER `wish_date`,
ADD COLUMN `wish_accomplished` INT NULL DEFAULT 0 AFTER `wish_file_path`,
ADD COLUMN `wish_private` INT NULL DEFAULT 0 AFTER `wish_accomplished`;

Next let’s modify our stored procedures sp_addWish and sp_updateWish to include the newly added fields to the database.

Modify the sp_addWish stored procedure to include the three newly added fields.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
USE `BucketList`;
DROP procedure IF EXISTS `sp_addWish`;
DELIMITER $$
USE `BucketList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_addWish`(
    IN p_title varchar(45),
    IN p_description varchar(1000),
    IN p_user_id bigint,
    IN p_file_path varchar(200),
    IN p_is_private int,
    IN p_is_done int
)
BEGIN
    insert into tbl_wish(
        wish_title,
        wish_description,
        wish_user_id,
        wish_date,
        wish_file_path,
        wish_private,
        wish_accomplished
    )
    values
    (
        p_title,
        p_description,
        p_user_id,
        NOW(),
        p_file_path,
        p_is_private,
        p_is_done
    );
END$$
DELIMITER ;

Also modify the stored procedure sp_updateWish to include the three newly added fields.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE `BucketList`;
DROP procedure IF EXISTS `sp_updateWish`;
DELIMITER $$
USE `BucketList`$$
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,
IN p_file_path varchar(200),
IN p_is_private int,
IN p_is_done int
)
BEGIN
update tbl_wish set
    wish_title = p_title,
    wish_description = p_description,
    wish_file_path = p_file_path,
    wish_private = p_is_private,
    wish_accomplished = p_is_done
    where wish_id = p_wish_id and wish_user_id = p_user_id;
END$$
DELIMITER ;

Next, modify the /addWish request handler’s method to read the newly posted fields and pass them to the stored procedure.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
if request.form.get('filePath') is None:
    _filePath = ''
else:
    _filePath = request.form.get('filePath')
    
if request.form.get('private') is None:
    _private = 0
else:
    _private = 1
    
if request.form.get('done') is None:
    _done = 0
else:
    _done = 1

Once the values have been read, we’ll pass them to the MySQL stored procedure call.

1
cursor.callproc('sp_addWish',(_title,_description,_user,_filePath,_private,_done))

In the addWish.html page we’ll need to set the name attribute for the elements to be posted. So add name to both the newly-added check boxes.

1
2
3
<input name="private" type="checkbox"> Mark as Private <span class="glyphicon glyphicon-lock" aria-hidden="true"></span>
<input name="done" type="checkbox"> Mark as Done <span class="glyphicon glyphicon-ok" aria-hidden="true"></span>

Now we also need to pass the upload file path. So we’ll create a hidden input field and set its value in the file upload success callback.

1
<input type="hidden" name="filePath" id="filePath"></input>

Set its value in the file upload success callback.

1
2
3
4
5
6
7
8
success: function(response, status) {
    var filePath = 'static/Uploads/' + response.filename;
    $('#imgUpload').attr('src', filePath);
    $('#filePath').val(filePath);
}

Save the above changes and restart the server. Sign in using valid credentials and try to add a new wish with all the required details. Once added successfully, it should be listed on the user home page.

First, we need to add some HTML code for the three new fields. So open up userHome.html and add the following HTML code after the title and description HTML.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<div class="form-group">
    <label for="txtPost">Photos</label>
    <div class="input-group">
        <span class="input-group-btn">
                    <span class="btn btn-primary btn-file">
                        Browse&hellip; <input type="file" id="fileupload" name="file" multiple>
                    </span>
        </span>
        <div class="pull-right">
            <img id="imgUpload" style="width: 140px; height: 140px;" class="img-thumbnail">
            <input type="hidden" name="filePath" id="filePath"></input>
        </div>
    </div>
</div>
<div class="form-group">
    <label>Mark this as private and not visible to others.</label>
    <br/>
    <input id="chkPrivate" name="private" type="checkbox"> Mark as Private <span class="glyphicon glyphicon-lock" aria-hidden="true"></span>
</div>
<div class="form-group">
    <label>Have you already accomplished this?</label>
    <br/>
    <input id="chkDone" name="done" type="checkbox"> Mark as Done <span class="glyphicon glyphicon-ok" aria-hidden="true"></span>
</div>

We’ll need to fetch the required data to populate the above fields on edit. So let’s modify the stored procedure sp_GetWishById to include the additional fields as shown:

1
2
3
4
5
6
7
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetWishById`(
IN p_wish_id bigint,
In p_user_id bigint
)
BEGIN
select wish_id,wish_title,wish_description,wish_file_path,wish_private,wish_accomplished from tbl_wish where wish_id = p_wish_id and wish_user_id = p_user_id;
END

Next, we’ll need to modify the JSON string in the /getWishById route method to include the new fields. Modify the wish list in /getWishById as shown:

1
wish.append({'Id':result[0][0],'Title':result[0][1],'Description':result[0][2],'FilePath':result[0][3],'Private':result[0][4],'Done':result[0][5]})

To render the result, we need to parse the data received in the success callback of the Edit JavaScript function in userHome.html.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
success: function(res) {
    var data = JSON.parse(res);
    
    $('#editTitle').val(data[0]['Title']);
  
    $('#editDescription').val(data[0]['Description']);
  
    $('#imgUpload').attr('src', data[0]['FilePath']);
  
    if (data[0]['Private'] == "1") {
        $('#chkPrivate').attr('checked', 'checked');
    }
  
    if (data[0]['Done'] == "1") {
        $('#chkDone').attr('checked', 'checked');
    }
  
    $('#editModal').modal();
}

Save the changes and restart the server. Sign in using valid credentials, and when on the user home page, try to edit a wish from the wish list. You should have the data populated in the Edit popup.

Edit Pop Up With Additional Fields

Now, similar to what we did on the add wish page, add the jQuery-File-Upload script reference in userHome.html.

1
2
3
4
5
6
7
8
9
<script src="../static/js/jquery-1.11.2.js"></script>
<script src="../static/js/jquery.ui.widget.js"></script>
<script type="text/javascript" src="../static/js/jquery.fileupload.js"></script>
<script type="text/javascript" src="../static/js/jquery.fileupload-process.js"></script>
<script type="text/javascript" src="../static/js/jquery.fileupload-ui.js"></script>

Initialize the file upload control in the edit popup using the same code we used on the add wish page.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
$(function() {
    $('#fileupload').fileupload({
        url: 'upload',
        dataType: 'json',
        add: function(e, data) {
            data.submit();
        },
        success: function(response, status) {
            
            var filePath = 'static/Uploads/' + response.filename;
            $('#imgUpload').attr('src', filePath);
            $('#filePath').val(filePath);
           
        },
        error: function(error) {
            console.log(error);
        }
    });
})

Next we need to modify the Update button click in the Edit popup to include the extra fields added. So, in the btnUpdate button click, modify the data parameters passed to include the three new fields as shown:

1
data : {title:$('#editTitle').val(),description:$('#editDescription').val(),id:localStorage.getItem('editId'),filePath:$('#imgUpload').attr('src'),isPrivate:$('#chkPrivate').is(':checked')?1:0,isDone:$('#chkDone').is(':checked')?1:0}

Open up app.py and modify the /updateWish request handler method to parse the newly added fields.

1
2
3
_filePath = request.form['filePath']
_isPrivate = request.form['isPrivate']
_isDone = request.form['isDone']

Modify the procedure calling method to include the extra parameters.

1
cursor.callproc('sp_updateWish',(_title,_description,_wish_id,_user,_filePath,_isPrivate,_isDone))

Now open up sp_updateWish and modify it to include the newly added fields.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
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,
IN p_file_path varchar(200),
IN p_is_private int,
IN p_is_done int
)
BEGIN
update tbl_wish set
    wish_title = p_title,
    wish_description = p_description,
    wish_file_path = p_file_path,
    wish_private = p_is_private,
    wish_accomplished = p_is_done
    where wish_id = p_wish_id and wish_user_id = p_user_id;
END

Save all the above changes and restart the server. Sign in using valid credentials and try to edit and update the existing entries.

In this part of the tutorial series, we saw how to integrate and use the blueimp jQuery-File-Upload plugin to upload images in our Python Flask application. In the next part of this series, we’ll show the wishes accomplished by users on the application home page and add the functionality to like the wishes.

Do let us know your thoughts, corrections and suggestions in the comments below. Source code from this tutorial is available on GitHub.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s