Django-like automated admin interface

Categories: python, programming, turbogears,

As I continue to upgrade ConsulTracker and work on my new (not-yet announced) project, I often find myself wanting a nice CRUD-like interface, something akin to what I’ve heard Django has. While less masochistic developers might go for the “real deal” of Django, I chose to begin adding admin-style CRUD pages for TurboGears, the Python web framework with which I’m most familiar.

Requirements

Now, TurboGears already has something called “FastData”, but I’ve found it somewhat limiting. What I started with, then, is a set of requirements:

Table View

There should be a (HTML) table view of the (SQL) table which shows all rows of the (SQL) table.

• The columns shown for each table must be customizable with a minimum of custom code.


• When a column contains a foreign key, something “useful” must show up in that column (e.g. not the numeric “id” column of the foreign table)


• The table should have the capability of displaying columns from foreign-key’d tables as native columns. (This is kind of like creating an SQL view containing columns from multiple tables.)


• The table should have a link for editing each row, a link for marking a row for deletion, and a button for actually deleting the marked items. This is really a pet peeve of mine. GET-style links should not modify data in a well-designed web application. That can lead to all sorts of nastiness, especially when dealing with web spiders that follow all links. So I’ll keep all the data-modifying stuff in buttons.

From these requirements, I determined that I needed some kind of information, call it “CRUD metadata” or crudmeta for short, which contained the following information for each SQLObject model I want to view:

• The fields to include in the table (note that these can be sub-fields, as in “address.street1”, etc.)


• The “name” of each row in the table—this is for displaying the row as a “foreign key” in another table.

Edit View

There should be an “edit” view which allows for the following functionality:

• Each SQLObject property refrenced in “crudmeta” should have a place on the form.


• The fields in the form should make sense for their data type. This means:
  
  
  
  • TextFields for StringCol, IntCol, FloatCol, etc. (with data validation)
  
  
  • CalendarDate[Time]Picker for Date[Time]Col
  
  
  • Select fields for ForeignKey – populated with the “name” from crudmeta, of course!
  
  
  • Tables for MultiJoin fields with the similar functionality as the “Table View” above.
  
  
  
  


• AJAX-y goodness to allow you to “drill down” in the object hierarchy without losing your context.

Note that some (most) of these fields have TurboGears 0.9 widgets-style names. Not a coincidince. I want to reuse as much as possible from existing TurboGears development so as to avoid “re-inventing the wheel.”

Status & next steps

As of today (2006–02-27), the basic functionality is there. It’s not pretty, it doesn’t handle errors well, it needs refactoring badly, and it mixes model and view a bit from MVC, but it exists. My current plan is to clean it up a bit this week and (hopefully) make it available by next weekend. To whet your appetite, here is an (abbreviated) model from my new application with the CRUD annotations present. (Currently, this is all the custom code you need in order to create the above-described interface.)

  class Store(SQLObject):
      class crudmeta:
          fields=crud.fields(‘name’, ‘note’,
                             ‘address.street1’, ‘address.street2’,
                             ‘address.city’, ‘address.state’, ‘address.zip’,
                             (‘distributionCenter’, ‘Distribution Center’), 
                             ‘orders’)
          key=‘name’
  
      name=StringCol(default=uniqueValue(‘Store’, ‘name’, ‘store’),
                     alternateID=True)
      note=StringCol(default=’’)
      distributionCenter=ForeignKey(‘DistributionCenter’,default=None, 
                                    cascade=None)
      address=ForeignKey(‘Address’,default=newAddressID)
      orders=MultipleJoin(‘StoreOrder’)
      deliveries=MultipleJoin(‘Delivery’)

Very Simple WSGI Overview

Categories: python, programming, wsgi, web
In all the posting and hype about full-featured frameworks, you may have overlooked a very small "un-framework", the Python web server gateway interface (WSGI). It's generally an option for deploying the large frameworks such as TurboGears or Django. What follows is a very simple and brief overview of how you can create a WSGI-compliant application server.

First off, the WSGI specification itself is a decent read, and I'd be amiss if I didn't at least mention it. Now, on to the simple overview!

Overview

First off, you need to realize that WSGI is exactly what its name implies: an interface. The best way I've found to think of it is "CGI for Python." In CGI, the shell is invoked to run some script. The shell's environment is populated with values from the HTTP request, and the script's output is returned to the client. WSGI is similar, substituting a Python function for the script, a Python dict for the shell environment, and skipping the shell altogether. A basic WSGI application server has the following outline:

def MyApplication(environ, start_response):
    try:
        ....maybe do some stuff in response to the environ arg...
        write_fn = start_response('200 OK', [('Content-type', 'text/html')...])  # send headers
        ....maybe do some more stuff....
        ... EITHER ...
        yield some things
        .... OR ....
        return 
        .... OR ....
        write_fn(response_text)   # deprecated
    except:
        start_response('500 OOPS', [('Content-type', 'text/html')...], sys.exc_info)
        ... yield, write, or return the text of the error page ...

Your application server, then, is just a function (or other callable) that takes two arguments, an "environment" and a "start_response". In the recommended implementation, your server will either return an iterable (generally a list of strings) or itself be an iterable (generally, a generator). The minimal "hello, world!" application is below:

def MyApplication(environ, start_response):
    start_response('200 OK', [('Content-type', 'text/plain')])
    yield "Hello, world!"

The "environment" is just a dict of strings, much like the CGI environment. The values available are summarized below. The "start_response" is a callable that your server must call to send the HTTP Headers. You can call it up to twice, once for "normal" headers, and once for "error" headers. If you call it a second time, you must call it before generating any output, and you must call it with an "exc_info" object. The original headers (if there were any) will be overwritten by the new headers.

To do anything useful, you'll need to parse two main variables in "environ": "PATH_INFO" and "QUERY_STRING". "PATH_INFO" gives you the "rest of the path" after the mount point for your application server, and "QUERY_STRING" gives you - you guessed it - the query string. You can then implement whatever kind of URL->object mapping your heart desires, whether it be CherryPy-style object publishing, or Django-style regular expressions. You could use the functions in Python's standard cgi module to parse the query string, but Ian Bicking has a great tutorial on how to use Paste to simplify matters quite a bit. All the other WSGI variables that are available in the environment are documented below.

Environment

The variables available in the environ dict are summarized below. For the examples, assume the user requested (using GET) "http://server.com/some/path/myserver/more/path?query_args", and that the application server was mounted at "http://server.com/some/path/myserver".

Variable	Example	Description	Always Present?
REQUEST_METHOD	"GET"	HTTP method, generally GET or POST	Yes
SCRIPT_NAME	"/some/path/myserver"	Location in URL of application server	No - if application server is mounted at server root
PATH_INFO	"/more/path"	The rest of the path after the application root	No - for instance, if user requests "http://server.com/some/path/myserver"
QUERY_STRING	"query_args"	Anything after the "?" in the URL	No
CONTENT_TYPE	<absent>	Any Content-Type fields in the HTTP request	No
CONTENT_LENGTH	<absent>	Any Content-Length fields in the HTTP request	No
SERVER_NAME	"server.com"	The server name part of the URL	Yes
SERVER_PORT	"80"	The server port part of the URL	Yes
SERVER_PROTOCOL	"HTTP/1.1"	The request HTTP protocol	Yes
HTTP_*	<absent>	Other HTTP headers in request	No
wsgi.version	The tuple (1,0)	WSGI version ID	Yes
wsgi.url_scheme	"http"	The initial part of the URL	Yes
wsgi.input	<empty file-like object>	An object from which the request body can be read - very useful for POSTs	Yes
wsgi.errors	<file-like object>	A file-like object to which the application server can write text errors to be logged by the web server	Yes
wsgi.multithread	False	Whether the application may be simultaneously invoked in a multithreaded manner	Yes
wsgi.multiprocess	True	Whether the application may be simultaneously invoked in a multiprocess manner	Yes
wsgi.run_once	False	Tune the application to expect to only run once (e.g. turn off caching)	Yes

Effective Decorators

Michele Simionato has written a very nice-looking module that allows decorators to be used quite effectively:

The aim of the decorator module it to simplify the usage of decorators for the average programmer, and to popularize decorators usage giving examples of useful decorators, such as memoize, tracing, redirecting_stdout, locked, etc.

Even if you don't end up using her module, I think the documentation is a great read, and a good example of how to use your own decorators more effectively.

Topics

Now, I'm a guy who can go on and on about a technical topic ad nauseum. (Ask my wife!) But sometimes it takes a while for me to get started. So if there are any topics you'd like me to cover here, particularly regarding Python, please post it in the comments. I'd like to make this blog a useful resource, but my creativity-challenged brain may need a bit of help. Thanks!

ConsulTracker.com is now live!

ConsulTracker.com is now live and accepting "real" customers! To sign up, just go to our signup page. Sincere thanks go out to our beta customers for helping us "get the bugs out." We are offering a free one-month trial period (4 user maximum), or you can sign up for just $6 per user per month.

If you just want to see the features before comitting, we also have a demo site set up. You can log into the demo site with userid "test", password "test", selecting "Test Domain" on the login screen.
And of course, if you have suggestions or feature requests, please feel free to comment on this blog, post a note in our forums, or enter a bug in our bug-tracking system.

SQL in SQL

Categories: python, sql, sqlobject, programming

One thing I think would be useful is the ability to create a SQL database "on top" of another SQL database. This would allow you to write an application where the user creates their own DB schema, somewhat like MS Access, but the underlying database schema doesn't change. I'm exploring this a bit right now for my next project.

The basic idea is to create four tables: vtable, vcol, vrow, and vcell. vtable is a "virtual table", vcol is a "virtual column," and so on. I'm currently using SQLObject for the DB layer, so all the examples will use SQLObject, even though I'm not using it to the extent it could be used.

The setup code for a table is below:

class VTable(SQLObject):
    tabname=StringCol(alternateID=True)
    vcols=MultipleJoin('VCol')
    vrows=MultipleJoin('VRow')

    @classmethod
    def create(klass, name, **columns):
        t=klass(tabname=name)
        for colname, coltype in columns.items():
            t.addColumn(colname, coltype)
        return t

    def addColumn(self, colname, coltype):
        VCol(colname=colname, coltype=coltype, vtable=self)

    def getColumn(self, colname):
        for c in self.vcols:
            if c.colname == colname: return c
        return None

    def insert(self, **values):
        row = VRow(vtable=self)
        for colname, value in values.items():
            col=self.getColumn(colname)
            cell=VCell(vcol=col, vrow=row, stringValue=value)
        return row

Fairly straightforward: a vtable is a collection of vcols & vrows. I also include helper functions for performing table creation and inserts. Next is the column definition:

class VCol(SQLObject):
    colname=StringCol(alternateID=True)
    coltype=StringCol(default='str')
    vtable=ForeignKey('VTable')
    vcells=MultipleJoin('VCell')

Even better: a column is just a "type" (currently ignored), a name, and a collection of vcells. The Row object is also simple:

class VRow(SQLObject):
    vtable=ForeignKey('VTable')
    vcells=MultipleJoin('VCell')

    def __getitem__(self, colname):
        for cell in vcells:
            if cell.vcol.colname == colname: return cell.stringValue
        return None

    def __setitem__(self, colname, value):
        cell = self[colname]
        if cell is None:
            vcol=self.vtable.getColumn(colname)
            cell=VCell(vcol=vcol, vrow=self, stringValue=value)
        else:
            cell.stringValue = value

A row's main purpose is to have an id value which ties all the cells of a row together. Without further ado, here is the vcell class:

       
class VCell(SQLObject):
    vcol=ForeignKey('VCol')
    vrow=ForeignKey('VRow')
    stringValue=StringCol(default=None)

A vcell is simply a vrow & vcol reference, and an attached data value. Now, what can we do with this? Well, we can create vtables and insert values into them fairly easily. Suppose we have created and populated a table as follows:

t=VTable.create('mytable', col1='str', col2='str', col3='date')
for i in range(3):
    t.insert(col1='foo%d' % i, col2='bar%d' % i, col3='baz')

This is equivalent to

CREATE TABLE mytable (col1 TEXT, col2 TEXT, col3 TEXT); 
INSERT INTO mytable(col1,col2,col3) VALUES ('foo0', 'bar0', 'baz');
INSERT INTO mytable(col1,col2,col3) VALUES ('foo1', 'bar1', 'baz');
INSERT INTO mytable(col1,col2,col3) VALUES ('foo2', 'bar2', 'baz');

So what if we want to do something like SELECT col1, col2 FROM mytable with the vtable? What does the SQL look like for that? Can you even do it in a single SQL statement? It turns out you can:

SELECT vcell0.string_value AS col1,vcell1.string_value AS col2 
FROM vtable vt0, vcol vcol0,vcell vcell0, vcol vcol1,vcell vcell1, 
WHERE vt0.tabname='mytable' 
  AND vcol0.vtable_id = vt0.id AND vcol0.colname='col1' AND vcell0.vcol_id=vcol0.id 
  AND vcol1.vtable_id = vt0.id AND vcol1.colname='col2' AND vcell1.vcol_id=vcol1.id 
  AND vcell0.vrow_id=vcell1.vrow_id;

If we run this, we get the expected results:

>>> conn.queryAll(sql)
[('foo0', 'bar0'), ('foo1', 'bar1'), ('foo2', 'bar2')]

But this is ugly and hard to write and read. What if there were a function to build a virtualized query automatically? Well, I've created such a function. Right now, it is very kludge-y and has limited functionality (it doesn't support a virtual "WHERE" clause yet, for example.) But it's a starting point. What I'd really like to have is a sql-ish interface that allows complete virtualization of table creation, query, update, etc., all without modifying the underlying database schema. If anyone is interested in possibly contributing to such a project, let me know in the comments below, and I'll set up a project on SourceForge.

Oh, and here's the "virtual query builder".

def vquery(columns, tables):
    '''vquery(columns, tables) - create a (real) query from a virtual one
    
    columns    : list of (table,column) tuples
    tables     : list of table names
    '''
    tabtrans = {} # tabtrans[tabname] = tab_label
    coltrans = {} # coltrans[colname] = (col_label, cell_label)
    num_tables = 0
    num_columns = 0
    sql='SELECT '
    real_tables, real_columns, real_cells = [], [], []
    for t in tables:
        tab_label = 'vt%d' % num_tables
        num_tables += 1
        tabtrans[t] = tab_label
        real_tables.append(tab_label)
    selectclause=[]
    for t,c in columns:
        col_label = 'vcol%d' % num_columns
        cell_label = 'vcell%d' % num_columns
        num_columns += 1
        coltrans[c] = (tabtrans[t], col_label, cell_label)
        real_columns.append(col_label)
        real_cells.append(cell_label)
        selectclause.append('%s.string_value as %s' % 
                            (cell_label, c))
    # Build virtualized whereclause & tablist
    whereclause=[]
    tablist = []
    for user_name, real_name in tabtrans.items():
        tablist.append('vtable %s' % real_name)
        whereclause.append("%s.tabname='%s'" % (real_name, user_name))
    last_cell_name = None
    for user_col_name, (real_table_name, real_col_name, real_cell_name) in coltrans.items():
        tablist.append('vcol %s' % real_col_name)
        tablist.append('vcell %s' % real_cell_name)
        whereclause.append("%s.vtable_id = %s.id" %
                           (real_col_name, real_table_name))
        whereclause.append("%s.colname='%s'" %
                           (real_col_name, user_col_name))
        whereclause.append("%s.vcol_id=%s.id" %
                           (real_cell_name, real_col_name))
        if last_cell_name is not None:
            whereclause.append("%s.vrow_id=%s.vrow_id" % 
                               (real_cell_name, last_cell_name))
        last_cell_name = real_cell_name
    sql = 'SELECT %s FROM %s WHERE %s' %           (','.join(selectclause),
           ','.join(tablist),
           ' AND '.join(whereclause))
    return sql

Python Atlanta Meeting Website

Categories: python, meetup, turbogears
There was some discussion at the last Atlanta Python Meetup about building our own "meetup" style website using one of the Python web development frameworks. I have thrown together a "meetup"-style website for the Atlanta Python Meeting using TurboGears 0.8.8. You can see it at here. You can download the source here. And of course, you are invited to the meeting on February 9.

If anyone else is interested in maintaining / enhancing the code, let me know and I'll give you CVS & login access. If anyone is interested in using this website, well then, register. And if you just want me to add features, then email me or post the requests in the comments.

Python Web Frameworks

Categories: python, web, programming, framework
Well, the BDFL is looking for a web framework. Laying aside all the arguments for TurboGears, Django, web.py, Nevow, Cheetah, Paste, etc., I noticed that Glyph Lefkowitz and Ian Bicking both piped up with their input. Glyph is involved in Nevow, and Ian is involved in Paste.

Glyph makes the point that web frameworks come with a bit of cognitive overhead, but that this overhead is necessary. You really need to see the world from the perspective of the framework. Even if you write your own, you have to invent your own webgeist, and it's still work. I couldn't agree more, as I discuss in my previous entry.

Ian, on the other hand, is (it seems) the father of Paste, which seems to be a toolbox of sorts enabling the easy creation of web frameworks. Ah, so he's the one responsible for making it so easy to roll your own! Anyway, Paste does seem to take care of many of the details of writing a web framework. Ian has written a nice tutorial on framework construction. Not that I want you to go there - no - don't - write - your - own - oh well, I've lost you. Seriously, it looks pretty easy. It would be nice to see all the existing frameworks that aren't already based on paste to port to Paste, if only to remove the redundant code. Ian has built a good tool. It would be nice to see people using it more.

Anyway, that's my $.02. Looks like Glyph and I have a common philisophical POV - drink the kool-aid of your framework of choice and get on with development. Ian, while not advocating the proliferation of frameworks, certainly makes the proliferation simpler. And, of course, if you want to find the framework that's "right" for you, the comments on Guido's blog are a great place to start.