def sample_n_rows_with_repeat(query: Query, num_of_rows: int) -> Iterator[VocabularyTerm]: out = [] count = query.count() for _ in range(num_of_rows): row = query.offset(math.floor(random.random() * count)).first() out.append(row) return out
def response(self, query: orm.Query) -> PaginatedResponse: count = query.count() self._response.headers.update(self.headers(query, count)) return { "results": list(self.paginate(query, count)), "count": count, }
def paginate(model, query: Query, params: Optional[PaginationParams]) -> BasePage: code = '200' message = 'Success' try: total = query.count() if params.order: direction = desc if params.order == 'desc' else asc data = query.order_by(direction(getattr(model, params.sort_by))) \ .limit(params.page_size)\ .offset(params.page_size * params.page) \ .all() else: data = query.limit(params.page_size).offset(params.page_size * params.page).all() metadata = MetadataSchema(current_page=params.page, page_size=params.page_size, total_items=total) except Exception as e: raise CustomException(http_code=500, code='500', message=str(e)) return PageType.get().create(code, message, data, metadata)
def paginate(query: Query, params: Optional[AbstractParams] = None) -> AbstractPage: params = resolve_params(params) total = query.count() items = paginate_query(query, params).all() return create_page(items, total, params)
def paginate_mods(mods: Query, page_size: int = 30) -> Tuple[List[Mod], int, int]: total_pages = math.ceil(mods.count() / page_size) page = get_page() if page > total_pages: page = total_pages if page < 1: page = 1 return mods.offset(page_size * (page - 1)).limit(page_size), page, total_pages
def paginate(query: Query, page: int, per_page: int, include_total=False): start = (page - 1) * per_page count = query.count() query = query.slice(start, start + per_page) if not include_total: return query return query, ceil(count / per_page)
def test_3_queries(self): session = self.get_session() p = session.query(User).first() print(f"{p.name}") all_rec = [rec for rec in session.query(Channel).all()] self.assertEqual(len(all_rec), 3) # # Check the Join table is there # all_user = [rec for rec in session.query(UserChannel).all()] self.assertEqual(len(all_user), 3) # Query with no Join ... Bad result q = Query([User, Channel, UserChannel], session=session) self.assertEqual(q.count(), 18) q = Query([User, Channel, UserChannel], session=session). \ filter(User.user_id == UserChannel.user_id). \ filter(Channel.channel_id == UserChannel.channel_id) self.assertEqual(q.count(), 3)
def sample_n_rows_no_repeat(query: Query, num_of_rows: int) -> Iterable[VocabularyTerm]: # Assumption: primary key out = {} count = query.count() pct = num_of_rows / count while len(out) < num_of_rows: sample = query.filter(func.random() < pct) for row in sample: out[row.id] = row if len(out) >= num_of_rows: break return out.values()
def cancel_expired_bookings(query: Query, batch_size: int = 500): expiring_bookings_count = query.count() logger.info("[cancel_expired_bookings] %d expiring bookings to cancel", expiring_bookings_count) if expiring_bookings_count == 0: return updated_total = 0 expiring_booking_ids = bookings_repository.find_expiring_booking_ids_from_query( query).limit(batch_size).all() max_id = expiring_booking_ids[-1][0] # we commit here to make sure there is no unexpected objects in SQLA cache before the update, # as we use synchronize_session=False db.session.commit() while expiring_booking_ids: updated = (Booking.query.filter(Booking.id <= max_id).filter( Booking.id.in_(expiring_booking_ids)).update( { "isCancelled": True, "status": BookingStatus.CANCELLED, "cancellationReason": BookingCancellationReasons.EXPIRED, "cancellationDate": datetime.datetime.utcnow(), }, synchronize_session=False, )) # Recompute denormalized stock quantity stocks_to_recompute = [ row[0] for row in db.session.query(Booking.stockId).filter( Booking.id.in_(expiring_booking_ids)).distinct().all() ] recompute_dnBookedQuantity(stocks_to_recompute) db.session.commit() updated_total += updated expiring_booking_ids = bookings_repository.find_expiring_booking_ids_from_query( query).limit(batch_size).all() if expiring_booking_ids: max_id = expiring_booking_ids[-1][0] logger.info( "[cancel_expired_bookings] %d Bookings have been cancelled in this batch", updated, ) logger.info( "[cancel_expired_bookings] %d Bookings have been cancelled", updated_total, )
def paginate_query(query: Query, page: int = 1, page_count: Optional[int] = None) -> (list, int, int): if page < 0 or (page_count and page_count < 0): raise ValueError('page and page count values should be non-negative') if page_count is None or page == page_count: title_count = query.count() page_count = 1 + (title_count - 1) // PAGE_SIZE page = min(page, page_count) if page_count == 0: return [], page, page_count last_index = page * PAGE_SIZE first_index = last_index - PAGE_SIZE titles = query[first_index:last_index] if not titles: return paginate_query(query, page) return titles, page, page_count
def _fetch_dag_runs( query: Query, *, end_date_gte: Optional[str], end_date_lte: Optional[str], execution_date_gte: Optional[str], execution_date_lte: Optional[str], start_date_gte: Optional[str], start_date_lte: Optional[str], limit: Optional[int], offset: Optional[int], order_by: str, ) -> Tuple[List[DagRun], int]: if start_date_gte: query = query.filter(DagRun.start_date >= start_date_gte) if start_date_lte: query = query.filter(DagRun.start_date <= start_date_lte) # filter execution date if execution_date_gte: query = query.filter(DagRun.execution_date >= execution_date_gte) if execution_date_lte: query = query.filter(DagRun.execution_date <= execution_date_lte) # filter end date if end_date_gte: query = query.filter(DagRun.end_date >= end_date_gte) if end_date_lte: query = query.filter(DagRun.end_date <= end_date_lte) total_entries = query.count() to_replace = {"dag_run_id": "run_id"} allowed_filter_attrs = [ "id", "state", "dag_id", "execution_date", "dag_run_id", "start_date", "end_date", "external_trigger", "conf", ] query = apply_sorting(query, order_by, to_replace, allowed_filter_attrs) return query.offset(offset).limit(limit).all(), total_entries
def create_paginated_response( self, items_per_page: int, query: Query, schema: SchemaMeta, page_index: Optional[int] = None, start_index: Optional[int] = None, ) -> dict: # Validate assert isinstance(query, Query) assert issubclass(schema, BaseSchema) assert isinstance(items_per_page, int) total_items = query.count() start_index, stop_index, page_index, total_pages = self._get_pagination_params( page_index, start_index, items_per_page, total_items) assert isinstance(start_index, int) # Slice items = query.slice(start_index - 1, stop_index) current_item_count = items.count() # Serialize items = schema(many=True).dump(items) # Prepare result data = dict( items_per_page=items_per_page, current_item_count=current_item_count, page_index=page_index, start_index=start_index, total_items=total_items, total_pages=total_pages, items=items, ) response = self._create_response(data=data) return APIPaginatedResponseSchema().dump(response)
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 paginated_update( query: Query, print_page_progress: Optional[Union[Callable[[int, int], None], bool]] = None, batch_size: int = DEFAULT_BATCH_SIZE, ) -> Iterator[Any]: """ Update models in small batches so we don't have to load everything in memory. """ start = 0 count = query.count() session: Session = inspect(query).session if print_page_progress is None or print_page_progress is True: print_page_progress = lambda current, total: print( f" {current}/{total}", end="\r") while start < count: end = min(start + batch_size, count) for obj in query[start:end]: yield obj session.merge(obj) session.commit() if print_page_progress: print_page_progress(end, count) start += batch_size
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 get_count(self, query:Query): """Calculate total item count based on query.""" return query.count()
def q_all_tq(query: Query): count = query.count() all = query.all() tq = tqdm(all, total=count, smoothing=0.05) return tq
def query_tq(query: Query): count = query.count() iterator = query_iterator(query) tq = tqdm(iterator, total=count, smoothing=0.05) return tq
def _paginated_query( self, query: Query, cursor: Optional[HistoryCursor], limit: Optional[int], ) -> PaginatedHistory[TokenChangeHistoryEntry]: """Run a paginated query (one with a limit or a cursor).""" limited_query = query # Apply the cursor, if there is one. if cursor: limited_query = self._apply_cursor(limited_query, cursor) # When retrieving a previous set of results using a previous # cursor, we have to reverse the sort algorithm so that the cursor # boundary can be applied correctly. We'll then later reverse the # result set to return it in proper forward-sorted order. if cursor and cursor.previous: limited_query = limited_query.order_by( TokenChangeHistory.event_time, TokenChangeHistory.id ) else: limited_query = limited_query.order_by( TokenChangeHistory.event_time.desc(), TokenChangeHistory.id.desc(), ) # Grab one more element than the query limit so that we know whether # to create a cursor (because there are more elements) and what the # cursor value should be (for forward cursors). if limit: limited_query = limited_query.limit(limit + 1) # Execute the query twice, once to get the next bach of results and # once to get the count of all entries without pagination. entries = limited_query.all() count = query.count() # Calculate the cursors, remove the extra element we asked for, and # reverse the results again if we did a reverse sort because we were # using a previous cursor. prev_cursor = None next_cursor = None if cursor and cursor.previous: if limit: next_cursor = HistoryCursor.invert(cursor) if len(entries) > limit: prev_cursor = self._build_prev_cursor(entries[limit - 1]) entries = entries[:limit] entries.reverse() elif limit: if cursor: prev_cursor = HistoryCursor.invert(cursor) if len(entries) > limit: next_cursor = self._build_next_cursor(entries[limit]) entries = entries[:limit] # Return the results. return PaginatedHistory[TokenChangeHistoryEntry]( entries=[TokenChangeHistoryEntry.from_orm(e) for e in entries], count=count, prev_cursor=prev_cursor, next_cursor=next_cursor, )
def get_count(self, query:Query): """Calculate total item count based on query.""" return query.count()
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()