[sqlalchemy] copying a one-to-many relationship
Hi, Assuming you have a one-to-many type of relationship, say one user has several roles. The underlying role table would hold one foreign key referring to the user table. A properly written SA ORM allows you to write: user1.roles Now assuming you have just created a new user (user2), what is the easiest way if you want to copy roles from user1? I tried: user2.roles = user1.roles But then it deletes the roles from user1. Same if I go through a copy.copy(): import copy user2.roles = copy.copy(user1.roles) Does anyone have an idea? I would like to avoid going through a loop and creating the roles one by one. Chris --~--~-~--~~~---~--~~ 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: copying a one-to-many relationship
Ops, not yet fully awake ... obviously you are right about the m-n relationship. Thank you for working around it ... Also thank you for your answer it is valid for any 1-n relationship I would have thought of! Chris From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Michael Bayer Sent: Monday, March 10, 2008 3:00 PM To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: copying a one-to-many relationship On Mar 10, 2008, at 9:19 AM, Christophe Alexandre wrote: Hi, Assuming you have a one-to-many type of relationship, say one user has several roles. The underlying role table would hold one foreign key referring to the user table. A properly written SA ORM allows you to write: user1.roles Now assuming you have just created a new user (user2), what is the easiest way if you want to copy roles from user1? I tried: user2.roles = user1.roles But then it deletes the roles from user1. my first impression is that a relation from users to roles is typically a many to many. That you are looking to have the same roles present on more than one user implies this as well. But we can still work with one-to-many. Same if I go through a copy.copy(): import copy user2.roles = copy.copy(user1.roles) copy is going to fail because its copying the full state of each user, including the primary key, instance key, and probably the _state which is putting your session into an invalid state. Does anyone have an idea? I would like to avoid going through a loop and creating the roles one by one. First I'd put a convenience method on Role to handle generating a new Role object with the same value: def asnew(self): return Role(self.name, ...) then I'd just use a list comprehension to make the copy: user2.roles = [r.asnew() for r in user.roles] --~--~-~--~~~---~--~~ 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
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] dictionary returning a collection
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.
Thank you for your help,
Christophe
--~--~-~--~~~---~--~~
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: access mapped object attributes
Hi, I am also interested in retrieving all the attributes resulting from the ORM. The loop on '.c' will list only the database columns. Is there a way to list the object attributes? Thanks a lot for your help, Chris -Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Paul Johnston Sent: Monday, November 05, 2007 8:45 PM To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: access mapped object attributes Hi, Given a Message object, do I have a way to retrieve all the attributes that result from the database mapping? Try this: for col in Message.c: ... Paul --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
