6. Data PersistenceΒΆ

A major problem with the application as implemented so far is that the data the user enters do not persist. Every time, 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 Wicket; 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 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
)

Tip

You can use the SQLite Manager add-on for Firefox to manage SQLite databases:

https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

The methods for adding a movie, removing a movie, updating a movie, getting a movie by id, and getting all movies are given below. These are simple JDBC operations and are not within the scope of this tutorial.

 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
import sqlite3 as dbapi2

from movie import Movie


class Store:
    def __init__(self, dbfile):
        self.dbfile = dbfile
        self.last_key = None

    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()
            self.last_key = cursor.lastrowid

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

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

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

    def get_movies(self):
        with dbapi2.connect(self.dbfile) as connection:
            cursor = connection.cursor()
            query = "SELECT ID, TITLE, YR FROM MOVIE ORDER BY ID"
            cursor.execute(query)
            movies = [(key, Movie(title, year))
                      for key, title, year in cursor]
        return movies
  • note the list comprehension (lines 46-47)

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

1
2
3
if __name__ == '__main__':
    app.store = Store(os.path.join(os.getenv('HOME'), 'movies.sqlite'))
    app.run(host='0.0.0.0', port=5000, debug=True)