Dear All,
Just to let you know how I solved my problem: according to the solution
#3 described below by Jason, I created in my local sqlalchemy
installation a MultiValuedMappedCollection class in the
sqlalchemy.orm.collections package Based on the MappedCollection class.
The newly created class can then be referenced in a method:
def multi_valued_mapped_collection(keyfunc):
return lambda: MultiValuedMappedCollection(keyfunc)
(Similarly to the existing mapped_collection(keyfunc))
The only change I brought to the MappedCollection resides in the 'set'
method and was really simple (a little too simplistic to be included
as-is in the framework I think):
def set(self, value, _sa_initiator=None):
"""Add a value to the collection of multi-values, with a key
provided by this instance's keyfunc."""
key = self.keyfunc(value)
if not self.has_key(key):
# creates a list on the fly if needed
self.__setitem__(key, [], _sa_initiator)
self.__getitem__(key).append(value)
set = collection.internally_instrumented(set)
set = collection.appender(set)
So that the inherited dict simply holds a list (created on-the-fly)
instead of the value directly. Values are then accumulated in the
existing list each time dict[key] = value is called.
-> not sure that people want to have something exactly like this
eventually embedded in the framework, but it helped in solving my
short-term needs and may also help any good soul willing to implement it
properly ...
Bottom-line:
-----------
Using a declaration like this in the Broker mapper:
'holdingsByDate': relation(Holding,
collection_class=attribute_multi_valued_mapped_collection('date'))
it allows me to write in my app:
date = datetime.datetime(2007,1,1)
holdings = broker.holdingsByDate[date]
Christophe
-----Original Message-----
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of jason kirtland
Sent: Thursday, January 17, 2008 7:53 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: dictionary returning a collection
Christophe Alexandre wrote:
> Dear All,
>
> Send me some study material on DBMS + $100 !
>
> Or if it fits you better, can you please help on the issue described
> below?
>
> The closest situation to what I am facing is described here:
>
http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy_
> building
>
> But unfortunately the situation is not close enough to help me.
>
> Now, based on the example above, here is the issue:
>
> stocks = Table("stocks", meta,
> Column('symbol', String(10), primary_key=True),
> Column('description', String(100), nullable=False),
> Column('last_price', Numeric)
> )
>
> brokers = Table("brokers", meta,
> Column('id', Integer,primary_key=True),
> Column('name', String(100), nullable=False)
> )
>
> holdings = 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),
> Column('date', DateTime, primary_key=True) # <-- HERE IS THE
> DIFFERENCE
> )
>
> The example in the doc provides a nice way to retrieve one holding:
> holding = broker.holdings[stock]
>
> But what if holdings are parameterized by a new attribute ('date' in
our
> case)?
>
> How would you implement a solution allowing you to do something like
> this:
>
> date = datetime.datetime(2007,1,1)
> holdings = broker.holdingsByDate[date]
>
> where 'holdings' is a collection of what the broker is holding at a
> given date.
A couple approaches come to mind. How many Holdings rows in total do
you expect per Broker?
If the number is large, then on-demand querying for chunks of the set by
date could be a good option. That can be wrapped up in a dict-like
class with __getattr__ for an interface like holdingsByDate[date]. You
could cache too, if you wanted. No mapper relation from Broker->Holding
would be needed, but you'd want Holding->Broker.
If the number is small enough that loading the entire collection of
associated Holdings per Broker isn't a big deal, then a whole bunch of
options come to mind, but none of these are built-in.
- A dict-like proxy similar to the large collection approach, except
scanning .holdings in-memory and filtering by .date on demand
- Add some append/remove event handlers to the collection backing
.holding, and use them to maintain a obj.holdingsByDate dictionary
transparently
- Use the collections framework to build a fully featured partitioned
collection type
These are all pretty simple to implement- something like #2 is just a
few lines of code.
An implementation of #3 (a dictionary of lists with full instrumented
goodness) is probably not a ton of work at all and could make a good
addition to either the collections or ext package.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
