sa_block.py

from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey
from sqlalchemy.orm import sessionmaker, scoped_session, relationship
from sqlalchemy.ext.declarative import declarative_base


Session = scoped_session(sessionmaker())


class Model(object):

    def save(self, session=Session):
        session.add(self)
        session.commit()
        return self


Base = declarative_base(cls=Model)


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(255), unique=True, nullable=False)


class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)
    body = Column(Text, nullable=False)
    user_id = Column(Integer, ForeignKey(User.id, ondelete='CASCADE'),
                     nullable=False)
    user = relationship(User, backref='posts')


def init_model(engine):
    """Call me before using any of the tables or classes in the model"""
    Session.configure(bind=engine)
    Base.metadata.bind = engine


def main():
    engine = create_engine('postgresql://test@localhost/test')
    init_model(engine)

    Base.metadata.create_all()
    user = User(username='dude').save()
    post = Post(user=user, title='foo', body='bar').save()
    print(user.posts)
    print(post.user)
    Base.metadata.drop_all()


if __name__ == '__main__':
    main()