Magic, a "New" ORM

May 17, 2011 at 07:36 PM | SQLAlchemy, Code | View Comments

TL;DR - Use SQLAlchemy to create your own Magic.

It's new, and easy! That's why we call it what it is: Magic. A new ORM that keeps things simple. Let's dive in !

from magic import (
            Entity, one_to_many, many_to_one, many_to_many, string

I like this so far ! What does a new model class look like ?

class Parent(Entity):
    children = one_to_many("Child", "child_id",

No. Way. That's it ? No tables and columns ? No foreign thingies ? Where's the meaningless boilerplate ?

class Child(Entity):
    parent = many_to_one("Parent", "child_id",

    tags = many_to_many("Tag", "child_tag",

class Tag(Entity):
    name = string(50)

OK that's a little more chatty but seriously zzzeek, don't you want some weird "==" signs in there ?

Entity.setup_database("sqlite://", create=True)

This is beginning to remind me of washing machines that also have a dryer built inside of them, or those TVs that have VCRs embedded inside the case.

t1, t2, t3 = Tag(name='t1'), Tag(name='t2'), Tag(name='t3')
            Child(tags={t1, t2}),
            Child(tags={t1, t3}),

p1 = Entity.session.query(Parent).first()
for child in p1.children:
    print child, [ for t in child.tags]

New-style sets! Hooray for Python.

And...that's it. Magic!

Would you want this ORM ? Or would you want a different one ? Well how does Magic work ? I'm pretty sure you can guess how it starts:

from sqlalchemy import (
            Column, ForeignKey, Table,
            Integer, String, create_engine

There's the zzzeek we know ! Blah blah blah tables, constraints, boring things. Well we might as well get on with it:

from sqlalchemy.orm import (
            class_mapper, mapper, relationship,
            scoped_session, sessionmaker, configure_mappers
from sqlalchemy.ext.declarative import declared_attr, declarative_base
from sqlalchemy import event
import re

I like how "re" is the honored guest of all that SQLAlchemy stuff.

@event.listens_for(mapper, "mapper_configured")
def _setup_deferred_properties(mapper, class_):
    """Listen for finished mappers and apply DeferredProp

    for key, value in class_.__dict__.items():
        if isinstance(value, DeferredProp):
            value._config(class_, mapper, key)

And here we have our first docstring. What is this "event" you speak of ?

zzzeek says: That's the new thing in 0.7 ! You're going to get a lot of mileage out of it - everything that used to be extension this, listener that, all goes through event. And there's lots of new events added with more on the way.

For this one in particular, just like it says, anytime a new mapper appears, this thing is going to run all the magic.

Well it was nice while it lasted, I guess it's about to get ugly huh.

Deep breath, just a slight pinch:

class DeferredProp(object):
    """A class attribute that generates a mapped attribute
    after mappers are configured."""

    def _setup_reverse(self, key, rel, target_cls):
        """Setup bidirectional behavior between two relationships."""

        reverse ='reverse')
        if reverse:
            reverse_attr = getattr(target_cls, reverse)
            if not isinstance(reverse_attr, DeferredProp):

class FKRelationship(DeferredProp):
    """Generates a one to many or many to one relationship."""

    def __init__(self, target, fk_col, **kw): = target
        self.fk_col = fk_col = kw

    def _config(self, cls, key):
        """Create a Column with ForeignKey as well as a relationship()."""

        target_cls = cls._decl_class_registry[]

        pk_target, fk_target = self._get_pk_fk(cls, target_cls)
        pk_table = pk_target.__table__
        pk_col = list(pk_table.primary_key)[0]

        if hasattr(fk_target, self.fk_col):
            fk_col = getattr(fk_target, self.fk_col)
            fk_col = Column(self.fk_col, pk_col.type, ForeignKey(pk_col))
            setattr(fk_target, self.fk_col, fk_col)

        rel = relationship(target_cls,
      'collection_class', set)
        setattr(cls, key, rel)
        self._setup_reverse(key, rel, target_cls)

class one_to_many(FKRelationship):
    """Generates a one to many relationship."""

    def _get_pk_fk(self, cls, target_cls):
        return cls, target_cls

class many_to_one(FKRelationship):
    """Generates a many to one relationship."""

    def _get_pk_fk(self, cls, target_cls):
        return target_cls, cls

class many_to_many(DeferredProp):
    """Generates a many to many relationship."""

    def __init__(self, target, tablename, local, remote, **kw): = target
        self.tablename = tablename
        self.local = local
        self.remote = remote = kw

    def _config(self, cls, key):
        """Create an association table between parent/target
        as well as a relationship()."""

        target_cls = cls._decl_class_registry[]
        local_pk = list(cls.__table__.primary_key)[0]
        target_pk = list(target_cls.__table__.primary_key)[0]

        t = Table(
                Column(self.local, ForeignKey(local_pk), primary_key=True),
                Column(self.remote, ForeignKey(target_pk), primary_key=True),
        rel = relationship(target_cls,
      'collection_class', set)
        setattr(cls, key, rel)
        self._setup_reverse(key, rel, target_cls)

That was highly unpleasant. Please don't paste that much code again.

zzzeek says: OK! It's just doing the foreign key and relationship() for us. If you've worked with straight SQLAlchemy before, most of what's in there shouldn't be too mysterious.

We're getting the "target" of the relationship using _decl_class_registry, a dictionary that gives us the target class based on the string, which is put there by Declarative. We're looking at the existing classes and their __table__ to get at the appropriate primary key (assumed to be non-composite.... a little more magic could certainly improve upon that though!), we create a Column() with ForeignKey() the way you'd normally be doing for all your mapped classes individually, or in the case of many-to-many we just put two of them into a Table. Then we send out a relationship() with what we've come up with. We can stick these attributes right on the classes and Declarative takes care of making sure they are mapped and such.

It's an entirely alternate form of relationship in just 80 lines - there's lots of ways to play with things like this. I personally don't need this much re-working of SQLAlchemy's usual relationship() syntax, and I think most of our users don't either - but the job first and foremost of relationship() is to have awesome functionality. I've seen some requests sometimes to make it do things like this, and one of our goals is to make whatever customizations people need as doable as possible. Patterns like these can change how the rest of your project looks. That one is pretty ambitious - but there's plenty of others that are a lot simpler, and can really cut down on noise throughout the bulk of mapping code:

def string(size):
    """Convenience macro, return a Column with String."""

    return Column(String(size))

def int():
    """Convenience macro, return a Column with Integer."""

    return Column(Integer)

Why thank you !

class Base(object):
    """Base class which auto-generates tablename, surrogate
    primary key column.

    Also includes a scoped session and a database generator.

    def __tablename__(cls):
        """Convert CamelCase class name to underscores_between_words
        table name."""
        name = cls.__name__
        return (
            name[0].lower() +
            re.sub(r'([A-Z])', lambda m:"_" +, name[1:])

    id = Column(Integer, primary_key=True)
    """Surrogate 'id' primary key column."""

    def setup_database(cls, url, create=False, echo=False):
        """'Setup everything' method for the ultra lazy."""

        e = create_engine(url, echo=echo)
        if create:
        cls.session = scoped_session(sessionmaker(e))

Entity = declarative_base(cls=Base)

Well now ! Why didn't you tell us you could do that before ? I've been putting __tablename__ and columns all over the place.

zzzeek says: We get into it to a good degree when we talk about "mixins" here , most of what mixins do can go on your "base" as well.

Alrighty. Short blog post today?

zzzeek says: Indeed. The moral of the story is, SQLAlchemy isn't a framework, and never's a toolkit - you should build things !

Look for SQLAlchemy 0.7's production release soon, in the meantime here's some magic.