SQLAlchemy中的继承问题 [英] Inheritance issue in SQLAlchemy

问题描述

在了解继承如何在SQLAlchemy中工作时，我需要一些帮助.我为具有一些基本功能的用户创建了一个基类.然后是一些特定的用户(管理员，cooluser，uncooluser).每个都有独特的功能，因此我决定在SQLAlchemy中使用继承.问题在于，我需要能够随时将用户升级为cooluser或uncooluser，并可以将cooluser降级为该用户.

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))

我可以创建CoolUser而不在'tbl_users'中创建新行，而是使用现有的吗?可以更改某些设置，以便在删除CoolUser时仅删除"tbl_cool_user"中的条目，而不是"tbl_user"中的条目吗?

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

我是否错过了SQLAlchemy中的继承点?

Am I missing the point of inheritance in SQLAlchemy?

推荐答案

我是否错过了SQLAlchemy中的继承点?

Am I missing the point of inheritance in SQLAlchemy?

我认为您普遍误用了inheritance概念，甚至没有滥用SA实现细节.
在经典的Animal (Cat, Dog (Terier))类型层次结构中，您是否曾经想到过猫会被上/下级成狗，甚至是特定类型的狗?我不这么认为.另外，我可以很好地想象某些CoolUser也可能同时是Administrator.您将如何解决这种类型的关系?

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?

因此，根据您的要求，继承不是解决此状态改变模型所要使用的概念.我建议Google为User-Role类型的关系和实现.您仍然必须解决存储特定于角色的数据思想的问题.

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.

如 @aquavitae 所述:在SA中，您可以修改并更改user_type.但是请注意，在这种情况下，将会发生以下情况:

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:

• 从数据库中加载对象时，其类将反映新的类型(GOOD)
• 当您将对象降级(从CoolUser降为User)时，对应于CoolUser的行将不删除(我认为它是BAD，但可能没问题)
• 在升级对象(从User到CoolUser)时，将不会为CoolUser表创建新行，并且所有值均为NULL.这样，设置/添加未创建的行中存储的任何属性都将引发错误.而且，当查询特定的子类时，由于INNER JOIN用于检索对象，您将不接收该对象. (不好)
• 总而言之，不要将猫变成狗

• 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

这篇关于SQLAlchemy中的继承问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！