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", reverse="parent")
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", reverse="children") tags = many_to_many("Tag", "child_tag", "child_id", "tag_id") 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') Entity.session.add(Parent( children={ Child(tags={t1, t2}), Child(tags={t1, t3}), Child() })) Entity.session.commit() p1 = Entity.session.query(Parent).first() for child in p1.children: print child, [t.name 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 configurations.""" 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 and....work 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 = self.kw.get('reverse') if reverse: reverse_attr = getattr(target_cls, reverse) if not isinstance(reverse_attr, DeferredProp): reverse_attr.property._add_reverse_property(key) rel._add_reverse_property(reverse) class FKRelationship(DeferredProp): """Generates a one to many or many to one relationship.""" def __init__(self, target, fk_col, **kw): self.target = target self.fk_col = fk_col self.kw = kw def _config(self, cls, key): """Create a Column with ForeignKey as well as a relationship().""" target_cls = cls._decl_class_registry[self.target] 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) else: fk_col = Column(self.fk_col, pk_col.type, ForeignKey(pk_col)) setattr(fk_target, self.fk_col, fk_col) rel = relationship(target_cls, primaryjoin=fk_col==pk_col, collection_class=self.kw.get('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): self.target = target self.tablename = tablename self.local = local self.remote = remote self.kw = kw def _config(self, cls, key): """Create an association table between parent/target as well as a relationship().""" target_cls = cls._decl_class_registry[self.target] local_pk = list(cls.__table__.primary_key)[0] target_pk = list(target_cls.__table__.primary_key)[0] t = Table( self.tablename, cls.metadata, Column(self.local, ForeignKey(local_pk), primary_key=True), Column(self.remote, ForeignKey(target_pk), primary_key=True), keep_existing=True ) rel = relationship(target_cls, secondary=t, collection_class=self.kw.get('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. """ @declared_attr 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:"_" + m.group(0).lower(), name[1:]) ) id = Column(Integer, primary_key=True) """Surrogate 'id' primary key column.""" @classmethod def setup_database(cls, url, create=False, echo=False): """'Setup everything' method for the ultra lazy.""" configure_mappers() e = create_engine(url, echo=echo) if create: cls.metadata.create_all(e) 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 was...it's a toolkit - you should build things !
Look for SQLAlchemy 0.7's production release soon, in the meantime here's some magic.