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.

35 thoughts on “Python Web Application Development Using Flask MySQL

  1. thanks.. its good tutorial for beginners…
    can you suggest me which MySQL version used for above app.

    please send me the link…

  2. Hi,

    I got this error, please your help is appreciated :

    /usr/bin/python /Users/majid/PycharmProjects/flaskMysql/hello.py

    Traceback (most recent call last):

    File “/Users/majid/PycharmProjects/flaskMysql/hello.py”, line 2, in

    from flaskext.mysql import MySQL

    ImportError: No module named flaskext.mysql

    1. You have to install flask-mysql, sudo pip install flask-mysql. If you have another problem during the installation, install libmysqlclient-dev. ..

  3. Thank you for this tutorial ! I have an Internal Server Error 500…
    The traceback says “NameError: global name ‘request’ is not defined” I
    need your help Thank you very much

  4. Hi Jay! I am a python programming beginner student. Will this post help me build my application? Or is it not for begineers? Also, if you know, can you please suggest me some sites that help begineers develop python applications?

      1. I actually have to develop an application in a week, so will your tutorial be good enough? I have basic idea of Python and MySQL.
        If you know any other sites that teach web app development in an easier way than yours, could you please give me the link? Since I am really confused as to which one to choose?

        1. Hey Ankita, there are a lot of resources which teach Python web development but ultimately it comes down to what really suits you the best. Here you can find a couple of resources. And there is no ultimate guide. I would suggest trying to create small apps on your own. In case you get stuck, a google search should be able to resolve your programming issue or try posting on stackoverflow. Thanks 🙂

  5. It seems that in the Authenticate function, you used request.args.get(), however it is not indicated in the header of the file where did you import this function. From which module does this function come from?

  6. Hey, I know we can connect to an existing MySQL DB via Flask.
    But, I want to know how can we CREATE a DB in MySQL via Flask ?

  7. hey hi, i have a doubt
    To insert a new user in the same above mentioned example,
    “cursor.execute(“INSERT INTO User (userId,userName,password) VALUES (%s,%s,%s)”, (”,username,password)) ”
    whether this code will work or if not guide me how to do it
    Thanks in adv

  8. Where to write this code:

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

  9. What version of python are you using because mine brings the following error “TypeError: Can’t convert ‘NoneType’ object to str implicitly” under line “cursor.execute(“SELECT * from User where Username='” + username + “‘ and Password='” + password + “‘”)” thanks

    1. This means that either the username or password variable is empty. I got this error because my code said “request.args.get(‘Password’)” while my URL said “&password=admin”. Note the case difference

      1. did you guys end up figuring out how to correct this error? cause i have same error but no idea how to fix it ?

        1. You have to make sure that the capitalization in your URL matches the capitalization in the get argument. So if your URL says &Password=admin but your code says request.args.get(“password”) that won’t work because the cases don’t match. That was what was causing my error, anyway.

          1. hey thanks for reply man i changed it to match everything like you said but im still getting that “nonetype” error >< any other ideas?

Leave a Reply

Your email address will not be published. Required fields are marked *