6. Persistence

A major problem with the application as implemented so far is that the data entered by the user do not persist. At each run, the application starts with an empty collection and added movies are lost when the application is shut down. In this chapter we will see how to store the data in a database. This chapter has nothing to do with Flask; it just stores the data in an SQL database and assumes that the reader knows about SQL.

To keep things simple and to avoid database installation or configuration issues, we will use an SQLite database.

Create a database with the name movies.sqlite in your home directory and create a table in it using the following SQL statement:

CREATE TABLE MOVIE (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  TITLE VARCHAR(80) NOT NULL,
  YR INTEGER
)

The code for SQLite database operations are given in Listing 6.1. These are not within the scope of this tutorial.

Listing 6.1 Database class for SQLite storage (file: database.py, version: v0601).
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import sqlite3 as dbapi2

from movie import Movie


class Database:
    def __init__(self, dbfile):
        self.dbfile = dbfile

    def add_movie(self, movie):
        with dbapi2.connect(self.dbfile) as connection:
            cursor = connection.cursor()
            query = "INSERT INTO MOVIE (TITLE, YR) VALUES (?, ?)"
            cursor.execute(query, (movie.title, movie.year))
            connection.commit()
            movie_key = cursor.lastrowid
        return movie_key

    def update_movie(self, movie_key, movie):
        with dbapi2.connect(self.dbfile) as connection:
            cursor = connection.cursor()
            query = "UPDATE MOVIE SET TITLE = ?, YR = ? WHERE (ID = ?)"
            cursor.execute(query, (movie.title, movie.year, movie_key))
            connection.commit()

    def delete_movie(self, movie_key):
        with dbapi2.connect(self.dbfile) as connection:
            cursor = connection.cursor()
            query = "DELETE FROM MOVIE WHERE (ID = ?)"
            cursor.execute(query, (movie_key,))
            connection.commit()

    def get_movie(self, movie_key):
        with dbapi2.connect(self.dbfile) as connection:
            cursor = connection.cursor()
            query = "SELECT TITLE, YR FROM MOVIE WHERE (ID = ?)"
            cursor.execute(query, (movie_key,))
            title, year = cursor.fetchone()
        movie_ = Movie(title, year=year)
        return movie_

    def get_movies(self):
        movies = []
        with dbapi2.connect(self.dbfile) as connection:
            cursor = connection.cursor()
            query = "SELECT ID, TITLE, YR FROM MOVIE ORDER BY ID"
            cursor.execute(query)
            for movie_key, title, year in cursor:
                movies.append((movie_key, Movie(title, year)))
        return movies

Finally, we have to change the database configuration of the application. The database is assumed to be a file named movies.sqlite in the user’s home directory.

Listing 6.2 Server that uses an SQLite database (file: server.py, version: v0601).
1
2
3
    home_dir = os.path.expanduser("~")
    db = Database(os.path.join(home_dir, "movies.sqlite"))
    app.config["db"] = db