Make SQLAlchemy COMMIT Instead Of ROLLBACK After A SELECT Query

I am developing an app together with a partner. I do the database part (PostgreSQL), my partner implements the app on the web-server with python using SQLAlchemy. We make heavy use

Solution 1:

If you're using SQLAlchemy's connection pooling, then what you're seeing is probably the automatic rollback that happens when a connection is closed after use. It's apparently necessary to guarantee that the connection is 'clean' for the next time it's pulled out of the pool. See this page for more info; search for 'pooling mechanism' near the top.

From what I recall (it's been a couple years since I last worked with this) changing the isolation level to autocommit won't solve the problem, since it won't see the SELECT statement as requiring a commit.

You really just want to wrap that statement in a transaction. I don't know how your code is structured, but you should just be able to use SQLAlchemy's connection.begin and connection.commit. You could even just execute the BEGIN and COMMIT as arbitrary SQL.