title: How to close and reconnect to avoid idle-in-transaction timeout using SQLAlchemy
body: <p>I wrote a Python app which connects to Postgres DB using SQLAlchemy. The <code>engine</code> and <code>session</code> are defined in <code>db.py</code></p>
  
  <pre><code>engine = create_engine(URL(**settings.DATABASE))
  session = scoped_session(sessionmaker(bind=engine))
  </code></pre>
  
  <p>Most of the db operations are in <code>service.py</code> which imports <code>session</code> from <code>db.py</code></p>
  
  <pre><code>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]
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>try:
      while True:
          run_task()
          time.sleep(120)
  except KeyboardInterrupt:
      print('Manual break by user')
  </code></pre>
  
  <p>The DB time out idle connections every minute. So I get error every time the process goes to sleep for more than 1 minute.</p>
  
  <pre><code>psycopg2.InternalError: terminating connection due to idle-in-transaction timeout
  SSL connection has been closed unexpectedly
  </code></pre>
  
  <p>I wonder if there is a way to close the session and reopen it after <code>time.sleep(120)</code> so it won't get timed out. Maybe in the main modole, I import <code>session</code> from <code>db</code> as a global variable and somehow pass it to methods in <code>services</code>. How do I do that? I can't import <code>session</code> in <code>services</code> from <code>main</code> instead of <code>db</code> since <code>main</code> imports functions from <code>services</code>.</p>