def count_status_by_user(nlp_task_id, current_user: CurrentUser): # compose query q = session.query(DocType) \ .join(MarkJob, DocType.doc_type_id == MarkJob.doc_type_id) \ .join(MarkTask, MarkJob.mark_job_id == MarkTask.mark_job_id) \ .filter(DocType.nlp_task_id == nlp_task_id, ~DocType.is_deleted, ~MarkJob.is_deleted, ~MarkTask.is_deleted) # filter by user if current_user.user_role in [ RoleEnum.manager.value, RoleEnum.guest.value ]: q = q.filter(DocType.group_id.in_(current_user.user_groups)) elif current_user.user_role in [RoleEnum.reviewer.value]: q = q.filter( func.json_contains(MarkJob.reviewer_ids, str(current_user.user_id))) elif current_user.user_role in [RoleEnum.annotator.value]: q = q.filter( func.json_contains(MarkJob.annotator_ids, str(current_user.user_id))) # get grouped (doc_type_id, mark_job_id, count) list all_status = q.group_by(MarkJob.doc_type_id, MarkJob.mark_job_id) \ .with_entities(MarkJob.doc_type_id, MarkJob.mark_job_id, func.count(MarkTask.mark_task_id)).all() # filter >= labeled status q = q.filter(MarkTask.mark_task_status >= int(StatusEnum.labeled)) # get grouped (doc_type_id, mark_job_id, >= labeled count) list all_finish_marking_status = q.group_by(MarkJob.doc_type_id, MarkJob.mark_job_id) \ .with_entities(MarkJob.doc_type_id, MarkJob.mark_job_id, func.count(MarkTask.mark_task_id)).all() return all_status, all_finish_marking_status
def get_by_nlp_task_id( self, nlp_task_id, search, order_by="created_time", order_by_desc=True, limit=10, offset=0, user_role=None, **kwargs): # Define allowed filter keys accept_keys = ["assign_mode", "mark_job_status", "mark_job_type", "doc_type_id"] # Compose query q = session.query(MarkJob, DocType).join( DocType, MarkJob.doc_type_id == DocType.doc_type_id ).filter(DocType.nlp_task_id == nlp_task_id, ~DocType.is_deleted, ~MarkJob.is_deleted) # Role if user_role == "管理员": q = q.filter(DocType.group_id.in_(g.user_groups)) elif user_role == "审核员": q = q.filter(func.json_contains(MarkJob.reviewer_ids, str(g.user_id))) elif user_role == "标注员": q = q.filter(func.json_contains(MarkJob.annotator_ids, str(g.user_id))) # Filter conditions for key, val in kwargs.items(): if key in accept_keys and val is not None: q = q.filter(getattr(MarkJob, key) == val) if search: q = q.filter(MarkJob.mark_job_name.like(f'%{search}%')) count = q.count() # Order by key q = q.order_by(text(f"{'-' if order_by_desc else ''}mark_job.{order_by}")) q = q.offset(offset).limit(limit) return count, q.all()
def return_nodes(self, query_type, query_params): try: if query_type == 'nodeId': return self.session.query(Nodes).filter( Nodes.nodeId == query_params) if query_type == 'nodeName': return self.session.query(Nodes).filter( Nodes.nodeName == query_params) if query_type == 'tierId': return self.session.query(Nodes).filter( Nodes.tierId == query_params) if query_type == 'appId': return self.session.query(Nodes).filter( Nodes.appId == query_params) if query_type == 'ipAddress': return self.session.query(Nodes).filter( func.json_contains(Nodes.ipAddress, query_params) == 1).all() if query_type == 'macAddress': return self.session.query(Nodes).filter( func.json_contains(Nodes.macAddress, query_params) == 1).all() except Exception as e: self.flush_session() logger.exception( "Internal backend error: could not return Node details. Error: " + str(e)) logger.info("Exception occured for query_type: " + query_type) logger.info("Exception occured for query_params: " + query_params) return []
def get_by_mark_job_ids(mark_job_ids, nlp_task_id, current_user: CurrentUser, limit=10, offset=0) -> (int, List): q = session.query(DocType)\ .outerjoin(MarkJob, MarkJob.doc_type_id == DocType.doc_type_id)\ .filter(DocType.nlp_task_id == nlp_task_id, ~DocType.is_deleted, or_(~MarkJob.is_deleted, MarkJob.is_deleted.is_(None))) # 权限filter if current_user.user_role in [ RoleEnum.manager.value, RoleEnum.guest.value ]: q = q.filter(DocType.group_id.in_(current_user.user_groups)) elif current_user.user_role in [ RoleEnum.reviewer.value, RoleEnum.annotator.value ]: q = q.filter( or_( func.json_contains(MarkJob.annotator_ids, str(current_user.user_id)), func.json_contains(MarkJob.reviewer_ids, str(current_user.user_id)))) if mark_job_ids: q = q.filter(MarkJob.mark_job_id.in_(mark_job_ids)) count = q.count() items = q.offset(offset).limit(limit).all() return count, items
def add_to_sqlalchemy_query(self, query, field): if self.value is None: return query.filter(field.is_(None)) if self.operator == Operator.NOT_EQUALS: return query.filter( func.json_contains(field, self.value, '$') == 0) else: return query.filter( func.json_contains(field, self.value, '$') == 1)
def exist(cls, sid=None, **kwargs): """Проверка существования пользователя с данными в базе.""" email_condition = {"value": kwargs.get('email'), "type": "primary"} if sid is None: exist = cls.query.filter((cls.login == kwargs.get('login')) | ( (func.json_contains(cls.email, json.dumps(email_condition)) )) | (cls.phone == kwargs.get('phone'))).first() else: exist = cls.query.filter(((cls.login == kwargs.get('login')) | ( (func.json_contains(cls.email, json.dumps(email_condition)))) | (cls.phone == kwargs.get('phone'))) & (cls.id != sid)).first() if exist: return True return False
def get_preview_and_next_mark_task_id(current_user, nlp_task_id, task_id, args): q = session.query(MarkTask.mark_task_id) \ .outerjoin(UserTask, UserTask.mark_task_id == MarkTask.mark_task_id) \ .join(MarkJob, MarkJob.mark_job_id == MarkTask.mark_job_id) \ .join(DocType, DocType.doc_type_id == MarkJob.doc_type_id) \ .filter( DocType.nlp_task_id == nlp_task_id, MarkTask.mark_task_status != int(StatusEnum.processing), ~MarkTask.is_deleted, or_(~UserTask.is_deleted, UserTask.is_deleted.is_(None)), ~MarkJob.is_deleted, ~DocType.is_deleted ) if args.get('job_id'): q = q.filter(MarkJob.mark_job_id == args['job_id']) if args.get("task_state"): q = q.filter(MarkTask.mark_task_status == args.get("task_state")) if args.get("query"): q = q.filter(Doc.doc_raw_name.contains(args.get("query"))) if current_user.user_role in [ RoleEnum.manager.value, RoleEnum.guest.value ]: q = q.filter(DocType.group_id.in_(current_user.user_groups)) elif current_user.user_role in [RoleEnum.reviewer.value]: q = q.filter( func.json_contains(MarkJob.reviewer_ids, str(current_user.user_id))) elif current_user.user_role in [RoleEnum.annotator.value]: q = q.filter( func.json_contains(MarkJob.annotator_ids, str(current_user.user_id))) q1 = Common().order_by_model_fields( q.filter(MarkTask.mark_task_id < task_id), MarkTask, ['-mark_task_id']) q2 = Common().order_by_model_fields( q.filter(MarkTask.mark_task_id > task_id), MarkTask, ['+mark_task_id']) next_task_id = q1.limit(1).first() preview_task_id = q2.limit(1).first() return preview_task_id[0] if preview_task_id else None, next_task_id[ 0] if next_task_id else None
def count_doc_type_by_nlp_task(current_user: CurrentUser) -> [(int, int)]: q = session.query(DocType.nlp_task_id, func.count(DocType.doc_type_id)) \ .filter(~DocType.is_deleted) if current_user.user_role in [ RoleEnum.manager.value, RoleEnum.guest.value ]: q = q.filter(DocType.group_id.in_(current_user.user_groups)) elif current_user.user_role in [ RoleEnum.reviewer.value, RoleEnum.annotator.value ]: # Reviewer and annotator joins mark_job to filter visible doc types q = session.query(DocType.nlp_task_id, func.count(DocType.doc_type_id)) \ .join(MarkJob, MarkJob.doc_type_id == DocType.doc_type_id) \ .filter(~DocType.is_deleted, ~MarkJob.is_deleted, or_(func.json_contains(MarkJob.annotator_ids, str(current_user.user_id)), func.json_contains(MarkJob.annotator_ids, str(current_user.user_id)))) count = q.group_by(DocType.nlp_task_id).all() return count
def authenticate(cls, check=False, **kwargs): """Функция аутентификации.""" login = kwargs.get('login') password = kwargs.get('password') if not login or not password: return (None, 'Не переданы данные\ для аутентификации пользователя!', 'empty') # email = {"value": login, "verified": True} # Вход через любую подтвержденную почту, привязанную к пользователю # user = cls.query.filter((cls.login == login) | # (func.json_contains(cls.email, json.dumps(email)))).first() user = cls.query.filter((cls.login == login) | (func.json_contains( cls.email, json.dumps({"value": login})))).first() if user: mail_status = list( filter(lambda mail: mail['type'] == "primary", user.email)) if mail_status and not mail_status[0]['verified']: return (None, 'Основная почта не активирована!', 'username') elif user.password['blocked'] and not check: return (None, 'Пароль заблокирован!', 'password') elif not bcrypt.check_password_hash(user.password['value'], password): max_fails = current_app.config['MAX_FAILED'] user.password['failed_times'] += 1 if user.password['failed_times'] >= max_fails: user.password['blocked'] = True user.password['failed_times'] = 0 CmsUsers.query.filter_by(id=user.id).update( {'password': user.password}) db.session.commit() return (None, 'Неверный пароль! Ваш пароль заблокирован!', 'password') else: CmsUsers.query.filter_by(id=user.id).update( {'password': user.password}) db.session.commit() return (None, 'Неверный пароль! ' 'Осталось попыток ввода: %i' % (max_fails - user.password['failed_times']), 'password') else: return (None, 'Пользователь не найден!', 'username') user.password['failed_times'] = 0 CmsUsers.query.filter_by(id=user.id).update( {'password': user.password}) db.session.commit() return (user, 'Успешно!')
def _json_path_clause(kv_pair): path_expr, value = kv_pair key, path = _parse_path_expr(path_expr) json_match = sa_func.json_contains( sa_func.json_extract(models.Variable.value, path), value) # NOTE(thomasem): Order is important here. MySQL will short-circuit and # not properly validate the JSON Path expression when the key doesn't exist # if the key match is first int he and_(...) expression. So, let's put # the json_match first. return and_(json_match, models.Variable.key == key)
def get_user_task_with_doc_and_doc_type(nlp_task_id, current_user: CurrentUser, args): q = session.query(UserTask, DocType, Doc) \ .join(MarkTask, MarkTask.mark_task_id == UserTask.mark_task_id) \ .join(MarkJob, MarkJob.mark_job_id == MarkTask.mark_job_id) \ .join(DocType, DocType.doc_type_id == MarkJob.doc_type_id) \ .join(Doc, Doc.doc_id == MarkTask.doc_id) \ .filter( DocType.nlp_task_id == nlp_task_id, ~UserTask.is_deleted, ~MarkTask.is_deleted, ~Doc.is_deleted ) # TODO # 权限 if current_user.user_role in [ RoleEnum.manager.value, RoleEnum.guest.value ]: q = q.filter(DocType.group_id.in_(current_user.user_groups)) elif current_user.user_role in [RoleEnum.reviewer.value]: q = q.filter( func.json_contains(MarkJob.reviewer_ids, str(current_user.user_id))) elif current_user.user_role in [RoleEnum.annotator.value]: # q = q.filter(func.json_contains(MarkJob.annotator_ids, str(current_user.user_id))) q = q.filter(UserTask.annotator_id == current_user.user_id) if args.get('job_id'): q = q.filter(MarkTask.mark_job_id == args['job_id']) if args.get('doc_type_id'): q = q.filter(MarkJob.doc_type_id == args['doc_type_id']) if args['task_state']: q = q.filter(MarkTask.mark_task_status == status_str2int_mapper().get(args['task_state'])) if args['query']: q = q.filter(Doc.doc_raw_name.like(f'%{args["query"]}%')) q = q.group_by(UserTask) count = q.count() processing_count = q.filter( MarkTask.mark_task_status == int(StatusEnum.processing)).count() if args['order_by'] and isinstance(args['order_by'], str): if args['order_by'][1:] == 'task_id': args['order_by'] = args['order_by'][0] + 'mark_task_id' q = Common().order_by_model_fields(q, UserTask, [args['order_by']]) items = [] for user_task, doc_type, doc in q.offset(args['offset']).limit( args['limit']).all(): user_task.doc = doc user_task.doc_type = doc_type items.append(user_task) return count, count - processing_count, items
def get_by_nlp_task_id_by_user(nlp_task_id, current_user: CurrentUser) -> [DocType]: q = session.query(DocType, func.group_concat(DocTerm.doc_term_id.distinct()))\ .outerjoin(DocTerm, DocTerm.doc_type_id == DocType.doc_type_id)\ .filter(DocType.nlp_task_id == nlp_task_id, ~DocType.is_deleted, or_(~DocTerm.is_deleted, DocTerm.is_deleted.is_(None))) # 权限filter if current_user.user_role in [ RoleEnum.manager.value, RoleEnum.guest.value ]: q = q.filter(DocType.group_id.in_(current_user.user_groups)) elif current_user.user_role in [ RoleEnum.reviewer.value, RoleEnum.annotator.value ]: q = q.outerjoin(MarkJob, MarkJob.doc_type_id == DocType.doc_type_id)\ .filter(~MarkJob.is_deleted, or_(func.json_contains(MarkJob.annotator_ids, str(current_user.user_id)), func.json_contains(MarkJob.reviewer_ids, str(current_user.user_id)))) q = q.group_by(DocTerm.doc_type_id, DocType) count = q.count() q = q.order_by(DocType.is_favorite.desc(), DocType.created_time.desc()) return count, q.all()
def test2(): # 修改普通数据 with open_session() as session: # json_contains 中的 val 需要用双引号括起来,所以目标值类型是字符串时,需要两层引号 query = session.query(Cluster).filter( Cluster.name == "a", sa_func.json_contains(Cluster.description, '"c"', "$[0]")) obj = query.one() print("cluster:", obj) obj.name = "b" # 修改 JSON 数组和对象 数据, 需要显示的转换成JSON # 如果路径标识数组元素,则将相应的值插入该元素位置,然后将任何后续值向右移动。如果路径标识了超出数组末尾的数组位置,则将值插入到数组末尾。 update_info = { "description": sa_func.json_array_insert( Cluster.description, '$[1]', sa_func.cast( { "x": 194, "y": 68, "o": 100, "a": 0, "f": "/bao/uploaded/i4/696944147/O1CN01qjcXhk1gVN66z6pog_!!2-item_pic.png", "type": "I", "w": 388, "h": 136 }, JSON)), "attr": sa_func.json_insert( Cluster.attr, "$.thumbnail", "/bao/uploaded/i1/696944147/O1CN0175JvxQ1gVN6HRrZ44_!!0-item_pic.jpg" ) } with open_session() as session: count = session.query(Cluster).filter(Cluster.name == "b").update( update_info, synchronize_session=False) print("update_data count:", count) with open_session() as session: for obj in session.query(Cluster).filter(Cluster.name == "b").all(): print(obj)
class Operator(object): OPERATORS = { 'is_null': lambda f: f.is_(None), 'is_not_null': lambda f: f.isnot(None), '==': lambda f, a: f == a, 'eq': lambda f, a: f == a, '!=': lambda f, a: f != a, 'ne': lambda f, a: f != a, '>': lambda f, a: f > a, 'gt': lambda f, a: f > a, '<': lambda f, a: f < a, 'lt': lambda f, a: f < a, '>=': lambda f, a: f >= a, 'ge': lambda f, a: f >= a, '<=': lambda f, a: f <= a, 'le': lambda f, a: f <= a, 'like': lambda f, a: f.like(a), 'ilike': lambda f, a: f.ilike(a), 'not_ilike': lambda f, a: ~f.ilike(a), 'in': lambda f, a: f.in_(a), 'not_in': lambda f, a: ~f.in_(a), 'any': lambda f, a: f.any(a), 'not_any': lambda f, a: func.not_(f.any(a)), 'json_contains': lambda f, a: func.json_contains(f, func.json_array(a)), } def __init__(self, operator=None): if not operator: operator = '==' if operator not in self.OPERATORS: raise BadFilterFormat('Operator `{}` not valid.'.format(operator)) self.operator = operator self.function = self.OPERATORS[operator] self.arity = len(signature(self.function).parameters)
def get(self): """ Retrieve the complete list of metric specifications. --- tags: - Metric Specifications parameters: - name: metric in: url type: string description: > A full qualified name for the Metric, e.g `validate_drp.AM1` - name: dataset_name in: url type: string description: > Name of the dataset as in the query metadata, e.g `validation_data_cfht` - name: filter_name in: url type: string description: > Name of the filter as in the query metadada, e.g. `r` - name: specification_tag in: url type: string description: > Name of the specification tag responses: 200: description: List of metric specifications successfully retrieved. """ queryset = SpecificationModel.query.join(MetricModel) args = self.parser.parse_args() metric = args["metric"] if metric: queryset = queryset.filter(MetricModel.name == metric) dataset_name = args["dataset_name"] if dataset_name: expr = (SpecificationModel.metadata_query["dataset_name"] == dataset_name) queryset = queryset.filter(expr) filter_name = args["filter_name"] if filter_name: expr = (SpecificationModel.metadata_query["filter_name"] == filter_name) queryset = queryset.filter(expr) specification_tag = args["tag"] if specification_tag: expr = func.json_contains(SpecificationModel.tags, '"{}"'.format(specification_tag)) queryset = queryset.filter(expr) return {"specs": [spec.json() for spec in queryset.all()]}
def get_mark_task_with_doc_and_doc_type(self, nlp_task_id, current_user: CurrentUser, args): q = session.query(MarkTask, DocType, Doc) \ .join(MarkJob, MarkJob.mark_job_id == MarkTask.mark_job_id) \ .join(DocType, DocType.doc_type_id == MarkJob.doc_type_id) \ .join(Doc, Doc.doc_id == MarkTask.doc_id) \ .filter( DocType.nlp_task_id == nlp_task_id, ~DocType.is_deleted, ~MarkTask.is_deleted, ~Doc.is_deleted ) # TODO # 权限 if current_user.user_role in [ RoleEnum.manager.value, RoleEnum.guest.value ]: q = q.filter(DocType.group_id.in_(current_user.user_groups)) elif current_user.user_role in [RoleEnum.reviewer.value]: q = q.filter( func.json_contains(MarkJob.reviewer_ids, str(current_user.user_id))) elif current_user.user_role in [RoleEnum.annotator.value]: q = q.filter( func.json_contains(MarkJob.annotator_ids, str(current_user.user_id))) if args.get('job_id'): q = q.filter(MarkTask.mark_job_id == args['job_id']) if args.get('doc_type_id'): q = q.filter(MarkJob.doc_type_id == args['doc_type_id']) if args['task_state']: q = q.filter(MarkTask.mark_task_status == status_str2int_mapper().get(args['task_state'])) if args['query']: q = q.filter(Doc.doc_raw_name.like(f'%{args["query"]}%')) q = q.group_by(MarkTask) count = q.count() processing_count = q.filter( MarkTask.mark_task_status == int(StatusEnum.processing)).count() if args['order_by'] and isinstance(args['order_by'], str): if args['order_by'][1:] == 'task_id': args['order_by'] = args['order_by'][0] + 'mark_task_id' q = Common().order_by_model_fields(q, MarkTask, [args['order_by']]) items = [] results = q.offset(args['offset']).limit(args['limit']).all() mark_task_ids = [mark_task.mark_task_id for mark_task, _, _ in results] user_task_map = self._get_user_task_map( mark_task_ids, select_keys=(UserTask)) # .annotator_id, UserTask.mark_task_id)) UserTaskPlaceholder = UserTask( annotator_id=0, is_deleted=False, user_task_status=StatusEnum.labeled.value) for mark_task, doc_type, doc in results: UserTaskPlaceholder.user_task_result = mark_task.mark_task_result user_task_list = user_task_map.get(str(mark_task.mark_task_id), [UserTaskPlaceholder]) mark_task.user_task_list = user_task_list mark_task.doc = doc mark_task.doc_type = doc_type items.append(mark_task) return count, count - processing_count, items