SQLAlchemy: Flush-Order for Inserts wrong?

Question

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?

Igor Atsberger Igor Atsberger · Accepted Answer · 2020-03-18T11:22:03

Had the same problem. What solved it in my case is creating a biderictional relationship - you need to make relationship from contracts to contract_items, as described HERE

UPD: actually you can do it simplier: just add relationship from contract_items table to contract table and that should do the thing.

SQLAlchemy: Flush-Order for Inserts wrong?

2 Answers