SQLAlchemy: Flush-Order for Inserts wrong?
I have 2 tables (contracts and contract_items) where the latter has a foreign key set to the first one.
When using SQLAlchemy for inserting new data from a list into my postgre database I'm basically doing the following:
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()
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 contract_item could not be inserted because it has an foreign key to contract and the contract row does not exist yet.
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 contract_item row should not be inserted before the contract row, when contract_item has a foreign key to contract set. Yet the order seems to be random.
I then tried to flush the contract manually before adding contract_item:
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()
This worked without any problems and the rows got inserted into the database.
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.
Is there a way to make SQLAlchemy get the order right?
The way session handles related objects is defined by cascades. Use "save-update" cascade on a relationship (that is enabled by default) to automatically add related objects, so that you only have to use one add call. The documentation I linked contains code example.