How do I define table columns in a base class and table names in a child class in SQLAlchemy?

Question

I need to have 2 separate tables with identical structures which seems like an inheritance scenario, but I don't want the base class to be associated with a table. The Base only exists to define the columns for the table(s) in the child class(es). The AbstractConcreteBase docs led me to implement it this way:

in status.py:

class Base(AbstractConcreteBase):
    id = Column('id', Integer, primary_key=True)
    date = Column('date', DateTime(), nullable=False)

class Status1(Base):
    __tablename__ = 'status1'
    __mapper_args__ = {
        'polymorphic_identity': __tablename__,
        'concrete': True
    }

class Status2(Base):
    __tablename__ = 'status2'
    __mapper_args__ = {
        'polymorphic_identity': __tablename__,
        'concrete': True
    }

in part.py:

class Part(declarative_base()):
    __tablename__ = 'parts'

    id = Column('id', Integer, primary_key=True)
    status1 = relationship('Status1', uselist=True, backref=backref('status1', order_by='Status1.date'))
    status2 = relationship('Status2', uselist=True, backref=backref('status2', order_by='Status2.date'))

When attempting this, I get the following error:

InvalidRequestError: When initializing mapper Mapper|Part|parts, expression 'Status1' failed to locate a name ("name 'Status1' is not defined"). If this is a class name, consider adding this relationship() to the <class 'package.Part'> class after both dependent classes have been defined.

Suggestions?

steven · Accepted Answer · 2014-07-02 18:27:27Z

What you really want is a Mixin class. Your base class should not inherit from the SQLAlchemy declarative_base if dont want to associate the base class with a table. A mixin will define the columns/table structure like you want. Your child classes then inherit from both the mixin and the declarative_base.

The mixin would be defined as follows:

class StatusMixin(object):
    id = Column('id', Integer, primary_key=True)
    date = Column('date', DateTime(), nullable=False)

The child classes become much simpler:

Base = declarative_base()

class Status1(StatusMixin, Base):
    __tablename__ = 'status1'

class Status2(StatusMixin, Base):
    __tablename__ = 'status2'

Finally, your relationships defined for the Part class are not correctly defined. To use backrefs you need to have a foreign key defined in status1 and status2 that link them together:

class Status1(StatusMixin, Base):
    __tablename__ = 'status1'
    part_id = Column('part_id', Integer, ForeignKey('parts.id'))

The table defining the relationship that has a backref needs to have its own table name as the backref instead of the names of the child1 and child2 tables:

class Part(declarative_base()):
    __tablename__ = 'parts'
    id = Column('id', Integer, primary_key=True)
    status1 = relationship('Status1', uselist=True, backref=__tablename__)
    status2 = relationship('Status2', uselist=True, backref=__tablename__)

Collectives™ on Stack Overflow

How do I define table columns in a base class and table names in a child class in SQLAlchemy?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related