Using multiple levels of inheritance with sqlalchemy declarative base

Question

I have many tables with identical columns. The difference is the table names themselves. I want to set up a inheritance chain to minimize code duplication. The following single layer inheritance works the way I want it to:

from sqlalchemy import Column, Integer, Text
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker

engine = sqlalchemy.create_engine('sqlite:///monDom5.db')


class Base(object):
    """base for all table classes"""
    __abstract__ = True
    __table_args__ = {'autoload': True, 'autoload_with': engine}
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()


Base = declarative_base(cls=Base)


class TransMap_HgmIntronVector(Base):
    AlignmentId = Column(Text, primary_key=True)

But requires me to specify the AlignmentId column for every instance of the Hgm base. I would instead like to do this, but get a sqlalchemy.exc.InvalidRequestError when I try to actually use it:

from sqlalchemy import Column, Integer, Text
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker

engine = sqlalchemy.create_engine('sqlite:///monDom5.db')

class Base(object):
    """base for all table classes"""
    __abstract__ = True
    __table_args__ = {'autoload': True, 'autoload_with': engine}
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()


# model for all Hgm tables

class Hgm(Base):
    __abstract__ = True
    AlignmentId = Column(Text, primary_key=True)


Base = declarative_base(cls=Hgm)

class TransMap_HgmIntronVector(Hgm):
    pass



metadata = Base.metadata
Session = sessionmaker(bind=engine)
session = Session()

Leads to the error

>>> metadata = Base.metadata
>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>> session.query(TransMap_HgmIntronVector).all()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/cluster/home/ifiddes/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1260, in query
    return self._query_cls(entities, self, **kwargs)
  File "/cluster/home/ifiddes/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 110, in __init__
    self._set_entities(entities)
  File "/cluster/home/ifiddes/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 118, in _set_entities
    entity_wrapper(self, ent)
  File "/cluster/home/ifiddes/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3829, in __init__
    "expected - got '%r'" % (column, )
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<class '__main__.TransMap_HgmIntronVector'>'

Are you actually planning to use sqlite? Or another database (some support different types of inheritance)? — Brendan Abel
– Brendan Abel, Commented Sep 23, 2016 at 17:38

univerio · Accepted Answer · 2016-09-23 18:46:14Z

1

An example is in the docs. In particular, __abstract__ = True is not necessary. This works fine:

class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

class Hgm(Base):
    AlignmentId = Column(Text, primary_key=True)

Base = declarative_base(cls=Hgm)

class TransMap_HgmIntronVector(Base):
    pass

Note that it may be simpler to just use a mixin for the identical columns instead.

edited Sep 23, 2016 at 18:46

answered Sep 23, 2016 at 18:17

univerio

20.7k3 gold badges73 silver badges73 bronze badges

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

2 Comments

Ian Fiddes Over a year ago

None of the examples in the docs fix this problem, as far as I can tell. Removing __abstract__ does not resolve the problem. Using a mixin could possibly help.

univerio Over a year ago

@IanFiddes That's because you derived TransMap_HgmIntronVector from Hgm instead of Base.

Collectives™ on Stack Overflow

Using multiple levels of inheritance with sqlalchemy declarative base

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related