Postgres inheritance with SQLAlchemy

Question

I have one question about make PostgreSQL table inheritance using SQLAlchemy.

I have this two tables:

CREATE TABLE his
(
  idg integer,
  idfk integer,
  idh integer NOT NULL defautl nextval('his_seq'),
  "type" character varying,
  CONSTRAINT __his_pkey PRIMARY KEY (idh)
);
CREATE TABLE data
(
  "text" character varying,
)
INHERITS (his);

Before execute any ddl command, I have made this python code:

from sqlalchemy  import *
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event

Base = declarative_base()

class His(Base):
    __tablename__ = 'his'

    idg = Column(Integer())
    idfk = Column(Integer())
    idh = Column(Integer(), Sequence('his_seq',  start=1,  increment=1),  primary_key=True)
    type= Column(String())

    __mapper_args__ = {'polymorphic_on': type}
    __table_args__ = {'implicit_returning':False}

    def __init__(self,  idg,  idfk,  type):
        self.idg = idg
        self.idfk = idfk
        self.type = type

class Data(His):
    __tablename__ = None
#    __mapper_args__ = {'polymorphic_identity': 'data',  'concrete':True}
    __mapper_args__ = {'polymorphic_identity': 'data'}
    text = Column(String())

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

@event.listens_for(His.__table__,  'after_create')
def create_child_tables(target, connection,  **kw):   
    connection.execute("""
        CREATE TABLE data(
        ) INHERITS (his)
    """)

    connection.execute("""
        CREATE OR REPLACE FUNCTION his_insert_trigger()
        RETURNS TRIGGER AS $$
        BEGIN
            IF (NEW.type='data') THEN
                INSERT INTO data VALUES (NEW.*);
            ELSE
                RAISE EXCEPTION 'Table type is unknown for historical porpurses.';
            END IF;
        RETURN NULL;
        END;
        $$
        LANGUAGE plpgsql;    
    """)

    connection.execute("""
        CREATE TRIGGER his_insert
        BEFORE INSERT ON his
        FOR EACH ROW EXECUTE PROCEDURE his_insert_trigger();
    """)

@event.listens_for(His.__table__, "before_drop")
def create_child_tables(target, connection, **kw):
    connection.execute("drop table data")
    connection.execute("drop table his")
    connection.execute("drop sequence his_seq")

e = create_engine('postgresql://localhost:5433/des', echo=True)
#Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)

s.add_all([
    Data('hola'), 
    Data('pedorrete'), 
    Data('pedorrete2')
])

s.commit()
s.close()

Well, this example (like explained in http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PostgreSQLInheritance) create two tables, but sqlalchemy always use his table to insert data records, and these are inserted on data and his. text field (on data) is really created on his table.

So, is there any way to specify to SQLAchemy that data table must inherits (Postgres inherits) from his, and must add text field to it, and must be used data and not his when I insert any record on data?

Regards.

zefciu · Accepted Answer · 2011-10-11 10:20:22Z

2

SQLAlchemy tries to be as portable as possible so it doesn't support many Postgres-specific features. Here is an answer given from SA guy to someone with similar problem: http://www.mail-archive.com/[email protected]/msg17443.html

answered Oct 11, 2011 at 10:20

zefciu

2,0762 gold badges17 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicholas H · Accepted Answer · 2023-04-28 16:18:09Z

Sqlalchemy's table inheritance is expecting your child table to only have the extra data, it does its own joins to fetch information from the combined tables. If you're going to use psql's built-in table inheritance you actually don't really want to use sqlalchemy's inheritance features at all, you just want to treat them like unrelated tables, since postgres is doing the work for you.

To avoid creating duplicate code you can put your column definitions into their own class that does not extend the Base class and have all your table classes extend it.


class HisCols:
    idg = Column(Integer())
    idfk = Column(Integer())
    idh = Column(Integer(), Sequence('his_seq',  start=1,  increment=1), primary_key=True)
    type = Column(String())


class His(Base, HisCols):
    __tablename__ = 'his'
    __table_args__ = {'implicit_returning':False}


class Data(Base, HisCols):
    __tablename__ = 'data'
    text = Column(String())

    # And if you really need it (you probably don't) you can add a relationship to the parent table
    idh = Column(Integer(), ForeignKey('his.idh'), Sequence('his_seq',  start=1,  increment=1),  primary_key=True)
    parent = relationship(His)

Collectives™ on Stack Overflow

Postgres inheritance with SQLAlchemy

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related