Category Archives: SQLAlchemy

Tesla 0.2 changes

A few things have happened in the Tesla project over the last week:

  • Tesla is now on Cheeseshop ! This means you can install Tesla by doing easy_install (-U) Tesla.
  • Migrations. Yes, Tesla can do Rails-style migrations, thanks to the migrate library for SQLAlchemy. Read more about it here. Migrate commands can now be called from paster and a lot of manual steps are taken care of.

Plus, a bunch of unit tests and minor fixes.

Overall, I’ve tried to keep Tesla as lightweight as possible – adding some Elixir/SQLAlchemy bindings to a basic Pylons framework. The big news in Python this week has been that TurboGears 2.0 is going to be rewritten on top of Pylons, which is a great step forward for both communities and Python in general.

However, TG 2.0 will appeal to those who like the TurboGears way of doing things, while many “core” Pylons developers may prefer a more lightweight framework that stays out of their way but takes care of some lower-level details. That’s pretty much what I’m trying to achieve here. The challenge is to keep Tesla from bloating – so for example I won’t be choosing a particular template engine, widget library etc. That’s up to you.

Some things that are in the pipeline:

  • Integrating Mike Orr’s SAContext, which should provide more configuration options.
  • An AuthKit template based on Tesla, with some default identity classes and helpers.
  • The amazing thing about writing Tesla was how little work was involved – the heavy lifting is done by Paste and Pylons. You just write your glue code and templates and voila – a new framework.

    Now, when will we see Django running on Pylons ?


Tesla, a Pylons/Elixir framework

David Bowie as Nicola Tesla in the Prestige

Tesla is a framework built on top of Pylons and Elixir/SQLAlchemy. It’s being actively developed by myself and Ben Bangert (of Pylons fame). Like Pylons, Tesla is a Paste template, so you just download it, install it and run paster create -t pylons_elixir myapp to get started. A tutorial can be found here.

Pylons and Elixir/SQLAlchemy give you great power and flexibility; however, getting SQLAlchemy and Pylons to work together is a non-trivial task, as the mailing lists and IRC chats will attest. Tesla takes care of that for you by providing the easiest possible setup. It handles unit tests, setup code, and shell integration, and adds some extra commands for creating models and managing your database schema.

There will be more to come. Ben is working on a template based on Tesla with AuthKit integration and basic identity models, and we’ll probably be adding more commands. Integration with the SQLAlchemy migrate library is a likely possibility at some point.

At the moment Tesla is only available on SVN from Google Code. It’s really pre-alpha, so it’s not quite stable yet. Once it’s a bit more tested and stable I’ll probably add it to the Cheeseshop. Any feedback, suggestions and criticism are welcome (please post to the Pylons mailing list or IRC chat).

The name comes from Nicola Tesla, one of the greatest (and most unrecognized) scientists and inventors of the last century.

Turtles, all the way down : WSGI middleware

WSGI (Web Standard Gateway Interface) provides a standard for connecting a Python web application or framework to a web server. The framework provides a function, or callable, that is called by code on the server side, for example a CGI or FastCGI script. Here’s an example, from Wikipedia:

def app(environ, start_response):
    start_response('200 OK', [('Content-Type', 'text/plain')])
    return ['Hello Worldn']

As you can see, it’s pretty basic and low-level. However, it is a complete WSGI application. As an aside, Paste, which is used by Pylons, is a WSGI implementation, with building blocks for building and deploying your own WSGI frameworks.

The nice thing about it is that you can add middleware between the server and your application. Middleware is just functions (or callables) like the one shown above, but you can implement both application and server-side code. This is a powerful,yet beautifully simple concept. Here is an example:

class PrintTimeMiddleware(object):
       def __init__(self, app):
   = app
       def __call__(self, environ, start_response):
            print str(

We then add this into our server script as so:

app = PrintTimeMiddleware(app)

The “app” in question is the instance of any previous middleware, so it will be executed in that order. Each middleware is called in that order as app(environ, start_response). In this case we simply print the current time to STDOUT. Middleware can be pretty much anything you imagine…for example, it can do XSLT transformations, user authentication, database connection pooling, browser checking…

The Pylons framework itself is middleware. If you look at a Pylons application, under config/, you see this line:

app = pylons.wsgiapp.PylonsApp(config,helpers=myapp.lib.helpers,g=app_globals.Globals)

Now for something actually useful. In a Pylons application, or any other WSGI-based framework, you can of course add your own middleware. In a previous article, “Using Elixir with Pylons”, I mentioned that you need to create a SQLAlchemy session with each new HTTP request thread, to ensure your SQLAlchemy or Elixir objects can connect to the engine in that thread. In addition, when starting the application, we need to create a new engine for our database which is then cached for later use. Now, we could do it in our code (for example in BaseController) but it is a lot more elegant to add it to our middleware. Let’s create a little middleware class for this:

from elixir import metadata, objectstore
from pylons.database import create_engine, make_session

session_context = objectstore.context
engine = None

# call this with every thread
def resync():
    session_context.current = make_session()

# connects engine to metadata
def connect():
    global engine
    if not engine:
        engine = create_engine()

def flush_all():


from myapp import models as model

class ModelMiddleware(object):
    def __init__(self, app): = app
    def __call__(self, environ, start_response):

Then, in config/, under the # YOUR MIDDLEWARE # comment, we add the middleware instance:

app = ModelMiddleware(app)

When the middleware is initialized, it creates the engine once in model.connect(). When it is called, the model.resync() line ensures a session instance is created and passed to the Elixir session context. This will now happen with each HTTP request.

We might want to modify it slightly, so that all changes are committed to the database at the end of the request. Personally, I don’t like to do this, as I prefer a more fine-grained approach, but others might want to do so.Let’s change __call__ slightly:

def __call__(self, environ, start_response):
    app =,start_response)
    return app

As you can see, you can hook into any part of the request lifecycle. One thing to remember is that this will happen to all requests; if I wanted more fine-grained control over individual requests, then the place for that would be the application code itself. For example, in Pylons, I can implement __before__ and __after__ methods in my controllers.

Using Elixir with Pylons

There seems to be a lot of discussion about best practices on how to create connections to SQLAlchemy or Elixir engines from within Pylons. The best solution I have found is here:

from elixir import metadata, objectstore
from pylons.database import make_session

def connect():

    session = make_session()
    objectstore.context.current = session

Bearing in mind that Elixir provides its own Session Context through objectstore.

Then, in my BaseController:

class BaseController(WSGIController):

    def __call__(self, environ, start_response):
        return WSGIController.__call__(self, environ, start_response)

This seems to work fine. The bonus is that I can call model.connect() from anywhere in my Pylons app: in, paster shell, even unit tests, and it automagically picks up the sqlalchemy.dburi setting in the correct configuration file (development.ini or test.ini, for example).

I gather that the above code should also take care of connection pooling, although I don’t really know for sure. If any SQLAlchemy experts have a take on this, please let me know.

Update: looking at the Pylons code, it seems that you don’t need to connect the engine to the metadata with each request. Pylons caches engine instances, so the engine is created once per application.  We just need to ensure that a new session is started with each new request thread. Here is the full recipe.

Elixir tip: relationship names of classes in other modules

Almost all examples of Elixir code have all the Entity classes in the same module, for example:

class User(Entity):

    has_many('newsitems', of_kind='NewsItem')

class NewsItem(Entity):

    belongs_to('author', of_kind='User')

However, I personally find that once you get beyond 3-4 classes in the same module it gets a bit unwieldy, moreso if you add additional methods to those classes. Although I dislike the way that Rails’ ActiveRecord encourages one file per class (actually,there is a lot I dislike about the way Rails does class loading, but that’s the subject for another post) I prefer to group model classes into logical modules.

However, one thing to remember when using Elixir, the of_kind argument in belongs_to, has_many, etc must then take the full module path name rather than just the class name, so if we split up the above code into two files, myapp/models/ and myapp/models/, we would do this:


class User(Entity):

    has_many('newsitems', of_kind='')


class NewsItem(Entity):

    belongs_to('author', of_kind='myapp.models.user.User')

Adding event callbacks to SQLAlchemy/Elixir classes

Rails’ ActiveRecord makes it quite easy to ad event callbacks when an object is inserted, updated or deleted:

class User < ActiveRecord::Base
    before_create :encrypt_password

    def encrypt_password


In SQLAlchemy we can do the same with a MapperExtension:

from sqlalchemy.orm.mapper import MapperExtension
class EncryptField(MapperExtension):

    self.__init__(self, field_for_encryption):
        self.field = field_for_encryption

    def before_insert(self, mapper, connection, instance):
         encrypted_value = do_some_encryption(getattr(instance, self.field))
         setattr(instance, self.field, encrypted_value)

When we declare our mapper, we pass an instance of this class to the extension argument. This can be a single MapperExtension instance, or list of instances(so we can chain extensions together):

users_mapper = mapper(User, users_table,  extension = EncryptField('password'))

If you are using Elixir you have to use the using_mapper_options statement:

class User(Entity):

     has_field('password', String(50))
     using_mapper_options(extension = EncryptField('password'))

The MapperExtension class provides various methods for you to override, for example before and after insert/update/delete and when the object is retrieved from the database.

OK, but what we really want is something like this:

class User(Entity):

    has_field('password', String(50))

    def encrypt_password(self):
        self.password = do_some_encryption(self.password)

With Elixir we can do this with a Statement. Elixir infact is little more than Statements (DSLs) on top of SQLAlchemy. See for an excellent introduction to Statements.

First, we need a MapperExtension subclass that handles the before_insert behaviour in a generic way:

from sqlalchemy.orm.mapper import MapperExtension
class MapperExtensionProxy(MapperExtension):

     def before_insert(self, mapper, connection, instance):
         if has_attr(instance, '__before_insert__'): instance.before_insert()

This simply checks if our Entity subclass has the __before_insert__ method (underscores added to prevent possible name clashes) and calls that method. We could do exactly the same with other behaviours, such as after_insert, before_destroy, etc; see the MapperExtension docs for a list of methods and arguments.

Then, we need to create the Statement class. This is just a plain Python new-style class:

from elixir.statements import Statement

class BeforeInsert(object):

    def __init__(self, entity, *callbacks):
        add_extension_proxy(entity, 'before_insert', callbacks)

before_insert = Statement(BeforeInsert)

The add_extension_proxy function simply adds the appropriate behaviour to the Entity class, then adds the MapperExtensionProxy object to the list of extensions the Entity has (that way, we don’t override other extensions we might want to use).

So now we can do this:

class User(Entity):

    has_field('password', String(50))

    def encrypt_password(self):
        self.password = do_some_encryption(self.password)

   def email_new_user(self):
        self.send_notification_mail('Welcome to the system')

   def email_ex_user(self):
        self.send_notificiation_mail('Your account is about to be destroyed, Bye!')

The full code for adding these events to Elixir can be found here.

Pylons, or Python is fun again !

I’ve always liked Python. I started using it back in 2003, and it’s always been my first choice when writing stuff like sysadmin scripts, built tasks, RSS feed readers, that kind of thing.

However, my bread and butter is web development, and Python never had a compelling story for me in that field. Zope/Plone is horrendously bloated and complex (the infamous Z-shaped learning curve), Django is nice and well thought out, but I’ve found it more for developing dynamic web sites; it gets in the way too much for complex web apps. Turbogears started out well, but seems broken and lacking in direction these days. Then we have a number of smaller, or older, frameworks –, Quixote, snakelets, WebWare…either too small a developer base, not actively developed, or just not what I’m looking for.

So, for web development, I’ve used J(2)EE, PHP and more recently, Rails. The latter has been the most fun, and Ruby is a nice language to work with. However, despite the deluge of Ruby hype and praise (much of it justified) I still prefer Python. Maybe it just fits my brain better, maybe it’s the scope and size of the libraries. I find Ruby harder to understand and debug, perhaps because of its Perlish syntax and metaprogramming tricks. This is not meant as flamebait: I just find Python easier for getting things done.

I can think better in Python; I find the process harder in Ruby, and often find there’s always a “better” way of doing it. As with Perl, I feel that while I may be a competent Ruby programmer, I’ll never be a Ruby guru (I think that may be the case of many Rails developers : they use Rails and its plugins without really needing to understand Ruby at any deeper level, thanks to Rails’ DSLs). However, the lack of a compelling framework, which would give me the same kind of productivity as Rails, has held me back from using Python for serious web projects.

Until I found Pylons. Unlike Rails and Django, Pylons is not marketed in any way; it is strictly minimalist, a “hacker’s framework”. This will not appeal to those who turn to Rails or Django for an out of the box solution with their decisions made for them (and nothing wrong with that, if you just want to get your project out the door), but it appeals to someone who enjoys building something just the way they want it, and dislikes “opinionated” frameworks that like to think they always know better.

Superficially, Pylons resembles Rails: it follows the same MVC pattern, supports AJAX, and uses Routes and helpers. That’s where the similarity ends. For starters, there’s no ActiveRecord: you provide your own model; most Pylons developers go with SQLAlchemy, possibly one of the finest ORMs in any language. Further, rather than just ERB on the menu, you can choose your own template engine. Myghty is the current default, but this is soon to be replaced with the awesome Mako, and you can use for example Genshi or Kid if you prefer something more XML-based, or Jinja if you like Django’s minimalist templates.

The most interesting thing about Pylons however is that it runs on Paste and WSGI, the Python web server gateway standard. You can run your Pylons apps on mod_python, FastCGI, SCGI, whatever you want, and build your own custom frameworks with Paste templates.

I can’t remember where I saw the quote, but David Heinemeier Hansson, the leading Rails developer, once compared Rails to a Ferrari. Well, a Ferrari is nice (and maybe not the best comparison, considering the performance of Ruby) but Pylons + SQLAlchemy + Mako is more like James Bond’s Aston Martin, customized by Q. It just looks like a normal car but comes with ejector seats, bulletproof glass and tons of gadgets. I know which one I’d like to drive.