Python sqlalchemy.exc.OperationalError() Examples

def upgrade():
    try:
        op.create_table('eventhandlercondition',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('eventhandler_id', sa.Integer(), nullable=True),
        sa.Column('Key', sa.Unicode(length=255), nullable=False),
        sa.Column('Value', sa.Unicode(length=2000), nullable=True),
        sa.Column('comparator', sa.Unicode(length=255), nullable=True),
        sa.ForeignKeyConstraint(['eventhandler_id'], ['eventhandler.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('eventhandler_id', 'Key', name='ehcix_1')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table eventhandlercondition already exists.")
        else:
            print("Table already exists")
            print(exx)

    except Exception as exx:
        print("Could not add Table eventhandlercondition")
        print (exx) 

def execute_query(instance: PluginInstance, sql_query: Union[str, Query],
                  rows_as_dict: bool = False) -> web.Response:
    try:
        res: ResultProxy = instance.inst_db.execute(sql_query)
    except exc.IntegrityError as e:
        return resp.sql_integrity_error(e, sql_query)
    except exc.OperationalError as e:
        return resp.sql_operational_error(e, sql_query)
    data = {
        "ok": True,
        "query": str(sql_query),
    }
    if res.returns_rows:
        row: RowProxy
        data["rows"] = [({key: check_type(value) for key, value in row.items()}
                         if rows_as_dict
                         else [check_type(value) for value in row])
                        for row in res]
        data["columns"] = res.keys()
    else:
        data["rowcount"] = res.rowcount
    if res.is_insert:
        data["inserted_primary_key"] = res.inserted_primary_key
    return web.json_response(data) 

def run(self):
        while True:
            i = self.queue.get()
            if i == 'dummy':
                self.queue.task_done()
                break
            if i['task'] == 'add_format':
                cur_book = self.session.query(Books).filter(Books.id == i['id']).first()
                cur_book.data.append(i['format'])
                try:
                    # db.session.merge(cur_book)
                    self.session.commit()
                except OperationalError as e:
                    self.session.rollback()
                    self.log.error("Database error: %s", e)
                    # self._handleError(_(u"Database error: %(error)s.", error=e))
                    # return
            self.queue.task_done() 

def upgrade():
    try:
        op.create_table('radiusserver',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('identifier', sa.Unicode(length=255), nullable=False),
        sa.Column('server', sa.Unicode(length=255), nullable=False),
        sa.Column('port', sa.Integer(), nullable=True),
        sa.Column('secret', sa.Unicode(length=255), nullable=True),
        sa.Column('description', sa.Unicode(length=2000), nullable=True),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('identifier')
        )
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table 'radiusserver' already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'radiusserver'")
        print (exx) 

def _pg_create_db(cfg, eng, ident):
    with eng.connect().execution_options(
            isolation_level="AUTOCOMMIT") as conn:
        try:
            _pg_drop_db(cfg, conn, ident)
        except Exception:
            pass
        currentdb = conn.scalar("select current_database()")
        for attempt in range(3):
            try:
                conn.execute(
                    "CREATE DATABASE %s TEMPLATE %s" % (ident, currentdb))
            except exc.OperationalError as err:
                if attempt != 2 and "accessed by other users" in str(err):
                    time.sleep(.2)
                    continue
                else:
                    raise
            else:
                break 

def _pg_create_db(cfg, eng, ident):
    with eng.connect().execution_options(
            isolation_level="AUTOCOMMIT") as conn:
        try:
            _pg_drop_db(cfg, conn, ident)
        except Exception:
            pass
        currentdb = conn.scalar("select current_database()")
        for attempt in range(3):
            try:
                conn.execute(
                    "CREATE DATABASE %s TEMPLATE %s" % (ident, currentdb))
            except exc.OperationalError as err:
                if attempt != 2 and "accessed by other users" in str(err):
                    time.sleep(.2)
                    continue
                else:
                    raise
            else:
                break 

def mysql_ping_listener(dbapi_conn, connection_rec, connection_proxy):
    """
    Ensures that MySQL connections checked out of the
    pool are alive.

    Borrowed from:
    http://groups.google.com/group/sqlalchemy/msg/a4ce563d802c929f

    :param dbapi_conn: DBAPI connection
    :param connection_rec: connection record
    :param connection_proxy: connection proxy
    """

    try:
        dbapi_conn.cursor().execute('select 1')
    except dbapi_conn.OperationalError as ex:
        if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
            msg = 'Got mysql server has gone away: %s' % ex
            raise DisconnectionError(msg)
        else:
            raise 

def upgrade():
    try:
        op.create_table('passwordreset',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('recoverycode', sa.Unicode(length=255), nullable=False),
        sa.Column('username', sa.Unicode(length=64), nullable=False),
        sa.Column('realm', sa.Unicode(length=64), nullable=False),
        sa.Column('resolver', sa.Unicode(length=64), nullable=True),
        sa.Column('email', sa.Unicode(length=255), nullable=True),
        sa.Column('timestamp', sa.DateTime(), nullable=True),
        sa.Column('expiration', sa.DateTime(), nullable=True),
        sa.PrimaryKeyConstraint('id')
        )
        op.create_index(op.f('ix_passwordreset_realm'), 'passwordreset',
                        ['realm'], unique=False)
        op.create_index(op.f('ix_passwordreset_username'), 'passwordreset',
                        ['username'], unique=False)
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Table passwordreset already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add table 'passwordreset'")
        print (exx) 

def upgrade():
    try:
        op.add_column('token', sa.Column('revoked', sa.Boolean(),
                                         default=False))
    except (OperationalError, ProgrammingError, InternalError) as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column revoked already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'revoked' to table 'token'")
        print (exx)

    try:
        op.add_column('token', sa.Column('locked', sa.Boolean(),
                                         default=False))
    except (OperationalError, ProgrammingError)  as exx:
        if "duplicate column name" in str(exx.orig).lower():
            print("Good. Column locked already exists.")
        else:
            print(exx)
    except Exception as exx:
        print ("Could not add column 'locked' to table 'token'")
        print (exx) 

def main(args, env):
    global Session

    if args.verbose >= 1:
        app.config['DEBUG'] = True
    sys.stderr.write("connecting to DB server {:s}\n".format(args.db))
    connection_succeeded = False
    while not connection_succeeded:
        try:
            engine = create_engine(args.db)
            Session = sessionmaker(bind = engine)
            Base.metadata.create_all(engine)
            sys.stderr.write("connection succeeded!\n")
            connection_succeeded = True
            app.run(debug = args.verbose >= 1, host = "0.0.0.0", port = 80)
        except OperationalError as err:
            if "Connection refused" in str(err):
                connection_succeeded = False
                time.sleep(10)
            else:
                raise 

def to_database(
    graph,
    manager: Optional[Manager] = None,
    use_tqdm: bool = True,
):
    """Store a graph in a database.

    :param BELGraph graph: A BEL graph
    :return: If successful, returns the network object from the database.
    :rtype: Optional[Network]
    """
    if manager is None:
        manager = Manager()

    try:
        return manager.insert_graph(graph, use_tqdm=use_tqdm)
    except (IntegrityError, OperationalError):
        manager.session.rollback()
        logger.exception('Error storing graph')
    except Exception as e:
        manager.session.rollback()
        raise e 

def __init__(self, db_str="sqlite:///:memory:"):
        if sqlalchemy is None:
            raise ImportError("Cannot import SQLAlchemy. Please install SQLAlchemy before using %s."
                              % self.__class__.__name__)

        # ORM declarations
        engine = create_engine(db_str)

        # create table
        try:
            Base.metadata.create_all(engine, checkfirst=True)
        except OperationalError:
            # table already exists
            pass

        self.Session = sessionmaker(bind=engine) 

def test_raises_without_using_transaction_retry(
    toxiproxy_db_session, toxiproxy, disconnect
):
    if not toxiproxy:
        pytest.skip('Toxiproxy not installed')

    def get_model_count():
        return toxiproxy_db_session.query(ExampleModel).count()

    toxiproxy_db_session.add(ExampleModel(data='hello1'))
    toxiproxy_db_session.add(ExampleModel(data='hello2'))
    toxiproxy_db_session.commit()

    disconnect(reconnect=True)

    with pytest.raises(OperationalError):
        get_model_count() 

def __init__(self):
        if __project__.name is None:
            DB_NAME = 'default.db'
            db_path = path.join(__project__.get_path(), DB_NAME)
        else:
            DB_NAME = __project__.name + '.db'
            db_path = path.join(__project__.get_path(), DB_NAME)

        # Connect to Postgres DB
        self.engine = create_engine('postgresql+psycopg2://{0}:{1}!@localhost/cirtkit'.format(DB_USER, DB_PASSWD))

        self.engine.echo = False
        self.engine.pool_timeout = 60

        try:
            Base.metadata.create_all(self.engine)
        except OperationalError:
            # Connect to local SQLite DB if cannot connect to Postgres
            self.engine = create_engine('sqlite:///{0}'.format(db_path), poolclass=NullPool)
            Base.metadata.create_all(self.engine)

        self.Session = sessionmaker(bind=self.engine) 

def _pg_create_db(cfg, eng, ident):
    with eng.connect().execution_options(
            isolation_level="AUTOCOMMIT") as conn:
        try:
            _pg_drop_db(cfg, conn, ident)
        except Exception:
            pass
        currentdb = conn.scalar("select current_database()")
        for attempt in range(3):
            try:
                conn.execute(
                    "CREATE DATABASE %s TEMPLATE %s" % (ident, currentdb))
            except exc.OperationalError as err:
                if attempt != 2 and "accessed by other users" in str(err):
                    time.sleep(.2)
                    continue
                else:
                    raise
            else:
                break 

def _retry_on_deadlock(fn):
    """Decorator to retry a DB API call if Deadlock was received."""
    lock_messages_error = ['Deadlock found', 'Lock wait timeout exceeded']

    @wraps(fn)
    def wrapped(*args, **kwargs):
        while True:
            try:
                return fn(*args, **kwargs)
            except dbexc.OperationalError as e:
                if any(msg in e.message for msg in lock_messages_error):
                    # msg = ("Deadlock detected when running %s Retrying..." %
                    #        (fn.__name__))
                    # rc_util.syslogout(msg, syslog.LOG_WARNING)
                    # Retry!
                    time.sleep(0.5)
                    continue
    return wrapped 

def on_get(self, req, resp):
        # TODO pull status args out of request
        engine = req.params.get('engine')
        type = req.params.get('type', '')
        try:
            gen = AirflowScheduler.query(engine)
        except OperationalError:
            logging.debug('Scheduler offline, using fake scheduler query')
            gen = AirflowScheduler.fakequery(engine)
        if type == 'jobs':
            ret = gen['jobs']
        elif type == 'reports':
            ret = gen['reports']
        else:
            ret = gen

        resp.content_type = 'application/json'
        resp.body = json.dumps(ret) 

def _op_exc(connection_invalidated=False):
    return OperationalError(
        None, None, None, connection_invalidated=connection_invalidated) 

def execute(self, *args, **kwargs):
        try:
            if not self._conn or self._conn.closed:
                self._conn = self.engine.connect()
            result = self._conn.execute(*args, **kwargs)
        except OperationalError:
            time.sleep(5)
            result = self.execute(*args, **kwargs)
        return result 

def test_view_delete_method(self, registry_simple_view):
        registry = registry_simple_view
        with pytest.raises(OperationalError):
            registry.TestView.query().delete() 

def test_view_update_method(self, registry_simple_view):
        registry = registry_simple_view
        with pytest.raises(OperationalError):
            registry.TestView.query().update({'val2': 3}) 

def add(self, *columns):
        """ Add the constraint

        :param *columns: list of SQLalchemy column
        :rtype: MigrationConstraintUnique instance
        :exception: MigrationException
        """
        if not columns:
            raise MigrationException("""To add an unique constraint you """
                                     """must define one or more columns""")

        columns_name = [x.name for x in columns]
        savepoint = 'uq_%s' % (self.name or '')
        try:
            self.table.migration.savepoint(savepoint)
            self.table.migration.operation.create_unique_constraint(
                self.name, self.table.name, columns_name,
                schema=self.table.schema)
        except (IntegrityError, OperationalError) as e:
            if not sgdb_in(self.table.migration.conn.engine,
                           ['MySQL', 'MariaDB']):
                self.table.migration.rollback_savepoint(savepoint)

            logger.warning(
                "Error during the add of new unique constraint %r "
                "on table %r and columns %r : %r " % (self.name,
                                                      self.table.name,
                                                      columns_name, str(e)))

        return self 

def test_savepoint_without_rollback(self, registry):
        registry.migration.savepoint('test')
        registry.migration.release_savepoint('test')
        with pytest.raises((InternalError, OperationalError)):
            registry.migration.rollback_savepoint('test') 

def setup_mock_db():
    # not setup database
    db._initialized = True
    mock_session = MagicMock()
    mock_session.query = MagicMock(
        side_effect=OperationalError(None, None, None))
    db._session = mock_session


# GET / 

def sql_operational_error(error: OperationalError, query: str) -> web.Response:
        return web.json_response({
            "ok": False,
            "query": query,
            "error": str(error.orig),
            "full_error": str(error),
            "errcode": "sql_operational_error",
        }, status=HTTPStatus.BAD_REQUEST) 

def ping_connection(self):
        """
        Required to maintain the connection to MySQL.
        Perform ping before each query to the database.
        OperationalError if an exception occurs, remove sessions, and connects again.
        Set the wait_timeout(Mysql session variable) for the session on self.wait_timeout.
        """
        try:
            self.session_db.execute("SELECT 1")
        except OperationalError as exc:
            # OperationalError: (2006, 'MySQL server has gone away')
            LOGGER.warning("Database server went away: %r", exc)
            LOGGER.info("Reconnect to server")
            self.session_db.remove()
            self.set_session_wait_timeout() 

def verify_db_ready(session):
    ready = True
    try:
        from scoring_engine.models.user import User
        session.query(User).get(1)
    except (OperationalError, ProgrammingError):
        ready = False
    return ready 

def close_database_session(session):
    """Close connection with the database"""

    try:
        session.close()
    except OperationalError as e:
        raise DatabaseError(error=e.orig.args[1], code=e.orig.args[0]) 

def test_raises_if_connection_is_not_invalidated():

    @transaction_retry
    def raise_error():
        raise OperationalError(None, None, None, connection_invalidated=False)

    with pytest.raises(OperationalError):
        raise_error() 

def item_update(self, par_dict, item_id):
        """
        Modify Item in database

        Args:
            par_dict (dict): Dictionary of the parameters to be modified,, where the key is the column name.
                            {'job' : 'maximize',
                             'subjob' : 'testing',
                             ........}
            item_id (int, list): Database Item ID (Integer) - '38'  can also be [38]

        Returns:

        """
        if not self._viewer_mode:
            if type(item_id) is list:
                item_id = item_id[-1]  # sometimes a list is given, make it int
            if np.issubdtype(type(item_id), np.integer):
                item_id = int(item_id)
            # all items must be lower case, ensured here
            par_dict = dict((key.lower(), value) for key, value in par_dict.items())
            query = self.simulation_table.update(
                self.simulation_table.c["id"] == item_id
            ).values()
            try:
                self.conn.execute(query, par_dict)
            except (OperationalError, DatabaseError):
                if not self._sql_lite:
                    self.conn = AutorestoredConnection(self._engine)
                else:
                    self.conn = self._engine.connect()
                    self.conn.connection.create_function("like", 2, self.regexp)

                self.conn.execute(query, par_dict)
            if not self._keep_connection:
                self.conn.close()
        else:
            raise PermissionError("Not avilable in viewer mode.")