How to build a basic web app in Flask

In our last tutorial, we explored the Postgres database, looking at how to set one up and use it to store some records. We used the Python programming language to access it and enter some records. A database isn't that useful on its own when you want to retrieve records, though, unless you're happy to repeatedly type Structured Query Language (SQL) commands into a terminal.

In this tutorial, we'll build a basic web app to retrieve those records using Flask. This is a micro-framework built using Python designed for building web applications; it handles the basic tasks of a web app, working out what pages to display and managing interactions with visitors. It's a basic platform to which we can add more functionality using third-party libraries, which makes it relatively simple to work with.

Begin by installing Flask using pip3 install flask. You can check the installation by dropping into a Python shell using python3 and then importing the package using from flask import Flask.

As a micro-framework, Flask doesn't come with built-in database interactivity, so we'll also need another tool to do that. In our last tutorial we used a database driver for Python called psycopg2 that allowed us to submit SQL commands to Postgres. However, it still required us to think more like a database.

Relational databases store data in tables and rows, whereas Python is an object-oriented language that treats things as objects. SQLAlchemy is an object-relational manager (ORM) that bridges the two, translating between Python's object-based model and the SQL syntax that psycopg2 uses to interact with Postgres. Install it using pip install SQLAlchemy.

We're not quite done with our setup yet, however. Because we're using the Flask framework, we must install yet another package called Flask-SQLAlchemy, which is an extension that enables Flask to work with SQLAlchemy. After installing it with pip install flask-sqlalchemy you can once again test it in the Python shell: from flask-sqlalchemy import SQLAlchemy.

Building a basic Flask app

Now it's time to begin working on our app, assuming that you've already created a database using our Postgres tutorial. Create a directory for your project, and within it create a file called retronerds.py. In this file, we begin with an import section for importing the packages we need, starting with the Flask package:

Next comes the configuration section where we define some parameters for our app. We start by simply declaring the app:

__name__ gives it the same name as our file, retronerds.

Next, we define the section of the app that describes our routes. These are functions based on which part of the web app we're accessing. To program a route, we use a concept known as a decorator, which is a function that wraps another function inside it, usually modifying its behaviour. This lets us define multiple functions to do different things inside a route.

For now, though, when a visitor hits the root directory of the site (the index page), the function inside the @app.route('/') decorator will display a message. Here is our route code:

At the end of our program is a section of code that runs the app in Flask:

Now we can tell Flask about our app directly in the terminal by entering FLASK_APP=retronerds.py. We will also put Flask into development mode so that it gives us debugging information and updates the web app on the fly when we save our file by entering export FLASK_ENV=development. Now we run it using python3 retronerds.py. If all is well, the server tells us:

That URL is on your own machine. Go visit it to see your welcome message.

Now we can connect our application to our database. To do this, we must configure the database connection for the app by adding this code to the end of the configuration section:

This sets a configuration variable that tells our Flask app about the Postgres database that we created in the last tutorial. It says that we're connecting to Postgres and then states our username and password, followed by the host that the database is on, the standard Postgres connection port, and then the database name. The second line defines db as an instance of SQLAlchemy that we can use to interact with our app. It becomes an object representing the underlying database.

We'll add the following code underneath our configuration section to create a database table and populate it with columns. If you already created the database table in the last tutorial this won't interfere with it, but we still need to define our class:

This code uses a SQLAlchemy class called Model, which maps directly to a table in Postgres. We use this to create a Python class called Game. This maps directly to a table in the retronerds database that we call games. We then use another object in SQLAlchemy called Column to create individual properties of the Game class that map directly to columns in the games table that it represents. We do all this via our db database instance.

Having described the table using our class, we then create it using SQLAlchemy's create_all() database method. If you run the app and use psql to check your database, you should see that it has now created that table.

If you followed the steps in our Postgres tutorial then your table already has some records. Regardless, we will add another record to show you how you can enter data directly using the SQLAlchemy interface rather than programming SQL commands.

To do this, be sure that you are in the same directory where you keep your retronerds.py file, because we need to access it in our code. Then we will drop into the Python shell using python3 and enter the following code, pressing enter after each line to run it:

We begin by importing the db and game objects that we defined in our retronerds app. Then we create an object representing a new record that we want to add to our table (remember that the Game class represents records in the games table). That object includes properties that will populate each field in the record.

Now we have to get that object into the database, which is a two-part process that uses SQLAlchemy's session method. We first ask it to add the record to the database, which stages it for processing. At this point you can still roll back the queued transaction because it has not yet been written to the database; The record is only written to the database when you call the commit command.

We can check to see that our new Game object made it into the database by checking Postgres directly via psql. We'll then adapt our Flask app to show us that last record. Adapt your routes section with this code:

For completeness, here is the full code listing for retronerds.py: The key line here is games = Game.query.all(). This queries the database and retrieves all of the records as a list. We then set up a loop that iterates over all the games in the list and prints out their details.

This brings us one step closer to a bona fide web app. We have the 'read' part of the create/read/update/delete (CRUD) functionality that makes a basic web app - but we still had to create the records outside of the web interface. In order to make it truly useful, however, we'll need to add the ability to create and modify entries in the database without leaving the app itself - in the next tutorial, we'll show you how to do just that.