[sqlalchemy] Re: dictionary returning a collection
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: dictionary returning a collection
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] 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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: dictionary returning a collection
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
-~--~~~~--~~--~--~---
