Building a CRUD application with Flask and SQLAlchemy

In this tutorial, we'll walk through building a minimal web application using Flask. We'll use SQLAlchemy in conjunction with SQLite to store information about books. The application that we build here is not intended to be useful on its own. However, once you can write a basic web application that takes user input and stores this in a database, you are well on your way to writing any web application that you can imagine. We will therefore keep the example application as simple as possible, so that you can focus on the tools themselves instead of application-specific details.

After going through this tutorial, you'll:

• Understand what a web application is and how to build one
• Know how databases work and how to integrate these into your web app
• Be able to quickly build web applications using Python and Flask

What you need

We'll be explaining each step of this tutorial in detail, but you'll find it easier to follow along if you've coded in Python before. It'll also be helpful if you've used HTML before, or at least know what it is.

You need to have Python 3 installed on your computer and be able to install Python libraries through the Python package manager, pip. You can find detailed instructions on how to setup Python for different operating systems over at this Django Girls Tutorial.

We'll be using SQLAlchemy, which is an ORM that hides a lot of SQL logic. If you've run some SQL statements before, it might be easier to understand what's happening under the hood. If you want to learn more about the relation between SQL and SQLAlchemy, you can see my tutorial specifically on that over at Compose.io.

All the code used in this tutorial is available as a GitHub repository. You can clone that if you want to start from the end product instead of building each step.

Project overview

We'll build a very basic book database in this tutorial. Specifically, we'll build an application that allows users to create book titles by entering them as text, read all the book titles entered, update the titles, and delete them. These four operations, create, read, update, and delete, more commonly referred to in database theory as "CRUD," form the basis of nearly all web applications, including Gmail, Amazon.com, and your online banking page. Whatever you want to build, you'll need to take input from your user and store it (let your user create information), display that information back to your user (allow your user to read information), find a solution for outdated or incorrect information (allow users to update information), and remove unnecessary information (allow users to delete information that was previously added). This will all be clearer when we see each of the CRUD operations in action, in our web application.

Getting started

Before we can build our application, we need to install some dependencies. Once we have these, we'll build a basic web page that simply displays static text. After we've got that working, we'll add the database, and then add each of the CRUD operations in turn.

Installing the dependencies

The first thing we need to do is install some third party libraries for Python. These are:

• Flask, which we'll use to route web traffic through HTTP requests to specific functions in our code base,
• SQLAlchemy, which we'll use to make the interaction between Python and our database smoother,
• Flask-SQLAlchemy, which we'll use to make the interaction between Flask and SQLAlchemy smoother.

You can install all of these through pip by running the following command:

pip3 install --user flask sqlalchemy flask-sqlalchemy

If you are used to using virtualenv for your Python projects, then install the libraries inside one of those and drop the --user flag.

Creating a static web page with Flask

One of Flask's selling points as a web framework is the simplicity of getting a basic web page running — we can do this in only a few lines of code. Create a directory for your project called flask-crud-app and create a file inside that called bookmanager.py. Your project structure should look as follows:

flask-crud-app/
    bookmanager.py

Add the following code to the bookmanager.py file:

from flask import Flask

app = Flask(__name__)

@app.route("/")
def home():
    return "My flask app"
  
if __name__ == "__main__":
    app.run(host='0.0.0.0', debug=True)

This is a web application in seven lines of code.

• In line 1, we import Flask
• In line 3, we initialize a flask application, passing in Python's special __name__ variable to let Flask intelligently configure other parts of our application.
• In line 5, we use a Python decorator that Flask provides. This maps the main part of our application (/) to the home() function. We'll see how routing works in more detail when we add more routes.
• In lines 6-7 we define a function that simply returns a static string. This is what will be displayed to the user when they visit our page.
• In lines 9-10, we run our application behind an if guard. This will ensure that we don't start up web servers if we ever import this script into another one (we'll only run the web server if we run this file explicitly).

Testing our static web application

To see Flask in action, run python3 bookmanager.py in your console, from the flask-crud-app directory. You should see output similar to the following:

The important part is the IP address and port, which should be http://127.0.0.1:5000, as in the screenshot. Visit this address in your web browser and you should see the "My flask app" string that we defined in our code displayed, as in the screenshot below. Note that I'm using localhost instead of 127.0.0.1. These are equivalent — they are both "loopback" addresses that send any web requests you make back to your own machine, which is exactly what we want, because we are running the Flask web server and testing from the same machine.

If this doesn't go as expected, you'll see debug output in your console and within the web browser itself (because we set the debug=True flag in the last line of our code).

Handling user input in our web application

At this point, we have a web application, but it doesn't do much. To make it interactive, we need to be able to accept input from our users. We'll do this by adding an HTML form that passes information from the front-end of our application (what our users see), through Flask, and to the back-end (our Python code).

For our application above, we defined the string "My flask app" within our Python code. This was okay, as it was a single line, but as our front-end code grows, it will be cumbersome to define everything in our Python file. Flask allows for separation of concerns though so-called "templates." These are usually .html files, with some Python logic mixed in through the Jinja template engine language. Let's add a template to our application to house all of our front-end code.

Creating an input form

Create a new directory called templates within your project directory. For all of the other names we use for our files and directories, you are free to deviate from what we specify and choose your own names. But for this step, it's important that you use the name templates exactly as specified, as Flask treats this directory differently and knows to look in it for your template files.

Inside the templates directory, create a file called home.html. Your project structure should now look as follows.

flask-crud-app/
    bookmanager.py
    templates/
        home.html

In the home.html file, put the following code:

<html>
  <body>
    <form method="POST" action="/">
      <input type="text" name="title">
      <input type="submit" value="Add">
    </form>
  </body>
</html>

This is a basic HTML page, which contains:

• A text input (line 4), which will associate any input with the name "title."
• A submit button (line 5), which contains the text "Add."
• A direction to send the data ("post") to our web application on the main page (the / route, which is the same page we defined in our .py file).

In the bookmanager.py file, we need to make two changes to make use of our new template. In the imports section, add the following line:

from flask import render_template

And change the return "My flask app" line to:

    return render_template("home.html")

This line tells Flask to look for the home.html file inside the templates directory, process any logic it contains (which it doesn't yet, but we'll be adding some soon), and return the result to the user.

The whole application should now look as follows.

from flask import Flask
from flask import render_template

app = Flask(__name__)

@app.route("/")
def home():
    return render_template("home.html")

if __name__ == "__main__":
    app.run(host='0.0.0.0', debug=True)

Run it again with python3 bookmanager.py in your console (or if it's still running, you should see that the server notices the changes and reloads automatically). Now visit http://127.0.0.1:5000 in your web browser again, and you should see our basic form instead of the "My flask app" string, as in the image below.

This is a simple input box where the user can enter text and press the "Add" button. Doing so will send the text to the back-end of our application and leave us on the same page. Before we can try it, we need to tweak our back-end code to handle this new functionality.

Handling input on the back-end

You'll notice in our home.html template, we had the following line:

<form method="POST" action="/">

The method="POST" section specifies that the data in the form should be sent using HTTP POST. By default, Flask routes only accept HTTP GET requests. You can read about the difference between HTTP POST and HTTP GET at this w3schools tutorial. The only important part for us is that if we submit our form at the moment, we'll get an error saying "Method not allowed." We therefore need to edit our bookmanager.py file to account for POST requests to our web application.

Add the following line to the imports section of bookmanager.py

from flask import request

This imports the request module from Flask, which allows us to easily handle many aspects of HTTP requests, including data sent via forms.

Now, modify the home() function to accept POST requests and to receive the input from our form. The new function should look as follows:

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        print(request.form)
    return render_template("home.html")

Let's look at what these changes do:

• We added methods=["GET", "POST"]) to our route decorator. This will solve the "Method not allowed" error that we saw if we tried submitting the form before. By default, Flask accepts GET requests for all routes, so here we're telling it to allow GET and POST requests.
• We use if request.form to check if someone just submitted the form. If they did, we can access the data that they submitted through the request.form variable. We’ll just print it out for now to see that our form is working.

Restart the web application, if necessary, and visit the page again. Type something into the input field and press the "Add" button. In the console, you should see the string you entered printed as output, as in the image below.

Flask represents all of the form data as an ImmutableMultiDict, which is just a fancy Python dictionary. It stored our user's input as a tuple, with title being the name we gave our input in the home.html template and "test" being the string that I inputted to the form.

Now that we can receive and process input from the user, let's learn how to store it.

Adding a database

We want to remember our users' input, so we'll want to be able to add items to a database. We'll use the popular SQLite database, which comes bundled with the standard installation of Python. It is a file-based database, so we can store our data in a file on our file system, without needing to install a huge Relational Database Management System (RDBMS). We'll use SQLite through SQLAlchemy, which provides a higher level abstraction. This means that we could easily use a different database, such as PostgreSQL or MariaDB, and we wouldn't need to change our codebase. SQLAlchemy also allows us to do database level operations in a more Pythonic way.

Configuring Flask-SQLAlchemy

Because we installed SQLAlchemy and the Flask extension Flask-SQLAlchemy at the start of this tutorial, we can go straight into using them now. We need to set up a database, open a connection to it, and associate this connection with our web application. Modify the first part of the code in your bookmanager.py file (before the first function definition) to look as follows.

import os

from flask import Flask
from flask import render_template
from flask import request

from flask_sqlalchemy import SQLAlchemy

project_dir = os.path.dirname(os.path.abspath(__file__))
database_file = "sqlite:///{}".format(os.path.join(project_dir, "bookdatabase.db"))

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = database_file

db = SQLAlchemy(app)

Let's take a quick look at what we've done.

• In line 1, we add an import for the os Python library, which will allow us to access paths on our file system relative to our project directory.
• In line 7, we import Flask's version of SQLAlchemy (we needed to install Flask-SQLAlchemy and SQLAlchemy. We only import Flask-SQLAlchemy, as this implicitly extends and relies on the base installation of SQLAlchemy).
• In lines 9-10, we figure out where our project path is and set up a database file with its full path and the sqlite:/// prefix to tell SQLAlchemy which database engine we're using.
• In line 13, we tell our web application where our database will be stored.
• In line 15, we initialize a connection to the database and keep this in the db variable. We'll use this to interact with our database.
• In line 16, we initialize the database

This is the configuration we need for our web application to interact with our database. Now we can define the concept of a book and how we'll store this in our database.

Adding a model to our web application

For a real book manager application, we'd want to model a lot of information, such as the book's title, author, publisher, page count, publication date, etc. To keep things simple, we'll pretend that books only have titles (we also only put one input field in our front-end form). Add the following code to bookmanager.py, which represents how we'll store each book in our database. Make sure you add the code below the db = SQLAlchemy(app) line, as we use db to define the book model.

class Book(db.Model):
    title = db.Column(db.String(80), unique=True, nullable=False, primary_key=True)

    def __repr__(self):
        return "<Title: {}>".format(self.title)

There's quite a bit happening in these five lines of code, so let's pull them apart.

• In line 1, we create a new class which inherits from a basic database model, provided by SQLAlchemy. This will also make SQLAlchemy create a table called book, which it will use to store our Book objects.
• In line 2, we create an attribute of our book called title. SQLAlchemy will use this as a column name in our book table. We say that a title consists of a String of at most 80 characters, that we should never store two or more books with the same title (book titles should be unique), that every book needs to have a title (the title isn't nullable), and that the title is the main way that we identify a specific book in our database (the title is the primary_key).
• In lines 4-5, we define how to represent our book object as a string. This allows us to do things like print(book), and see meaningful output.

Creating and storing books

Now, when a user enters a book title, we can create a Book object and store this in our database. To do this, modify the home() function again to look as follows.

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        book = Book(title=request.form.get("title"))
        db.session.add(book)
        db.session.commit()
    return render_template("home.html")

Now, when we receive input from the user, we no longer print it out to the console. Instead, we grab the "title" input from our form, and use it to initialize a new Book object. We save this new Book to a variable named book (line 4).

We then add the book to our database and commit our changes to persist them (lines 5-6).

You can run the code again at this point if you want and try to add an item through the form, but you'll get an error because we haven't finished setting up our database yet.

Initializing our database

Most of our code needs to be run every time we use our application. We need to run some one-off setup code though. Run the following commands in a Python shell in your project directory in order to create our database and create the book table where we'll store our books. You can start a Python shell by running python3 in your console (making sure you are in the project directory).

>>> from bookmanager import db, app
>>> with app.app_context():
>>>>    db.create_all()
>>> exit()

Now you can visit your web application again and submit as many book titles as you want. We're now done with the C part of our CRUD application — we can create books. Next up, we want to be able to read them again.

Retrieving books from our database

Every time the user visits our web application, we want to get all of the current books out of the database and display them. SQLAlchemy makes it easy to load all of the books stored in our database into a Python variable. Add a line to retrieve all of the books just before the end of the home() function and modify the last line to pass these books through to our front-end template. The last two lines of the home() should look as follows.

    books = Book.query.all()
    return render_template("home.html", books=books)

Now, update the home.html file to render each of the books as HTML by using a Jinja for loop. While you're editing the file, you can also add the some headers for the form and the list of books that we'll display. The entire code for home.html should now look as follows.

<html>
  <body>
    <h1>Add book</h1>
    <form method="POST" action="/">
        <input type="text" name="title">
        <input type="submit" value="Add">
    </form>

    <h1>Books</h1>
    {% for book in books %}
      <p>{{book.title}}</p>
    {% endfor %}
  </body>
</html>

Lines 9-12 loop through all of the books that we passed from the back-end code and display each title in a new HTML paragraph. Jinja's syntax is very similar to Python's, but it has some differences. We use {% to indicate that we're using a Jinja control pattern (in this case, a for loop), and we use {{ to indicate that we're referring to a variable instead of a raw string. We also need to use {% endfor %} to close our for loop because, unlike Python, Jinja doesn't regard white space as significant.

Save the template and reload the application in your browser. You should now be able to view books as you add them, as in the image below.

That's CR of CRUD done and dusted — we can now Create and Read books. Next up, we'll look at how to Update the titles of existing books.

Updating book titles

Updating existing data is the most complicated process. Because we are only showing a representation of the data front-end, the user can't edit it directly. Instead, we need our users to submit an updated title while we keep track of the old title. Then, in our back-end code, we can retrieve the correct book based on the old title and update it to the title submitted by the user.

Because the user won't want to type out the old title and the new one, we'll turn every title into its own form. That way, when the user submits the new title, we can automatically get the old title as well. We'll do this with a hidden HTML input so that we can retrieve the old title back-end without it cluttering up our UI front-end.

Modify the for loop in the home.html to look as follows.

{% for book in books %}
  <p>{{book.title}}</p>
  <form method="POST" action="./update">
    <input type="hidden" value="{{book.title}}" name="oldtitle">
    <input type="text" value="{{book.title}}" name="newtitle">
    <input type="submit" value="Update">
  </form>
{% endfor %}

The form is very similar to our previous form that we used to add new books. Some important differences are:

• On line 3, we specify that this form should post its data to the /update route, instead of to our base / route. (We haven't defined the /update route yet. We'll do so below).
• On line 4, we include a hidden input which is prepopulated with the book's "old" title. This section of the form will not be shown to our user, but we'll be able to access the value from our Python code.

Now we need to update our Python code to handle this new functionality. Add the following import to imports section of bookmanager.py

from flask import redirect

And now add the following route and function definition to the bookmanager.py file.

@app.route("/update", methods=["POST"])
def update():
    newtitle = request.form.get("newtitle")
    oldtitle = request.form.get("oldtitle")
    book = Book.query.filter_by(title=oldtitle).first()
    book.title = newtitle
    db.session.commit()
    return redirect("/")

This function is similar to the home() one, but instead of creating books, it

• Gets the old and updated title from the form (lines 3-4)
• Fetches the book with the old title from the database (line 5)
• Updates that book's title to the new title (line 6)
• Saves the book to the database (line 7)
• Redirects the user the the main page

Reload the application in your browser and you'll see a page similar to that shown in the screenshot below. You can change any of the existing books' titles by modifying the text in the input field directly below the title and pressing the "Update" button.

Now we have CRU out of the way, the last thing we need to do is Delete books that we no longer want.

Deleting books from our database

Deleting books is very similar to Updating them, but we don't need to worry about asking the user input a new title.

In the home.html file, add another form inside the for loop, directly below the Update form. This should look as follows.

<form method="POST" action="./delete">
  <input type="hidden" value="{{book.title}}" name="title">
  <input type="submit" value="Delete">
</form>

And add a /delete route and function to the bookmanager.py file, which should look as follows.

@app.route("/delete", methods=["POST"])
def delete():
    title = request.form.get("title")
    book = Book.query.filter_by(title=title).first()
    db.session.delete(book)
    db.session.commit()
    return redirect("/")

This is very similar to our /update route, but we use db.session.delete(book) on line 5 to remove the book from the database, instead of updating its title.

Reload your web application yet again and you should see a "Delete" button below every book. Press this and you'll see the book disappear.

That's each of the CRUD operations done! You now have the basic building blocks to build any web application you want.

Finishing touches

We weren't aiming to build a useful and pretty web application, but we can make one or two small touch-ups to improve the layout and functionality of what we have. If we want to display the book's title, the update, and the delete buttons on a single line, we can put the books into an HTML table, with each book in a new row, and the different sections in different columns.

To do this, update the home.html file to look as follows.

<html>
  <body>
    <h1>Add book</h1>
    <form method="POST" action="/">
      <input type="text" name="title">
      <input type="submit" value="Add">
    </form>

    <h1>Books</h1>
    <table>
      {% for book in books %}
        <tr>
          <td>
            {{book.title}}
          </td>
          <td>
            <form method="POST" action="./update" style="display: inline">
              <input type="hidden" value="{{book.title}}" name="oldtitle">
              <input type="text" value="{{book.title}}" name="newtitle">
              <input type="submit" value="Update">
            </form>
          </td>
          <td>
            <form method="POST" action="./delete" style="display: inline">
              <input type="hidden" value="{{book.title}}" name="title">
              <input type="submit" value="Delete">
            </form>
          </td>
        </tr>
      {% endfor %}
    </table>
  </body>
</html>

If you've never done HTML before, you might want to go through an HTML-specific tutorial quickly. The important parts of the above code are:

• In line 10, we start the table
• In line 12, we start a new row (for each book, as this is within the for loop)
• In lines 13, 16, and 23, we start the three separate columns of the table. These are for the title, the update button, and the delete button respectively.
• In lines 17 and 24, we add a style component to our HTML forms to say that we don't want to display each one on a new line.

If you reload the application now, you'll see that the layout has changed, as in the image below.

The last improvement we'll make is to add some error handling. When we were setting up the database, we specified that the book titles had to be unique. If we try to create a duplicate book, or update an existing title to one that already exists, we'll break our application. Add a try: except: block around the the home() and update() functions to account for this. The final code for both should look as follows.

@app.route('/', methods=["GET", "POST"])
def home():
    books = None
    if request.form:
        try:
            book = Book(title=request.form.get("title"))
            db.session.add(book)
            db.session.commit()
        except Exception as e:
            print("Failed to add book")
            print(e)
    books = Book.query.all()
    return render_template("home.html", books=books)

@app.route("/update", methods=["POST"])
def update():
    try:
        newtitle = request.form.get("newtitle")
        oldtitle = request.form.get("oldtitle")
        book = Book.query.filter_by(title=oldtitle).first()
        book.title = newtitle
        db.session.commit()
    except Exception as e:
        print("Couldn't update book title")
        print(e)
    return redirect("/")

Of course, there are a lot more improvements you could make. If you're looking to extend the application, you could:

• Add more components to the books, including Author, Publisher, and Date of Publication.
• Split off Author and Publisher into their own tables, so that we can represent complicated relationships among books and authors and publishers (each author can have many publishers and many books; each book can have many authors and publishers; each publisher can have many authors and books).
• Display error messages to the user instead of just logging and ignoring them in the back-end code.
• Make the application more aesthetically pleasing by adding some CSS, either custom CSS or through a framework like Bootstrap.

If you enjoyed this post, and want to learn more about Flask, take a look at my book Flask by Example. In it, I walk you through building three fun and useful web applications and show you how to deploy Flask applications to a Virtual Private Server so that they can be accessed by anyone over the internet.

If you have any questions about this post, feel free to comment below or reach out to me on Twitter.