Python sqlalchemy.exc() Examples

def get_rse(rse_id, session=None):
    """
    Get a RSE or raise if it does not exist.

    :param rse_id:  The rse id.
    :param session: The database session in use.

    :raises RSENotFound: If referred RSE was not found in the database.
    """

    false_value = False  # To make pep8 checker happy ...
    try:
        tmp = session.query(models.RSE).\
            filter(sqlalchemy.and_(models.RSE.deleted == false_value,
                                   models.RSE.id == rse_id))\
            .one()
        tmp['type'] = tmp.rse_type
        return tmp
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE with id \'%s\' cannot be found' % rse_id) 

def _retry_on_exceptions(exc):
    if not isinstance(exc, exception.DBError):
        return False
    inn_e = exc.inner_exception
    if not isinstance(inn_e, sqlalchemy.exc.InternalError):
        return False
    return ((
        pymysql and
        isinstance(inn_e.orig, pymysql.err.InternalError) and
        (inn_e.orig.args[0] == pymysql.constants.ER.TABLE_DEF_CHANGED)
    ) or (
        # HACK(jd) Sometimes, PostgreSQL raises an error such as "current
        # transaction is aborted, commands ignored until end of transaction
        # block" on its own catalog, so we need to retry, but this is not
        # caught by oslo.db as a deadlock. This is likely because when we use
        # Base.metadata.create_all(), sqlalchemy itself gets an error it does
        # not catch or something. So this is why this function exists. To
        # paperover I guess.
        psycopg2
        and isinstance(inn_e.orig, psycopg2.InternalError)
        # current transaction is aborted
        and inn_e.orig.pgcode == '25P02'
    )) 

def del_rse(rse_id, session=None):
    """
    Disable a rse with the given rse id.

    :param rse_id: the rse id.
    :param session: The database session in use.
    """

    old_rse = None
    try:
        old_rse = session.query(models.RSE).filter_by(id=rse_id, deleted=False).one()
        if not rse_is_empty(rse_id=rse_id, session=session):
            raise exception.RSEOperationNotSupported('RSE \'%s\' is not empty' % get_rse_name(rse_id=rse_id, session=session))
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE with id \'%s\' cannot be found' % rse_id)
    rse = old_rse.rse
    old_rse.delete(session=session)
    try:
        del_rse_attribute(rse_id=rse_id, key=rse, session=session)
    except exception.RSEAttributeNotFound:
        pass 

def restore_rse(rse_id, session=None):
    """
    Restore a rse with the given rse id.

    :param rse_id: the rse id.
    :param session: The database session in use.
    """

    old_rse = None
    try:
        old_rse = session.query(models.RSE).filter_by(id=rse_id, deleted=True).one()
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE with id \'%s\' cannot be found' % rse_id)
    old_rse.deleted = False
    old_rse.deleted_at = None
    old_rse.save(session=session)
    rse = old_rse.rse
    add_rse_attribute(rse_id=rse_id, key=rse, value=True, session=session) 

def __call__(self):
        """
        Alias for serialize
        """

        # try:
        [idx.drop(bind=self.engine) for idx in Orf.__table__.indexes]
        self.serialize()
        [idx.create(bind=self.engine) for idx in Orf.__table__.indexes]
        # except (sqlalchemy.exc.IntegrityError, sqlite3.IntegrityError) as exc:
        #     self.logger.error("DB corrupted, reloading data. Error: %s",
        #                       exc)
        #     self.session.query(Query).delete()
        #     self.session.query(Orf).delete()
        #     self.serialize()
        # except InvalidSerialization:
        #     raise 

def error(exc, request):
        """Error method to return jsonapi compliant errors."""
        request.response.content_type = 'application/vnd.api+json'
        request.response.status_code = exc.code
        errors = {
            'errors': [
                {
                    'code': str(exc.code),
                    'detail': exc.detail,
                    'title': exc.title,
                }
            ]
        }
        if asbool(request.registry.settings.get('pyramid_jsonapi.debug_traceback', False)):
            errors['traceback'] = traceback.format_exc()
        return errors 

def test_ensure_backend_available_no_connection_raises(self):
        log = self.useFixture(fixtures.FakeLogger())
        err = OperationalError("Can't connect to database", None, None)
        with mock.patch.object(
                sqlalchemy.engine.base.Engine, 'connect') as mock_connect:
            mock_connect.side_effect = err

            exc = self.assertRaises(
                exception.BackendNotAvailable,
                provision.Backend._ensure_backend_available,
                self.connect_string)
            self.assertEqual(
                "Backend 'postgresql' is unavailable: "
                "Could not connect", str(exc))
            self.assertEqual(
                "The postgresql backend is unavailable: %s" % err,
                log.output.strip()) 

def get_schema_version(engine):
	results = None
	# try the new style storage
	try:
		results = engine.execute(
			sqlalchemy.text("SELECT value FROM storage_data WHERE namespace = :namespace AND key = 'schema_version'"),
			namespace=_metadata_namespace
		).fetchone()
	except sqlalchemy.exc.DatabaseError:
		pass
	if results:
		return _metadata_serializer.loads(results[0])

	# try the old style storage
	try:
		results = engine.execute(sqlalchemy.text("SELECT value_type, value FROM meta_data WHERE id = 'schema_version'")).fetchone()
	except sqlalchemy.exc.DatabaseError:
		pass
	if results:
		value_type, value = results
		if value_type != 'int':
			raise TypeError('the value is not of type: int')
		return int(value)
	return models.SCHEMA_VERSION 

def test_ensure_backend_available_no_dbapi_raises(self):
        log = self.useFixture(fixtures.FakeLogger())
        with mock.patch.object(sqlalchemy, 'create_engine') as mock_create:
            mock_create.side_effect = ImportError(
                "Can't import DBAPI module foobar")

            exc = self.assertRaises(
                exception.BackendNotAvailable,
                provision.Backend._ensure_backend_available,
                self.connect_string)

            mock_create.assert_called_once_with(
                sa_url.make_url(self.connect_string))

            self.assertEqual(
                "Backend 'postgresql' is unavailable: "
                "No DBAPI installed", str(exc))
            self.assertEqual(
                "The postgresql backend is unavailable: Can't import "
                "DBAPI module foobar", log.output.strip()) 

def assertTableNotExists(self, engine, table):
        self.assertRaises(sqlalchemy.exc.NoSuchTableError,
                          oslodbutils.get_table, engine, table) 

def scoped_session(self, rollback=True, fail=False):
        """
        Provide a transactional scope around a series of operations.

        :param rollback: rollback transactions after catch
        :param fail: raise error after catch

        :type rollback: bool
        :type fail: bool
        """
        session = self._Session()
        try:
            yield session

            # commit transactions
            session.commit()

        # catch errors
        except sqlalchemy.exc.SQLAlchemyError:
            # log the error
            logging.exception('caught SQL exception')

            # rollback transactions
            if rollback:
                session.rollback()

            # reraise error
            if fail:
                raise RuntimeError('SQL Failed')

        # cleanup
        finally:
            session.close() 

def do_exec(args):
            msg, sql = args
            with log_duration(msg):
                with engine.begin() as trans:
                    res = trans.execute(sql)
                    try:
                        logger.info(f"SQL result", job=msg, result=res.fetchall())
                    except ResourceClosedError:
                        pass  # Nothing to do here
                    except Exception as exc:
                        logger.error("Hit an issue.", exc=exc)
                        raise exc 

def migrate_up(self, version, with_data=False):
        """Migrate up to a new version of the db.

        :param version: id of revision to upgrade.
        :type version: str
        :keyword with_data: Whether to verify the applied changes with data,
            see :ref:`Auxiliary Methods <auxiliary-dynamic-methods>`.
        :type with_data: Bool
        """
        # NOTE(sdague): try block is here because it's impossible to debug
        # where a failed data migration happens otherwise
        try:
            if with_data:
                data = None
                pre_upgrade = getattr(
                    self, "_pre_upgrade_%03d" % version, None)
                if pre_upgrade:
                    data = pre_upgrade(self.migrate_engine)

            self.migration_api.upgrade(self.migrate_engine,
                                       self.REPOSITORY, version)
            self.assertEqual(version,
                             self.migration_api.db_version(self.migrate_engine,
                                                           self.REPOSITORY))
            if with_data:
                check = getattr(self, "_check_%03d" % version, None)
                if check:
                    check(self.migrate_engine, data)
        except exc.DBMigrationError:
            msg = "Failed to migrate to version %(ver)s on engine %(eng)s"
            LOG.error(msg, {"ver": version, "eng": self.migrate_engine})
            raise 

def test_single_no_dispatcher(self):
        callable_fn = mock.Mock()

        dispatcher = utils.dispatch_for_dialect("sqlite")(callable_fn.sqlite)
        dispatcher = dispatcher.dispatch_for("mysql")(callable_fn.mysql)
        exc = self.assertRaises(
            ValueError,
            dispatcher, "postgresql://s:t@localhost/test"
        )
        self.assertEqual(
            "No default function found for driver: 'postgresql+psycopg2'",
            str(exc)
        ) 

def test_invalid_target(self):
        dispatcher, callable_fn = self._single_fixture()

        exc = self.assertRaises(
            ValueError,
            dispatcher, 20
        )
        self.assertEqual("Invalid target type: 20", str(exc)) 

def test_single_only_one_target(self):
        callable_fn = mock.Mock()

        dispatcher = utils.dispatch_for_dialect("*")(callable_fn.default)
        dispatcher = dispatcher.dispatch_for("sqlite")(callable_fn.sqlite)

        exc = self.assertRaises(
            TypeError,
            dispatcher.dispatch_for("sqlite"), callable_fn.sqlite2
        )
        self.assertEqual(
            "Multiple functions for expression 'sqlite'", str(exc)
        ) 

def test_multiple_no_return_value(self):
        dispatcher, callable_fn = self._multiple_fixture()
        callable_fn.sqlite.return_value = 5

        exc = self.assertRaises(
            TypeError,
            dispatcher, "sqlite://"
        )
        self.assertEqual(
            "Return value not allowed for multiple filtered function",
            str(exc)
        ) 

def __check_transcript(self, current_transcript, current_locus, counter, max_intron,
                           gene_counter, curr_chrom, locus_printer, submit_locus):

        if current_transcript is not None:
            current_transcript.finalize()
            if current_transcript.max_intron_length > max_intron:
                self.logger.warning(
                    "%s has an intron (%s) which is greater than the maximum allowed (%s). Removing it.",
                    current_transcript.id, current_transcript.max_intron_length, max_intron)
            elif current_locus and Superlocus.in_locus(
                            current_locus, current_transcript,
                            flank=self.json_conf["pick"]["clustering"]["flank"]) is True:
                    current_locus.add_transcript_to_locus(current_transcript, check_in_locus=False)
            else:
                counter += 1
                self.logger.debug("Analysing locus # %d", counter)
                try:
                    for stranded_locus in submit_locus(current_locus, counter):
                        if stranded_locus.chrom != curr_chrom:
                            curr_chrom = stranded_locus.chrom
                            gene_counter = 0
                        gene_counter = locus_printer(stranded_locus, gene_counter)
                except KeyboardInterrupt:
                    raise
                except Exception as exc:
                    self.logger.exception(
                        "Superlocus %s failed with exception: %s",
                        None if current_locus is None else current_locus.id, exc)

                current_locus = Superlocus(current_transcript, stranded=False, json_conf=self.json_conf,
                                           source=self.json_conf["pick"]["output_format"]["source"])
                if self.regressor is not None:
                    current_locus.regressor = self.regressor

        return current_locus, counter, gene_counter, curr_chrom 

def __init__(self, query_id, source_id, score):

        self.query_id = query_id
        self.source_id = source_id
        if not isinstance(score, numbers.Number):
            raise sqlalchemy.exc.ArgumentError("Invalid score for external values: {}".format(type(score)))
        score = str(score)
        assert score.strip()
        self.score = str(score) 

def delete(self, table, keys, where=None, limit=None, order_by=None):
        """ Deletes all features with id in list of keys

        Args:
          table: The Table to use.
          keys: primary keys to remove
          where: filter to delete matching records
          limit: max records to delete
          order_by: order of records to delete
        Returns:
            On success an empty dictionary is returned.
            On error, a dictionary with error information is returned.
        """
        if not auth.authorize("write", table):
            return error_message('Unauthorized', status=401)
        if keys is not None or where is not None:
            tbl = self.initialize_table(table)
            primary_key = get_primary_key(tbl)

            query = tbl.delete()

            if where is not None:
                query = query.where(sqlalchemy.text(where))
            if limit is not None:
                query = query.limit(limit)
            if order_by is not None:
                query = query.order(order_by)
            if keys is not None:
                query = query.where(primary_key.in_(keys))
            try:
                connection = self._engine.connect()
                transaction = connection.begin()
                connection.execute(query)
                transaction.commit()
            except sqlalchemy.exc.SQLAlchemyError as e:
                transaction.rollback()
                return error_message("Database error: %s" % e)
            return []
        else:
            return error_message("Either list of keys or where statement required") 

def run_from_commandline(cls):
        parser = cls.make_argument_parser()
        arguments = cls.parse_arguments(parser)
        try:
            script = cls(**arguments)
            script.run()
        except sqlalchemy.exc.StatementError as exc:
            sys.exit(ascii_str(exc))
        else:
            script.msg('Done.') 

def __exit__(self, exc_type, exc, tb):
        self.context_deposit.on_exit(exc_type, exc, tb,
                                     context_finalizer=self.finalize_nested_savepoint,
                                     outermost_context_finalizer=self.finalize_session) 

def _install_reconnector(self):
        # copied from:
        # http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic
        # and slightly adjusted
        @sqlalchemy.event.listens_for(sqlalchemy.pool.Pool, "checkout")
        def ping_connection(dbapi_connection, connection_record, connection_proxy):
            cursor = dbapi_connection.cursor()
            try:
                cursor.execute("SELECT 1")
            except Exception:
                # dispose the whole pool instead of invalidating one at a time
                connection_proxy._pool.dispose()
                # pool will try connecting again up to three times before giving up
                raise sqlalchemy.exc.DisconnectionError()
            cursor.close() 

def test2():
	fetcher = RawArchiver.RawJobDispatcher.RawJobFetcher()

	with common.database.session_context() as sess:
		try:


			job = common.database.RawWebPages(
				state    = 'new',
				url      = 'http://somethingpositive.net',
				starturl = 'http://somethingpositive.net',
				netloc   = 'somethingpositive.net',
				priority = common.database.DB_LOW_PRIORITY,
				distance = 0,
				)
			sess.add(job)
			sess.commit()

		except sqlalchemy.exc.IntegrityError:
			sess.rollback()
			r = sess.query(common.database.RawWebPages) \
				.filter(common.database.RawWebPages.url == 'http://somethingpositive.net') \
				.update({'state' : 'new', 'ignoreuntiltime' : datetime.datetime.min })
			sess.commit()
			print("Did update?")
			print(r)


		archiver = RawSiteArchiver(total_worker_count=1, worker_num=0, new_job_queue=fetcher.get_queue(), cookie_lock=None, response_queue=None, db_interface=sess, db=common.database)
		archiver.taskProcess() 

def job_context(self, jobid=None, joburl=None):
		assert jobid or joburl, "JobID and JobURL are both None!"
		tries = 0

		while 1:
			tries += 1

			with self.db.session_context() as sess:

				try:
					if jobid:
						job = sess.query(self.db.RawWebPages) \
							.filter(self.db.RawWebPages.id == jobid)    \
							.scalar()
					elif joburl:
						job = sess.query(self.db.RawWebPages) \
							.filter(self.db.RawWebPages.url == joburl)    \
							.scalar()
					else:
						raise RuntimeError("You need to pass either a JobID or a JobURL")

					yield sess, job
					sess.commit()

					return

				except sqlalchemy.exc.OperationalError:
					self.log.warning("sqlalchemy.exc.OperationalError!")
					sess.rollback()
					if tries > 5:
						traceback.print_exc()

				except sqlalchemy.exc.InvalidRequestError:
					self.log.warning("sqlalchemy.exc.InvalidRequestError!")
					sess.rollback()
					if tries > 5:
						traceback.print_exc()
				except Exception as e:
					print("Exception!")
					print(traceback.print_exc())
					raise e 

def update_rse(rse_id, parameters, session=None):
    """
    Update RSE properties like availability or name.

    :param rse_id: the id of the new rse.
    :param  parameters: A dictionnary with property (name, read, write, delete as keys).
    :param session: The database session in use.

    :raises RSENotFound: If RSE is not found.
    """
    try:
        query = session.query(models.RSE).filter_by(id=rse_id).one()
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE \'%s\' cannot be found' % rse_id)
    availability = 0
    rse = query.rse
    for column in query:
        if column[0] == 'availability':
            availability = column[1] or availability
    param = {}
    availability_mapping = {'availability_read': 4, 'availability_write': 2, 'availability_delete': 1}
    for key in parameters:
        if key == 'name' and parameters['name'] != rse:  # Needed due to wrongly setting name in pre1.22.7 clients
            param['rse'] = parameters['name']
        elif key in ['availability_read', 'availability_write', 'availability_delete']:
            if parameters[key] is True:
                availability = availability | availability_mapping[key]
            else:
                availability = availability & ~availability_mapping[key]
        elif key in ['latitude', 'longitude', 'time_zone', 'rse_type', 'volatile', 'deterministic', 'region_code', 'country_name', 'city', 'staging_area']:
            param[key] = parameters[key]
    param['availability'] = availability
    query.update(param)
    if 'rse' in param:
        add_rse_attribute(rse_id=rse_id, key=parameters['name'], value=True, session=session)
        query = session.query(models.RSEAttrAssociation).filter_by(rse_id=rse_id).filter(models.RSEAttrAssociation.key == rse)
        rse_attr = query.one()
        rse_attr.delete(session=session) 

def get_rse_name(rse_id, session=None, include_deleted=True):
    """
    Get a RSE name or raise if it does not exist.

    :param rse_id: the rse uuid from the database.
    :param session: The database session in use.
    :param include_deleted: Flag to toggle finding rse's marked as deleted.

    :returns: The rse name.

    :raises RSENotFound: If referred RSE was not found in the database.
    """

    if include_deleted:
        cache_key = 'rse-name_{}'.format(rse_id)
        result = REGION.get(cache_key)
        if result != NO_VALUE:
            return result

    try:
        query = session.query(models.RSE.rse).filter_by(id=rse_id)
        if not include_deleted:
            query = query.filter_by(deleted=False)
        result = query.one()[0]
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE with ID \'%s\' cannot be found' % rse_id)

    if include_deleted:
        REGION.set(cache_key, result)
    return result 

def get_rse_id(rse, session=None, include_deleted=True):
    """
    Get a RSE ID or raise if it does not exist.

    :param rse: the rse name.
    :param session: The database session in use.
    :param include_deleted: Flag to toggle finding rse's marked as deleted.

    :returns: The rse id.

    :raises RSENotFound: If referred RSE was not found in the database.
    """

    if include_deleted:
        cache_key = 'rse-id_{}'.format(rse).replace(' ', '.')
        result = REGION.get(cache_key)
        if result != NO_VALUE:
            return result

    try:
        query = session.query(models.RSE.id).filter_by(rse=rse)
        if not include_deleted:
            query = query.filter_by(deleted=False)
        result = query.one()[0]
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE \'%s\' cannot be found' % rse)

    if include_deleted:
        REGION.set(cache_key, result)
    return result 

def update(self, table, features):
        """ Updates a feature with corresponding values. Only properties of the feature
        that have a value will be updated. If geometry is given, it is replaced.

        Args:
          table: The Table to use.
          features: String of GeoJSON features.
        Returns:
            On success an empty dictionary is returned.
            On error, a dictionary with error information is returned.
        """
        if not auth.authorize("write", table):
            return error_message('Unauthorized', status=401)

        #attempt to parse geojson
        try:
            features = geojson.loads(features)['features']
        except ValueError as e:
            return error_message("Unable to parse request data. %s" % (e))

        tbl = self.initialize_table(table)
        primary_key = get_primary_key(tbl)
        if primary_key is None:
            return error_message('Primary key is not defined for table')
        index = None
        feature_id = None
        try:
            connection = self._engine.connect()
            transaction = connection.begin()
            for index, feature in enumerate(features):
                query = tbl.update()

                feature_id = get_feature_id(primary_key.name, feature)
                if feature_id is None:
                    return error_message("No primary key", index=index)

                #Make sure all attributes are columns in the table
                try:
                    verify_attributes(tbl.columns, feature['properties'])
                except ValueError as e:
                    return error_message(e, index=index, feature_id=feature_id)

                query = query.where(primary_key == feature_id)
                del(feature['properties'][primary_key.name])

                if 'geometry' in feature and feature['geometry'] is not None:
                    feature['properties'][self._geometry_field] = feature['geometry']
                query = query.values(feature['properties'])
                connection.execute(query)
            transaction.commit()
        except sqlalchemy.exc.SQLAlchemyError as e:
            transaction.rollback()
            return error_message(("Database error: %s" % e), feature_id=feature_id, index=index)
        return [] 

def create(self, table, features):
        """ Creates new records in table corresponding to the pass GeoJSON features.

        Args:
          table: The Table to use.
          features: String of GeoJSON features.

        Returns:
            On success an empty dictionary is returned.
            On error, a dictionary with error information is returned.
        """
        if not auth.authorize("write", table):
            return error_message('Unauthorized', status=401)

        #attempt to parse geojson
        try:
            features = geojson.loads(features)['features']
        except ValueError as e:
            return error_message("Unable to parse request data. %s" % (e))

        #loads the table schema from the database
        tbl = self.initialize_table(table)
        data = []
        for index, feature in enumerate(features):
            #Make sure all attributes are columns in the table
            try:
                verify_attributes(tbl.columns, feature['properties'])
            except ValueError as e:
                return error_message(e, index=index)

            properties = feature['properties']
            #Add the geometry field
            properties[self._geometry_field] = geomet.wkt.dumps(feature['geometry'])
            data.append(properties)

        try:
            connection = self._engine.connect()
            transaction = connection.begin()
            connection.execute(tbl.insert(), data)
            transaction.commit()
        except sqlalchemy.exc.SQLAlchemyError as e:
            transaction.rollback()
            return error_message("Database error: %s" % e)
        return []