1. Michael Bayer
  2. sqlalchemy

Wiki

Clone wiki

sqlalchemy / UsageRecipes / PostgreSQLInheritance

PostgreSQLInheritance

Here we demonstrate mapping to a partitioned PostgreSQL structure, using the schema techniques described in:

http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION

There's nothing special on the SQLAlchemy side here. Just map to the base table, using standard single table inheritance for subclasses. The rest of it is straight from the PostgreSQL documentation.

The only caveat for the moment is that the RETURNING statement against "parent.id" doesn't seem to obey the INHERITS mechanics otherwise in place. So we turn off "implicit returning" for the table so that parent_id_seq is pre-executed beforehand.

from sqlalchemy import *
from sqlalchemy.orm import Session
from sqlalchemy import event

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    discriminator = Column(String(50))
    data = Column(String(50))
    __mapper_args__ = {'polymorphic_on':discriminator}

    __table_args__ = {'implicit_returning':False}

class Child1(Parent):
    __tablename__ = None
    __mapper_args__ = {'polymorphic_identity':'ops'}

class Child2(Parent):
    __tablename__ = None
    __mapper_args__ = {'polymorphic_identity':'eng'}

@event.listens_for(Parent.__table__, "after_create")
def create_child_tables(target, connection, **kw):
    connection.execute("""
        CREATE TABLE child1(
            CHECK (discriminator='ops')
        ) INHERITS (parent)

    """)

    connection.execute("""
        CREATE TABLE child2(
            CHECK (discriminator='eng')
        ) INHERITS (parent)
    """)

    connection.execute("""
        CREATE OR REPLACE FUNCTION parent_insert_trigger()
        RETURNS TRIGGER AS $$
        BEGIN
            IF (NEW.discriminator='ops') THEN
            INSERT INTO child1 VALUES (NEW.*);
        ELSIF (NEW.discriminator='eng') THEN
            INSERT INTO child2 VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Discriminator out of range.  Expected "ops", "eng"';
        END IF;
        RETURN NULL;
        END;
        $$
        LANGUAGE plpgsql;
    """)

    connection.execute("""
    CREATE TRIGGER insert_parent
    BEFORE INSERT ON parent
    FOR EACH ROW EXECUTE PROCEDURE parent_insert_trigger();
    """)

@event.listens_for(Parent.__table__, "before_drop")
def create_child_tables(target, connection, **kw):
    connection.execute("drop table child1")
    connection.execute("drop table child2")

e = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)


s = Session(e)

s.add_all([
    Child1(data='c1'),
    Child2(data='c2'),
    Child1(data='c3'),
    Child2(data='c4'),
    Child1(data='c5'),
])
s.commit()
s.close()

assert s.scalar("select count(1) from child1") == 3
assert s.scalar("select count(1) from child2") == 2
print s.query(Parent).order_by(Parent.data).all()

Updated