Python Web Application Development Using Flask MySQL

Python Web Application Development Using Flask MySQL

Python is one of the widely used languages for web development and Django is the de facto web development framework. But still there are some other frameworks which though not much popular, but are still appreciated by folks who have used it. One such web development framework which is gaining popularity is Flask. Flask is also widely used for creating simple and easy RESTful APIs. In this tutorial, we’ll see how to create a Python web application using Flask MySQL.

Also read : Creating a AngularJS App Powered by Python Flask RESTful API

Developing a Web Application Using Flask MySQL

Flask is a microframework for Python based on Werkzeug, the python WSGI Utility Library and Jinja2, a template engine for Python. Flask has just the bare essentials to get you started with web development without much hassle. To get started with development in Flask :

  • Make sure you have Python installed.
  • Install Flask using Pip pip install Flask
  • Make sure you have MySQL installed.

Now, suppose we are developing a login page for a web application. From the terminal, create a directory for our application.


mkdir FlaskApp
cd FlaskApp

Inside the FlaskApp directory lets create a hello.py. This is how it looks like


from flask import Flask
app = Flask(__name__)
 
@app.route("/")
def hello():
    return "Welcome to Python Flask App!"

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

In the above code, we have simply defined a route / and the function to call hello. Now, time to run the application. From the terminal, type


python hello.py

It would show http://127.0.0.1:5000/. Browse the above URL and it would display the message Welcome to Python Flask App!.

Congrats on running you First Python Flask App 🙂

Also read : How to write Error Log in Python Flask Web App

Connecting Flask MySQL

In order to connect Flask MySQL, we would need to install Flask-MySQL which is an extension, which allows us to connect to MySQL database. In order to install that get to your terminal and type


mysql -u  -p

and then enter your password when prompted. Now, create a new database called EmpData and a table called User with 3 columns – UserId, Username, Password.


mysql> CREATE DATABASE EmpData;
 
mysql> CREATE TABLE User(
 userId INT NOT NULL AUTO_INCREMENT,
 userName VARCHAR(100) NOT NULL,
 password VARCHAR(40) NOT NULL,
 PRIMARY KEY(userId)
 );

Now, insert some data into the table User


mysql> insert into User values('','Admin','admin');

So, we are all set with the MySQL database. Now, let’s go back to the hello.py and try to connect MySQL using flask-mysql.
First of all, initialize the extension by importing it in hello.py as shown below:


from flaskext.mysql import MySQL

Now, we need to configure the database host, database name, user name and password into our hello.py. Simply add the config keys and modify the hello.py as follows:


from flask import Flask
from flaskext.mysql import MySQL
 
mysql = MySQL()
app = Flask(__name__)
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'EmpData'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
 
@app.route("/")
def hello():
    return "Welcome to Python Flask App!"
 
if __name__ == "__main__":
    app.run()

We will be accessing the data from MySQL using cursor. We can create one using our mysql object. We would also be adding another method called Authenticate in order to validate login process.

We’ll be using the request module to parse the get request parameters. Import the Python request module:


from flask import Flask,request

Let’s add the following code for authentication:


@app.route("/Authenticate")
def Authenticate():
    username = request.args.get('UserName')
    password = request.args.get('Password')
    cursor = mysql.connect().cursor()
    cursor.execute("SELECT * from User where Username='" + username + "' and Password='" + password + "'")
    data = cursor.fetchone()
    if data is None:
     return "Username or Password is wrong"
    else:
     return "Logged in successfully"

At this point, we are almost done with out coding and now its time to test our app. Let’s run it by typing


python hello.py

And browse to http://127.0.0.1:5000/Authenticate?UserName=jay&Password=jay since our Authenticate methods expects a UserName and Password to validate. Browsing the above url it shows :


Username or Password is wrong

which is obvious. So let’s now try to browse using the correct username and password http://127.0.0.1:5000/Authenticate?UserName=Admin&Password=admin and we get


Logged in successfully.

Hence It Worked 🙂

Conclusion

Flask is a simple framework for Python web application development. In this tutorial,we saw how to get started with creating Python web application using Flask MySQL. There are a number of different frameworks available for the same purpose. But it varies from individual to individual and depends on how one picks up a particular framework. But for beginners, I think Flask gives a better understanding of the basics of WSGI and Python web development in particular. Next read how to create RESTful APIs using Python Flask and MySQL.

NOTE: SQL statements used in this article are for the sake of simplicity and should not be used as such since they may be prone to SQL injections.