reason for sqlalchemy to roll back

Question

I am using sqlalchemy to store data into a postgresql database. I am a little bit confused that sqlalchemy rolls back on execution without throwing an exception. I found an article in the documentary and tried to prevent rollbacks by setting pool_reset_on_return='commit'

This actually leads sqlalchemy to do nothing. Hence, I inserted a line that explicitly calls trans.commit() that leads to the following output:

2014-03-03 18:03:33,390,p:32764 Thread-3, sqlalchemy.engine.base.Engine(_execute_context) [INFO]: INSERT INTO _fd (**)
2014-03-03 18:03:33,391,p:32764 Thread-3, sqlalchemy.engine.base.Engine(_execute_context) [INFO]: {****}
2014-03-03 18:03:33,392,p:32764 Thread-3, sqlalchemy.engine.base.Engine(__init__) [DEBUG]: Col ('fid',)
2014-03-03 18:03:33,393,p:32764 Thread-3, sqlalchemy.engine.base.Engine(process_rows) [DEBUG]: Row (*,)
2014-03-03 18:03:33,393,p:32764 Thread-3, sqlalchemy.engine.base.Engine(_rollback_impl) [INFO]: ROLLBACK

The code is quit simple so far:

1837     with conn.begin() as trans:                                                                                                                                                                                                           
1838         statement = meta.tables[_table_name].insert().values(
...
1847         )
1848         res = conn.execute(statement)
1849         trans.commit()

Does anyone have an idea, what could be the cause for the rollbacks?

Answer 1

Not 100% sure about the reason but looking at the source, we can see that when you do trans.commit() within your with.... construct, it sets the transaction to inactive. Now when the Context Manager for the transaction tries to run the __exit__() method at the end of the with statement, it triggers a rollback() because the is_active flag has been set to False. (I don't know what happens when you commit a transaction and then roll it back).

Anyways the "problem" is that with construct handles the commit and rollback part implicitly. Per the docs, all you need to do is

with conn.begin() as trans:      
    statement = meta.tables[_table_name].insert().values(
    ...
    )
    res = conn.execute(statement)