35

So I have a bunch of tables using SQLAlchemy that are modelled as objects which inherit from the result to a call to declarative_base(). Ie:

Base = declarative_base()
class Table1(Base):
    # __tablename__ & such here

class Table2(Base):
     # __tablename__ & such here

Etc. I then wanted to have some common functionality available to each of my DB table classes, the easiest way to do this according to the docs is to just do multiple inheritance:

Base = declarative_base()

class CommonRoutines(object):
    @classmethod
    def somecommonaction(cls):
        # body here

class Table1(CommonRoutines, Base):
    # __tablename__ & such here

class Table2(CommonRoutines, Base):
     # __tablename__ & such here

The thing I don't like about this is A) multiple inheritance in general is a bit icky (gets tricky resolving things like super() calls, etc), B) if I add a new table I have to remember to inherit from both Base and CommonRoutines, and C) really that "CommonRoutines" class "is-a" type of table in a sense. Really what CommonBase is is an abstract base class which defines a set of fields & routines which are common to all tables. Put another way: "its-a" abstract table.

So, what I'd like is this:

Base = declarative_base()

class AbstractTable(Base):
    __metaclass__ = ABCMeta  # make into abstract base class

    # define common attributes for all tables here, like maybe:
    id = Column(Integer, primary_key=True)

    @classmethod
    def somecommonaction(cls):
        # body here

class Table1(AbstractTable):
    # __tablename__ & Table1 specific fields here

class Table2(AbstractTable):
     # __tablename__ & Table2 specific fields here

But this of course doesn't work, as I then have to A) define a __tablename__ for AbstractTable, B) the ABC aspect of things causes all sorts of headaches, and C) have to indicate some sort of DB relationship between AbstractTable and each individual table.

So my question: is it possible to achieve this in a reasonable way? Ideally I'd like to enforce:

  • No multiple inheritance
  • CommonBase/AbstractTable be abstract (ie cannot be instantiated)
Improve this question

4 Answers 4

Reset to default
59

SQLAlchemy version 0.7.3 introduced the __abstract__ directive which is used for abstract classes that should not be mapped to a database table, even though they are subclasses of sqlalchemy.ext.declarative.api.Base. So now you create a base class like this:

Base = declarative_base()

class CommonRoutines(Base):
    __abstract__ = True

    id = Column(Integer, primary_key=True)

    def __init__(self):
        # ...

Notice how CommonRoutines doesn't have a __tablename__ attribute. Then create subclasses like this:

class Foo(CommonRoutines):
    __tablename__ = 'foo'

    name = Column(...)

    def __init__(self, name):
        super().__init__()
        self.name = name
        # ...

This will map to the table foo and inherit the id attribute from CommonRoutines.

Source and more information: http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/declarative.html#abstract

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

3 Comments

It doesn't work in my case. SqlAlchemy reports the lack of a __tablename__ as an error when creating the abstract class
Link is broken :(
I recommend scrolling down to the confirmed answer but here is a working link to the declarative abstract link shared by Aston. docs.sqlalchemy.org/en/13/orm/extensions/declarative/…
17

It is pretty straigh-forward, you just make declarative_base() to return a Base class which inherits from your CommonBase using cls= parameter. Also shown in Augmenting The Base docs. Your code might then look similar to below:

class CommonBase(object):
    @classmethod
    def somecommonaction(cls):
        # body here

Base = declarative_base(cls=CommonBase)

class Table1(Base):
    # __tablename__ & Table1 specific fields here

class Table2(Base):
     # __tablename__ & Table2 specific fields here
Improve this answer

2 Comments

Correct me if I'm wrong, but those "AbstractTable" inheritances should read "Base", correct? (ie class Table1(Base):)
Yup, this was exactly what I was looking for. Thanks! The only drawback to the approach is that now the CommonBase class's __init__ does not get called (declarative_base produces a class which doesn't call super in its __init__ so Python's cooperative multiple inheritance mechanisms don't work). Hmm....
4

You can use AbstractConcreteBase to make an absract base model:

from sqlalchemy.ext.declarative import AbstractConcreteBase


class AbstractTable(AbstractConcreteBase, Base):
    id = db.Column(db.Integer, primary_key=True)

    @classmethod
    def somecommonaction(cls):
        # body here
Improve this answer

Comments

3

If you want to have several models with common columns, then you can use __abstract__ and @declared_attr to inherit shared table attributes. Example:

Base = declarative_base()

class CommonRoutines(Base):
    __abstract__ = True

    id = Column(Integer, primary_key=True)
    modified_at = Column(DateTime)
    
    @declared_attr
    def modified_by(self):
        # `user.id` is another table called `user` with an `id` field
        return Column(Integer, ForeignKey('user.id', name='fk_modified_by_user_id')) 


    def __init__(self):
        self.modified_by = None
        super().__init__()


class Foo(CommonRoutines):
    __tablename__ = 'foo'

    name = Column(...)

With this solution you will have a Foo table with the fields of Foo class (name) and the ones in CommonRoutines (id, modified_at and modified_by)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.