async def get_report_content_all( query: Query = Depends(_get_report_content_query)): try: contentsall = query.all() return contentsall except: raise HTTPException(status_code=400)
async def get_my_reports( query:Query=Depends(get_current_users_reports) ): try: return query.all() except: raise HTTPException(status_code=400)
async def get_my_headers( headers: Query = Depends(get_current_users_header_query) ): try: return headers.all() except: raise HTTPException(status_code=400)
def query_paginate(query: Query, page: int, limit: int) -> Tuple[list, int]: if limit: page_ob = query.paginate(page=page, per_page=limit, error_out=False) result = page_ob.items count = page_ob.total else: result = query.all() count = len(result) return result, count
def get_paginated_result(query: Query, skip: int, limit: int): count = query.order_by(None).count() query = query.offset(skip) if limit >= 0: query = query.limit(limit) return { 'pagination': {'skip': skip, 'limit': limit, 'all_records_count': count}, 'result': query.all(), }
def clone(db: Session, query: Query): source_obj = query.all()[0] db.expunge(source_obj) # expunge the object from session make_transient( source_obj ) # http://docs.sqlalchemy.org/en/rel_1_1/orm/session_api.html#sqlalchemy.orm.session.make_transient delattr(source_obj, 'id') return source_obj
def __get_query_result(self, query: Query) -> Tuple[List[Tuple[Any]], List[str]]: """Fetch query result and returns result and column names of query. Ensures that query result is in form List of Tuples.""" columns_names = self.__get_columns_name(query) result = query.all() if not self.__is_proper_format(result): result = self.__to_list_of_tuples(result) return result, columns_names
def list_query(self, query: Query, **kwargs): _limit = kwargs.pop('limit', None) _offset = kwargs.pop('offset', None) if _limit: query = query.limit(_limit) if _offset: query = query.offset(_offset) logger.debug(f'list_query query: {render_query(query, self.session)}') return query.all()
def get_eligible_facts(self, query: Query, skip: int = None, limit: int = None) -> List[models.Fact]: begin_overall_start = time.time() if skip: query = query.offset(skip) if limit: query = query.limit(limit) facts = query.all() overall_end_time = time.time() overall_total_time = overall_end_time - begin_overall_start logger.info("overall time facts: " + str(overall_total_time)) return facts
def fetch_all(cls, session: Session, limit: int, offset: int, company_id: int = None) -> List['Match']: query = Query(cls, session=session) if company_id: query = query.filter( or_(Match.left_company_id == company_id, Match.right_company_id == company_id)) query = query.limit(limit).offset(offset) return query.all()
def test_get_objects_with_status(self): """Test the method def _get_objects(self, obj_type, status) Test scenario: Get the object when the status is not None """ self.aMox.StubOutWithMock(session, 'query') session.query(model.PowerVCMapping).AndReturn(query) self.aMox.StubOutWithMock(query, 'filter_by') query.filter_by(obj_type=self.powerVCMapping.obj_type, status=self.powerVCMapping.status).AndReturn(query) self.aMox.StubOutWithMock(query, 'all') query.all().AndReturn(self.powerVCMapping) self.aMox.ReplayAll() returnValue = self.powervcagentdb._get_objects( obj_type=self.powerVCMapping.obj_type, status=self.powerVCMapping.status) self.aMox.VerifyAll() self.assertEqual(returnValue, self.powerVCMapping) self.aMox.UnsetStubs()
def test_get_objects_with_status(self): """Test the method def _get_objects(self, obj_type, status) Test scenario: Get the object when the status is not None """ self.aMox.StubOutWithMock(session, 'query') session.query(model.PowerVCMapping).AndReturn(query) self.aMox.StubOutWithMock(query, 'filter_by') query.filter_by(obj_type=self.powerVCMapping.obj_type, status=self.powerVCMapping.status).AndReturn(query) self.aMox.StubOutWithMock(query, 'all') query.all().AndReturn(self.powerVCMapping) self.aMox.ReplayAll() returnValue = self.powervcagentdb._get_objects( obj_type=self.powerVCMapping.obj_type, status=self.powerVCMapping.status) self.aMox.VerifyAll() self.assertEqual(returnValue, self.powerVCMapping) self.aMox.UnsetStubs()
def get_or_404(query: Query, query_type: str = "one"): """ .. todo :: use flask-sqlalchemy's get_or_404 instead :param query: The queryset that should be queried. E.g. ``session.db.query(Model).filter(something=='Foo')`` :param query_type: String identifier for method. Either ``one`` or ``all`` """ try: if query_type == "one": return query.one() elif query_type == "all": return query.all() else: raise ValueError(f"Unexpected type {query_type}. Allowed types: one, all") except NoResultFound: raise NotFound()
def fetch_as_tree(query: Query, relationship: Optional[InstrumentedAttribute] = None, root_value: Union[str, None] = None, ) -> List[DeclarativeMeta]: """Builds the full tree for the given table (adjacency list). This method is efficient in the sense that it issues only one SELECT query to the database. Args: query: A query that selects all relevant model records, that is, all records that make up the adjacency list. For example: `session.query(Album).order_by(Album.nameLC)`. relationship: The parent/child relationship that describes both fields (columns). For example: `Album.parent` If not set, one record is fetched from the query to read its `parent` property. root_value: Parent property's value at the root nodes. Default is None, which commonly retrieves the whole structure. Returns: A list of root nodes with child nodes (the tree) pre-fetched recursively. """ if relationship is None: # Fetch one record to discover the parent relationship. relationship = next(iter(query)).__class__.parent parent_field = relationship.expression.right.name child_field = relationship.expression.left.name back_populates = relationship.property.back_populates nodes = query.all() children = defaultdict(list) for node in nodes: children[getattr(node, parent_field)].append(node) for node in nodes: set_committed_value(node, back_populates, children[getattr(node, child_field)]) return children[root_value]
def get_all(instance: Query, offset: int=None, limit: int=None, filters: list=()) -> list: """ Gets all instances of the query instance :param instance: sqlalchemy queriable :param offset: Offset for request :param limit: Limit for request :param filters: Filters and OrderBy Clauses """ for expression in filters: if _is_ordering_expression(expression): instance = instance.order_by(expression) else: instance = instance.filter(expression) if offset is not None: instance = instance.offset(offset) if limit is not None: instance = instance.limit(limit) return instance.all()
def execute_query(self, query: Query, use_list=True, count=False): """ Executes a new database query and return its result. :param query: The SQLAlchemy query object :param use_list: Flag to specify if want the returned elements as a list or not :param count: Flag to specify if we only want to count the rows that match the query :return: The query result """ try: if self._session_object is not None: query = query.with_session(self._session_object) if count: return query.count() elif use_list: return query.all() else: return query.first() except exc.SQLAlchemyError as e: self.app.logger.error( "Can't execute the requested query. Details: %s", str(e)) raise DBInternalError("Can't execute the requested query")
def apply_filter_from_string(model, query: Query, filters: Sequence[str]): for f in filters: as_lower = f.lower() if f == "": pass elif as_lower.startswith("order by"): attribute_name = as_lower.replace("order by", "") descending = False if attribute_name.endswith("desc") or attribute_name.endswith( "descending"): descending = True if attribute_name.endswith("desc"): attribute_name = attribute_name.replace("desc", "") else: attribute_name = attribute_name.replace("descending", "") to_order_on = getattr(model, attribute_name.strip()) if descending: to_order_on = to_order_on.desc() query = query.order_by(to_order_on) elif as_lower == "first": return query.first() elif as_lower.startswith("limit"): to_limit = as_lower.replace("limit", "") query = query.limit(int(to_limit.strip())) else: raise NotImplementedError(f"Have not implemented filter: {f}") return query.all()
def orm_get_rows(self, table_name, filter_text=None, distinct=False, delete=False): """ Returns all rows from selected columns in a table, provides options to filter your query and return only distinct values. Parameters ---------- table_name: str | DeclarativeMeta The table name you want to search within. Alternatively an ORM model may be passed directly. filter_text: str | dict Text that you want to filter source by. Allows a dict of multiple filter texts to be passed. distinct: bool True indicates you only want distinct source without duplicates. delete : bool True indicates you want to delete all returned rows from the target table. Returns ---------- source : list """ self.session = Session(bind=self.sa_engine) if type(table_name) == str: table_model = self.reflect_database_table(table_name=table_name) else: table_model = table_name if filter_text is None: if distinct: results = Query(table_model, self.session).distinct().all() else: results = Query(table_model, self.session).all() elif type(filter_text) == dict: query = Query(table_model, self.session) for attr, value in filter_text.items(): if value == '': pass else: query = Query(table_model, self.session).filter( getattr(table_model, attr) == value, ) if distinct: results = query.distinct().all() else: results = query.all() elif type(filter_text) == BinaryExpression: if distinct: results = Query( table_model, self.session).filter(filter_text).distinct().all() else: results = Query(table_model, self.session).filter(filter_text).all() else: if distinct: results = Query( table_model, self.session).filter(filter_text).distinct().all() else: results = Query(table_model, self.session).filter(filter_text).all() if delete and len(results) > 0: print( f'Attempting to delete {len(results)} from {table_name.name}.') try: if filter_text is None: Query(table_model, self.session).delete(synchronize_session=False) else: Query(table_model, self.session).filter(filter_text).delete( synchronize_session=False) self.session.commit() return except Exception as e: print( f'ERROR:: The delete operation was unsuccessful, operation aborted.' ) self.session.rollback() raise DbObjectError(e) return results
def entity_search_execute_orm(query: Query): """ Execute query :param query: query to execute """ return query.all()
query.filter(text("id<10")) # use textual SQL (query.filter(text("id<:value and name=:name")) # bind parameters .params(value=10, name='hello')) # other option query.order_by(User.id) query.distinct() query.limit() query.offset() # emit query and get result ################################# # the select Query instance will not emit a query until the # following method invoked query.all() # fetch all as list query.first() # fetch one or None query.one() # fetch one or NoResultFound or MultipleResultsFound query.one_or_none() # fetch one or None or MultipleResultsFound query.scalar() # invokes the one() method and return the first column result = query[1:3] # list with limit and offset # batch operator ######################################### # batch update and delete will emit the query immediately, # return the number of row that changed. changed_number = (Query(User) .filter_by(name='a') .update({User.name: 'b'}, synchronize_session=False)) delete_number = Query(User).filter_by(ID=6).delete()
def all(self, query_builder: Query) -> List[Base]: return query_builder.all()
def _query_with_filters( response: Response, query: Query, range: Optional[List[int]] = None, sort: Optional[List[str]] = None, filters: Optional[List[str]] = None, ) -> List: if filters is not None: for filter in chunked(filters, 2): if filter and len(filter) == 2: field = filter[0] value = filter[1] value_as_bool = value.lower() in ("yes", "y", "ye", "true", "1", "ja", "insync") if value is not None: if field.endswith("_gt"): query = query.filter( SubscriptionTable.__dict__[field[:-3]] > value) elif field.endswith("_gte"): query = query.filter( SubscriptionTable.__dict__[field[:-4]] >= value) elif field.endswith("_lte"): query = query.filter( SubscriptionTable.__dict__[field[:-4]] <= value) elif field.endswith("_lt"): query = query.filter( SubscriptionTable.__dict__[field[:-3]] < value) elif field.endswith("_ne"): query = query.filter( SubscriptionTable.__dict__[field[:-3]] != value) elif field == "insync": query = query.filter( SubscriptionTable.insync.is_(value_as_bool)) elif field == "tags": # For node and port selector form widgets sub_values = value.split("-") query = query.filter( func.lower(ProductTable.tag).in_( [s.lower() for s in sub_values])) elif field == "tag": # For React table 7 sub_values = value.split("-") query = query.filter( func.lower(ProductTable.tag).in_( [s.lower() for s in sub_values])) elif field == "product": sub_values = value.split("-") query = query.filter( func.lower(ProductTable.name).in_( [s.lower() for s in sub_values])) elif field == "status": # For React table 7 statuses = value.split("-") query = query.filter( SubscriptionTable.status.in_( [s.lower() for s in statuses])) elif field == "statuses": # For port subscriptions sub_values = value.split("-") query = query.filter( SubscriptionTable.status.in_( [s.lower() for s in sub_values])) elif field == "organisation": try: value_as_uuid = UUID(value) except (ValueError, AttributeError): msg = "Not a valid customer_id, must be a UUID: '{value}'" logger.exception(msg) raise_status(HTTPStatus.BAD_REQUEST, msg) query = query.filter( SubscriptionTable.customer_id == value_as_uuid) elif field == "tsv": logger.debug("Running full-text search query.", value=value) query = query.search(value) elif field in SubscriptionTable.__dict__: query = query.filter( cast(SubscriptionTable.__dict__[field], String).ilike("%" + value + "%")) if sort is not None and len(sort) >= 2: for item in chunked(sort, 2): if item and len(item) == 2: if item[0] in ["product", "tag"]: field = "name" if item[0] == "product" else "tag" if item[1].upper() == "DESC": query = query.order_by( expression.desc(ProductTable.__dict__[field])) else: query = query.order_by( expression.asc(ProductTable.__dict__[field])) else: if item[1].upper() == "DESC": query = query.order_by( expression.desc( SubscriptionTable.__dict__[item[0]])) else: query = query.order_by( expression.asc( SubscriptionTable.__dict__[item[0]])) if range is not None and len(range) == 2: try: range_start = int(range[0]) range_end = int(range[1]) if range_start >= range_end: raise ValueError("range start must be lower than end") except (ValueError, AssertionError): msg = "Invalid range parameters" logger.exception(msg) raise_status(HTTPStatus.BAD_REQUEST, msg) total = query.count() query = query.slice(range_start, range_end) response.headers[ "Content-Range"] = f"subscriptions {range_start}-{range_end}/{total}" return query.all()
def _query_with_filters( response: Response, model: BaseModel, query: Query, range: Optional[List[int]] = None, sort: Optional[List[str]] = None, filters: Optional[List[str]] = None, ) -> List: if filters is not None: for filter in chunked(filters, 2): if filter and len(filter) == 2: field = filter[0] value = filter[1] value_as_bool = value.lower() in ( "yes", "y", "ye", "true", "1", "ja", "insync", ) if value is not None: if field.endswith("_gt"): query = query.filter( model.__dict__[field[:-3]] > value) elif field.endswith("_gte"): query = query.filter( model.__dict__[field[:-4]] >= value) elif field.endswith("_lte"): query = query.filter( model.__dict__[field[:-4]] <= value) elif field.endswith("_lt"): query = query.filter( model.__dict__[field[:-3]] < value) elif field.endswith("_ne"): query = query.filter( model.__dict__[field[:-3]] != value) elif field == "tsv": logger.debug("Running full-text search query.", value=value) query = query.search(value) elif field in model.__dict__: query = query.filter( cast(model.__dict__[field], String).ilike("%" + value + "%")) if sort is not None and len(sort) >= 2: for sort in chunked(sort, 2): if sort and len(sort) == 2: if sort[1].upper() == "DESC": query = query.order_by( expression.desc(model.__dict__[sort[0]])) else: query = query.order_by( expression.asc(model.__dict__[sort[0]])) if range is not None and len(range) == 2: try: range_start = int(range[0]) range_end = int(range[1]) assert range_start < range_end except (ValueError, AssertionError): msg = "Invalid range parameters" logger.exception(msg) raise_status(HTTPStatus.BAD_REQUEST, msg) total = query.count() query = query.slice(range_start, range_end) response.headers[ "Content-Range"] = f"items {range_start}-{range_end}/{total}" return query.all()
def q_all_tq(query: Query): count = query.count() all = query.all() tq = tqdm(all, total=count, smoothing=0.05) return tq
def make_flashcard_run(endpoint: str, query: Query, sorting: FlashcardSorting = FlashcardSorting.NONE, start_at_word_id: Optional[SupportsInt] = None) \ -> Response: """Uses a query to fetch a set of words from the database, and redirects to the correct endpoint. Parameters ---------- endpoint : str Endpoint to be redirected to after words have been fetched. query : sqlalchemy.orm.Query Query used to retrieve words from the database. sorting : FlashcardSorting In what order should the words be presented. See documentation for FlashcardSorting for more information. start_at_word_id : int | str | None ID of the word to display first. If not specified, start with the first word available. If you specify some integer, make sure the word with that ID is actually present among the words retrieved by the input query! Returns ------- werkzeug.Response """ words = [word for word in query.all()] if not words: app.logger.error("Query %s returned no words!", query) return redirect(url_for("home.index")) if sorting is FlashcardSorting.NONE: app.logger.debug("No sorting required") elif sorting is FlashcardSorting.ALPHABETICAL: app.logger.debug("Alphabetical sorting required") words.sort(key=lambda word: remove_niqqudot(word.hebrew)) elif sorting is FlashcardSorting.SHUFFLE: app.logger.debug("Random sorting (shuffling) required") random.shuffle(words) else: raise ValueError words_ids = [word.id for word in words] try: start_index = words_ids.index(int(start_at_word_id)) # type: ignore except TypeError: # start_at_word_id is None start_index = 0 except ValueError: # start_at_word_id is int-castable app.logger.error( "Could not find word with desired ID %i " "among words queried from the database", start_at_word_id) return redirect(url_for("home.index")) # Set word list and reset other state FlashcardRunState( words=words_ids, index=start_index, side=FlashcardSide.prompt_side(), progress=[0, len(words)], ).to_flask_session() return redirect(url_for(endpoint))
def list_people(qs: Query = Depends(get_queryset)): return qs.all()
async def get_content_list(query: Query = Depends(_get_my_content_query)): try: return query.all() except: raise HTTPException(status_code=400)