Tutorial: ORMs in Flask

3.3. Tutorial: ORMs in Flask#

In this tutorial we will work step-by-step to build a web app using Flask and SQLAlchemy ORM to keep track of a list of books in your personal collection.

At the end of the tutorial you will have built a web app that:

  • lists all books in your collection

  • supports adding new books

  • does not require any SQL!

3.3.1. Setup#

To begin, we will make a basic Flask web app that shows a list of books in the database, which at start will be empty.

We will use the flask-sqlalchemy extension, which simplifies the integration of SQLAlchemy’s ORM with Flask.

Directory structure#
my-books
│
├── app.py
├── static
└── templates
    └── index.html

Flask app#

The code below is our Flask app definition.

app.py#
 1from flask import Flask, render_template
 2from flask_sqlalchemy import SQLAlchemy
 3
 4app = Flask(__name__)
 5
 6app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'
 7db = SQLAlchemy(app)
 8
 9# Define the Book model
10class Book(db.Model):
11    id = db.Column(db.Integer, primary_key=True)
12    title = db.Column(db.String(150), nullable=False)
13    author = db.Column(db.String(100), nullable=False)
14    publication_date = db.Column(db.String(10))  # Simplified for demo
15    genre = db.Column(db.String(50))
16
17
18# Create the database if it doesn't exist
19with app.app_context():
20    db.create_all()
21
22@app.route('/')
23def home():
24    # Query all books (empty for now)
25    books = Book.query.all()
26    return render_template('index.html', books=books)
27
28if __name__ == '__main__':
29    app.run(debug=True, reloader_type='stat', port=5000)

Explanation:

  • Because we are using flask-sqlalchemy, our database configuration and Model specification is different

  • Note that most SQLAlchemy functions and classes are available through the db object

  • A nice feature of flask-sqlalchemy is that common database operations are also available as methods of the model class e.g. Book.query.all(), instead of through a session object.

Homepage template#

The template for the homepage is relatively simple. It will show a list of books if available, otherwise it shows that no books are found.

index.html#
 1<!-- templates/home.html -->
 2<!DOCTYPE html>
 3<html lang="en">
 4<head>
 5    <meta charset="UTF-8">
 6    <title>Book List</title>
 7</head>
 8<body>
 9    <h1>My Book Collection</h1>
10    <ul>
11        {% for book in books %}
12          <li>{{ book.title }} by {{ book.author }}</li>
13        {% else %}
14          <li>No books found yet!</li>
15        {% endfor %}
16    </ul>
17</body>
18</html>

3.3.2. Adding Books#

To add books we need to:

  • Add a form to our homepage to collect the book information and submit a POST request to the web app

  • Add a new route to our Flask app that accepts POST requests and converts form data to a model object

Homepage template#

The template for the homepage should now include a form for adding books, like the one shown below.

index.html#
 1<!-- templates/home.html -->
 2<!DOCTYPE html>
 3<html lang="en">
 4<head>
 5    <meta charset="UTF-8">
 6    <title>Book List</title>
 7</head>
 8<body>
 9    <h1>My Book Collection</h1>
10
11    <h2>Add a New Book</h2>
12    <form action="/add" method="post">
13        <label for="title">Title:</label>
14        <input type="text" id="title" name="title" required><br>
15        
16        <label for="author">Author:</label>
17        <input type="text" id="author" name="author" required><br>
18        
19        <label for="publication_date">Publication Date:</label>
20        <input type="text" id="publication_date" name="publication_date"><br>
21        
22        <label for="genre">Genre:</label>
23        <input type="text" id="genre" name="genre"><br>
24        
25        <input type="submit" value="Add Book">
26    </form>
27
28    <ul>
29        {% for book in books %}
30          <li>{{ book.title }} by {{ book.author }}</li>
31        {% else %}
32          <li>No books found yet!</li>
33        {% endfor %}
34    </ul>
35</body>
36</html>

Flask app#

Now we need a corresponding route in our Flask app to accept the form data.

Below is the route function that we need to add.

Excerpt of app.py#
28@app.route('/add', methods=['POST'])
29def add_book():
30    title = request.form.get('title')
31    author = request.form.get('author')
32    publication_date = request.form.get('publication_date')
33    genre = request.form.get('genre')
34    
35    new_book = Book(
36        title=title,
37        author=author,
38        publication_date=publication_date,
39        genre=genre
40    )
41    db.session.add(new_book)
42    db.session.commit()
43    
44    return redirect(url_for('home'))

Explanation:

  • Inside the route /add the form data is collected

  • This data is used to create a new Book instance

  • Using the db.session object it is added and committed to the database

  • The user is redirected to the homepage, where the new book will appear on the list