SqlAlchemy: UPDATE and INSERT order wrong with foreign key to self

I have a table which has a foreign key relationship with itself (on a compound primary key)

E.g. something like the following:

CREATE TABLE graph (
  start_id character varying(50) NOT NULL,
  end_id character varying(50) NOT NULL,
  weight integer,
  other_start_id character varying(50),
  other_end_id character varying(50),
  CONSTRAINT graph_pkey PRIMARY KEY (start_id, end_id),
  CONSTRAINT graph_other FOREIGN KEY (other_start_id, other_end_id)
     REFERENCES graph (start_id, end_id) MATCH SIMPLE
     ON UPDATE SET NULL ON DELETE SET NULL,
)

In SqlAlchemy, I create a new (pending) graph object 'new_obj', and assign it to the other attribute of an existing persistent object, i.e.:

exist_obj.other = new_obj

When I commit the session, SqlAlchemy issue开发者_如何转开发s the UPDATE on the existing object before it issues the INSERT to create the new_obj. My database rightly complains on the UPDATE that it can't find the foreign key graph_other (as new_obj hasn't been inserted yet).

I thought SqlAlchemy was supposed to be smart about the ordering of SQL? I'm using version 0.5.4

Is there a way of manually ordering the operations?

Though this question is quite old and the OP likely lost interest, it has an answer in the current 0.7 version of SA. The documentation has a section discussing special cases of Many-to-Many relationships that includes a simple graph Node example using the ORM.

The OP's specific question was how to control the order of INSERT and UPDATE for those cases where the new elements point to one another. The SA docs provide examples here for handling this kind of problem with Mutually-Dependent rows:

To enable the usage of a supplementary UPDATE statement, we use the post_update option of relationship(). This specifies that the linkage between the two rows should be created using an UPDATE statement after both rows have been INSERTED