Tales of a lazy developer

Posted on Sun 22 January 2017 in programming

Some say laziness makes a good developer. I don't know if that's always true.

Anyhow, just like everybody else, I hate typing, so I've created some helpers for sqlalchemy to get rid of the boring stuff in creating ORM-Classes

import sqlalchemy.orm.exc
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import relationship, backref

def pluralize(word):
    if word.lower()[-1] == 'y':
        return word[0:-1] + 'ies'
    elif word.lower()[-2:] == 'is':
        return word[0:-2] + 'es'
    elif word.lower()[-1] != 's':
        return word + "s"
        return word

def references(clsname, colname=None, primary=False, nullable=True, join_path=None, backrefs=True):

    if colname is None:
        colname = clsname.lower()

    def column(cls):
        return Column(colname,
                      ForeignKey(pluralize(clsname) + '.id', ondelete='RESTRICT', onupdate='CASCADE'),
                      primary_key=primary, nullable=nullable)

    if backrefs:
        def relation(cls):
            return relationship(clsname, backref=backref(pluralize(cls.__name__.lower())), foreign_keys=join_path)
        def relation(cls):
            return relationship(clsname, foreign_keys=join_path)

    methods = {colname + '_id': column,
               colname: relation}
    return type('Ref'+clsname+'Mixin', (object,), methods)

def selfreferences():
    def column(cls):
        return Column('parent',
                      ForeignKey(pluralize(cls.__name__) + '.id', ondelete='RESTRICT', onupdate='CASCADE'))

    def children(cls):
        return relationship(cls.__name__, backref=backref('parent', remote_side=[cls.id]), post_update=True)

    return type('SelfrefMixin', (object,), {'parent_id': column,
                                            'children': children})

class TableNameMixin:
    """ Mixin for defining the Tablename
        for the implementing ORM-Class

    def __tablename__(cls):
        return pluralize(cls.__name__)

class PrimaryTableMixin(TableNameMixin):
    """ Mixin for Tables with a Primary key
        adds a column "id" which will be
        the primary key for the Table
    id = Column(Integer, primary_key=True, autoincrement=True)

class CodedTableMixin:
    """ Mixin for Tables with a human "readable" code as unique """
    code = Column(String, unique=True, nullable=False)

    def get(cls, code):
            return cls.query.filter(cls.code == code).one()
        except sqlalchemy.orm.exc.NoResultFound:
            raise sqlalchemy.orm.exc.NoResultFound('No "{cls}" found with code "{code}"'.format(cls=cls.__name__,

This allows for some nice code like:

class Field(PrimaryTableMixin, Base):

class Sender(PrimaryTableMixin, Base):

class SenderField(PrimaryTableMixin,
                  references('Sender', nullable=False),
                  references('Field', nullable=False),

which would easily some 20 line monster if done the intended way:

class Field(Base):
    __tablename__ = 'Fields'
    id = Column(Integer, primary_key=True, autoincrement=True)

class Sender(Base):
    __tablename__ = 'Senders'
    id = Column(Integer, primary_key=True, autoincrement=True)

class SenderField(Base):
    __tablename__ = 'SenderFields'
    id = Column(Integer, primary_key=True, autoincrement=True)
    sender_id = Column('sender', ForeignKey('Senders.id', ondelete='RESTRICT', onupdate='CASCADE'), primary_key=primary, nullable=nullable)
    field_id = Column('field', ForeignKey('Fields.id', ondelete='RESTRICT', onupdate='CASCADE'), primary_key=primary, nullable=nullable)

    sender = relationship(Sender, backref=backref('senderfields'))
    field = relationship(Fields, backref=backref('senderfields'))