How to close and reconnect to avoid idle-in-transaction timeout using SQLAlchemy

Question

I wrote a Python app which connects to Postgres DB using SQLAlchemy. The engine and session are defined in db.py

engine = create_engine(URL(**settings.DATABASE))
session = scoped_session(sessionmaker(bind=engine))

Most of the db operations are in service.py which imports session from db.py

from app.db import engine, session

def get_door_ids():
    result = session.query(ControllerDetail.door_id).distinct().all()
    ids = [c[0] for c in result]
    return ids

def get_last_health_cd(door_id):
    result = session.query(ControllerDetail.door_health_cd).filter(ControllerDetail.door_id == door_id).order_by(ControllerDetail.etl_insert_ts.desc()).first()
    return result[0]

Now everything works great, but the problem is I need to run the same thing every couple minutes repeatedly. So I have the following code in my main module:

try:
    while True:
        run_task()
        time.sleep(120)
except KeyboardInterrupt:
    print('Manual break by user')

The DB time out idle connections every minute. So I get error every time the process goes to sleep for more than 1 minute.

psycopg2.InternalError: terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly

I wonder if there is a way to close the session and reopen it after time.sleep(120) so it won't get timed out. Maybe in the main modole, I import session from db as a global variable and somehow pass it to methods in services. How do I do that? I can't import session in services from main instead of db since main imports functions from services.

Answer 1

First and foremost, you should end your transactions timely by committing / rolling back. Closing the session will also implicitly rollback. In case of scoped session removing will close the session (and remove the session from the registry). All in all "When do I construct a Session, when do I commit it, and when do I close it?" is a good read at this point.

What might surprise some people is that the Session starts a new transaction as soon as you start communicating with the database. It is then your job to end that transaction. Without seeing what run_task() actually does it's hard to say which part should be handling the session's lifetime, but it's safe to say that if the task runs every couple minutes, you should not leave a transaction hanging for that long.