13

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?

A. Nadjar
  • 2,440
  • 2
  • 19
  • 20
mkiss
  • 423
  • 1
  • 3
  • 13
  • 3
    Keeping a session alive for an infinite period is a bad idea. As the interval between sessions is 20 seconds - a long time in database connection terms - I'd create a new session for each iteration. – snakecharmerb Oct 14 '19 at 15:23
  • Thank you, I'll give it a try, and I'll write back. – mkiss Oct 14 '19 at 17:01
  • 2
    Relative to connections, sessions are quite efficient to create so you don't need to be concerned with them having a short life-cycle. The SQLAlchemy engine maintains a connection pool for you so each session is not making a new connection to the database, just establishing a new transaction. – SuperShoot Oct 15 '19 at 03:55

1 Answers1

16

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()
mkiss
  • 423
  • 1
  • 3
  • 13
  • 1
    Is it possible to maintain connections by SQLAlchemy, not by the user, now I do not know where not close the connection cause this problem. @mkiss – Dolphin Oct 12 '20 at 11:49