sqlalchemy + the JavaScript InfoVis Toolkit (jit)

Mar 06, 2011 | categories: python, toscawidgets, turbogears View Comments

I've been working on some new ToscaWidgets2 widgetry. Today's post is a tutorial on how to use tw2.jit.SQLARadialGraph with the Turbogears 2.1 framework. You can check out the entire source tree for this tutorial from my github page.

I really like tw2.jit.SQLARadialGraph and have been getting overly-excited about it: foaming at the mouth, rambling... I've tried to cut all that out of this post and just give you the details on how to get started using it and Turbogears 2.1. In short, SQLARadialGraph itself is a python web component that produces an interactive graph of your database via javascript and asynchronous requests for JSON data. Most all of its real rendering work is accomplished with the awesome JavaScript InfoVis Toolkit by Nicolas Garcia Belmonte.

For this post, first we'll configure Turbogears 2.1 to use Toscawidgets 2 middleware. Second we'll modify our new app's bootstrapping process to insert some random data into its development sqlite database. Third, we'll configure and display an instance of tw2.jit.SQLARadialGraph to visualize the data we inserted.

1. Setting up Turbogears

Start with a fresh TG 2.1 quickstarted app by running $ paster quickstart tw2.jit-tg2.1-demo. It will ask you some questions -- for this tutorial we are using mako templates and we are using authentication.


Jump into your newly quickstarted app's directory and run $ virtualenv --no-site-packages virtualenv and $ source virtualenv/bin/activate to cordon off your app's dependencies from your system-wide python site-packages directory.

Configure TG 2.1 for Toscawidgets 2

Add the following three lines to the install_requires argument in your setup.py file.


A fresh Turbogears 2.1 quickstarted app depends on Toscawidgets 1 by default. We'll be using Toscawidgets 2 so we need to remove all the old references as well as tell TG2.1 to use the tw2 middleware instead of tw1.

Remove the following three lines from tw2jittg21demo/controllers/root.py:

from tgext.admin.tgadminconfig import TGAdminConfig
from tgext.admin.controller import AdminController
 ... < snip > ...
    admin = AdminController(model, DBSession, config_type=TGAdminConfig)

Remove the following one line from tw2jittg21demo/lib/base.py:

from tw.api import WidgetBunch

Add the following two lines to the bottom of tw2jittg21demo/config/app_cfg.py:

base_config.use_toscawidgets = False
base_config.use_toscawidgets2 = True

You will also need to enable the query property on all models.

Uncomment the following line in tw2jittg21demo/model/__init__.py:

DeclarativeBase.query = DBSession.query_property()

Test it all with the following commands.

$ python setup.py develop
$ paster setup-app development.ini
$ paster serve --reload development.ini

And point your browser at http://localhost:8080. If all has gone well, you should see the default index page for your newly quickstarted TG2.1 web-app.

2. Add some `interesting` data

We'll use the existing models defined in tw2jittg21demo/model/ to make this tutorial shorter and smoother.

Edit tw2jittg21demo/websetup/bootstrap.py (which is the code that gets run when you issue $ paster setup-app development.ini) and add the following two new function definitions just above the definition of the bootstrap function.

from random import choice, randint

def add_random_users():
    """ Add 9 random users """
    import string
    chars = string.letters
    for first in [u'Sally', u'John', u'Tim']:
        for last in [u'Anderson', u'Flanderson', u'Block']:
            user = model.User()
            user.user_name = unicode((first[0] + last).lower())
            user.display_name = u'%s %s' % (first, last)
            user.email_address = u'%s@socialistworker.org' % user.user_name
            user.password = u''.join([choice(chars) for i in range(12)])


def add_random_groups():
    """ Generate a number of random groups and add users to them """
    for name in ['developer', 'system admin', 'shmeveloper', 'crispin gladbin']:
        group = model.Group()
        group.group_name = name
        group.display_name = (u"%ss group" % name).title()

        all_users = model.User.query.all()
        for i in range(randint(0, len(all_users)-2)):
            user = choice(all_users)
            while user in group.users:
                user = choice(all_users)


These new functions still need to be called during the boostrap process, so add two invocation lines just above the transaction.commit() line at the end of the boostrap function.



    except IntegrityError:
        print 'Warning, there was a problem adding your auth data, it may have already been added:'

Blow away and re-create your database with the following commands:

$ rm devdata.db
$ paster setup-app development.ini

Run $ paster serve --reload development.ini and point your browser at http://localhost:8080 again to make sure nothing is broken.

3. Visualize the database with tw2.jit.SQLARadialGraph

Make the widget available in your root controller

Create a module tw2jittg21demo/widgets.py with the following content:

from tw2jittg21demo import model
from tw2.jit import SQLARadialGraph

def makeUserGraph():
    class UserGraph(SQLARadialGraph):
        id = 'whatever'
        base_url = '/jit_data'
        entities = [model.User, model.Group, model.Permission]
        excluded_columns = ['_password', 'password',
                            'user_id', 'group_id', 'permission_id']
        width = '920'
        height = '525'
        rootObject = model.User.query.first()
    return UserGraph

Modify your root controller in tw2jittg21demo/controllers/root.py to make use of the new widget.

Add the following import above the RootController definition:

from tw2jittg21demo.widgets import makeUserGraph

Modify the index(self) method so that the return statement looks like:

        return dict(page='index', widget=makeUserGraph())

And add a new method to serve json data to the jit widget.

    def jit_data(self, *args, **kw):
        """ Serve data from the tw2.jit built-in controller """
        return makeUserGraph().request(request).body

Display the widget in your root template

Replace all of the contents of tw2jittg21demo/templates/index.mak with the following two lines:

<%inherit file="local:templates.master"/>
${widget.display() | n }

Give it a spin

Point your browser once again at http://localhost:8080 and you should be greeted by something like the following:

Screenshot of the tw2.jit.SQLARadialGraph in action

Getting fancy

tw2.jit.SQLARadialGraph can make use of various method defined on the sqlalchemy entities of which its been made aware. One is the __unicode__(self) method which is already present on our models from the TG2.1 quickstart process. Another is __jit_data__(self) which must return a json-ifiable python dict.

In the future, we expect to support more attribute, but at the time of this writing the only attribute that is actually used on the client side is the value of an hover_html key if it is present at all.

Add the following two methods to your tw2jittg21demo.model.User class which is defined in tw2jittg21demo/model/auth.py:

    def gravatar_url(self):
        """ Return a link to the gravatar image for this email addy """
        import hashlib
        hsh = hashlib.md5(self.email_address).hexdigest()
        base = "http://www.gravatar.com/avatar/{hsh}?d=monsterid"
        return base.format(hsh=hsh)

    def __jit_data__(self):
        """ 'hover_html' is the only supported key at present """
        return {
            'hover_html' : """
            <img src="{gravatar_url}" />
            """.format(gravatar_url=self.gravatar_url, **self.__dict__)

Restart your webapp and re-visit http://localhost:8080. You should see fancy pop-ups now when you mouseover any entity for which a __jit_data__ method returns a dict containing a hover_html key.

sqlalchemy entities can provide tw2.jit.SQLARadialGraph with content to be displayed on mouseover


Lastly, you may want to re-style the jit graph to fit your page. Modify tw2jittg21demo/widgets.py by adding the following import statement at the top:

from tw2.core import JSSymbol

and by adding the following extra specifications to the UserGraph class:

        # Try to match colors to the TG banner
        backgroundcolor = '#FFFFFF'
        background = { 'CanvasStyles': { 'strokeStyle' : '#FFFFFF' } }
        Node = { 'color' : '#ffcb2f' }
        Edge = { 'color' : '#307e8a', 'lineWidth':1.5, }

        # Override the label style
        onPlaceLabel = JSSymbol(src="""
            (function(domElement, node){
                domElement.style.display = "none";
                domElement.innerHTML = node.name;
                domElement.style.display = "";
                var left = parseInt(domElement.style.left);
                domElement.style.width = '120px';
                domElement.style.height = '';
                var w = domElement.offsetWidth;
                domElement.style.left = (left - w /2) + 'px';

                domElement.style.cursor = 'pointer';
                if ( node._depth <= 1 )
                    domElement.style.color = 'black';
                    domElement.style.color = 'grey';

Once again restart your webapp and reload the page to get the following:

tw2.jit.SQLARadialGraph styled to fit more nicely with the TG2.1 default appearance


I hope this post was helpful and got you interested enough to check out the code and improve on it. Comments, questions, and patches are all appreciated.

View Comments
blog comments powered by Disqus