[Grok-dev] Grok with existing SQLAlchemy mapped classes

Kevin Teague kevin at bud.ca
Thu Jul 30 19:35:02 EDT 2009

Yes, I had the same requirements, that the classes that the ORM is
mapping onto don't have any Grok (or web-specific) dependencies. In my
case, this was for a bioinformatic setting, where some developer's
were working with the model from command-line scripts, and other
developer's needed to display that same model in an internal-only web

For the "core" model, I made a package who's only dependency is on SQL
Alchemy. Inside that package the mappings are defined. There are
functions for:

def setup_tables(metadata, tables):
def setup_mappers(tables, mappers):

Then in the web-specific package which depends upon Grok and
collective.lead there is a class to provide the IDatabase interface
for collective.lead which simply references the core package:

    class SbsDatabase(grok.GlobalUtility, Database):

        def _url(self):
            conf = SolexaConfig()
            db = conf.solexa_metadata_db
            return URL( drivername='postgres',

        def _setup_tables(self, metadata, tables):
            Map the database structure to SQLAlchemy Table objects
            solexa.model.saconfig.setup_tables(metadata, tables)

        def _setup_mappers(self, tables, mappers):
            Map the database Tables to SQLAlchemy Mapper objects
            solexa.model.saconfig.setup_mappers(tables, mappers)

Then the next step is to map the model classes to URLs, so that
traversal and view.url() works as expected. Here I wrote module which
is kinda messy (some of the code below is just noise ... it could use
a bit of a clean-up) but it does the job for our somewhat modest
requirements. The key is to provide traversers that know how to get
from a URL to an SQL Alchemy model, and then when those model objects
are instantiated use the zope.location.location.locate() function to
give the object the correct __name__ and __parent__ attribtues.

Overall, I'm pretty happy with this solution though ... we've had many
projects in my eight years in bioinformatics where we have "command-
line only" developers and "web only" developers, and they've always
ended up developing their own logic specific to "web" or "command-
line" and maintenance always becomes a royal PITA. By keeping the
dependency on the core model package to just SQL Alchemy the command-
line only people don't get freaked out by a bunch of web-specific code
and we can get the dev's to collaborate on a shared model :)

    The sbs.locate module handles mapping from SQL Alchemy objects
    into our URL structure.
    import grok
    import zope.location.location
    from collective.lead.interfaces import IDatabase
    import solexa.model
    import sbs.app
    import sbs.interfaces

    mappings = {
        'libraries': (sbs.interfaces.ILibraries, solexa.model.Library,
        'projects': (sbs.interfaces.IProjects, solexa.model.Project,
        'protocols': (sbs.interfaces.IProtocols,
solexa.model.Protocol, 'id'),
        'adaptor_protocols': (sbs.interfaces.IProtocols,
solexa.model.AdaptorProtocol, 'id'),
        'flowcells': (sbs.interfaces.IFlowcells,
solexa.model.Flowcell, 'lims_flowcell_code'),
        'runqueue': (sbs.interfaces.IRunQueue, solexa.model.RunQueue,
        'maqqueue': (sbs.interfaces.IMaqQueue, solexa.model.MaqQueue,

    def locate(context, obj):
        Locate an SQL Alchemy object with our URL scheme
        container = sbs.app.SQLContainer()

        # walk up the __parent__'s of the context until we get
        # to the ISequencingTechnology object (e.g. Illumina or SOLID)
        seqtech = context
        while not sbs.interfaces.ISequencingTechnology.providedBy
            seqtech = seqtech.__parent__

        for key, value in mappings.items():
            klass = value[1]
            if isinstance(obj, klass):
                name = key
                primary_key_name = value[2]
                zope.interface.alsoProvides(container, value[0])

        container.__parent__ = seqtech
        container.__name__ = name

        obj.__name__ = str(getattr(obj, primary_key_name))
        obj.__parent__ = container

    class SQLContainerTraverser(grok.Traverser):
        Each SBS technology (e.g. Illumina, SOLID) has a simple URL
space that is
        the name of the table, then the name of the id for a record in
that table.

        For example:

        /projects/10 - Project #10
        /libraries/MM0246 - Library MM0246
        /protocols/SLX-SAGE - Procotol SLX-SAGE

        This traverser will support traversal to just the table
        A second traverser will support navigation into individual SQL

        mappings = mappings

        def traverse(self, name):
            container = sbs.app.SQLContainer()
            # assign __parent__ and __name__ attributes
            # URL stands for Uniform Resource Location
            zope.location.location.locate(container, self.context,
            if self.mappings.has_key(name):
                # declare that the generic container has a specific
                zope.interface.alsoProvides(container, self.mappings
                container.model_class = self.mappings[name][1]
                container.primary_key_fieldname = self.mappings[name]
                return container
                return None

    class SQLModelTraverser(grok.Traverser):
        Based on the Container that represents a SQL Table, fetch a
model object
        from that table, and give that model a location. Also mark the
        object as providing the ISQLModel interface so that Views for
that model
        can have the default view name of 'index'.

        def traverse(self, name):
            session = zope.component.getUtility(IDatabase,
            model = session.query(
self.context.primary_key_fieldname) == name
            if model:
                zope.location.location.locate(model, self.context,
            return model

More information about the Grok-dev mailing list