Great! The solution #3 seems especially appealing ...
Thank you for the comprehensive answer! -----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 -~----------~----~----~----~------~----~------~--~---
