CODEWITHSUNDEEP
pythonoopsqlalchemy
Ominus
Ominus

Reputation: 5721

sqlalchemy access parent class attribute

Looking at the bottom of the post you can see i have three classes. The code here is pseudo code written on the fly and untested however it adequately shows my problem. If we need the actual classes I can update this question tomorrow when at work. So ignore syntax issues and code that only represents a thought rather than the actual "code" that would do what i describe there.

Question 1 If you look at the Item search class method you can see that when the user does a search i call search on the base class then based on that result return the correct class/object. This works but seems kludgy. Is there a better way to do this?

Question 2 If you look at the KitItem class you can see that I am overriding the list price. If the flag calc_list is set to true then I sum the list price of the components and return that as the list price for the kit. If its not marked as true I want to return the "base" list price. However as far as I know there is no way to access a parent attribute since in a normal setup it would be meaningless but with sqlalchemy and shared table inheritance it could be useful.

TIA

class Item(DeclarativeBase):
    __tablename__ = 'items'
    item_id = Column(Integer,primary_key=True,autoincrement=True)
    sku = Column(Unicode(50),nullable=False,unique=True)
    list_price = Column(Float)
    cost_price = Column(Float)
    item_type = Column(Unicode(1))
    __mapper_args__ = {'polymorphic_on': item_type}
    __
    def __init__(self,sku,list_price,cost_price):
        self.sku = sku
        self.list_price = list_price
        self.cost_price = cost_price

    @classmethod
    def search(cls):
        """
        " search based on sku, description, long description
        " return item as proper class
        """
        item = DBSession.query(cls).filter(...) #do search stuff here
        if item.item_type == 'K': #Better way to do this???
            return DBSession.query(KitItem).get(item.item_id)

class KitItem(Item):
    __mapper_args__ = {'polymorphic_identity': 'K'}
    calc_list = Column(Boolean,nullable=False,default=False)

    @property
    def list_price(self):
        if self.calc_list:
            list_price = 0.0
            for comp in self.components:
                list_price += comp.component.list_price * comp.qty
            return list_price
        else:
            #need help here
            item = DBSession.query(Item).get(self.item_id)
            return item.list_price

class KitComponent(DeclarativeBase):
    __tablename__ = "kit_components"
    kit_id = Column(Integer,ForeignKey('items.item_id'),primarykey=True)
    component_id = Column(Integer,ForeignKey('items.item_id'),primarykey=True)
    qty = Column(Integer,nullable=False, default=1)
    kit = relation(KitItem,backref=backref("components"))
    component = relation(Item)

Upvotes: 3

Views: 2522

Answers (1)

van
van

Reputation: 76992

Answer-1: in fact you do not need to do anything special here: given that you configured your inheritance hierarchy properly, your query will already return proper class for every row (Item or KitItem). This is the advantage of the ORM part. What you could do though is to configure the query to immediatelly load also the additional columns which do belong to children of Item (from your code this is only calc_list column), which you can do by specifying with_polymorphic('*'):

@classmethod
def search(cls):
    item = DBSession.query(cls).with_polymorphic('*').filter(...) #do search stuff here
    return item

Read more on this in Basic Control of Which Tables are Queried.
To see the difference, enabled SQL logging, and compare your tests scripts with and without with_polymorphic(...) - you will most probably require less SQL statements being executed.

Answer-2: I would not override one entry attributed with one which is purely computed. Instead I would just create another computed attribute (lets call it final_price), which would look like following for each of two classes:

class Item(Base):
    ...
    @property
    def total_price(self):
        return self.list_price

class KitItem(Item):
    ...
    @property
    def total_price(self):
        if self.calc_list:
            _price = 0.0
            for comp in self.components:
                _price += comp.component.list_price * comp.qty
            return _price
        else:
            # @note: again, you do not need to perform any query here at all, as *self* is that you need
            return self.list_price

Also in this case, you might think of configuring the relationship KitItem.components to be eagerly loaded, so that the calculation of the total_price will not trigger additional SQL. But you have to decide yourself if this is beneficial for your use cases (again, analyse the SQLs generated in your scenario).

Upvotes: 1

Related Questions