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?

Answers


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.


Need Your Help

Detect click events outside div inside usercontrol

asp.net javascript

I have a div inside a user control that is displayed by the user after some event.

Jumping to a Ruby bang method using Ctags in Vim

ruby vim ctags

I'm having a problem with jumping to a Ruby bang method using Exhuberant Ctags. I have searched for others having a similar problem and am unable to find anything. An example of the problem can be ...