SQLAlchemy - Multiple Classes, Identical Tables

Question

Been searching for this answer for a bit now, but I can't seem to find it, as everything refers back to joined table inheritance, which I understand, but do not want to use. I am looking to create multiple classes in SQLAlchemy that are identical in table construction, only differing in the class name and database table name. I am intentionally separating the tables and not using a discriminator because I expect these tables to grow to very large sizes. There is also a potential that the table schemas may diverge slowly over time, with some fields added to one but not another.

I know the following code doesn't work, as SQLAlchemy tries to find a foreign key for joined table inheritance rather than making them independent tables, but it's basically what I'm going for. I've been over the docs, but can't figure out the way to properly implement this. Is there a way (or multiple ways) to do this?

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class HDD(Base):
    """Class representing a hard drive."""

    __tablename__ = 'HDDs'

    _id = Column(
        Integer,
        doc="Auto-incrementing primary key",
        name="id",
        primary_key=True)
    manufacturer = Column(
        String(40),
        doc="Hard drive manufacturer.")
    ...

class SDD(HDD):
    __tablename__ = 'SSDs'
    ...

jkmacc · Accepted Answer · 2015-08-05 19:22:43Z

5

Use __abstract__ = True with no __tablename__ on the base class, and inherit from that.

class HDD(Base):
    __abstract__ = True
    _id = ...

class SDD(HDD):
    __tablename__ = 'SSDs'

class SSD2(HDD):
    __tablename = 'SSDs2'

answered Aug 5, 2015 at 19:22

jkmacc

6,5973 gold badges33 silver badges30 bronze badges

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

2 Comments

ZetaSyanthis Over a year ago

I haven't tried this method yet (see below for my solution), but I may give it a shot in the future!

ZetaSyanthis Over a year ago

Got around trying this too, and I like it a lot! Very clean!

inklesspen · Accepted Answer · 2015-08-05 17:19:50Z

0

This looks like Concrete Table Inheritance, as discussed in the Mapping Class Inheritance Hierarchies chapter of SQLAlchemy docs.

answered Aug 5, 2015 at 17:19

inklesspen

1,1866 silver badges11 bronze badges

1 Comment

ZetaSyanthis Over a year ago

Thanks for your help, including via IRC. I mentioned you above in thanks. :)

ZetaSyanthis · Accepted Answer · 2015-08-10 17:00:48Z

0

This is the way I ended up getting this working, with help from inklesspen from #sqlalchemy on freenode. (Thank you!)

class ComponentMixin(object):
    """Pythonic Mixin that defines columns common to most component types."""

    _id = Column(
        Integer,
        doc="Auto-incrementing primary key",
        name="id",
        primary_key=True)
    ....

    @declared_attr
    def _server_id(self):  # pylint: disable=R0201
        """
        Foreign key to owning server.

        MUST BE A DECLARED_ATTR since it's a foreign key!
        """

Subclass below inherits:

class HDD(ComponentMixin, ConcreteBase, Base):
    """Class representing a hard drive."""

    __tablename__ = 'HDDs'

    __mapper_args__ = {
        'polymorphic_identity': 'hdd',
        'concrete': True}

answered Aug 10, 2015 at 17:00

ZetaSyanthis

816 bronze badges

2 Comments

BrendanSimon Over a year ago

and was SDD still a subclass of HDD, or was it a subclass of the mixin... i.e. class SDD(HDD): or class SDD(ComponentMixin, ConcreteBase, Base): ??

BrendanSimon Over a year ago

A more complete implementation would be helpful. E.g. how is _server_id(self): implemented? Does it return a Column object? e.g. Column( Integer, ForeignKey( 'server.id' ) ) or something similar?

Collectives™ on Stack Overflow

SQLAlchemy - Multiple Classes, Identical Tables

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related