[sqlalchemy] Tutorial/Example Code

[David Bowser]

Hi,

I had a lot of trouble finding a decent set of examples on how to write a mixin 
that creates a class/table derived from a class. I've finally come up with a 
pattern that I'm pretty happy with an seems to fit most use cases that I saw 
when I was googling.  I extracted, simplified, and commented an example, but I 
didn't see how to submit it for consideration for use the in the documentation. 
Please see attached.

~David Bowser

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.


"""
Example of a mixin that supports creation of a table 
derived from a class, supports inheritance and multiple primary keys.
Written by David Bowser <dbowser(&)testedperfection.com>
Copyright (c) 2012 Tested Perfection, Inc
Released under the MIT License.
See http://www.opensource.org/licenses/mit-license.php
"""

from sqlalchemy.ext.declarative import declared_attr as _declared_attr
import sqlalchemy as sa
from sqlalchemy import event
import sqlalchemy.orm as orm
import sqlalchemy.types as typ

## This is what I use to generate table names. Change to whatever fits your enviroment
## {{{ http://code.activestate.com/recipes/577781/ (r1)
_ABERRANT_PLURAL_MAP = {
    'appendix': 'appendices',
    'barracks': 'barracks',
    'cactus': 'cacti',
    'child': 'children',
    'criterion': 'criteria',
    'deer': 'deer',
    'echo': 'echoes',
    'elf': 'elves',
    'embargo': 'embargoes',
    'focus': 'foci',
    'fungus': 'fungi',
    'goose': 'geese',
    'hero': 'heroes',
    'hoof': 'hooves',
    'index': 'indices',
    'knife': 'knives',
    'leaf': 'leaves',
    'life': 'lives',
    'man': 'men',
    'mouse': 'mice',
    'nucleus': 'nuclei',
    'person': 'people',
    'phenomenon': 'phenomena',
    'potato': 'potatoes',
    'self': 'selves',
    'syllabus': 'syllabi',
    'tomato': 'tomatoes',
    'torpedo': 'torpedoes',
    'veto': 'vetoes',
    'woman': 'women',
    }

_VOWELS = set('aeiou')

def pluralize(singular):
    if not singular:
        return ''
    plural = _ABERRANT_PLURAL_MAP.get(singular)
    if plural:
        return plural
    root = singular
    try:
        if singular[-1] == 'y' and singular[-2] not in _VOWELS:
            root = singular[:-1]
            suffix = 'ies'
        elif singular[-1] == 's':
            if singular[-2] in _VOWELS:
                if singular[-3:] == 'ius':
                    root = singular[:-2]
                    suffix = 'i'
                else:
                    root = singular[:-1]
                    suffix = 'ses'
            else:
                suffix = 'es'
        elif singular[-2:] in ('ch', 'sh'):
            suffix = 'es'
        else:
            suffix = 's'
    except IndexError:
        suffix = 's'
    plural = root + suffix
    return plural
## end of http://code.activestate.com/recipes/577781/ }}}

##CamelCase to Underscore case
def convert(name):
    s1 = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', name)
    return re.sub('([a-z0-9])([A-Z])', r'\1_\2', s1).lower()

##Pluralize the lastword in underscore case
def tablename(name):
    mn = convert(name)
    r = mn.rfind('_')
    p = pluralize(mn[r+1:])
    return mn[:r+1]+p
##End of Table name generation

class CommentTableMixin(object):

    @_declared_attr
    def comments(cls):
        #Set these so we can use them in our event listener
        my_name = cls.__name__+'Comment'
        table_name = tablename(my_name)
        
        #Easiest to wait until the table for cls exists
        @event.listens_for(orm.mapper,'instrument_class')
        def go(mapper,klass):
            #Make sure it doen't run for every class
            if klass != cls:
                return
            
            #Add any other columns you want to __klass_data, remove __tablename__ 
            #if making an assocation table
            __klass_data={'__tablename__' : table_name,
                          'id':sa.Column(typ.Integer,primary_key=True),
                          'comment':sa.Column(typ.Unicode(200))}

            for column in klass.__table__.primary_key.columns:
                __klass_data[convert(klass.__name__)+'_'+convert(column.name)] = \
                    #if making an association table instead of a class make this a primary key
                    sa.Column(column.type,sa.ForeignKey('{}.{}'.format(klass.__tablename__,column.name)))

            #Note: You can declare a table object here instead and add a fk  
            #constraint to your pk in __klass_data to create an association 
            #table the same way. An example use case would be to tie into a
            #global tags table. It gets slightly complicated if you want to
            #be able to query across all tags at once however. The solution
            #I have so far for that is to use single table inheritance, and
            #just indicate the type with an int.
            klass.CommentClass = type(my_name, ( #Add any other mixins here
                DeclarativeBase,object,),__klass_data)
        
        #Use table_name as secondary and replace my_name with the string of the name of the 
        #class you want the mixin to associate with for an association table
        return orm.relationship(my_name,
            lazy="dynamic",backref=orm.backref('%s'%convert(cls.__name__),uselist=False))



class Foo(CommentTableMixin,DeclarativeBase):
    __tablename__ = 'foos'
    id = sa.Column()

foo = Foo()
comment = Foo.CommentClass(foo=foo)