gistfile1.py

#!/usr/bin/env python
# playing with column_property to keep counters up to date

__author__ = "Yoan Blanc"


from sqlalchemy import (create_engine, MetaData,
                       Table, Column, Table, Integer, ForeignKey,
                       select, func)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (mapper, relation, column_property,
                           sessionmaker, scoped_session)

from sqlalchemy.orm.exc import ObjectDeletedError


meta = MetaData()
session = None


Base = declarative_base(metadata=meta)


class Post(Base):
    __tablename__ = "posts"
    
    id = Column("id", Integer, primary_key=True)
    blog_id = Column("blog_id", Integer, ForeignKey("blogs.id"))

    def __init__(self, blog):
        self.blog = blog


class Blog(Base):
    __tablename__ = "blogs"

    id = Column("id", Integer, primary_key=True)

    posts = relation(Post,
                     backref='blog',
                     order_by=Post.id.desc(),
                     cascade='all,delete-orphan')
    
    posts_counter = column_property(select(
            [func.count(Post.id)],
            Post.blog_id == id
        ).label('posts_counter')) 


def test_main():
    global session
    
    engine = create_engine("sqlite://", echo=False)
    connection = engine.connect()
    
    session = scoped_session(sessionmaker(engine, autoflush=True, autocommit=False))
    
    meta.create_all(engine)

    b = Blog()
    session.save(b)
    session.commit()

    p = Post(b)
    q = Post(b)

    session.save(p)
    session.save(q)
    session.commit()

    assert b.posts_counter == 2, b.posts_counter
    assert p.blog.id == b.id

    session.delete(p)
    session.commit()
    
    assert b.posts_counter == 1, b.posts_counter
    
    session.delete(b)
    session.commit()
    
    try:
        q._sa_instance_state()
        assert False, "Should have been deleted"
    except ObjectDeletedError, e:
        pass


if __name__ == "__main__":
    import nose
    nose.main(argv=['', __file__])