Of course, there are some pre-requisites: Git, PostgreSQL, Python, Psycopg2 and Pyrseas. If you don’t want to install the latter, create the film table according to the 0.1 version here. If you simply want to look at the code, you can find dbapp.py at GitHub here.

Caveat emptor: The code is very succint and has limited error checking.

Database User Interface Basics

Examining the sample application leads us to identify the following essential features:

The application needs to present data both as single records and in list or tabular form.

The data is entered and displayed as characters but possibly stored or interfaced to the DBMS in binary formats, hence conversion procedures between external and internal formats are necessary.

Data items need an initialization or default value.

Many or most data items need validation procedures, including a failure message. Some validations are generic to the data type, others are specific to the item.

Depending on the operation mode, modification of some items may be prevented, e.g., primary key cannot be entered or changed in Update mode.

One or more facilities are needed to select a record for Update or Delete, e.g., by the primary key, from a list, etc.

When reporting actions taken, a short external representation of a record is desirable1.

Some of these may seem tautological, but they apply regardless of whether the user interface is line-oriented, character-oriented (like ncurses), graphical or web-based.

A program to develop database applications needs to assist the developer in specifying these features. For example, the program has to provide a means to design a single record or tabular format. The advantage of having access to the database catalogs is that the program can facilitate this by supplying useful defaults at various stages in the design process.

Once the design is finalized, we’ll probably want to store it in the database itself, but –mindful of version control– it’s recommended we keep the design in a YAML specification file that can be stored in a VCS.

1 The primary key for the film table is an integer, and in the sample session, the IMDb identifier was used as it were a “natural” key. In most applications, such an id would be auto-generated and possibly not shown to the user.

Thanks. The database I’ve chosen is PostgreSQL and in order to interface to it, if I’m going to use Python, I need an adapter, hence psycopg2.

That said, it’s probably fairly easy to modify dbapp.py to use SQLite.

Git is not a dependency but is useful if you want to get the source to experiment with (although you could copy-and-paste from browser to an editor). Pyrseas is not really a dependency, but this blog is about that, my pet project.