bidirectional attribute_mapped_collection not working (with example)

[Pau Tallada]

Hi!

I have a model with a many-to-many relation, similar to the Broker/Holding/Stocks example in the docs. I've set up relationships between both sides using the association object pattern. Finally, I've set up the collection class of those relationships to an attribute_mapped_collection. It is expected that when an instance of Holding is created, it should appear on BOTH attribute_mapped_collections, but the KEY is only present on one of them.

Following there is a small adaptation of the Broker/Stocks/Holding model to explain the problem.

from sqlalchemy import Column, ForeignKey, MetaData, Table
from sqlalchemy import Integer, String, Numeric
from sqlalchemy.orm import mapper, relationship
from sqlalchemy.orm.collections import attribute_mapped_collection
meta = MetaData()
stocks_table = Table("stocks", meta,
   Column('symbol', String(10), primary_key=True),
   Column('last_price', Numeric)
)
brokers_table = Table("brokers", meta,
   Column('id', Integer,primary_key=True),
   Column('name', String(100), nullable=False)
)
holdings_table = Table("holdings", meta,
  Column('broker_id', Integer, ForeignKey('brokers.id'), primary_key=True),
  Column('symbol', String(10), ForeignKey('stocks.symbol'), primary_key=True),
  Column('shares', Integer)
)
class Broker(object):
    def __init__(self, name):
        self.name = name
class Stock(object):
    def __init__(self, symbol):
        self.symbol = symbol
        self.last_price = 0
class Holding(object):
    def __init__(self, broker=None, stock=None, shares=0):
        self.broker = broker
        self.stock = stock
        self.shares = shares
mapper(Stock, stocks_table, properties={
    'by_broker': relationship(Holding, back_populates="stock",
        collection_class=attribute_mapped_collection('broker'))
})
mapper(Broker, brokers_table, properties={
    'by_stock': relationship(Holding, back_populates="broker",
        collection_class=attribute_mapped_collection('stock'))
})
mapper(Holding, holdings_table, properties={
    'stock': relationship(Stock, back_populates="by_broker"),
    'broker': relationship(Broker, back_populates="by_stock")
})
broker = Broker('paj')
stock  = Stock('ZZK')
holding = Holding(broker, stock, 10)
print stock.by_broker
print broker.by_stock

The expected behaviour would be:

{<__main__.Broker object at 0xefce10>: <__main__.Holding object at 0xf00910>}
{<__main__.Stock object at 0x1c74190>: <__main__.Holding object at 0xf00910>}

But I get:

{<__main__.Broker object at 0xefce10>: <__main__.Holding object at 0xf00910>}
{None: <__main__.Holding object at 0xf00910>}

Debugging in the code of sqlalchemy I assume the problem is that the addition of the Holding instance in the attribute_mapped_collections is done in two steps. During Holding object creation, when the first attribute (broker) is set, it fires an event and causes its addition to the broker.by_stock collection, but as stock is still not set, the key is None. Then, when the second attribute (stock) is set, it get correctly added to the collection as broker value is already set.

Does sqlalchemy support any other way of achieving the correct behaviour? Is this a bug? Should I file a report?

Thank you in advance!

Pau.

[Michael Bayer]

that would appear to be correct.

As far as a bug, mmm, how could such a thing be worked around in an automated fashion ?   Attribute set events are just that.   Either the backref events would not fire off at all, waiting for the database round trip to populate (just don't use back_populates to achieve that), or the backref events would be delayed until after constructor, but that is quite awkward and arbitrary, as the same kinds of events can happen in any other number of ways besides being set within the constructor.   

There's another behavior, which is that a backref populates the incoming record into the so-called "pending" collection for an unloaded collection, which populates upon access of the collection after the database population has occurred.  This behavior is to save on SQL calls for backref events, and it does not kick in on a transient or pending object since there's no collection to load.   Some awkward mechanics could allow this behavior to occur on a pending/transient, but then the moment you access the actual dictionary for a read, its loaded, and then you'd have this undesirable behavior again for subsequent populations of Holding.   As long as the dictionary we're dealing with is just a plain dict and not some dynamic view type of thing (which is in fact a feature here), I'm not seeing how this kind of issue is avoidable (don't take that to mean there's no solution though...I can't say for sure).

It would appear the simplest approach is to just populate the dictionaries directly:

class Holding(object):

    def __init__(self, broker, stock):

        stock.by_broker[broker] = self

        broker.by_stock[stock] = self

[Pau Tallada]

Hi Michael,

Thank you very much for your response!

I'll try to code some workaround, and post it here if it works.

Thank you again!

2011/8/3 Michael Bayer <mik...@zzzcomputing.com>

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--

----------------------------------

Pau Tallada Crespí

Dep. d'Astrofísica i Cosmologia

Port d'Informació Científica (PIC)

Tel: +34 93 586 8233

----------------------------------