Building a database front end with Jam.py

Data Window

© Lead Image © Kheng Ho Toh, 123RF.com

© Lead Image © Kheng Ho Toh, 123RF.com

Article from Issue 241/2020
Author(s):

Create a convenient interface to your database with Jam.py.

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.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy Linux Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus

Direct Download

Read full article as PDF:

Price $2.95

News