Aiohttp + sqlalchemy: cannot reconnect until invalid transaction is canceled

Question

I use aiohttp and sqlalchemy , and I created Singleton, which helps me connect when I need an instance of SQLAlchemy (the following code). Unfortunately, every time after a while I get the following error (which I "solve" when the server restarts):

Dec 11 09:35:29 ip-xxx-xxx-xxx-xxx gunicorn [16513]: sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Unable to reconnect until the invalid transaction is canceled [SQL : '... \ nFROM ... \ nWHERE ... =% (username_1) s \ n LIMIT% (param_1) s'] [parameters: [{}]] `` `

Is there a way to fix the current code? Thanks:)

 CONNECTION_DETAILS = { 'driver': 'pymysql', 'dialect': 'mysql', 'host': os.environ.get('HOST'), 'port': 3306, 'user': 'master', 'password': os.environ.get('PASSWORD'), 'database': 'ourdb', 'charset': 'utf8' } _instance = None def __new__(cls, *args, **kwargs): if not cls._instance: con_str = '{dialect}+{driver}://{user}:{password}@' \ '{host}:{port}/{database}?charset={charset}'\ .format(**cls.CONNECTION_DETAILS) try: engine = sqlalchemy.create_engine(con_str) Session = scoped_session(sessionmaker(bind=engine)) session = Session() # Create the ORM handle except sqlalchemy.exc.OperationalError: logger.exception('Establishing database connection error.') cls._instance = super().__new__(cls) logger.debug("Returning database session.") cls._instance.session = session # Initializing tables cls._instance.Users = Users cls._instance.Services = Services cls._instance.APIKeys = APIKeys return cls._instance 

Answer 1

That would be a rather late answer. This is what happens: when using a session, a sqlalchemy error occurs (anything that can also cause an error when used as pure SQL: syntax errors, unique constraints, key collisions, etc.).

You will have to find this error, wrap it in try/except -block and execute session.rollback() .

After that, you can restore the session.