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


engine = create_engine('postgresql://test:test@localhost/test', echo=True)
Session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base(bind=engine)


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))
    user = relationship(User, backref='posts')


def main():
    Base.metadata.create_all()

    user = User(username='dude')
    Session.add(user)
    Session.commit()

    post = Post(user=user, title='foo', body='bar')
    Session.add(post)
    Session.commit()

    user.posts
    post.user

    Base.metadata.drop_all()


if __name__ == '__main__':
    main()