September 06, 2010

shabti – Elixir and SQLAlchemy

The default Shabti template adds Elixir to Pylons for those developers who wish to use a high-level declarative layer on top of SQLAlchemy.

About Elixir

Elixir is a declarative layer on top of the SQLAlchemy library. It is a fairly thin wrapper that provides the ability to create simple Python classes that map directly to relational database tables. This pattern is often referred to as the Active Record design pattern and provided many of the benefits of traditional databases without losing the convenience of Python objects.

Elixir is intended to replace the ActiveMapper SQLAlchemy extension [..] but does not intend to replace SQLAlchemy’s core features and instead focuses on providing a simpler syntax for defining model objects when you do not need the full expressiveness of SQLAlchemy’s manual mapper definitions.

Note

The elixir FAQ is a particularly good source of examples of elixir use patterns.

Note

It is important to appreciate that Elixir’s added layer can make debugging relational database issues significantly more difficult. Addressing these issues will typically require the developer to have considerable familiarity with SQLAlchemy. Such issues can arise when dealing with legacy and/or complex databases.

Note

shabti_default source code is in the bitbucket code repository

Examples

Model

Here is a very short model definition example.

# model/person.py

class Person(Entity):
    username = Field(String(128))
    addresses = OneToMany('Address')

class Address(Entity):
    email = Field(Unicode(128))
    owner = ManyToOne('Person')

Controller

Here is an example of a typical query performed in the controller.

# controllers/person.py

Session.query(Person).filter_by(name = u'John Smith').first()

Elixir provides some convenience accessors such as get_by() which lies at the core of this simple but handy object access filter that behaves similarly to the Django shortcut of the same name:

# lib/helpers.py

def get_object_or_404(model, **kw):
    from pylons.controllers.util import abort
    obj = model.get_by(**kw)
    if obj is None:
        abort(404)
    return obj

This is used very simply in a controller as a guard:

# controllers/person.py

def show(self, name):
    user = get_object_or_404(model.Page, username=name)
    [ ... ]

Note

the “About” text” and the model example above was lifted from the elixir wiki

Using the default Shabti template

After successfully installing Shabti, additional paster templates will be available. Simply create a Shabti-configured project by specifying that paster should use the shabti template:

$ paster create -t shabti myproj

These are the option dialogue choices appropriate for the default Shabti template — which uses mako templates and SQLAlchemy ...

(mako/genshi/jinja/etc: Template language) ['mako']:
(True/False: Include SQLAlchemy 0.4 configuration) [False]: True
(True/False: Setup default appropriate for Google App Engine) [False]:

Once the project has been created, navigate to the project directory and start the Pylons web app with:

$ paster serve --reload development.ini

The Shabti default template’s variant on the standard Pylons welcome screen is browsable at at http://localhost:5000/ ...

And Elixir is now available for use in modelling.

Integration details

This template follows the standard Pylons model template quite closely. Shown below is the difference between the Shabti and Pylons versions of model/meta.py: at the time of writing, Shabti is slightly in ahead of Pylons in moving to SQLAlchemy 0.5, otherwise there is no difference:

Pylons vs Shabti model/meta.py

--- model/meta.py
+++ model/meta.py
@@ -8,7 +8,11 @@
 engine = None

 # SQLAlchemy session manager.  Updated by model.init_model()
-Session = scoped_session(sessionmaker(autoflush=True, transactional=True))
+import sqlalchemy
+if sqlalchemy.__version__ > '0.5':
+    Session = scoped_session(sessionmaker())
+else:
+    Session = scoped_session(sessionmaker(autoflush=True, transactional=True))

 # Global metadata. If you have multiple databases with overlapping table
 # names, you'll need a metadata for each database

The main variant from standard Pylons occurs in model/__init__.py which brings in the elixir metadata, hooks up elixir to the SQLA database Session and runs elixir’s setup_all() to set up elixir’s mapping between the Python objects and the database schemata.

Pylons vs Shabti model/__init__.py

--- pylons/model/__init__.py
+++ shabti/model/__init__.py
@@ -1,18 +1,20 @@
 """The application's model objects"""
-import sqlalchemy as sa
-from sqlalchemy import orm
+import elixir

 from {{package}}.model import meta

+Session = elixir.session = meta.Session
+elixir.options_defaults.update({'shortnames': True})
+metadata = elixir.metadata
+
+# this will be called in config/environment.py
 def init_model(engine):
-    """Call me before using any of the tables or classes in the model"""
-    ## Reflected tables must be defined and mapped here
-    #global reflected_table
-    #reflected_table = sa.Table("Reflected", meta.metadata, autoload=True,
-    #                           autoload_with=engine)
-    #orm.mapper(Reflected, reflected_table)
-    #
-    meta.Session.configure(bind=engine)
-    meta.engine = engine
+    elixir.session.configure(bind=engine)
+    metadata.bind = engine
+
+# import other entities here, e.g.
+# from {{package}}.model.blog import *
+
+elixir.setup_all()

The Elixir model is initialised by Pyons via the call to init_engine() that Pylons provides as standard in config/environment.py (when SQLA is selected in paster’s “create project” dialogue).

lib/base.py

# Setup SQLAlchemy database engine
engine = engine_from_config(config, 'sqlalchemy.')
init_model(engine)

The model and the database Session are made available in the standard Pylons import from myapp import model in the BaseController file lib/base.py. The standard change to BaseController is made, again as per standard Pylons/SQLA practice, to remove the Session in a try/finally statement, preventing stale data from contaminating other sessions:

config/environment.py

try:
    return WSGIController.__call__(self, environ, start_response)
finally:
    model.Session.remove()

Finally, websetup.py is adjusted to suit use with elixir:

Pylons vs Shabti websetup.py

--- pylons/websetup.py
+++ shabti//websetup.py
@@ -1,3 +1,4 @@
+# -*- coding: utf-8 -*-
 """Setup the {{project}} application"""
 import logging

@@ -5,13 +6,21 @@

 log = logging.getLogger(__name__)

+from paste.deploy import loadapp
+from pylons import config
+from elixir import *
+from {{package}} import model
+
 def setup_app(command, conf, vars):
     """Place any commands to setup {{package}} here"""
     load_environment(conf.global_conf, conf.local_conf)
+    model.metadata.create_all(checkfirst=True)

-    # Load the models
-    from {{package}}.model import meta
-    meta.metadata.bind = meta.engine
+    # Initialisation here ... this sort of stuff:

-    # Create the tables if they aren't there already
-    meta.metadata.create_all(checkfirst=True)
+    # e = model.Session.query(model.<modelfile>.<An_Entity>).get(1)
+    # e.g. foo = model.Session.query(model.identity.User).get(1)
+    # from datetime import datetime
+    # some_entity.poked_on = datetime.now()
+    # model.Session.add(some_entity)
+    model.Session.commit()

Notes on the template

At one point Elixir was under consideration as an supported component of Pylons but this intention had to be abandoned as people persistently failed to understand that developing with high-level abstractions is not as simple as it might appear at first glance.

For those developers who are interested in more details, see the associated practical notes on using Elixir with Pylons and other Elixir topics including the generation of Elixir models from UML diagrams produced in ArgoUML.

author:Graham Higgins <gjh@bel-epa.com>

September 06, 2010