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. 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> -- 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.
