Tying Mapping class inheritance with SQLAlchemy.

alchemydemo.rst

requirements

• SQLAlchemy==0.9.1

python initialdb.py
python demo.py

demo.py

from models import DBSession, engine, Item, FreeItem, ChargedItem


if __name__ == '__main__':
    DBSession.configure(bind=engine)
    session = DBSession()

    print('=' * 50)
    query = session.query(Item)
    print(query)
    print('-' * 50)
    print(session.query(Item).all())
    print()
    print('=' * 50)
    query = session.query(Item).with_polymorphic([FreeItem, ChargedItem]).\
        filter(~ChargedItem.is_ended | FreeItem.is_ended)
    print(query)
    print('-' * 50)
    print(query.all())

initialdb.py

from datetime import datetime
from models import engine, DBSession, Base, Item, FreeItem, ChargedItem


def main():
    DBSession.configure(bind=engine)
    Base.metadata.create_all(engine)

    session = DBSession()
    for i in range(5):
        free = FreeItem(name='free{}'.format(i),
                        end_accepting_time=datetime.now())
        charged = ChargedItem(name='charged{}'.format(i),
                              stock=i*10)

        session.add(free)
        session.add(charged)
    session.commit()


if __name__ == '__main__':
    main()

models.py

from datetime import datetime

import sqlalchemy as sa
from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property


engine = sa.create_engine('sqlite:///db.sqlite3')

DBSession = scoped_session(sessionmaker())
Base = declarative_base()


class Item(Base):
    __tablename__ = 'item'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(50))
    type = sa.Column(sa.String(50))

    __mapper_args__ = {
        'polymorphic_identity': 'item',
        'polymorphic_on': type,
    }

    def __repr__(self):
        return "<Item (name='%s')>" % self.name

    def is_ended(self):
        raise NoImplementedError


class FreeItem(Item):
    __tablename__ = 'free_item'
    id = sa.Column(sa.Integer, sa.ForeignKey('item.id'), primary_key=True)
    end_accepting_time = sa.Column(sa.DateTime)

    __mapper_args__ = {
        'polymorphic_identity':'free_item',
    }

    def __repr__(self):
        return "<FreeItem (name='%s')>" % self.name

    @hybrid_property
    def is_ended(self):
        return self.end_accepting_time < datetime.now()


class ChargedItem(Item):
    __tablename__ = 'charged_item'
    id = sa.Column(sa.Integer, sa.ForeignKey('item.id'), primary_key=True)
    stock = sa.Column(sa.Integer)

    __mapper_args__ = {
        'polymorphic_identity':'charged_item',
    }

    def __repr__(self):
        return "<ChargedItem (name='%s')>" % self.name

    @hybrid_property
    def is_ended(self):
        return self.stock < 15