CODEWITHSUNDEEP
pythondatabaseclassobjectsqlalchemy
ZetaSyanthis
ZetaSyanthis

Reputation: 81

SQLAlchemy - Multiple Classes, Identical Tables

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'
    ...

Upvotes: 2

Views: 1499

Answers (3)

ZetaSyanthis
ZetaSyanthis

Reputation: 81

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}

Upvotes: 0

jkmacc
jkmacc

Reputation: 6427

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'

Upvotes: 5

inklesspen
inklesspen

Reputation: 1186

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

Upvotes: 0

Related Questions