title: SQLAlchemy: Flush-Order for Inserts wrong?
body: <p>I have 2 tables (<strong>contracts</strong> and <strong>contract_items</strong>) where the latter has a <strong>foreign key</strong> set to the first one.</p>
  
  <p>When using SQLAlchemy for inserting new data from a list into my postgre database I'm basically doing the following:</p>
  
  <pre><code>for row in list:
  
      # Get contract and item from row
      ...
  
      session.add(contract)
      session.add(contract_item)
  
      # Do some select statements (which will raise an auto-flush)
      ...
  
  session.commit()
  </code></pre>
  
  <p>Now... this works for maybe 2-3 runs, sometimes more, sometimes less. Then the part where an auto-flush is executed will end in an exception telling me that <strong>contract_item</strong> could not be inserted because it has an foreign key to <strong>contract</strong> and the <strong>contract</strong> row does not exist yet.</p>
  
  <p>Is the order in which I pass the data to the add-function of the session not the order in which the data will be flushed? I actually hoped SQLAlchemy would find the right order in which to flush statements on it's own based on the dependencies. It should be clear that the <strong>contract_item</strong> row should not be inserted before the <strong>contract</strong> row, when <strong>contract_item</strong> has a foreign key to <strong>contract</strong> set. Yet the order seems to be random.</p>
  
  <p>I then tried to flush the <strong>contract</strong> manually before adding <strong>contract_item</strong>:</p>
  
  <pre><code>for row in list:
  
      # Getting contract and item from row
      ...
  
      session.add(contract)
      session.flush() # Flushing manually
  
      session.add(contract_item)
  
      # Do some select statements (which will raise an auto-flush)
      ...
  
  session.commit()
  </code></pre>
  
  <p>This worked without any problems and the rows got inserted into the database.</p>
  
  <p>Is there any way to set the order in which statements will be flushed for the session? Does SQLAlchemy really not care about dependencies such as foreign keys or am I making a mistake when adding the data? I'd rather not manage the flushs manually if somehow possible.</p>
  
  <p>Is there a way to make SQLAlchemy get the order right?</p>