1

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?

Improve this question

3 Answers 3

Reset to default
6

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+1 I should have put all those caveats in my answer too! In this use case, its probably better to simply query the Users with a filter.
Thank you. I guess I will have to rethink the entire thing. The reason I thought inheritance would be subtable is because every user (CoolUser, UnCoolUser, Admin) have to log-in, log-out and such. But I guess I was wrong...
I would suggest you create a separate table for login oriented tasks (call it User), and create an hierarchy (like Person (->CoolPerson, ->UncoolPerson, ->Distributor) ) for the data model you have, and then have a foreign key on Person.user_id (you could even use the same ID value). In this case, the Administrator would probably still be a role (or just a flag on User table) and will be related only to User table without knowing anything about Person and its descendants.
1

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.

Improve this answer

2 Comments

Unless User is an abstract class, one needs polymorhic_identity also on the User table.
A more detailed example: stackoverflow.com/questions/1337095/sqlalchemy-inheritance
0

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

Improve this answer

3 Comments

Thanks for replying, but I'm still confused. How do I change the type of a User to CoolUser? If I create new CoolUser, will it duplicate the User in CoolUser table?
Please check the example and try polymorphic_on. If you get any error then write that error so we can solve it.
The database with a certain model already exists and cannot be changed, because there is already another software that connects to it. What I tried to do is make the python code so that it conforms to that model. I will have to rethink the whole thing... I am grateful for the help though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.