Using Flask on Python 3 with MySQL

If you’re looking to build a simple web application with a nice frontend, you may think your options are limited as far as what languages to use. For a long time, when I thought about web development, Python never really sprung to mind as an ideal language to use, mostly because I thought it was reserved purely for scripting and other basic operating system-level functions.

As it happens, Python has a fantastic microframework called Flask—which is used to power some incredibly popular websites such as Pinterest, LinkedIn and the community web page for Flask. One of the things that concerned me the most when beginning to use this framework (along with Python itself for such a task) was the ability to interact with a database such as MySQL. Although there are toolkits available such as SQLAlchemy, which are very powerful indeed, I found this to be a little complex for my basic requirements and instead I set out to find an easier way to interact with MySQL. This article will show you what I discovered!

Here’s what we’re going to cover. Grab a coffee and let’s get started!

1. Setting up our development environment
2. Create a basic Flask application which displays a “Hello, World!” web page
3. Introduce database queries to our application
4. Conclusion

Setting up the development environment

There are many seasoned Python developers who will create and maintain their scripts or applications from the command line using tools like Vim (on Linux). However, if you’re fairly new to Python development, then it’s important that you understand the need for an IDE (at least when starting out). The reason for this? Python is quite particular on indentation within your code, and if you get it wrong, you’ll run into some very strange problems indeed. Using a decent IDE will provide you with tips as you go along when it detects mistakes you may have made. I’ve found this to be hugely beneficial since starting Python development, and I really encourage it.

So, which IDE should I use?!

There are many different IDEs available, including complex notepad editors that have Python syntax plugins, such as Visual Studio Code. These tend to miss out on some key features of a decent IDE, though, and as such, I would highly recommend using PyCharm, which is by far widely recognised in the community as the de facto gold standard IDE for Python development. There are community, open-source(!), and paid versions available, and in my short coding career with Python, I’ve found the community version to be more than enough for me.

So without further ado, let’s get started! For the purposes of this article, I will be using PyCharm on Windows 10 (there is a version for Linux and MacOS too, so if you’re following along, please keep an eye out for minor differences).

Let’s start by firing up PyCharm and selecting New Project from the File menu:

Set the name of the project and click Create when you’re ready to proceed.

From the same File menu, select New -> Python File, give it a name, and click OK:

Now we can populate our new file with the code to get us up and running with a basic Flask “Hello, World!” application. At this point, if you haven’t used Flask before on your system, then you’ll need to install it using pip. This can be done with one command from your terminal: pip3 install flask

 from flask import Flask
app = Flask(__name__)

@app.route('/')
def hello_world():
    return 'Hello, World!'

Once you’ve pasted the above code into your IDE, you can test it out by navigating to your project folder in the terminal and running the following commands:

 # set FLASK_APP=mysql-test.py (Use ‘export’ instead of ‘set’ if running under Linux or MacOS)
# python -m flask run

You should see something similar to the following:

This means that a local web server is now listening on port 5000 and you should be able to browse to the URL above and see “Hello, World!” in your web browser. Let’s try it!

Great! So we now have the basis for our application up and running.

Introducing database queries to our application

Now we will want to add some database functionality. MySQL actually publishes an “employees” sample database, which allows one to test database queries and get to grips with SQL, so we’ll use that for our example. At this point, I will be restoring the database into a local MySQL instance on my Windows 10 machine. Doing this on a Linux system with MySQL installed is just as easy.

The easiest way to get this test database is simply to clone it locally using the following command:

 git clone https://github.com/datacharmer/test_db.git

If you are on a Linux machine or have the MySQL client installed locally on your Windows machine, go ahead and run the following commands whilst in the test_db directory:

 # mysql  < employees.sql

If you are running the command above in some environments, you may need to specify a username and password. This can be done with the -u and -p flags respectively.

Once we have imported the employee data, we can then update our main Python script by removing the “Hello, World!” app route and replacing it with a new one that will be the home for our database connection request. We’ll also be adding a small database class to house the database query itself.

 
from flask import Flask, render_template
import pymysql

app = Flask(__name__)


class Database:
    def __init__(self):
        host = "127.0.0.1"
        user = "test"
        password = "password"
        db = "employees"

        self.con = pymysql.connect(host=host, user=user, password=password, db=db, cursorclass=pymysql.cursors.
                                   DictCursor)
        self.cur = self.con.cursor()

    def list_employees(self):
        self.cur.execute("SELECT first_name, last_name, gender FROM employees LIMIT 50")
        result = self.cur.fetchall()

        return result

@app.route('/')
def employees():

    def db_query():
        db = Database()
        emps = db.list_employees()

        return emps

    res = db_query()

    return render_template('employees.html', result=res, content_type='application/json')

Furthermore, we’ll add a basic HTML page with a table to illustrate our database query in action. For this, you’ll need to create a “templates” folder, as Flask requires you to have HTML templates in this structure to work. Once you have done this, your structure should look similar to the following:

The contents of the HTML page are as follows:

{% if result %}

{% for row in result %}

{% endfor %} {% endif %}

If you copy and paste the code above and run your application, you should now see something similar to the following in your browser (please note that we have limited the query output to 50 rows in order to not crash your browser!):

Conclusion

As you can see from this basic example, interacting with MySQL and presenting data on a web page using Python and Flask is actually incredibly simple. If you are fairly new to Python web development, then this is a good way to get to grips with how things work. That being said, once you start to advance and your requirements become more complex, looking at something like SQLAlchemy may be a better route to go down.

Resources

1. The code for this tutorial can be found at: https://github.com/devopper/python3-mysql-example
2. The Flask microframework: http://flask.pocoo.org/
3. W3Schools HTML tables introduction: https://www.w3schools.com/html/html_tables.asp
4. Jinja2 template engine documentation: http://jinja.pocoo.org/docs/2.10/
5. PyMySQL (MySQL client for Python): https://github.com/PyMySQL/PyMySQL
6. More information on SQLAlchemy: https://www.sqlalchemy.org/