SQLAlchemy, flush vs. transaction

[mcmc]

I have the following piece of code:

transaction = session.create_transaction()
try:
    # Do some work here that might fail
    p=P()
    p.ID=333
    session.save(p)
    session.flush()
    # Do some more work here that might fail
    p1=P()
    p1.ID=333
    session.save(p1)
    session.flush()
    # Success, commit everything
    transaction.commit()
except:
    # Make sure the transaction is rolled back ...
    transaction.rollback()
    # ... then propagate the error upwards to be handled elsewhere
    raise

The 2nd flush fails (because there is already an ID - 333) and then rollback is called, but the first row entered remains in the database.
Wasn't rollback supposed to remove it?

[bartonc]

I have the following piece of code:

transaction = session.create_transaction()
try:
    # Do some work here that might fail
    p=P()
    p.ID=333
    session.save(p)
    session.flush()
    # Do some more work here that might fail
    p1=P()
    p1.ID=333
    session.save(p1)
    session.flush()
    # Success, commit everything
    transaction.commit()
except:
    # Make sure the transaction is rolled back ...
    transaction.rollback()
    # ... then propagate the error upwards to be handled elsewhere
    raise

The 2nd flush fails (because there is already an ID - 333) and then rollback is called, but the first row entered remains in the database.
Wasn't rollback supposed to remove it?

Looks like you're leaving out the transaction.begin() call.
I'm not familiar with that RDBM, but these docs look very well done.

[mcmc]

SessionTransaction do not have a begin() method.
None of the examples have it.
See http://www.sqlalchemy.org/docs/tutorial.html#tutorial_orm_transactions

[bartonc]

SessionTransaction do not have a begin() method.
None of the examples have it.
See http://www.sqlalchemy.org/docs/tutorial.html#tutorial_orm_transactions

Connections do, though:

The Connection object provides a begin() method which returns a Transaction object. This object is usually used within a try/except clause so that it is guaranteed to rollback() or commit():

[mcmc]

OK, but the question was about the code that I posted. Why doesn't it work?

[mcmc]

Found the problem.
I needed to use the InnoDB engine.

[bartonc]

Found the problem.
I needed to use the InnoDB engine.

Nice! Thanks for the update.