Flask-SQLAlchemy Single Table Inheritance

Question

SQLAlchemy support single table inheritance.

I have structure like:

class User(db.Model):
    __tablename__ = 'tbl_user'
    type = db.Column(db.String(32)) 
    ...
    __mapper_args__ = {
        'polymorphic_identity': 'user',
        'polymorphic_on': type,
        'with_polymorphic': '*'
    }

class Tourist(User):
    __mapper_args__ = {
        'polymorphic_identity': 'tourist'
    }
    ...

class Guide(User):
    __mapper_args__ = {
        'polymorphic_identity': 'guide'
    }
    ...

When I try to run code, i get error like:

sqlalchemy.exc.InvalidRequestError: Table 'tbl_user' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

When I add 'extend_existing' as table property:

__table_args__ = {'extend_existing': True}

Then I get next error when I try anything with model mentioned:

sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'tbl_user' and 'tbl_user'.

This should be something straight forward, and should be solvable by single attribute, especially that it is working fine with SQLAlchemy. Any ideas how to solve problem?

Answer 1

I've finally found a way how mentioned should work. I need to remove with_polymorfic option from initial mapper args and to add __tablename__ = None for children classes, so exact code look like:

class User(db.Model):
    __tablename__ = 'tbl_user'
    type = db.Column(db.String(32)) 
    ...
    __mapper_args__ = {
        'polymorphic_identity': 'user',
        'polymorphic_on': type,
    }  # remove with_polymorphic

class Tourist(User):
    __tablename__ = None  # Add table name to be None
    __mapper_args__ = {
        'polymorphic_identity': 'tourist'
    }
    ...

class Guide(User):
    __tablename__ = None  # Add table name to be None
    __mapper_args__ = {
        'polymorphic_identity': 'guide'
    }
    ...