CODEWITHSUNDEEP
pythoninheritancesqlalchemy
hitripekac
hitripekac

Reputation: 83

Inheritance issue in SQLAlchemy

I need some help with understanding how inheritance works in SQLAlchemy. I've created a base class for a user with some basic functionality. And then some specific users (admin, cooluser, uncooluser). Each one has unique functionality so I decided to use inheritance in SQLAlchemy. The problem is that I need to be able to upgrade a user to cooluser or uncooluser, and downgrade a cooluser to user, at any time,.

class User(Base):
    __tablename__ = 'tbl_users'
    __table_args__ = {'mysql_engine': 'InnoDB'}

    user_id = Column(Integer, primary_key = True, unique = True, nullable = False)
    user_name = Column(String(100), nullable = False, unique = True)
    password = Column(String(100), nullable = False)
    user_type = Column('user_type', String(50))
    first_name = Column(String(50), nullable = False)
    last_name = Column(String(50), nullable = False)
    address = Column(String(50), nullable = False)
    city = Column(String(20), nullable = False)
    postal_code = Column(String(10), nullable = False)
    country_id = Column(Integer, ForeignKey(Contries.country_id))

    country = relationship('Country', backref = 'users')

    query = Session.query_property()

    __mapper_args__ = {'polymorphic_on': user_type, 'polymorphic_identity': 'User'}
class CoolUser(User):
    __tablename__ = 'tbl_cool_user'
    __table_args__ = {'mysql_engine': 'InnoDB'}
    __mapper_args__ = {'polymorphic_identity': 'CoolUser'}

    cool_user_id = Column(Integer, ForeignKey(User.user_id, ondelete = 'CASCADE'), primary_key = True)
    cool_user_balance = Column(Numeric(15, 3))

Can I create a CoolUser without creating a new row in 'tbl_users', but use an existing one? Can change some setting so that when a CoolUser gets removed it just removes the entry in 'tbl_cool_user', not 'tbl_user'?

Am I missing the point of inheritance in SQLAlchemy?

Upvotes: 1

Views: 2262

Answers (3)

van
van

Reputation: 76952

Am I missing the point of inheritance in SQLAlchemy?

I think that you misusing the inheritance concept in general, and not even in SA implementation specifics.
In a classical Animal (Cat, Dog (Terier)) type of hierarchy would you ever imagine that a Cat would be up/down-graded to a Dog, or even a specific type of a Dog? I don't think so. Also, I can well imagine that some CoolUser could also be an Administrator at the same time. How will you resolve this type of relationship?

Therefore, for your requirements, inheritance is just not the concept to employ to solve this state-changing model. I would suggest to google for User-Role type of relationships and implementations. You would still have to resolve the issue of storing Role-specific data thought.

As @aquavitae mentioned: in SA, you can hack-around and change the user_type. But be aware that in this case the following will happen:

  • when you load the object from the database, its class will reflect the new type (GOOD)
  • when you downgrade the object (from CoolUser to User), the row which corresponds to the CoolUser will not be deleted (i think it is BAD, but it might be OK)
  • when you upgrade the object (from User to CoolUser), no new row for the CoolUser table will be created, and all your values will be NULL. As such, setting/adding any property that is stored in the non-created row will throw a nice error. And when queries for the specific subclass, you will not recieve the object back as the INNER JOIN is used to retrieve the object. (BAD)
  • in summary, do not change a Cat to a Dog

Upvotes: 6

aquavitae
aquavitae

Reputation: 19114

I don't think you should be using 'polymorphic_on' and 'polymorphic_identity' in the same table. What you need to do is create a base table User containing all users (i.e. with mapper_args 'polymorphic_on'), then CoolUser subclassed from that (with 'polymorphic_identity'):

class User(Base):
    ...
    __mapper_args__ = {'polymorphic_on': user_type}

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

Then you can change user_type as you like.

Upvotes: 1

Neel
Neel

Reputation: 21243

You have to use Concrete Table Inheritance. All all common attribute in base table and create 2 different CoolUser and UnCoolUser.

Upvotes: 0

Related Questions