Thank you very much for your help. The post_update=True addition is what I was missing.
The test case I was using was c1.created_by = c1 where created_by was previously null. (c1 having previously been saved and flushed) This seems to be the one corner case where post_update=True was necessary. I have attached a script to demonstrate the problem I encountered. I don't know if this is expected or known behavior or not, but it was certainly confusing to me. I would assume that saving against a null vs non-null value should not result in different behavior. Thank you again. -brad On May 28, 8:47 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > Here's a script which exercises your mapping against 0.4 - the > remote_side should be on the "many-to-one" side of "updated_by". > > Addtionally, depending on what kind of combinations of Contact objects > you want to store, you might run into the scenario where ContactA > references ContactB, and ContactB references ContactA. Theres no way > to INSERT data like that without an UPDATE (assuming FK integrity and > no sequences), so SQLA wants you to use a flag called "post_update" if > that occurs. It needs the flag if the co-dependent items are being > UPDATEd too since it works out dependencies for UPDATE and INSERTs in > the same way...this is something that could perhaps be improved upon. > So the second half of the script illustrates that mapping as an > alternative. > > cyclical_contacts.py > 3KDownload --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
from sqlalchemy import * from sqlalchemy import exceptions from sqlalchemy.orm import * import logging #logging.basicConfig() #logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) #logging.getLogger('sqlalchemy.orm.properties.PropertyLoader').setLevel(logging.INFO) #logging.getLogger('sqlalchemy.orm.strategies.LazyLoader').setLevel(logging.INFO) meta = MetaData(create_engine('sqlite://')) contacts = Table('contacts', meta, Column('id', Integer, primary_key=True), Column('name', String(50)), Column('created_by', Integer), Column('updated_by', Integer), ForeignKeyConstraint(['created_by'], ['contacts.id']), ForeignKeyConstraint(['updated_by'], ['contacts.id']) ) meta.create_all() class Contact(object): def __init__(self, name, created_by=None): self.name = name self.created_by = created_by def __eq__(self, other): return other.name == self.name def __repr__(self): return "Contact(created_by=%r, updated_by=%r)" % (self.created_by, self.updated_by) # create a sample mapper with and without post_update=True # updated_by uses post_update, created_by does not. mapper(Contact, contacts, properties={ '_created_by': contacts.c.created_by, '_updated_by': contacts.c.updated_by, 'created_by': relation(Contact, primaryjoin=contacts.c.created_by==contacts.c.id, remote_side=[contacts.c.id], ), 'updated_by': relation(Contact, primaryjoin=contacts.c.updated_by==contacts.c.id, remote_side=[contacts.c.id], post_update=True, ) }) sess = create_session() # saving indivisually seems to be necessary to ensure ID ordering c1 = Contact('c1') sess.save(c1) sess.flush() c2 = Contact('c2') sess.save(c2) sess.flush() # assert proper data assert contacts.select().order_by(contacts.c.name).execute().fetchall() == [ (1, 'c1', None, None), (2, 'c2', None, None), ] sess.clear() [c1, c2] = sess.query(Contact).order_by(Contact.name).all() c1.created_by = c2 c1.updated_by = c2 sess.flush() # saving against a null key with a non self referential key works as expected assert contacts.select().order_by(contacts.c.name).execute().fetchall() == [ (1, 'c1', 2, 2), (2, 'c2', None, None), ] sess.clear() [c1, c2] = sess.query(Contact).order_by(Contact.name).all() c1.created_by = c1 c1.updated_by = c1 c2.created_by = c2 c2.updated_by = c2 sess.flush() # updating a non-null key with a self referential key works as expected # with and without post_update=True # # saving against a null key with a self referential key works only with # post_update=True. # # This does not seem to be a true circular relationship as the resulting SQL # can be one simple UPDATE. # # expected outcome (to me): # c1 created_by=1, updated_by=1 # c2 created_by=2, updated_by=2 # actual outcome: # c1 created_by=1, updated_by=1 # c2 created_by=None, updated_by=2 assert contacts.select().order_by(contacts.c.name).execute().fetchall() == [ (1, 'c1', 1, 1), (2, 'c2', 2, 2), ] sess.clear()