In this article by Gareth Dwyer, author of the book, Flask By Example, we will cover how to set up a MySQL database on our VPS and creating a database for the crime data. We’ll follow on from this by setting up a basic page containing a map and textbox. We’ll see how to link Flask to MySQL by storing data entered into the textbox in our database.
We won’t be using an ORM for our database queries or a JavaScript framework for user input and interaction. This means that there will be some laborious writing of SQL and vanilla JavaScript, but it’s important to fully understand why tools and frameworks exist, and what problems they solve, before diving in and using them blindly.
(For more resources related to this topic, see here.)
We’ll cover the following topics:
- Introduction to SQL Databases
- Installing MySQL on our VPS
- Connecting to MySQL from Python and creating the database
- Connecting to MySQL from Flask and inserting data
Setting up
We’ll create a new git repository for our new code base, since although some of the setup will be similar, our new project should be completely unrelated to our first one. If you need more help with this step, head back to the setup of the first project and follow the detailed instructions there. If you’re feeling confident, see if you can do it just with the following summary:
- Head over to the website for bitbucket, GitHub, or whichever hosting platform you used for the first project. Log in and use their Create a new repository functionality.
- Name your repo crimemap, and take note of the URL you’re given.
- On your local machine, fire up a terminal and run the following commands:
mkdir crimemap cd crimemap git init git remote add origin <git repository URL>
We’ll leave this repository empty for now as we need to set up a database on our VPS. Once we have the database installed, we’ll come back here to set up our Flask project.
Understanding relational databases
In its simplest form, a relational database management system, such as MySQL, is a glorified spreadsheet program, such as Microsoft Excel: We store data in rows and columns. Every row is a “thing” and every column is a specific piece of information about the thing in the relevant row. I put “thing” in inverted commas because we’re not limited to storing objects. In fact, the most common example, both in the real world and in explaining databases, is data about people. A basic database storing information about customers of an e-commerce website could look something like the following:
| ID | First Name | Surname | Email Address | Telephone |
| 1 | Frodo | Baggins | [email protected] | +1 111 111 1111 |
| 2 | Bilbo | Baggins | [email protected] | +1 111 111 1010 |
| 3 | Samwise | Gamgee | [email protected] | +1 111 111 1001 |
If we look from left to right in a single row, we get all the information about one person. If we look at a single column from top to bottom, we get one piece of information (for example, an e-mail address) for everyone. Both can be useful—if we want to add a new person or contact a specific person, we’re probably interested in a specific row. If we want to send a newsletter to all our customers, we’re just interested in the e-mail column.
So why can’t we just use spreadsheets instead of databases then?
Well, if we take the example of an e-commerce store further, we quickly see the limitations. If we want to store a list of all the items we have on offer, we can create another table similar to the preceding one, with columns such as “Item name”, “Description”, “Price”, and “Quantity in stock”. Our model continues to be useful. But now, if we want to store a list of all the items Frodo has ever purchased, there’s no good place to put the data. We could add 1000 columns to our customer table: “Purchase 1”, “Purchase 2”, and so on up to “Purchase 1000”, and hope that Frodo never buys more than 1000 items. This isn’t scalable or easy to work with: How do we get the description for the item Frodo purchased last Tuesday? Do we just store the item’s name in our new column? What happens with items that don’t have unique names?
Soon, we realise that we need to think about it backwards. Instead of storing the items purchased by a person in the “Customers” table, we create a new table called “Orders” and store a reference to the customer in every order. Thus, an order knows which customer it belongs to, but a customer has no inherent knowledge of what orders belong to them.
While our model still fits into a spreadsheet at the push of a button, as we grow our data model and data size, our spreadsheet becomes cumbersome. We need to perform complicated queries such as “I want to see all the items that are in stock and have been ordered at least once in the last 6 months and cost more than $10.”
Enter Relational database management systems (RDBMS). They’ve been around for decades and are a tried and tested way of solving a common problem—storing data with complicated relations in an organized and accessible manner. We won’t be touching on their full capabilities in our crime map (in fact, we could probably store our data in a .txt file if we needed to), but if you’re interested in building web applications, you will need a database at some point. So, let’s start small and add the powerful MySQL tool to our growing toolbox.
I highly recommend learning more about databases. If the taster you experience while building our current project takes your fancy, go read and learn about databases. The history of RDBMS is interesting, and the complexities and subtleties of normalization and database varieties (including NoSQL databases, which we’ll see something of in our next project) deserve more study time than we can devote to them in a book that focuses on Python web development.
Installing and configuring MySQL
Installing and configuring MySQL is an extremely common task. You can therefore find it in prebuilt images or in scripts that build entire stacks for you. A common stack is called the LAMP (Linux, Apache, MySQL, and PHP) stack, and many VPS providers provide a one-click LAMP stack image.
As we are already using Linux and have already installed Apache manually, after installing MySQL, we’ll be very close to the traditional LAMP stack, just using the P for Python instead of PHP. In keeping with our goal of “education first”, we’ll install MySQL manually and configure it through the command line instead of installing a GUI control panel. If you’ve used MySQL before, feel free to set it up as you see fit.
Installing MySQL on our VPS
Installing MySQL on our server is quite straightforward. SSH into your VPS and run the following commands:
sudo apt-get update
sudo apt-get install mysql-serverYou should see an interface prompting you for a root password for MySQL. Enter a password of your choice and repeat it when prompted. Once the installation has completed, you can get a live SQL shell by typing the following command and entering the password you chose earlier:
mysql –pWe could create a database and schema using this shell, but we’ll be doing that through Python instead, so hit Ctrl + C to terminate the MySQL shell if you opened it.
Installing Python drivers for MySQL
Because we want to use Python to talk to our database, we need to install another package. There are two main MySQL connectors for Python: PyMySql and MySqlDB. The first is preferable from a simplicity and ease-of-use point of view. It is a pure Python library, meaning that it has no dependencies. MySqlDB is a C extension, and therefore has some dependencies, but is, in theory, a bit faster. They work very similarly once installed.
To install it, run the following (still on your VPS):
sudo pip install pymysqlCreating our crimemap database in MySQL
Some knowledge of SQL’s syntax will be useful for the rest of this article, but you should be able to follow either way. The first thing we need to do is create a database for our web application. If you’re comfortable using a command-line editor, you can create the following scripts directly on the VPS as we won’t be running them locally and this can make them easier to debug. However, developing over an SSH session is far from ideal, so I recommend that you write them locally and use git to transfer them to the server before running. This can make debugging a bit frustrating, so be extra careful in writing these scripts. If you want, you can get them directly from the code bundle that comes with this book. In this case, you simply need to populate the Password field correctly and everything should work.
Creating a database setup script
In the crimemap directory where we initialised our git repo in the beginning, create a Python file called db_setup.py, containing the following code:
import pymysql
import dbconfig
connection = pymysql.connect(host='localhost',
user=dbconfig.db_user, passwd=dbconfig.db_password)
try:
with connection.cursor() as cursor:
sql = "CREATE DATABASE IF NOT EXISTS crimemap"
cursor.execute(sql)
sql = """CREATE TABLE IF NOT EXISTS crimemap.crimes (
id int NOT NULL AUTO_INCREMENT,
latitude FLOAT(10,6),
longitude FLOAT(10,6),
date DATETIME,
category VARCHAR(50),
description VARCHAR(1000),
updated_at TIMESTAMP,
PRIMARY KEY (id)
)"""
cursor.execute(sql);
connection.commit()
finally:
connection.close()Let’s take a look at what this code does. First, we import the pymysql library we just installed. We also import dbconfig, which we’ll create locally in a bit and populate with the database credentials (we don’t want to store these in our repository). Then, we create a connection to our database using localhost (because our database is installed on the same machine as our code) and the credentials that don’t exist yet.
Now that we have a connection to our database, we can get a cursor. You can think of a cursor as being a bit like the blinking object in your word processor that indicates where text will appear when you start typing. A database cursor is an object that points to a place in the database where we want to create, read, update, or delete data. Once we start dealing with database operations, there are various exceptions that could occur. We’ll always want to close our connection to the database, so we create a cursor (and do all subsequent operations) inside a try block with a connection.close() in a finally block (the finally block will get executed whether or not the try block succeeds).
The cursor is also a resource, so we’ll grab one and use it in a with block so that it’ll automatically be closed when we’re done with it. With the setup done, we can start executing SQL code.
Creating the database
SQL reads similarly to English, so it’s normally quite straightforward to work out what existing SQL does even if it’s a bit more tricky to write new code. Our first SQL statement creates a database (crimemap) if it doesn’t already exist (this means that if we come back to this script, we can leave this line in without deleting the entire database every time). We create our first SQL statement as a string and use the variable sql to store it. Then we execute the statement using the cursor we created.
Using the database setup script
We save our script locally and push it to the repository using the following command:
git add db_setup.py
git commit –m “database setup script”
git push origin masterWe then SSH to our VPS and clone the new repository to our /var/www directory using the following command:
ssh [email protected]
cd /var/www
git clone <your-git-url>
cd crimemapAdding credentials to our setup script
Now, we still don’t have the credentials that our script relies on. We’ll do the following things before using our setup script:
- Create the dbconfig.py file with the database and password.
- Add this file to .gitignore to prevent it from being added to our repository.
The following are the steps to do so:
- Create and edit dbconfig.py using the nano command:
nano dbconfig.py - Then, type the following (using the password you chose when you installed MySQL):
db_username = “root” db_password = “<your-mysql-password>” - Save it by hitting Ctrl + X and entering Y when prompted.
- Now, use similar nano commands to create, edit, and save .gitignore, which should contain this single line:
dbconfig.py
Running our database setup script
With that done, you can run the following command:
python db_setup.pyAssuming everything goes smoothly, you should now have a database with a table to store crimes. Python will output any SQL errors, allowing you to debug if necessary. If you make changes to the script from the server, run the same git add, git commit, and git push commands that you did from your local machine.
That concludes our preliminary database setup! Now we can create a basic Flask project that uses our database.
Creating an outline for our Flask app
We’re going to start by building a skeleton of our crime map application. It’ll be a basic Flask application with a single page that:
- Displays all data in the crimes table of our database
- Allows users to input data and stores this data in the database
- Has a “clear” button that deletes all the previously input data
Although what we’re going to be storing and displaying can’t really be described as “crime data” yet, we’ll be storing it in the crimes table that we created earlier. We’ll just be using the description field for now, ignoring all the other ones.
The process to set up the Flask application is very similar to what we used before. We’re going to separate out the database logic into a separate file, leaving our main crimemap.py file for the Flask setup and routing.
Setting up our directory structure
On your local machine, change to the crimemap directory. If you created the database setup script on the server or made any changes to it there, then make sure you sync the changes locally. Then, create the templates directory and touch the files we’re going to be using, as follows:
cd crimemap
git pull origin master
mkdir templates
touch templates/home.html
touch crimemap.py
touch dbhelper.pyLooking at our application code
The crimemap.py file contains nothing unexpected and should be entirely familiar from our headlines project. The only thing to point out is the DBHelper() function, whose code we’ll see next. We simply create a global DBHelper() function right after initializing our app and then use it in the relevant methods to grab data from, insert data into, or delete all data from the database.
from dbhelper import DBHelper
from flask import Flask
from flask import render_template
from flask import request
app = Flask(__name__)
DB = DBHelper()
@app.route("/")
def home():
try:
data = DB.get_all_inputs()
except Exception as e:
print e
data = None
return render_template("home.html", data=data)
@app.route("/add", methods=["POST"])
def add():
try:
data = request.form.get("userinput")
DB.add_input(data)
except Exception as e:
print e
return home()
@app.route("/clear")
def clear():
try:
DB.clear_all()
except Exception as e:
print e
return home()
if __name__ == '__main__':
app.run(debug=True)Looking at our SQL code
There’s a little bit more SQL to learn from our database helper code. In dbhelper.py, we need the following:
import pymysql
import dbconfig
class DBHelper:
def connect(self, database="crimemap"):
return pymysql.connect(host='localhost',
user=dbconfig.db_user,
passwd=dbconfig.db_password,
db=database)
def get_all_inputs(self):
connection = self.connect()
try:
query = "SELECT description FROM crimes;"
with connection.cursor() as cursor:
cursor.execute(query)
return cursor.fetchall()
finally:
connection.close()
def add_input(self, data):
connection = self.connect()
try:
query = "INSERT INTO crimes (description) VALUES
('{}');".format(data)
with connection.cursor() as cursor:
cursor.execute(query)
connection.commit()
finally:
connection.close()
def clear_all(self):
connection = self.connect()
try:
query = "DELETE FROM crimes;"
with connection.cursor() as cursor:
cursor.execute(query)
connection.commit()
finally:
connection.close()As in our setup script, we need to make a connection to our database and then get a cursor from our connection in order to do anything meaningful. Again, we perform all our operations in try: …finally: blocks in order to ensure that the connection is closed.
In our helper code, we see three of the four main database operations. CRUD (Create, Read, Update, and Delete) describes the basic database operations. We are either creating and inserting new data or reading, modifying, or deleting existing data. We have no need to update data in our basic app, but creating, reading, and deleting are certainly useful.
Creating our view code
Python and SQL code is fun to write, and it is indeed the main part of our application. However, at the moment, we have a house without doors or windows—the difficult and impressive bit is done, but it’s unusable. Let’s add a few lines of HTML to allow the world to interact with the code we’ve written.
In /templates/home.html, add the following:
<html>
<body>
<head>
<title>Crime Map</title>
</head>
<h1>Crime Map</h1>
<form action="/add" method="POST">
<input type="text" name="userinput">
<input type="submit" value="Submit">
</form>
<a href="/clear">clear</a>
{% for userinput in data %}
<p>{{userinput}}</p>
{% endfor %}
</body>
</html>There’s nothing we haven’t seen before. We have a form with a single text input box to add data to our database by calling the /add function of our app, and directly below it, we loop through all the existing data and display each piece within <p> tags.
Running the code on our VPS
Finally, we just need to make our code accessible to the world. This means pushing it to our git repo, pulling it onto the VPS, and configuring Apache to serve it. Run the following commands locally:
git add
git commit –m "Skeleton CrimeMap"
git push origin master
ssh <username>@<vps-ip-address>And on your VPS use the following command:
cd /var/www/crimemap
git pull origin masterNow, we need a .wsgi file to link our Python code to Apache:
nano crimemap.wsgiThe .wsgi file should contain the following:
import sys
sys.path.insert(0, "/var/www/crimemap")
from crimemap import app as applicationHit Ctrl + X and then Y when prompted to save.
We also need to create a new Apache .conf file and set this as the default (instead of the headlines.conf file that is our current default), as follows:
cd /etc/apache2/sites-available
nano crimemap.confThis file should contain the following:
<VirtualHost *>
ServerName example.com
WSGIScriptAlias / /var/www/crimemap/crimemap.wsgi
WSGIDaemonProcess crimemap
<Directory /var/www/crimemap>
WSGIProcessGroup crimemap
WSGIApplicationGroup %{GLOBAL}
Order deny,allow
Allow from all
</Directory>
</VirtualHost>This is so similar to the headlines.conf file we created for our previous project that you might find it easier to just copy that one and substitute code as necessary.
Finally, we need to deactivate the old site (later on, we’ll look at how to run multiple sites simultaneously off the same server) and activate the new one:
sudo a2dissite headlines.conf
sudo a2enssite crimemap.conf
sudo service apache2 reloadNow, everything should be working. If you copied the code out manually, it’s almost certain that there’s a bug or two to deal with. Don’t be discouraged by this—remember that debugging is expected to be a large part of development! If necessary, do a tail –f on /var/log/apache2/error.log while you load the site in order to see any errors. If this fails, add some print statements to crimemap.py and dbhelper.py to narrow down the places where things are breaking.
Once everything is working, you should be able to see the following in your browser:
Notice how the data we get from the database is a tuple, which is why it is surrounded by brackets and has a trailing comma. This is because we selected only a single field (description) from our crimes table when we could, in theory, be dealing with many columns for each crime (and soon will be).
Summary
That’s it for the introduction to our crime map project.
Resources for Article:
Further resources on this subject:
- Web Scraping with Python[article]
- Python 3: Building a Wiki Application[article]
- Using memcached with Python[article]
