I will use PJT from here on. It means "your Pylons project name."

When Pylons rolled out version 1.0 of the framework, they changed a number of things. I love these changes because in my opinion they cleaned up syntax and shortened obvious things.

One of the major changes I want to talk about is how you define tables in your SqlAlchemy (SA from now on) models. Previously the documentation showed us how to define them using the Table object that comes with SA. This method required a large amount of extra code and looked very cluttered. This is one of the reasons I was turned off by SA about a year ago.

import sqlalchemy as sa
from sqlalchemy import orm, types, desc

from PJT.model import meta

t_mmpps_description = sa.Table(
    'mmpps_description',
    meta.metadata,
    sa.Column(
        'id',
        sa.types.Integer,
        primary_key=True
    ),
    sa.Column(
        'parentId',
        sa.types.Integer,
        sa.ForeignKey('mmpps_description.id')
    ),
    sa.Column(
        'typeId',
        sa.types.Integer,
        sa.ForeignKey('mmpps_type.id')
    ),
    sa.Column(
        'clientId',
        sa.types.Integer,
        sa.ForeignKey('client.id'),
        nullable=False
    ),
    sa.Column(
        'description',
        sa.types.Text,
        nullable=False
    ),
    sa.Column(
        'imageId',
        sa.types.Integer,
        sa.ForeignKey('mmpps_image.id')
    ),
    sa.Column(
        'createdDate',
        sa.types.DateTime,
        default=sa.text('NOW()'),
        nullable=False
    )
)

orm.mapper(MmppsDescription, t_mmpps_description,
    properties={
        'type': orm.relation(MmppsType),
        'client': orm.relation(Client),
        'image': orm.relation(MmppsImage),
        'parent': orm.relation(MmppsDescription)
    }
)

class MmppsDescription(object):
    pass

The new documentation describes how to create a table definition and the relations in one bang. This method is called the Declarative syntax. Basically you build a class that inherits from sqlalchemy.ext.declarative.declarative_base.

If you are using Pylons 1.x, Base is already defined for you in PJT.model.meta.

import sqlalchemy as sa
from sqlalchemy import Column
from sqlalchemy.types import *
from sqlalchemy.orm import relation, backref
from PJT.model.meta import Base

class MmppsDescription(Base):
    __tablename__ = 'mmpps_description'

    id = Column(Integer, primary_key=True)
    parentId = Column(Integer, ForeignKey('mmpps_description.id'))
    typeId = Column(Integer, ForeignKey('mmpps_type.id'),
            nullable=False)

    ...

    type = relation('MmppsType', backref=backref('descriptions'))

    def __init__(self, typeId...):
        self.typeId = typeId
        if parentId is not None:
            self.parentId = parentId
        ...

I didn't type out the whole class because the documentation shows a more clear picture of what you need to do that I can. As you can see the code to define a class and some relations is a lot smaller and cleaner looking.

The declarative method still requires you to define a many to many relation table using the SA Table object. But those tables are typically only 3 columns big anyway.