jason kirtland wrote: > 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.
hmm. this sounds interesting. in dbcook i have a similar testcase, where the intermediate association table has 3 columns in its primary key, but a) they are all references to somewhere b) its only sort of "syntax" test and doesnt do anything real with 3rd column. thanks for reminding me.. one day i may need it --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
