On Wednesday 11 May 2011 15:58:21 Michael Bayer wrote: > On May 11, 2011, at 9:18 AM, Felix Wolfsteller wrote: > > Hi, > > I am using sqlalchemy 0.6.3-3 with python 2.6.6 and sqlite 3.7.3 (all > > from debian squeeze packages). > > > > In an M:N relation i have e.g. following objects: > > > > obj1 > > obj2 > > > > and want to store a list like > > [obj1, obj2, obj1, obj2] > > > > in another object (yes, a list with duplicates, that in best case > > maintains order). > > > > My assumption was that is a "normal" M:N relation. However, sqlalchemy > > will condense the list to contain (rferences to) each object only once > > (i.e. [obj1, obj2, obj1, obj2] -> [obj1, obj2]) in a session. > > > > This changes the list at runtime when a Session.commit() is issued. > > > > Interestingly, when adding a list as shown above, mutliple links are > > added to the "secondary" table and the debug output shows me that the > > query to retrieve the objects also results in these multiple links. > > Smells like a bug at either end to me. > > > > Describing my problem was difficult, I have attached a "minimal" example > > to demonstrate this behaviour. Reading and running the code should make > > the problem clear (before commit: 6 items in list, after commit and with > > query: 3 items in list). > > The example can also be found at > > http://codetidy.com/691 . > > > > Is there a standard way to approach settings like mine? > > SQLAlchemy relationship() does not support collections that contain > multiple instances of the same identity. "secondary" is going to treat > the table as a plain association table and assumes the two FKs form a > unique constraint - there is otherwise no way to tell apart two rows that > have the identical information in them.
This unique constraint is not enforced, then (rows with same content are added to the secondary table). Thanks for your quick answer and recipe. --felix > So you'd need to assign association rows an identity and break it into two > relationships, which means the association object pattern: > > http://www.sqlalchemy.org/docs/orm/relationships.html#association-object > > however, the association would be more like this: > > class Association(Base): > __tablename__ = 'association' > id = Column(Integer, primary_key=True) > left_id = Column(Integer, ForeignKey('left.id')) > right_id = Column(Integer, ForeignKey('right.id')) > > > Probably related pointer from "nickph" in #sqlalchemy on irc (freenode): > > (10:59:38) nickph: > > http://hg.sqlalchemy.org/sqlalchemy/file/23be17cf5025/CHANGES > > (10:59:48) nickph: "Duplicate items in a list-based collection will be > > maintained"... > > > > Enjoy, > > > > --felix > > > > -- > > Felix Wolfsteller | ++49 541 335083-783 | http://www.intevation.de/ > > PGP Key: 39DE0100 > > Intevation GmbH, Neuer Graben 17, 49074 Osnabrück | AG Osnabrück, HR B > > 18998 Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver > > Wagner > > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. To post to this group, send email to > > [email protected]. 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. > > > > <data.py> -- Felix Wolfsteller | ++49 541 335083-783 | http://www.intevation.de/ PGP Key: 39DE0100 Intevation GmbH, Neuer Graben 17, 49074 Osnabrück | AG Osnabrück, HR B 18998 Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. 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.
