SQLAlchemy idle in transaction

Question

I have application writen Python 3.6, Flask and SQLAlchemy (PostgreSQL).

I encountered problems with hanging idle in transaction connections in my db. It's probably because I don't commit nor rollback after select queries.

I use default SQLALchemy configuration: db = SQLAlchemy()

Sample endpoint that creates hanging connections:

class Test(Resource):

    def get(self, pk):
        return User.query.get(pk).serialize()

What's the way of handling such select queries? Should I select then commit? Or select then rollback? or entirely close connection after request? But closing connection causes that on every request there will be new connection to database opened.

What's best way?

Answer 1

This article describes what's happening and how to deal with it: http://oddbird.net/2014/06/14/sqlalchemy-postgres-autocommit/

Short answer: SQLAlchemy defaults to implicitly opening a new transactions. You could either commit after every SELECT or turn on autocommit (read the article to learn more).

Here's an SO post on the matter.
sqlalchemy, postgresql and relationship stuck in "idle in transaction"