Data Window

Jam.py is an open source environment that lets you build a browser-based interface for a database. With Jam.py, you can design and then use custom applications to store, share, and analyze data inside an SQLite, PostgreSQL, MySQL, Firebird, MSSQL, or Oracle database. The interfaces that Jam.py creates are clean, customizable, pretty fast and, with minimal effort, portable on every platform that supports Python, from your laptop to your website.

As the Jam.py website [1] puts it, "What MS Access is for Desktops, Jam.py is for the Web. And much more."

Architecture and Main Features

The Jam.py framework consists of just two components [2]. The server side, which talks with your browser, manages the configuration and directly accesses your database. The server side component runs on any computer with Python 2.6 or later. (For this tutorial, I tested Jam.py on three different Linux systems, two running Ubuntu 20.04 LTS and one with Centos 7, all with Python 3.8.) The client side is a pair of dynamic web pages that receive JavaScript code sent from the server. The client side component relies on popular open source libraries like jQuery and Bootstrap.

To work with Jam.py, you just need to load those two client web pages in separate tabs or windows of your browser. The first page is the Home page, where you will test, and eventually use, the finished application. In the default Jam.py configuration, this page will be at the address http://localhost:8080. The second page (http:localhost:8080/builder.html), is called the Application Builder or just Builder. The Builder page is where you build and configure the same application. In practice, you will move back and forth between those two pages, making changes in the Builder, then testing how the changes work from the Home page.

In the Builder, you can create or modify tables in the database, manage users, load different CSS themes, and add buttons that interact with the underlying operating system. The client side of Jam.py can generate charts with the results of database queries and interact with the server side to produce reports based on OpenDocument templates.

Every Jam.py interface, or project, is structured as a tree of tasks that are organized in groups. You may create custom groups if you like; however, at least for your first Jam.py projects, I strongly suggest that you stick with the default task tree, which consists of four groups called Catalogs, Journals, Details, and Reports.

The most important groups are Catalogs and Journals. Catalogs are tables that store actual records, like the inventory of a store or the students of some school. Journals store information about events or transactions pertaining to the records in the catalogs – such as invoices or purchase orders.

All the databases managed via Jam.py are fully accessible with other clients – and even shell scripts. If you wish to access the system from another client, stop the Jam.py server or temporarily switch the tables to read-only to ward off any potential data corruption.

Installation

The most efficient way to install Jam.py is with the package manager for Python 3 called pip3. On Ubuntu or other Debian derivatives, type the following commands to install pip3 and then install (or upgrade) Jam.py:

#> sudo apt-get install python3-pip
#> pip3 install jam.py --upgrade

Depending on the database engine, you may also need to install the necessary Python modules.

Creating a Jam.py Project

Once you have successfully installed Jam.py, the next step is to create a new folder for your Jam.py application. Then run jam-project.py inside the new folder to set up the necessary files and folders. For example:

#> mkdir bookmarks
#> cd bookmarks
#> jam-project.py

The jam-project.py command creates the following files and folders:

#> ls -l
- admin.sqlite
d css
- index.html
d js
- langs.sqlite
d locks
d __pycache__
d reports
- server.py
d static
- wsgi.py

index.html is the dynamic template that creates the two web pages described earlier in this article, and server.py is the Python script that powers the server side. wsgi.py is an implementation of the Web Server Gateway Interface, which allows Python scripts to work behind standard Web servers like Apache or NGINX with little or no extra configuration.

The folders (recognizable from the letter d at the beginning of the line) contain CSS stylesheets for several visual themes, the JavaScript code that is sent to the user's browser, the lock files that prevent conflicts when accessing the database or the local Python cache (__pycache__), and, finally, report templates or static files (such as images) that are linked to database records. The two files with the .sqlite extension are SQLite3 single-file, server-less databases that contain configuration and localization data.

In order to configure and run your Jam.py application, you must start the server.py script. When I started server.py, however, I encountered another unexpected error:

#> ./server.py
/usr/bin/env: 'python': No such file or directory

This problem might have been due to my own Ubuntu boxes being misconfigured because of too much testing. My quick fix was to explicitly pass to server.py the location of Python 3 on my system, replacing the first line of the script:

#! /usr/bin/env python

with:

#! /usr/bin/python3

Depending on your Linux configuration and on how many versions of Python you have installed, you may need to enter a similar fix. After that simple edit, I could start server.py without errors and finally load the Builder page at http:localhost:8080/builder.html in my browser.

If you wish to use a different TCP port (other than 8080), just pass the port number to server.py:

#> server.py 9000

If you want to run more than one Jam.py application at the same time on the same computer, repeat all the preceding steps for each application, giving each application its own reserved folder and TCP port number. Of course, the URLs for the Home and Builder pages of each application would need to change to reflect the correct port number.

Until you are familiar with how Jam.py works, keep the terminal where you launched server.py open alongside your browser. In that way, you will see the error messages caused by any mistakes.

A Simple Bookmark Manager

In Linux Magazine 232, I introduced the Shaarli online bookmark manager [3]. I still use Shaarli for managing bookmarks, but after writing that piece, I realized that I also needed an alternative interface to the bookmarks I keep collecting. I needed a browser-based interface that was much quicker and more script-friendly than Shaarli. Most of all, I needed an interface that was compatible with complex searches and could process search data in SQL format.

I decided copying my bookmarks to a Jam.py application would be an effective solution for my problem – and also a nice example for this tutorial. To build the first version of that bookmark manager, I followed the standard Jam.py development flow.

The first thing to do in the Builder page of a Jam.py application is to set the application name, the interface language, and the main parameters, most of which are visible in Figure 1. For your first project, you may safely leave all default values as they are. The Interface tab of Figure 1 lets you choose among several graphic themes for your application.

Figure 1: The main parameters for every Jam.py application.

The second crucial step is to define the database engine that your application will use, as well as the parameters to connect to it. For SQLite, the only parameters I had to enter in the Database box of Figure 2 were the type and name (bookmarks) of the database. This step told the Builder to create and format a local file in SQLite format, called bookmarks, and store all my data in the file.

Figure 2: The database configuration panel, showing all the databases supported by Jam.py.

So far all I had was the shell of the database. I had to create at least one table inside it for my bookmarks and define all its columns. In the Builder, you perform these operations by clicking on Catalogs in the left pane then selecting the New button to create a new item. You'll then see a form similar to the form shown in Figure 3, which serves two purposes. In the upper part, you define the name, primary fields, and some other properties. The lower part of the form lists all the columns in the table, along with their properties. To add a column, click on the New button at the bottom of that form (not visible in Figure 3), and enter all its properties. When I finished adding columns, my BOOKMARKS_MAIN table looked like Figure 3.

Figure 3: The SQLite table for a bookmark manager, with all the fields configured and ready to use.

By default, a Jam.py application automatically applies every change you make in the Builder interface to the underlying database. If you want to see how some features work without risking data corruption, click on Project in the left pane, then click on the Database button on the right side and check the DB Manual Update box. This step will prevent the application from actually changing the underlying database until you uncheck the box.

Before configuring how to display and filter the contents of the BOOKMARKS_MAIN table, I needed to fill it – that is, to import all the bookmarks I had stored in Shaarli. It is of course possible to add records to a table with just one click, but that is no way to insert hundreds or thousands of records.

You can (re)read my tutorial [3] to see how I backed up Shaarli data to a plain text file with one record per line and fields separated by pipe (|) characters. Importing files like that in an SQLite table is very simple, as long as the columns in the text file are in the same order as they are in the table. To see what columns you have in an SQLite table, you can use the table_info directive at the SQLite prompt (Listing 1).

#> sqlite3 bookmarks
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> pragma table_info('BOOKMARKS_MAIN');
0|ID|INTEGER|0||1
1|DELETED|INTEGER|0||0
2|TITLE|TEXT|0||0
3|URL|TEXT|0||0
4|PUBLISHED|TEXT|0||0
5|INSERTED|TEXT|0||0
6|EDITED|TEXT|0||0
7|BROKEN|INTEGER|0||0
8|PRIVATE|INTEGER|0||0
9|SHORT_URL|TEXT|0||0
10|COMMENT|TEXT|0||0
11|DESCRIPTION|TEXT|0||0
12|TAGS|TEXT|0||0

In my case, I had a small problem: the column order in Listing 1 is the order I wanted for the SQLite table, but it was not the same as in the Shaarli backup file, and there are two extra columns (ID and DELETED). Therefore, if I told SQLite to read the Shaarli backup file, almost all values would end up in the wrong column or be just discarded. I wrote the ugly, but effective little Perl script to generate another plain text file with all the columns in the right sequence (Listing 2).

In Listing 2, line 3 initializes a record counter called $ID. The loop in lines 4 to 13 reads the file passed as the first argument one line at the time and removes the final newline character (line 5). Then, the script splits the whole line using the pipe character as a separator, saving each field in the variables listed in line 6. Lines 8 and 9 simply replace the text value of the broken and private fields with numbers, because in BOOKMARKS_MAIN, those fields are integers, not strings: $broken is converted to 1 if it contains an x; otherwise it is 0 (line 8) and $private becomes 1 if it is equal to true (line 1).

1 #! /usr/bin/perl
2
3 $ID = 1;
4 while (<>) {
5 chomp;
6 ($published, $broken, $edited, $private, $dummy, $short_url, $title, $url, $tags, $description) = split /\|/;
7
8 $broken  = ($broken  =~ m/^\s*x\s*$/) ? 1 : 0;
9 $private = ($private =~ m/^\s*true\s*$/) ? 1 : 0;
10
11 print join("|", $ID, 0, $title, $url, $published, $edited, $edited, $broken, $private, $short_url, '', $description, $tags), "\n";
12 $ID++;
13 }

01 .mode list
02 .separator |
03 .import bookmarks-for-sqlite.csv BOOKMARKS_MAIN
04 .quit

Line 11 just prints all the same fields, joining them with pipe characters and with two extra fields at the beginning. The fields that were in the original file are printed in the order they are inside the SQLite database, as you can see when comparing line 11 with the output of the table_info command in Listing 1. Line 12 increments the line/record counter and then the loop moves to the next line.

To load the output of rearrange_columns.pl into the actual database, I had to prepare the instruction file shown in Listing 3, which I called import.sql:

The instructions in import.sql are (almost) self-explanatory: the first two lines specify that the input file stores one record per line, with columns separated by pipe characters; the third line imports the whole content of that file into the BOOKMARKS_MAIN table, and the .quit statement obviously quits SQLite. At that point, I can finally use the two scripts in sequence:

#> ./rearrange_columns.pl shaarli-bookmarks.csv bookmarks-for-sqlite.csv
#> sqlite3 --init import.sql bookmarks

The result is shown in Figure 4: all the bookmarks I saved using Shaarli are now in a quicker, much more flexible graphical interface that I can use even when I have no Internet connection. Figure 4 also shows some other interesting features of the Jam.py Builder. First of all, you can decide which columns to show – and in which order. Then, there is the Filters button, which opens a form to create search filters. For instance, you could display only bookmarks with titles that include a certain string and were published in a certain range of dates, as in Figure 5.

Figure 4: Jam.py displays the contents of a database table, using its default graphic theme.

Figure 5: The configurable filters, together with the search box, make it easy to find just the records you want.

Two other things to notice in Figures 4 and 5 are the tiny arrows near the names of each column, and the small search box in the top right corner. The arrows mean that records can be sorted by that column. The search box lets you find and display only the records that have the given string in the textual field that you select.

Users and Roles

By default, a Jam.py program only has one user account with full powers, called admin, with a default password equal to 111. Even if you are the only user of that program, it is good practice to have a separate account with fewer privileges for daily use. Figures 6 and 7 show how to create an account. First, click on the Users and Roles entries of the Builder left menu to create new users and new roles for them [4]. Next, go back to the catalog, or wherever you defined your table, select it, and click the Privilege button on the left to specify what every role can do with that table (Figure 7). To make all these changes work as intended, click on Project | Parameters and tick the Safe mode box to force all users to log in with their password.

Figure 7: Defining roles and access rights.

Figure 6: Creating a user account.

Custom HTML

If you compare Figures 4 and 5, you will notice that the titles are formatted differently. The reason for the difference is that, in Figure 4, titles are plain text, but in Figure 5 they are hyperlinks pointing to the bookmarks.

Clickable titles are just one example of how you can customize Jam.py tables. To make the titles clickable, select the BOOKMARKS_MAIN table in the catalogs, push the Client Module button, and then select the Events tab shown in Figure 8. The function names on the left correspond to all the events that the Jam.py JavaScript can detect, and the editor on the right lets you insert whatever JavaScript you want in those functions. What I did, following the documentation [5], was to add the following code:

if (field.field_name === 'title') {
        return '<a href="' + field.owner.url.value + '">' + field.value + '</a>';
    }

Figure 8: In the Client Module panel of the Jam.py builder, you can add your JavaScript code to customize how records are displayed.

In plain English, this code means "whenever a title should be printed, replace that string with the HTML markup for a clickable link, pointing at the URL that corresponds to that title." Figure 9 shows that this is what happens when you save that JavaScript code. The meaning and features of all the functions visible in Figure 8 are described in the Jam.py user manual.

Figure 9: The result of the JavaScript code shown in Figure 8: Bookmark titles become clickable links to the corresponding pages.

Custom Charts or Buttons

You can add much more than links to a Jam.py interface. The most interesting objects are dynamic charts built in your browser by libraries like chart.js, as well as buttons that make server.py interact with the underlying operating system. For instance, you could add a button that sends an email message. See the Jam.py documentation for details on how to add these features. You will find some of these advanced features in the dashboard of the official demo [6] [7] [8].

Portability

A great advantage of Jam.py database interfaces is their portability. The actual SQLite database of my bookmark manager is one file, situated in the same folder where all the other files of that project live, which means if I copy the folder to another computer, I can run the bookmark manager – as long as the new computer has Python and Jam.py installed. (Actually, it is even simpler than that. The only real requirement is Python, because you can include the Jam.py package inside the same folder with the rest of the application, and move or backup everything together.)

Portability of Metadata

In some cases, you might wish to import a finished Jam.py configuration to another computer. For instance, you might wish to use an existing application as a template, or perhaps you want to test some changes to the current configuration on another computer to avoid disrupting the production system.

To export a Jam.py application, select Project in the left pane of the Builder and click the Export button. The Export button saves the admin.sqlite database that every Jam.py builder creates for its own use, plus all the other project files (CSS stylesheets, reports, custom code of your application, images…) into one ZIP archive. You can then use the Import button on another instance of Jam.py to load the complete project from the archive.

External Databases

So far I have discussed the case where the Jam.py application and the database are created together. In those cases, using SQLite spares you from installing and maintaining a separate server. However, in some situations, you might wish to build a Jam.py application for a database that already exists.

Figure 10 shows what to pass to the Builder in order to connect to a local or remote MySQL server. All the values shown in the figure must be filled in, even if they are not marked with asterisks, otherwise the connection will fail. If the connection succeeds, the next step is to import the structure of the MySQL tables and to create the necessary forms and filters. The complete procedure has its own, detailed page in the Jam.py documentation [9]. It is not complicated, but must be followed to the letter and it might change by the time you read this tutorial. The basic steps are:

Figure 10: Each database engine needs different configuration parameters. This figure shows the parameters for MySQL.

1. Click on Project, then on the Database button and set DB manual mode to true.
2. Click on Groups, and then select the specific group (Journals, Catalogs, etc.) in which you want to import a table.
3. Click the Import button.

This procedure should open a form listing the tables found in the MySQL database. Click on the table you want to import, and then check if all the fields of that table were recognized with valid types. If the type of a field is empty or written in red, double click on that field, and give it the most appropriate type among those listed by the Builder. If this is not possible, or if you do not need to import the whole table, just import only the fields you really need.

If the Builder does not recognize the primary key, specify a primary key for the table. In my case, as you can see in Figure 11, all fields were properly recognized, except those of type ENUM. I have informed the developers of this issue, and it might be fixed by the time you read this.

Figure 11: A MySQL table imported by the Jam.py Builder.

System Administration Tips

If you use SQLite databases, it is easy to back up and synchronize different copies of the same application. Just include the whole jam.py folder in your backup lists and use rsync to propagate any changes made to the database to the backup computer.

Another system administration issue is how to start the application automatically – and how to shut it down cleanly when you power down your computer. Look online for a tutorial on how to start Jam.py automatically with an init script [10]. The example is based on Ubuntu, but other distributions are similar.

Last but not least, a tip about port numbers and URLs. The default way to run jam.py on a web server creates URLs that reference the port number (example.com:9000). But suppose you would prefer to access your Jam.py application at a normal address like mybookmarks.example.com. This is possible in two ways. One is to set up a standard web server like Apache or NGINX as a proxy between the application and the rest of the Internet using the WSGI interface [11]. Another way is to deploy your application on platforms like Python Anywhere [12].

Conclusion

Jam.py is quick and simple to use, and the architecture makes it easy to migrate an application to another database if the need arises. One small problem I had with Jam.py was getting used to its terminology. The Jam.py manual [2] is very rich, but I confess it took me some time to figure out what is probably the first thing you need to understand to make the best use of Jam.py: the difference between catalogs and journals.

I really like how easy it is to manage SQLite databases and move them anywhere with Jam.py. I have only implemented the most basic features of my Jam.py bookmarks manager, but I like it, and I plan to add thumbnails and maybe even statistics. Two features I would like to see added to Jam.py are bookmarklet support (to insert JavaScript-based bookmarklets from web pages) and a plugin to embed Jam.py in a NextCloud installation. All in all, I recommend Jam.py if you are looking for a nice, quick way to build browser interfaces for your databases. Finally, I would like to thank Jam.py developers Andrew Yushev and Drazen Babic for their support in the preparation of this tutorial.