def test_removing_from_cluster(self): self.env.create(cluster_kwargs={}, nodes_kwargs=[{"role": "controller"}]) node_db = self.env.nodes[0] node2_db = self.env.create_node() objects.Node.remove_from_cluster(node_db) self.assertEqual(node_db.cluster_id, None) self.assertEqual(node_db.roles, []) self.assertEqual(node_db.pending_roles, []) exclude_fields = [ "group_id", "id", "hostname", "fqdn", "mac", "meta", "name", "agent_checksum", "uuid", "timestamp", "nic_interfaces", "attributes", ] fields = set(c.key for c in sqlalchemy_inspect(objects.Node.model).attrs) - set(exclude_fields) for f in fields: self.assertEqual(getattr(node_db, f), getattr(node2_db, f))
def has_translation(self, language: str) -> 'bool': if not language: raise ValueError('language') if sqlalchemy_inspect(self).transient: raise ValueError('Translatable is transient and cannot have translations') if language == self.original_language: return True return db.session.query(Translation.query.filter_by(translatable_id=self.id, language=language).exists()).scalar()
def _merge_task_properties_and_drm_options(self, task, drm_options): drm_options = dict(drm_options) task_state = sqlalchemy_inspect(task) for drm_option_name, task_property in self.drm_options_to_task_properties.iteritems( ): task_value = task_state.attrs[task_property.key].value if task_value: drm_options[drm_option_name] = task_value return drm_options
def add_translation(self, language: str, text: str, provider: str = None) -> 'Translation': if sqlalchemy_inspect(self).transient: raise ValueError('Translatable is transient and cannot have translations') if language == self.original_language: return self._get_dummy_translation() translation = Translation.query.filter_by(translatable_id=self.id, language=language).first() if translation is None: translation = Translation(self.id, language, text, provider) db.session.add(translation) return translation
def row_to_dict(r, keep_relationships = False): '''Converts an SQLAlchemy record to a Python dict. We assume that _sa_instance_state exists and is the only value we do not care about. If DeclarativeBase is passed then all DeclarativeBase objects (e.g. those created by relationships) are also removed. ''' d = {} if not keep_relationships: # only returns the table columns t = r.__table__ for c in [c.name for c in list(sqlalchemy_inspect(t).columns)]: d[c] = getattr(r, c) return d else: # keeps all objects including those of type DeclarativeBase or InstrumentedList and the _sa_instance_state object return copy.deepcopy(r.__dict__)
def _merge_task_properties_and_drm_options(self, task, drm_options): drm_options = dict(drm_options) task_state = sqlalchemy_inspect(task) for drm_option_name, task_mapping in list(self.drm_options_to_task_properties.items()): if callable(task_mapping): task_value = task_mapping(task) else: task_value = task_state.attrs[task_mapping.key].value if task_value: # Translate cosmos memory requirements (in Kilobytes) to k8s-jobs memory (in bytes) if drm_option_name == "memory": drm_options[drm_option_name] = str(task_value) + "K" else: drm_options[drm_option_name] = task_value return drm_options
def __init__(self, logger, settings=None, globalSettings=None, env=None, poolSize=35, maxOverflow=5, poolRecycle=1800): """Constructor for the database client connection. Arguments: logger : handler for the database log settings (json) : database connection parameters globalSettings (json) : global settings env : module containing the env paths poolSize (int) : pool_size param for sqlalchemy.create_engine() maxOverflow (int) : max_overflow param for sqlalchemy.create_engine() poolRecycle (int) : pool_recycle param for sqlalchemy.create_engine() """ try: self.session = None self.settings = settings if self.settings is None: self.settings = {} ## Load an external database library for the wrapped connection; ## the provided library must contain a 'createEngine' function that ## takes a previously created dictionary with the database ## settings (if empty, it will be filled), the globalSettings which ## contains named references to modules in the external directory, ## and parameters to use in the call for sqlalchemy.create_engine(). externalLibrary = utils.loadExternalLibrary('externalDatabaseLibrary', env, globalSettings) self.engine = externalLibrary.createEngine(self.settings, globalSettings, poolSize, maxOverflow, poolRecycle) ## Validate we are actually talking to the DB engineInspector = sqlalchemy_inspect(self.engine) schemas = engineInspector.get_schema_names() if schemas is None or len(schemas) <= 0: raise EnvironmentError('Could not connect to database') logger.debug('Schemas found from connection pool inspection: {}'.format(str(schemas))) self.createScopedSession(logger) except exc.OperationalError: ## Intentionally catch database connection errors logger.error('Exception in DatabaseClient: {}'.format(str(sys.exc_info()[1]))) raise except: stacktrace = traceback.format_exception(sys.exc_info()[0], sys.exc_info()[1], sys.exc_info()[2]) logger.error('Exception in DatabaseClient: {}'.format(str(stacktrace))) raise
def get_translation(self, language: str, translator: 'TranslationService' = None) -> 'Translation': if not language: raise ValueError('language expected (got {})'.format(language)) if translator is not None and not isinstance(translator, TranslationService): raise expected_or_none('translator', translator, TranslationService) if sqlalchemy_inspect(self).transient: raise ValueError('Translatable is transient and cannot have translations') if language == self.original_language: return self._get_dummy_translation() translation = Translation.query.filter_by(translatable_id=self.id, language=language).first() if translation is None: if translator is None: raise ValueError('Cannot translate without translator function') translation_text = translator.translate(self.original_text, self.original_language, language) translation = self.add_translation(language, translation_text, translator.identifier) return translation
def create_query(session, model, search_params, _ignore_order_by=False): """Builds an SQLAlchemy query instance based on the search parameters present in ``search_params``, an instance of :class:`SearchParameters`. This method returns a SQLAlchemy query in which all matched instances meet the requirements specified in ``search_params``. `model` is SQLAlchemy declarative model on which to create a query. `search_params` is an instance of :class:`SearchParameters` which specify the filters, order, limit, offset, etc. of the query. If `_ignore_order_by` is ``True``, no ``order_by`` method will be called on the query, regardless of whether the search parameters indicate that there should be an ``order_by``. (This is used internally by Flask-Restless to work around a limitation in SQLAlchemy.) Building the query proceeds in this order: 1. filtering 2. ordering 3. grouping 3. limiting 4. offsetting Raises one of :exc:`AttributeError`, :exc:`KeyError`, or :exc:`TypeError` if there is a problem creating the query. See the documentation for :func:`_create_operation` for more information. """ if search_params.group_by: select_fields = [func.count()] for groupby in search_params.group_by: select_fields.append(getattr(model, groupby.field)) query = session.query(*select_fields) else: query = session.query(model) # For the sake of brevity, rename this method. create_filt = QueryBuilder._create_filter # This function call may raise an exception. valid_model_fields = [ str(algo).split('.')[1] for algo in sqlalchemy_inspect(model).attrs ] filters = [] for filt in search_params.filters: if not getattr(filt, 'fieldname', False) or filt.fieldname in valid_model_fields: try: filters.append(create_filt(model, filt)) except AttributeError: # Can't create the filter since the model or submodel does not have the attribute (usually mapper) pass # Multiple filter criteria at the top level of the provided search # parameters are interpreted as a conjunction (AND). query = query.filter(*filters) # Order the search. If no order field is specified in the search # parameters, order by primary key. if not _ignore_order_by: if search_params.order_by: joined_models = set() for val in search_params.order_by: field_name = val.field if '__' in field_name: field_name, field_name_in_relation = \ field_name.split('__') relation = getattr(model, field_name) relation_model = relation.mapper.class_ field = getattr(relation_model, field_name_in_relation) direction = getattr(field, val.direction) if relation_model not in joined_models: query = query.join(relation_model) joined_models.add(relation_model) query = query.order_by(direction()) else: field = getattr(model, val.field) direction = getattr(field, val.direction) query = query.order_by(direction()) else: if not search_params.group_by: pks = primary_key_names(model) pk_order = (getattr(model, field).asc() for field in pks) query = query.order_by(*pk_order) # Group the query. if search_params.group_by: for groupby in search_params.group_by: field = getattr(model, groupby.field) query = query.group_by(field) # Apply limit and offset to the query. if search_params.limit: query = query.limit(search_params.limit) if search_params.offset: query = query.offset(search_params.offset) return query
def get_or_create_in_transaction(tsession, model, values, missing_columns = [], variable_columns = [], updatable_columns = [], only_use_supplied_columns = False, read_only = False): ''' Uses the SQLAlchemy model to retrieve an existing record based on the supplied field values or, if there is no existing record, to create a new database record. :param tsession: An SQLAlchemy transactioned session :param model: The name of the SQLAlchemy class representing the table :param values: A dict of values which will be used to populate the fields of the model :param missing_columns: Elements of missing_columns are expected to be fields in the model but are left blank regardless of whether they exist in values. This is useful for auto_increment fields. :param updatable_columns: If these are specified, they are treated as missing columns in the record matching and if a record is found, these fields will be updated :param variable_columns: If these are specified, they are treated as missing columns in the record matching but are not updated. A good use of these are for datetime fields which default to the current datetime :param read_only: If this is set then we query the database and return an instance if one exists but we do not create a new record. :return: Note: This function is a convenience function and is NOT efficient. The "tsession.query(model).filter_by(**pruned_values)" call is only (sometimes) efficient if an index exists on the keys of pruned_values. If any of the fields of pruned_values are large (even if otherwise deferred/loaded lazily) then you will incur a performance hit on lookup. You may need to reconsider any calls to this function in inner loops of your code.''' values = copy.deepcopy(values) # todo: this does not seem to be necessary since we do not seem to be writing fieldnames = [c.name for c in list(sqlalchemy_inspect(model).columns)] for c in missing_columns: fieldnames.remove(c) for c in updatable_columns: fieldnames.remove(c) for c in variable_columns: if c in fieldnames: fieldnames.remove(c) if only_use_supplied_columns: fieldnames = sorted(set(fieldnames).intersection(set(values.keys()))) else: unexpected_fields = set(values.keys()).difference(set(fieldnames)).difference(set(variable_columns)).difference(set(updatable_columns)) if unexpected_fields: raise Exception("The fields '{0}' were passed but not found in the schema for table {1}.".format("', '".join(sorted(unexpected_fields)), model.__dict__['__tablename__'])) pruned_values = {} for k in set(values.keys()).intersection(set(fieldnames)): v = values[k] pruned_values[k] = v instance = tsession.query(model).filter_by(**pruned_values) if instance.count() > 1: raise Exception('Multiple records were found with the search criteria.') instance = instance.first() if instance: if read_only == False: for c in updatable_columns: setattr(instance, c, values[c]) tsession.flush() return instance else: if read_only == False: if sorted(pruned_values.keys()) != sorted(fieldnames): # When adding new records, we require that all necessary fields are present raise Exception('Some required fields are missing: {0}. Either supply these fields or add them to the missing_columns list.'.format(set(fieldnames).difference(pruned_values.keys()))) instance = model(**pruned_values) tsession.add(instance) tsession.flush() return instance return None
def object_session(self): return sqlalchemy_inspect(self).session
def create_query(session, model, search_params, _ignore_order_by=False): """Builds an SQLAlchemy query instance based on the search parameters present in ``search_params``, an instance of :class:`SearchParameters`. This method returns a SQLAlchemy query in which all matched instances meet the requirements specified in ``search_params``. `model` is SQLAlchemy declarative model on which to create a query. `search_params` is an instance of :class:`SearchParameters` which specify the filters, order, limit, offset, etc. of the query. If `_ignore_order_by` is ``True``, no ``order_by`` method will be called on the query, regardless of whether the search parameters indicate that there should be an ``order_by``. (This is used internally by Flask-Restless to work around a limitation in SQLAlchemy.) Building the query proceeds in this order: 1. filtering 2. ordering 3. grouping 3. limiting 4. offsetting Raises one of :exc:`AttributeError`, :exc:`KeyError`, or :exc:`TypeError` if there is a problem creating the query. See the documentation for :func:`_create_operation` for more information. """ # TODO: Esto no se puede hacer abajo con el group by? joined_models = set() query = session.query(model) if search_params.group_by: select_fields = [func.count()] for groupby in search_params.group_by: field_name = groupby.field if '__' in field_name: field_name, field_name_in_relation = field_name.split('__') relation = getattr(model, field_name) relation_model = relation.mapper.class_ field = getattr(relation_model, field_name_in_relation) if relation_model not in joined_models: if relation_model == User: query = query.join( relation_model, model.creator_id == relation_model.id) else: query = query.join(relation_model) joined_models.add(relation_model) select_fields.append(field) else: select_fields.append(getattr(model, groupby.field)) query = query.with_entities(*select_fields) # This function call may raise an exception. valid_model_fields = [] for orm_descriptor in sqlalchemy_inspect(model).all_orm_descriptors: if isinstance(orm_descriptor, InstrumentedAttribute): valid_model_fields.append(str(orm_descriptor).split('.')[1]) if isinstance(orm_descriptor, hybrid_property): valid_model_fields.append(orm_descriptor.__name__) valid_model_fields += [ str(algo).split('.')[1] for algo in sqlalchemy_inspect(model).relationships ] filters_generator = map( # pylint: disable=W1636 QueryBuilder.create_filters_func(model, valid_model_fields), search_params.filters) filters = [filt for filt in filters_generator if filt is not None] # Multiple filter criteria at the top level of the provided search # parameters are interpreted as a conjunction (AND). query = query.filter(*filters) # Order the search. If no order field is specified in the search # parameters, order by primary key. if not _ignore_order_by: if search_params.order_by: for val in search_params.order_by: field_name = val.field if '__' in field_name: field_name, field_name_in_relation = field_name.split( '__') relation = getattr(model, field_name) relation_model = relation.mapper.class_ field = getattr(relation_model, field_name_in_relation) direction = getattr(field, val.direction) if relation_model not in joined_models: query = query.join(relation_model, isouter=True) joined_models.add(relation_model) query = query.order_by(direction()) else: field = getattr(model, val.field) direction = getattr(field, val.direction) query = query.order_by(direction()) else: if not search_params.group_by: pks = primary_key_names(model) pk_order = (getattr(model, field).asc() for field in pks) query = query.order_by(*pk_order) # Group the query. if search_params.group_by: for groupby in search_params.group_by: field_name = groupby.field if '__' in field_name: field_name, field_name_in_relation = field_name.split('__') relation = getattr(model, field_name) relation_model = relation.mapper.class_ field = getattr(relation_model, field_name_in_relation) else: field = getattr(model, groupby.field) query = query.group_by(field) # Apply limit and offset to the query. if search_params.limit: query = query.limit(search_params.limit) if search_params.offset: query = query.offset(search_params.offset) return query