Wisdom is the prime thing

I’m constantly finding myself in need of small, quick, personal database programs. Something light and flexible. Quick to mock up, quick to write, easy to use, painless to dump. And I find myself wanting and writing the same kinds of applications again and again.

Sometimes a text file just isn’t enough. And writing redundant SQL queries can get tiresome. Perhaps I need to print nicely-formatted reports. Perhaps I need some web connectivity for retrieving remote information. Or, what if I want to give this program to someone else to run? How many people know how to do database administration? The data needs to be conveniently stored in one place for the easiest possible backups.

What I really need is a framework. A cross-platform framework that will allow me to create these types of applications in a heartbeat. I should even be looking forward to the two minutes it would take to copy a template project and build a whole new application and have it up and running in minutes. Any changes I want to make to the data model should be immediately reflected everywhere with no extra work. But if I want something powerful, I want the framework to be flexible enough to let me get in and make it so.

Interested? Here’s my solution…

I’m posting the project in my public gitweb repository. You can browse all the source code, and download snapshots if you don’t have git locally installed (hint: look for “snapshot” links). Now that the framework supports Windows, I felt it was time to release it. All patches are welcome.

You can start by looking at the contacts project to see a simple address-book type application. The master project is a blank (but working) application that is mostly useful as a base for creating a new application. A simple git diff master..contacts, for instance, will help you see the application-specific code in each project.

What software is it dependent on?

All the dependencies are free and open source. You will need:

• Python: obviously
• Cherrypy: a small, embeddable web engine
• Cheetah templates: a pythonic templating solution
• SQLite: a small, fast, embeddable, SQL database
• Pysqlite: a simple Python connector to sqlite

Here are some more open-source optional packages for the full effect:

• OpenOffice: office suite, used for nice reports
• make: to automate template compilation
• git: for easily creating new applications with git checkout -b myapp

What GUI is the application written for?

Firstly, the GUI is the most natural, simple, and extensible: HTML+CSS. I’ve used wxwindows, gtk, QT, fltk, and other cross-platform GUI’s. But in the end I’ve come to the conclusion that the best GUI — the most universally understood and friendly — is in your web browser. It is easy to program for, is already installed on all computers, is cross-platform, and coupled with CSS and javascript allows for dynamic and useful MVC applications. And cherrypy makes it easy to deal with the stateless, resource-based paradigm of web programming.

What does the project look like?

This is the layout of the project:

app.py     - Set up view structure here
base/      - Base model and view classes
model.py   - All application-specific models defined here
reports/   - Put your `.odt` reports in here
static/    - Javascript, CSS, and image files
templates/ - All view templates

How does it work?

In a word, simply. Open up the folder where you downloaded the code, and run app.py. For the first time you’ll probably want to run it in a terminal or command line. The application loads quickly (compiling templates if necessary), and tells you it’s up and running on port 8080. So open your browser to http://localhost:8080 and play with your application!

When you’re finished, close app.py and your web browser and you’re done.

What does webpy do well?

When you run the app.py script, the program looks to see if you have a SQLite database file (either a db file in the current directory, or another file specified with the DB environment variable). If not, then it will be created, and all the tables and indexes will be generated, per your model classes in model.py. If any columns or tables have changed since last time, your data will be preserved, and new columns will be generated on those tables to hold the new data.

This makes it safe (and fun!) to change the model as you work, since all changes will be immediately propagated everywhere. You can actually tweak the model as you go, without having to restart the application or your web browser! That encourages you to get started working with your data. If you get a half-hour in and realize you need a new Phone Number field, just add it in model.py and keep going. It will automatically show up on all screens, as if it were there all along!

In addition, the whole database is stored in one file (db by default). That makes it super simple to backup. And since it’s SQLite, it’s fast and easy: a drop-in solution.

In fact, because webpy creates and updates tables as necessary, you can actually have multiple applications using the same database file! It’s pretty wild the first time you do a git branch otherbranch while the application is running and see the cherrypy output letting you know it’s set up the new application and it’s ready to go! Your data isn’t destroyed, and this could even be a useful feature for having, say, one “Person” table, and multiple applications that reference various fields in that table.

The reporting features allow you to set up an OpenDocument word processing file with all the formatting you need. You can specify a table in the document that will be the basis for the report. The reporting script will generate the rest of the table, substituting text for each record in the recordset. You have full control over the SQL that is run. Since all textareas in the application are dynamically resizable, you have as much space as you want. You can make fairly complex reports easily with the built-in reporting features. Updates to the report templates in the templates folder will be automatically reflected the next time you run the report.

The HTML generated is exceptionally clean. And the CSS is neatly organized. Because of smart selector usage, you can actually tweak the layout of each screen individually. You can see examples of this in the bundled example projects.

Finally, the URLs are RESTful: easy to read, save, and construct. For example: http://localhost:8080/person/12 is a link to the Person record with ID 12.

What if I need to do something complicated?

You can! webpy tries to make easy things easy and hard things easier. The territory application shows how you can use any Python library, or custom code, to do arbitrarily complex things, like fetching URLs and parsing them to help fill out forms.

The templates are made to be smart enough to handle the standard datatypes: strings, integers, long strings, dates, foreign-key dropdowns, checkboxes, phone numbers, and email addresses. Validation code is included. But if you want special handling for a certain model class, no problem. Create a new template, point to it in app.py and you’re set.

Need your application to be visible to others? Set up Apache to proxy your application and it will sit comfortably behind and serve pages.

What is it good for?

I’ve used it for a contact database, for managing territories, for keeping track of timecards, and for many other one-shot needs. I’ve converted full-blown LAMP stacks to this framework with some easy shell-scripting and mysql-dump calls.

What still needs to be done?

1. The project needs a better name. Currently it’s too close to web.py.
2. I’d like to go through the model and column code and clean it up. Less magic, more obvious.
3. I haven’t done any testing on the Macintosh platform. I’m imagining that all the Python code should be equally available, but I haven’t checked.
4. I want a nice, smart CLI interface. This has been a design goal from the very beginning. I should be able to type: cli show pers 12 from a terminal and have a nice printout of the Person record #12. The way the application is constructed this should not be difficult to add.
5. Mobile phone support. This is already written and working, I just haven’t pushed the branch yet. I have it set up where I can text message myself with a simple query (like the one above), and the program will text me back with the data I’ve requested. Potentially super handy!