Standalone Usage of web2py's DAL, gluon

For a web project, I had to use the model of the MVC pattern outside the web server. I could do without it, but why to drop it if I could use the model without repeating myself? What I need, is to have a conventional web server serving a conventional web application. So far so good. But I also need to access the database from some other servers which has nothing to do with the web interface.

There are many many web frameworks existing for Python. Among them, both Django and web2py are innovative, great ones. Of course I am not a user of all the frameworks in Python, but these two are the ones that I am focusing on. Both Django and web2py are full-stack web frameworks, that means they provide all the layers of the MVC pattern without using extensions. They are both candidate for the job.

I had some constraints for this project.
  • The system should have an easy install. As I will use it in more than one server, I should not repeat myself in each update.
  • The model layer of the framework must run outside a web server.
  • Database Migration is preferred, but it should be controlled.
  • When I update the model, the others should not migrate, if they do, they would revert the database to the previous state. Only one migration is required. Somehow I must be able to stop them.
  • I should be able to reach my Python libraries installed in site-packages.
Both Django(provides an ORM) and web2py(provides a DAL) seems capable for my task. Django is more explicit, web2py is more automagic. Django supports database migrations using extensions like Django-South, web2py supports migrations by default.

I have attacked the problem using web2py.
Please note that this is not a introductory tutorial for web2py, if it were, would not be better than the official web2py book.

I am using Ubuntu 11.04 Natty amd_64, and the commands will be fully compatible with it, but there is no reason not to work for other operating systems too.

Downloading and installing web2py
This is a great advantage of web2py, it has no configuration nor installation. It works out of the box. I have downloaded the source distribution. And just unzipped it, that's it.

Adding the standalone module
Just created a directory, called "alone" in the web2py directory. This is not a "web2py application", actually it can be anywhere. Added a file "single_stuff.py" inside the directory. Overview of the directory and file structure, is this:

You know what, the DAL of web2py, it can handle all my needs and constraints:
  • The installation is.. There is not even an installation, this constraint is already done.
  • After adding the root folder of web2py to PYTHON_PATH, only some of the modules that are related with gluon can be imported. See how to do it in the source code. Done.
  • Migrations are automatic in web2py but we can fine tune it, we can disabled migrations for some objects. Done, very done.
  • The last constraint, accessing other modules, sure it works, we did nothing unconventional. Run it as "python single_stuff.py" as usual and do whatever you like.
web2py is now the choice for me in this project, for its simple yet powerful database abstraction layer, gluon.

Here is the source of the "single_stuff.py" with inline comments:

Or you can directly jump to see the code on Pastebin.
You can also download whole the code from Pastebin.

An example to use only gluon, which is the name of
web2py's data abstraction layer.

import os
import sys

# web2py is distributed and deployed with zero configuration.
# Let us follow the same approach.
# To do this, I have opened a directory called "alone" inside
# the web2py directory, next to applications.

# I want to distribute the whole web2py directory.
# So, I want to make sure that the gluon is automatically found.

# print sys.path

from gluon.sql import DAL, Field
from gluon.validators import *

# For simplicity, we will be using SQLite.
# First, determine the path for the database file.
# We are headed for portability, so let us open the database
# file in the same directory with this module.

module_path = os.path.abspath(os.path.dirname(__file__))
# print module_path # => /home/alex/Projects/web2py/alone

# This is only the file name:
db_name = "our_db.sqlite"

# The protocol and full path to our database file.
db = DAL('sqlite://' + db_name, folder=module_path)

# Define the table "genre", note that the field "id" is automatic.
db.genre.name.requires = IS_NOT_EMPTY()

# Define the table "band", note that the field "id" is automatic.
# band.genre_id references to genre.id field.
        Field("genre_id", db.genre))
db.band.name.requires = IS_NOT_EMPTY()
db.band.genre_id.requires = IS_IN_DB(db, db.genre.id, '%(name)s')
db.band.genre_id.writable = False
db.band.genre_id.readable = False

# Let us insert some data for table "genre".
# Each insert statement returns the unique_id.
id_rock = db.genre.insert(name="rock")
id_metal = db.genre.insert(name="metal")
id_grunge = db.genre.insert(name="grunge")  # we will use the ID.
id_jazz = db.genre.insert(name="jazz")

# The data will be stored only after a commit.

# We can also do a rollback, before a commit.
print db.genre.insert(name="banana")
# It will print 5 to the screen.
# But it will not be stored in the database.

# Let us insert some data for table "band".
db.band.insert(name="Nirvana", origin="Aberdeen", genre_id=id_grunge)
# Do not forget to commit.

# A simple select.
for row in db(db.band.name == 'Nirvana').select():
    print row.name, row.origin
    #prints Nirvana Aberdeen

# We can also take the last query.
print db._lastsql
# It will print this:
# SELECT  band.id, band.name, band.origin, band.genre_id FROM band
# WHERE (band.name = 'Nirvana');

# We can use free, raw SQL queries too.
print db.executesql('SELECT * FROM genre;')
# [(1, u'rock'), (2, u'metal'), (3, u'grunge'), (4, u'jazz')] 

Exploring the database
If you want to look into the database using a graphical manager, you can use SQLite Database Browser, which can already be found in Ubuntu repositories, or SQLite Manager, which is a Mozilla Firefox extension and works anywhere.

Here are some screenshots after opening the database created and manipulated by "single_stuff.py":

As a result, web2py is too good to be true, but it is a fact that it is so.


  1. Hi
    Does the computer need to have Python installed already for this to work. Also does DAL work only with a certain python version?
    Alok(ajoshi at flash.net)

  2. Fantastic :)