SQLAlchemy: Can't reconnect until invalid transaction is rolled back

Question

I have a weird problem. I have a simple py3 app, which uses sqlalchemy.

But several hours later, there is an error:

(sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

My init part:

self.db_engine = create_engine(self.db_config, pool_pre_ping=True) # echo=True if needed to see background SQL
Session = sessionmaker(bind=self.db_engine)
self.db_session = Session()

The query (this is the only query that happens):

while True:
    device_id = self.db_session.query(Device).filter(Device.owned_by == msg['user_id']).first()
    sleep(20)

The whole script is in infinite loop, single threaded (SQS reading out). Does anybody cope with this problem?

Answer 1

The solution: don't let your connection open a long time. SQLAlchemy documentation also shares the same solution: session basics

@contextmanager
    def session_scope(self):
        self.db_engine = create_engine(self.db_config, pool_pre_ping=True) # echo=True if needed to see background SQL        
        Session = sessionmaker(bind=self.db_engine)
        session = Session()
        try:
            # this is where the "work" happens!
            yield session
            # always commit changes!
            session.commit()
        except:
            # if any kind of exception occurs, rollback transaction
            session.rollback()
            raise
        finally:
            session.close()