def get_search_results(self, term, offset=None, order=None, limit=None): order = order or [Books.sort] pagination = None term.strip().lower() self.session.connection().connection.connection.create_function("lower", 1, lcase) q = list() authorterms = re.split("[, ]+", term) for authorterm in authorterms: q.append(Books.authors.any(func.lower(Authors.name).ilike("%" + authorterm + "%"))) result = self.session.query(Books).filter(self.common_filters(True)).filter( or_(Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")), Books.series.any(func.lower(Series.name).ilike("%" + term + "%")), Books.authors.any(and_(*q)), Books.publishers.any(func.lower(Publishers.name).ilike("%" + term + "%")), func.lower(Books.title).ilike("%" + term + "%") )).order_by(*order).all() result_count = len(result) if offset != None and limit != None: offset = int(offset) limit_all = offset + int(limit) pagination = Pagination((offset / (int(limit)) + 1), limit, result_count) else: offset = 0 limit_all = result_count ub.store_ids(result) return result[offset:limit_all], result_count, pagination
def upgrade(): op.execute( sa.schema.CreateIndex(sa.Index('ix_user_email', func.lower(User.email)))) # op.execute(sa.schema.CreateIndex(sa.Index('users_username_index', func.lower(User.username)))) sa.Index('ix_user_username', func.lower(User.email)).create(bind=op.get_bind())
def resolve_no_team_students(root, info, division_id, school_id): return Student.get_query(info).filter_by( school_id=school_id, current_division_id=division_id, current_team_id=None).order_by(func.lower(StudentModel.last), func.lower( StudentModel.first)).all()
def sort( self, trans, query, ascending, column_name=None ): """ Sort column using case-insensitive alphabetical sorting on item's username. """ if ascending: query = query.order_by( func.lower( self.model_class.username ).asc() ) else: query = query.order_by( func.lower( self.model_class.username ).desc() ) return query
def search_query(self, term, *join): term.strip().lower() self.session.connection().connection.connection.create_function( "lower", 1, lcase) q = list() authorterms = re.split("[, ]+", term) for authorterm in authorterms: q.append( Books.authors.any( func.lower(Authors.name).ilike("%" + authorterm + "%"))) query = self.session.query(Books) if len(join) == 3: query = query.outerjoin(join[0], join[1]).outerjoin(join[2]) elif len(join) == 2: query = query.outerjoin(join[0], join[1]) elif len(join) == 1: query = query.outerjoin(join[0]) return query.filter(self.common_filters(True)).filter( or_( Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")), Books.series.any( func.lower(Series.name).ilike("%" + term + "%")), Books.authors.any(and_(*q)), Books.publishers.any( func.lower(Publishers.name).ilike("%" + term + "%")), func.lower(Books.title).ilike("%" + term + "%")))
def sort( self, trans, query, ascending, column_name=None ): """ Sort column using case-insensitive alphabetical sorting on item's username. """ if ascending: query = query.order_by( func.lower( self.model_class.username ).asc() ) else: query = query.order_by( func.lower( self.model_class.username ).desc() ) return query
def get_filter(self, trans, user, column_filter): # Parse filter to extract multiple tags. if isinstance(column_filter, list): # Collapse list of tags into a single string; this is redundant but effective. TODO: fix this by iterating over tags. column_filter = ",".join(column_filter) raw_tags = trans.app.tag_handler.parse_tags(column_filter.encode("utf-8")) clause_list = [] for name, value in raw_tags.items(): if name: # Filter by individual's tag names. clause_list.append( self.model_class.tags.any( and_( func.lower(self.model_tag_association_class.user_tname).like("%" + name.lower() + "%"), self.model_tag_association_class.user == user, ) ) ) if value: # Filter by individual's tag values. clause_list.append( self.model_class.tags.any( and_( func.lower(self.model_tag_association_class.user_value).like("%" + value.lower() + "%"), self.model_tag_association_class.user == user, ) ) ) return and_(*clause_list)
def filter(self, query, q, **kwargs): if q: query = query.filter( or_(func.lower(User.first_name).like(q + "%"), func.lower(User.last_name).like(q + "%")) ) return query
def filter(self, query, q, **kwargs): if q: query = query.filter( or_( func.lower(User.first_name).like(q + "%"), func.lower(User.last_name).like(q + "%"))) return query
def addMod(message): sysSettings = settings.settings.query.first() if '@' in str(message['JID']): JID = str(message['JID']) else: username = str(message['JID']) userQuery = Sec.User.query.filter( func.lower(Sec.User.username) == func.lower(username)).first() if userQuery is not None: JID = username + '@' + sysSettings.siteAddress channelLoc = str(message['channelLoc']) channelQuery = Channel.Channel.query.filter_by( channelLoc=channelLoc, owningUser=current_user.id).first() if channelQuery is not None and JID != "": from app import ejabberd ejabberd.set_room_affiliation(channelLoc, 'conference.' + sysSettings.siteAddress, JID, 'admin') emit('addMod', { 'mod': str(JID), 'channelLoc': str(channelLoc) }, broadcast=False) else: pass db.session.commit() db.session.close() return 'OK'
def data(self, *args, **kw): security = get_service('security') length = int(kw.get("iDisplayLength", 0)) start = int(kw.get("iDisplayStart", 0)) sort_dir = kw.get("sSortDir_0", "asc") echo = int(kw.get("sEcho", 0)) search = kw.get("sSearch", "").replace("%", "").strip().lower() end = start + length query = Group.query \ .options(sa.orm.noload('*')) total_count = query.count() if search: # TODO: gérer les accents query = query.filter( func.lower(Group.name).like("%" + search + "%"), ) count = query.count() columns = [func.lower(Group.name)] direction = asc if sort_dir == 'asc' else desc order_by = list(map(direction, columns)) # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses engine = query.session.get_bind(Group.__mapper__) if engine.name != 'sqlite': order_by[0] = nullslast(order_by[0]) query = query.order_by(*order_by) \ .add_columns(Group.members_count) groups = query.slice(start, end).all() data = [] for group, members_count in groups: # TODO: this should be done on the browser. group_url = url_for(".groups_group", group_id=group.id) name = escape(getattr(group, "name") or "") roles = [r for r in security.get_roles(group) if r.assignable] columns = [ '<a href="{url}">{name}</a>'.format(url=group_url, name=name), text_type(members_count or 0), render_template_string( '''{%- for role in roles %} <span class="badge badge-default">{{ role }}</span> {%- endfor %}''', roles=roles, ), '\u2713' if group.public else '', ] data.append(columns) return { "sEcho": echo, "iTotalRecords": total_count, "iTotalDisplayRecords": count, "aaData": data, }
def order_by_name(query, table, language=None, *extra_languages, **kwargs): """Order a query by name. query: The query to order table: Table of the named objects language: The language to order names by. If None, use the connection default. extra_languages: Extra languages to order by, should the translations for `language` be incomplete (or ambiguous). name_attribute (keyword argument): the attribute to use; defaults to 'name' Uses the identifier as a fallback ordering. """ name_attribute = kwargs.pop('name', 'name') if kwargs: raise ValueError('Unexpected keyword arguments: %s' % kwargs.keys()) order_columns = [] if language is None: query = query.outerjoin(table.names_local) order_columns.append(func.lower(getattr(table.names_table, name_attribute))) else: extra_languages = (language, ) + extra_languages for language in extra_languages: names_table = aliased(table.names_table) query = query.outerjoin(names_table) query = query.filter(names_table.foreign_id == table.id) query = query.filter(names_table.local_language_id == language.id) order_columns.append(func.lower(getattr(names_table, name_attribute))) order_columns.append(table.identifier) query = query.order_by(coalesce(*order_columns)) return query
def order_by_name(query, table, language=None, *extra_languages, **kwargs): """Order a query by name. query: The query to order table: Table of the named objects language: The language to order names by. If None, use the connection default. extra_languages: Extra languages to order by, should the translations for `language` be incomplete (or ambiguous). name_attribute (keyword argument): the attribute to use; defaults to 'name' Uses the identifier as a fallback ordering. """ name_attribute = kwargs.pop('name', 'name') if kwargs: raise ValueError('Unexpected keyword arguments: %s' % list(kwargs.keys())) order_columns = [] if language is None: query = query.outerjoin(table.names_local) order_columns.append(func.lower(getattr(table.names_table, name_attribute))) else: extra_languages = (language, ) + extra_languages for language in extra_languages: names_table = aliased(table.names_table) query = query.outerjoin(names_table) query = query.filter(names_table.foreign_id == table.id) query = query.filter(names_table.local_language_id == language.id) order_columns.append(func.lower(getattr(names_table, name_attribute))) order_columns.append(table.identifier) query = query.order_by(coalesce(*order_columns)) return query
def get_filter(self, trans, user, column_filter): # Parse filter to extract multiple tags. if isinstance(column_filter, list): # Collapse list of tags into a single string; this is redundant but effective. TODO: fix this by iterating over tags. column_filter = ",".join(column_filter) raw_tags = trans.app.tag_handler.parse_tags( column_filter.encode("utf-8")) clause_list = [] for name, value in raw_tags.items(): if name: # Filter by individual's tag names. clause_list.append( self.model_class.tags.any( and_( func.lower(self.model_tag_association_class. user_tname).like("%" + name.lower() + "%"), self.model_tag_association_class.user == user))) if value: # Filter by individual's tag values. clause_list.append( self.model_class.tags.any( and_( func.lower( self.model_tag_association_class.user_value ).like("%" + value.lower() + "%"), self.model_tag_association_class.user == user))) return and_(*clause_list)
def apply_filters(query): if 'filters' not in query_params: return query filters = query_params['filters'] for key, val in filters.items(): op, field_name = key.split('_', 1) try: field = getattr(cls, field_name) except AttributeError: log.warn('RestMixin.rest_get_list(): filter "%s=%s": unknown attribute %s', key, val, field_name) continue if op == 'e': query = query.filter(field == val) elif op == 'n': query = query.filter(or_(field == val, field == None)) elif op == 'l': query = query.filter(func.lower(field).like('%' + val.lower() + '%')) elif op == 's': query = query.filter(func.lower(field).like(val.lower() + '%')) else: log.error('get_for_rest_grid: filter "%s=%s": unknown op: %s' % (key, val, op)) return query
def data(self, *args, **kw) -> Dict: security = get_service("security") length = int(kw.get("iDisplayLength", 0)) start = int(kw.get("iDisplayStart", 0)) sort_dir = kw.get("sSortDir_0", "asc") echo = int(kw.get("sEcho", 0)) search = kw.get("sSearch", "").replace("%", "").strip().lower() end = start + length # pyre-fixme[16]: `Group` has no attribute `query`. query = Group.query.options(sa.orm.noload("*")) total_count = query.count() if search: # TODO: gérer les accents query = query.filter( func.lower(Group.name).like("%" + search + "%")) count = query.count() columns = [func.lower(Group.name)] direction = asc if sort_dir == "asc" else desc order_by = list(map(direction, columns)) # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses # pyre-fixme[16]: `Group` has no attribute `__mapper__`. engine = query.session.get_bind(Group.__mapper__) if engine.name != "sqlite": order_by[0] = nullslast(order_by[0]) query = query.order_by(*order_by).add_columns(Group.members_count) groups = query.slice(start, end).all() data = [] for group, members_count in groups: # TODO: this should be done on the browser. group_url = url_for(".groups_group", group_id=group.id) name = html.escape(group.name or "") # pyre-fixme[16]: `Service` has no attribute `get_roles`. roles = [r for r in security.get_roles(group) if r.assignable] columns = [ f'<a href="{group_url}">{name}</a>', str(members_count or 0), render_template_string( """{%- for role in roles %} <span class="badge badge-default">{{ role }}</span> {%- endfor %}""", roles=roles, ), "\u2713" if group.public else "", ] data.append(columns) return { "sEcho": echo, "iTotalRecords": total_count, "iTotalDisplayRecords": count, "aaData": data, }
def validate_username(form, field): log.error("validate_username") if not re.match('[a-zA-Z0-9-_]+', field.data): raise validators.ValidationError( 'Username may only contain alpha-numeric characters') if DBSession.query(User).filter( func.lower(User.username) == func.lower(field.data)).first(): raise validators.ValidationError('Username already taken')
def exact_name_lookup(card, edition=None): cquery = session.query(CardInfo).filter(func.lower(CardInfo.name)==func.lower(card)) if edition is not None: cquery = cquery.filter(func.lower(CardInfo.edition)==func.lower(edition)) cards = cquery.all(); if cards == []: print 'not found:', card return cards[0:1]
def quicksearch(request): message = None query = DBSession.query(Languoid) term = request.params['search'].strip() titlecase = term.istitle() term = term.lower() params = {'iso': '', 'country': '', 'name': '', 'namequerytype': 'part', 'multilingual': ''} if not term: query = None elif len(term) < 3: query = None message = ('Please enter at least four characters for a name search ' 'or three characters for an iso code') elif len(term) == 3 and not titlecase: query = query.filter(Languoid.identifiers.any( type=IdentifierType.iso.value, name=term)) kind = 'ISO 639-3' elif len(term) == 8 and GLOTTOCODE_PATTERN.match(term): query = query.filter(Languoid.id == term) kind = 'Glottocode' else: _query = query.filter(func.lower(Languoid.name) == term) if DBSession.query(_query.exists()).scalar(): query = _query else: query = query.filter(or_( func.lower(Languoid.name).contains(term), Languoid.identifiers.any(and_( Identifier.type == u'name', Identifier.description == Languoid.GLOTTOLOG_NAME, func.lower(Identifier.name).contains(term))))) kind = 'name part' params['name'] = term if query is None: languoids = [] else: languoids = query.order_by(Languoid.name)\ .options(joinedload(Languoid.family)).all() if not languoids: term_pre = HTML.kbd(term, style='white-space: pre') message = 'No matching languoids found for %s "' % kind + term_pre + '"' elif len(languoids) == 1: raise HTTPFound(request.resource_url(languoids[0])) map_, icon_map, family_map = get_selected_languages_map(request, languoids) layer = list(map_.get_layers())[0] if not layer.data['features']: map_ = None countries = json.dumps(['%s (%s)' % (c.name, c.id) for c in DBSession.query(Country).order_by(Country.description)]) return {'message': message, 'params': params, 'languoids': languoids, 'map': map_, 'countries': countries}
def json_search(): query = request.args['query'] return jsonify(users=map( lambda u: {"id": u.id, "name": u.name}, session.query(User.id, User.name).filter(or_( func.lower(User.name).like(func.lower(u"%{}%".format(query))), User.id.like(u"{}%".format(query)) )) ))
def sort( self, trans, query, ascending, column_name=None ): """Sort column using case-insensitive alphabetical sorting.""" if column_name is None: column_name = self.key if ascending: query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).asc() ) else: query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).desc() ) return query
def check_exists_book(self, authr, title): self.session.connection().connection.connection.create_function("lower", 1, lcase) q = list() authorterms = re.split(r'\s*&\s*', authr) for authorterm in authorterms: q.append(Books.authors.any(func.lower(Authors.name).ilike("%" + authorterm + "%"))) return self.session.query(Books)\ .filter(and_(Books.authors.any(and_(*q)), func.lower(Books.title).ilike("%" + title + "%"))).first()
def sort( self, trans, query, ascending, column_name=None ): """Sort column using case-insensitive alphabetical sorting.""" if column_name is None: column_name = self.key if ascending: query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).asc() ) else: query = query.order_by( func.lower( self.model_class.table.c.get( column_name ) ).desc() ) return query
def data(self, *args, **kw): security = current_app.services['security'] length = int(kw.get("iDisplayLength", 0)) start = int(kw.get("iDisplayStart", 0)) sort_dir = kw.get("sSortDir_0", "asc") echo = int(kw.get("sEcho", 0)) search = kw.get("sSearch", "").replace("%", "").strip().lower() end = start + length q = Group.query \ .options(sa.orm.noload('*')) total_count = q.count() if search: # TODO: gérer les accents q = q.filter(func.lower(Group.name).like("%" + search + "%")) count = q.count() columns = [func.lower(Group.name)] direction = asc if sort_dir == 'asc' else desc order_by = map(direction, columns) # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses engine = q.session.get_bind(Group.__mapper__) if engine.name != 'sqlite': order_by[0] = nullslast(order_by[0]) q = q.order_by(*order_by) \ .add_columns(Group.members_count) groups = q.slice(start, end).all() data = [] for group, members_count in groups: # TODO: this should be done on the browser. group_url = url_for(".groups_group", group_id=group.id) name = escape(getattr(group, "name") or "") roles = [r for r in security.get_roles(group) if r.assignable] columns = [] columns.append( u'<a href="{url}">{name}</a>'.format(url=group_url, name=name) ) columns.append(unicode(members_count or 0)) columns.append(render_template_string( u'''{%- for role in roles %} <span class="badge badge-default">{{ role }}</span> {%- endfor %}''', roles=roles)) columns.append(u'\u2713' if group.public else u'') data.append(columns) return { "sEcho": echo, "iTotalRecords": total_count, "iTotalDisplayRecords": count, "aaData": data, }
def data(self, *args, **kw): security = get_service("security") length = int(kw.get("iDisplayLength", 0)) start = int(kw.get("iDisplayStart", 0)) sort_dir = kw.get("sSortDir_0", "asc") echo = int(kw.get("sEcho", 0)) search = kw.get("sSearch", "").replace("%", "").strip().lower() end = start + length query = Group.query.options(sa.orm.noload("*")) total_count = query.count() if search: # TODO: gérer les accents query = query.filter(func.lower(Group.name).like("%" + search + "%")) count = query.count() columns = [func.lower(Group.name)] direction = asc if sort_dir == "asc" else desc order_by = list(map(direction, columns)) # sqlite does not support 'NULLS FIRST|LAST' in ORDER BY clauses engine = query.session.get_bind(Group.__mapper__) if engine.name != "sqlite": order_by[0] = nullslast(order_by[0]) query = query.order_by(*order_by).add_columns(Group.members_count) groups = query.slice(start, end).all() data = [] for group, members_count in groups: # TODO: this should be done on the browser. group_url = url_for(".groups_group", group_id=group.id) name = html.escape(group.name or "") roles = [r for r in security.get_roles(group) if r.assignable] columns = [ f'<a href="{group_url}">{name}</a>', str(members_count or 0), render_template_string( """{%- for role in roles %} <span class="badge badge-default">{{ role }}</span> {%- endfor %}""", roles=roles, ), "\u2713" if group.public else "", ] data.append(columns) return { "sEcho": echo, "iTotalRecords": total_count, "iTotalDisplayRecords": count, "aaData": data, }
def rss_category(self): category = func.lower(self.request.matchdict['category']) query = self.Entry.get_all_active(self.request) query = query.join(self.Category) query = query.filter( func.lower(self.Category.title) == category ) entries = query.all() return self.get_rss_data(entries)
def wildcard_handler(col, wc_term): if wc_term.startswith('*') and not wc_term.endswith('*'): #postfix == endswith wc_term = remove_prefix(wc_term, prefix='*') return func.lower(col).endswith(func.lower(wc_term)) elif wc_term.startswith('*') and wc_term.endswith('*'): #wildcard == ilike wc_term = remove_prefix(wc_term, prefix='*') wc_term = remove_suffix(wc_term, suffix='*') return func.lower(col).contains(func.lower(wc_term))
def teacher_by_name(first_name, last_name): """ Returns the FIRST DistrictTeacher object that matches a given CASE INSENSITIVE first and last name """ return (db_session.query(DistrictTeacher).filter( func.lower(DistrictTeacher.teacher_first_name) == first_name.lower()).filter( func.lower(DistrictTeacher.teacher_last_name) == last_name.lower()).first())
def wildcard_handler(col, wc_term): if wc_term.startswith('*') and not wc_term.endswith('*'): #postfix == endswith wc_term = remove_prefix(wc_term, prefix='*') return func.lower(col).endswith(wc_term) elif wc_term.startswith('*') and wc_term.endswith('*'): #wildcard == ilike wc_term = remove_prefix(wc_term, prefix='*') wc_term = remove_suffix(wc_term, suffix='*') return func.lower(col).contains(wc_term)
def search_query(self, term, config_read_column, *join): term.strip().lower() self.session.connection().connection.connection.create_function( "lower", 1, lcase) q = list() authorterms = re.split("[, ]+", term) for authorterm in authorterms: q.append( Books.authors.any( func.lower(Authors.name).ilike("%" + authorterm + "%"))) if not config_read_column: query = (self.session.query( Books, ub.ArchivedBook.is_archived, ub.ReadBook).select_from(Books).outerjoin( ub.ReadBook, and_(Books.id == ub.ReadBook.book_id, int(current_user.id) == ub.ReadBook.user_id))) else: try: read_column = cc_classes[config_read_column] query = (self.session.query( Books, ub.ArchivedBook.is_archived, read_column.value).select_from(Books).outerjoin( read_column, read_column.book == Books.id)) except (KeyError, AttributeError): log.error( "Custom Column No.%d is not existing in calibre database", config_read_column) # Skip linking read column query = self.session.query(Books, ub.ArchivedBook.is_archived, None) query = query.outerjoin( ub.ArchivedBook, and_(Books.id == ub.ArchivedBook.book_id, int(current_user.id) == ub.ArchivedBook.user_id)) if len(join) == 6: query = query.outerjoin(join[0], join[1]).outerjoin(join[2]).outerjoin( join[3], join[4]).outerjoin(join[5]) if len(join) == 3: query = query.outerjoin(join[0], join[1]).outerjoin(join[2]) elif len(join) == 2: query = query.outerjoin(join[0], join[1]) elif len(join) == 1: query = query.outerjoin(join[0]) return query.filter(self.common_filters(True)).filter( or_( Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")), Books.series.any( func.lower(Series.name).ilike("%" + term + "%")), Books.authors.any(and_(*q)), Books.publishers.any( func.lower(Publishers.name).ilike("%" + term + "%")), func.lower(Books.title).ilike("%" + term + "%")))
def deleteInvitedUser(message): newUsername = message['username'] userQuery = Sec.User.query.filter( func.lower(Sec.User.username) == func.lower(newUsername)).first() if userQuery is None: emit('checkUniqueUsernameAck', {'results': str(1)}, broadcast=False) else: emit('checkUniqueUsernameAck', {'results': str(0)}, broadcast=False) db.session.commit() db.session.close() return 'OK'
def users_json(): q = request.args.get("q").replace("%", " ").lower() if not q or len(q) < 2: raise InternalServerError() query = User.query.filter( or_( func.lower(User.first_name).like(q + "%"), func.lower(User.last_name).like(q + "%"), )).order_by(func.lower(User.last_name)) with_membership = request.args.get("with_membership") if with_membership is not None: # provide membership info for a community with_membership = int(with_membership) query = (query.outerjoin( Membership, and_( Membership.user.expression, Membership.community_id == with_membership, ), ).filter(User.can_login == True).add_columns(Membership.role)) exclude_community = request.args.get("exclude_community") if exclude_community is not None: exclude_community = int(exclude_community) exclude = (~Membership.query.filter( Membership.user.expression, Membership.community_id == exclude_community).options(sa.orm.noload("user"), sa.orm.noload("community")).exists()) query = query.filter(exclude) results = [] for user in query.all(): role = None if with_membership is not None: user, role = user if role is not None: role = text_type(role) item = { "id": user.id, "text": "{} ({})".format(user.name, user.email), "name": user.name, "email": user.email, "role": role, } results.append(item) return jsonify({"results": results})
def resolve_viewer_attendees_by_contest(root, info, contest_id, **kwargs): user = get_current_user() contest = Contest.get_query(info).get(localize_id(contest_id)) if not contest: raise GraphQLError("Invalid contest provided!") return Student.get_query(info).filter_by( school_id=user.school_id).filter( StudentModel.attendance.any( contest_id=contest.id, attended=True)).order_by( StudentModel.current_team_id.desc(), func.lower(StudentModel.last), func.lower(StudentModel.first), ).all()
def search_all_members(self, query, limit=10): """ Searches for members from this place or places below this by name, phone or email. :param query: search query :return: List of object Member """ like_q = "%{0}%".format(query.lower()) q1 = Member.query.filter( place_parents.c.child_id == Member.place_id, place_parents.c.parent_id == self.id, (func.lower(Member.name).like(like_q) | Member.phone.like(like_q) | func.lower(Member.email).like( like_q))).limit(limit) return q1.all()
def findCard(self, card, edition=None): cquery = datasources.database.db.session.query(CardInfo).filter(func.lower(CardInfo.name) == func.lower(card)) edition = self.getRealEditionName(edition) if edition is not None: cquery = cquery.filter(func.lower(CardInfo.edition) == func.lower(edition)) cards = cquery.all() if not cards: logger.warn('not found: %s', card) if len(cards) > 1 and self.forceSingleMatch: logger.warn('more than one matches found for card: {card}, selecting first match: {ed}'.format(card=card, ed=cards[0].edition)) return cards[0:1] return cards
def search_all_members(self, query, limit=10): """ Searches for members from this place or places below this by name, phone or email. :param query: search query :return: List of object Member """ like_q = "%{0}%".format(query.lower()) q1 = Member.query.filter( place_parents.c.child_id == Member.place_id, place_parents.c.parent_id == self.id, (func.lower(Member.name).like(like_q) | Member.phone.like(like_q) | func.lower(Member.email).like(like_q))).limit(limit) return q1.all()
def users_json(): q = request.args.get("q").replace("%", " ").lower() if not q or len(q) < 2: raise InternalServerError() query = User.query \ .filter(or_(func.lower(User.first_name).like(q + "%"), func.lower(User.last_name).like(q + "%"))) \ .order_by(func.lower(User.last_name)) with_membership = request.args.get('with_membership') if with_membership is not None: # provide membership info for a community with_membership = int(with_membership) query = query \ .outerjoin(Membership, and_(Membership.user.expression, Membership.community_id == with_membership)) \ .add_columns(Membership.role) exclude_community = request.args.get('exclude_community') if exclude_community is not None: exclude_community = int(exclude_community) exclude = ~Membership.query \ .filter(Membership.user.expression, Membership.community_id == exclude_community) \ .options(sa.orm.noload('user'), sa.orm.noload('community')) \ .exists() query = query.filter(exclude) results = [] for user in query.all(): role = None if with_membership is not None: user, role = user if role is not None: role = text_type(role) item = { 'id': user.id, 'text': u'{} ({})'.format(user.name, user.email), 'name': user.name, 'email': user.email, 'role': role, } results.append(item) return jsonify({'results': results})
def get_by_name_or_email(self, name_or_email): """Get a user by name or email """ User = tables.User user = ( self.session .query(User) .filter(or_( func.lower(User.user_name) == name_or_email.lower(), func.lower(User.email) == name_or_email.lower() )) ) return user.first()
def get_search_results(self, term): term.strip().lower() self.session.connection().connection.connection.create_function("lower", 1, lcase) q = list() authorterms = re.split("[, ]+", term) for authorterm in authorterms: q.append(Books.authors.any(func.lower(Authors.name).ilike("%" + authorterm + "%"))) return self.session.query(Books).filter(self.common_filters(True)).filter( or_(Books.tags.any(func.lower(Tags.name).ilike("%" + term + "%")), Books.series.any(func.lower(Series.name).ilike("%" + term + "%")), Books.authors.any(and_(*q)), Books.publishers.any(func.lower(Publishers.name).ilike("%" + term + "%")), func.lower(Books.title).ilike("%" + term + "%") )).order_by(Books.sort).all()
def resolve_viewer_students_by_contest(root, info, contest_id, **kwargs): user = get_current_user() contest = Contest.get_query(info).get(localize_id(contest_id)) if not contest: raise GraphQLError("Invalid contest provided!") return Student.get_query(info).filter_by( current_division_id=contest.division_id, school_id=user.school_id).join( StudentDivisionAssociationModel, StudentModel.current_division_assoc).order_by( StudentModel.current_team_id.desc(), StudentDivisionAssociationModel.is_alternate.desc(), func.lower(StudentModel.last), func.lower(StudentModel.first), ).all()
def account_query(q): ''' Returns an SQLAlchemy query object. Example usage: for row in account_query(u'work pensions').all(): print row ''' query = model.Session.query(model.Account) for word in q.split(): query = query.filter(or_( func.lower(model.Account.name).contains(word.lower()), func.lower(model.Account.notes).contains(word.lower()), model.Account.id.startswith(word.lower()) )) return query
def apply_search(query): search_columns = getattr(cls, '_rest_search_columns', None) if not search_columns or not 'search' in query_params: return query search = query_params['search'].lower() if len(search_columns) == 1: col = search_columns[0] search_filter = func.lower(col).like('%' + search + '%') else: # > 1 clauses = [func.lower(col).like('%' + search + '%') for col in search_columns] search_filter = or_(*clauses) return query.filter(search_filter)
def get_typeahead(self, database, query, replace=('', ''), tag_filter=true()): query = query or '' self.session.connection().connection.connection.create_function("lower", 1, lcase) entries = self.session.query(database).filter(tag_filter). \ filter(func.lower(database.name).ilike("%" + query + "%")).all() json_dumps = json.dumps([dict(name=r.name.replace(*replace)) for r in entries]) return json_dumps
def _query_active_users_by_email(email): q = session.query(UserData.email, UserData.firstname + u" " + UserData.lastname) q = q.filter(UserData.enabled == True) q = q.filter( func.lower(UserData.email).like(u"%" + email.lower() + u"%")) return q
def update_tag(self, tag, topic): restr = r',|,|:|:|\|' tags = [ x.strip() for x in re.split(restr, tag) if x.strip() != '' ] old_id_list = [ x.id for x in topic.tags] taglist = [] # TODO: dotag is ugly. dotag = [] for tagname in tags: if tagname in dotag: continue dotag.append(tagname) T = self.db.query(ForumTopicTag).filter( func.lower(ForumTopicTag.name) == tagname.lower() ).first() if T: if T.id not in old_id_list: T.hit += 1 else: T = ForumTopicTag( name = tagname ) self.db.add(T) taglist.append( T ) topic.tags = taglist # delete hit new_id_list = [ x.id for x in topic.tags] for ID in old_id_list: if ID not in new_id_list: T = self.db.query(ForumTopicTag).get(ID) if T.hit == 1: self.db.delete(T) else: T.hit -= 1 self.db.commit()
def auto_movie(self, q): orig_q = q q = q.lstrip().lower().split(' ') full_words, q = q[:-1], q[-1] if 'the' in full_words: full_words.remove('the') target = literal(' ').op('||')(Movie.title) filters = [] for word in full_words: filters.append(target.ilike('%% %s %%' % word)) filters.append(target.ilike('%% %s%%' % q)) if len(filters) > 1: filters = and_(*filters) else: filters = filters[0] res = self.session.query(Movie.id, Movie.title, Rating.rating)\ .outerjoin((Rating, and_(Rating.movie_id == Movie.id, Rating.user == self.user)))\ .filter(filters)\ .order_by(func.similarity(func.lower(target), orig_q).desc())\ .limit(7).all() self.return_json(res)
def check_auth(username, password): if sys.version_info.major == 3: username = username.encode('windows-1252') user = ub.session.query(ub.User).filter( func.lower(ub.User.nickname) == username.decode( 'utf-8').lower()).first() return bool(user and check_password_hash(str(user.password), password))
def index(self): # Return a rendered template p = int(request.params.get('page', 1)) c.user = User.get(self.rhodecode_user.user_id) all_repos = self.sa.query(Repository)\ .filter(Repository.user_id == c.user.user_id)\ .order_by(func.lower(Repository.repo_name)).all() c.user_repos = ScmModel().get_repos(all_repos) c.following = self.sa.query(UserFollowing)\ .filter(UserFollowing.user_id == self.rhodecode_user.user_id)\ .options(joinedload(UserFollowing.follows_repository))\ .all() journal = self._get_journal_data(c.following) c.journal_pager = Page(journal, page=p, items_per_page=20) c.journal_day_aggreagate = self._get_daily_aggregate(c.journal_pager) c.journal_data = render('journal/journal_data.html') if request.environ.get('HTTP_X_PARTIAL_XHR'): return c.journal_data return render('journal/journal.html')
def show(self, group_name, format='html'): """GET /repos_groups/group_name: Show a specific item""" # url('repos_group', group_name=GROUP_NAME) c.group = c.repos_group = ReposGroupModel()._get_repo_group(group_name) c.group_repos = c.group.repositories.all() #overwrite our cached list with current filter gr_filter = c.group_repos c.repo_cnt = 0 groups = RepoGroup.query().order_by(RepoGroup.group_name)\ .filter(RepoGroup.group_parent_id == c.group.group_id).all() c.groups = self.scm_model.get_repos_groups(groups) c.repos_list = Repository.query()\ .filter(Repository.group_id == c.group.group_id)\ .order_by(func.lower(Repository.repo_name))\ .all() repos_data = RepoModel().get_repos_as_dict(repos_list=c.repos_list, admin=False) #json used to render the grid c.data = json.dumps(repos_data) return render('admin/repos_groups/repos_groups.html')
def init_db(app_db_path): # Open session for database connection global session global app_DB_path app_DB_path = app_db_path engine = create_engine(u'sqlite:///{0}'.format(app_db_path), echo=False) Session = scoped_session(sessionmaker()) Session.configure(bind=engine) session = Session() if os.path.exists(app_db_path): Base.metadata.create_all(engine) migrate_Database(session) clean_database(session) else: Base.metadata.create_all(engine) create_admin_user(session) create_anonymous_user(session) if cli.user_credentials: username, password = cli.user_credentials.split(':') user = session.query(User).filter(func.lower(User.nickname) == username.lower()).first() if user: user.password = generate_password_hash(password) if session_commit() == "": print("Password for user '{}' changed".format(username)) sys.exit(0) else: print("Failed changing password") sys.exit(3) else: print("Username '{}' not valid, can't change password".format(username)) sys.exit(3)
def critic(): """ Return all movies reviewed by a given critic. Takes in name and publication as JSON parameters.""" name = request.args.get('name') publication = request.args.get('publication') if not name: abort(400) query = session.query(Critic).filter(func.lower(Critic.name) == func.lower(name)) if publication: query = query.filter(func.lower(Critic.publication) == func.lower(publication)) critic = query.first() reviews = [review.to_json() for review in critic.reviews] if critic else [] return jsonify(reviews=reviews)
def get_searches_ordered_by_label_matches( labels, searcher_role, searching_for_role, offset_number=0, max_number=10): if offset_number > 0: offset_number *= max_number labelnames = [label.name.lower() for label in labels] query = store.session.query(Search, func.count(Label.id).label('matches')) query = query.join(Search.labels) query = query.filter(func.lower(Label.name).in_(labelnames)) query = query.filter(Search.active==True) query = query.filter(Search.searcher_role==searcher_role) query = query.filter(Search.searching_for_role==searching_for_role) query = query.join(Search.searcher_user) query = query.filter(User.email_confirmed==True) query = query.filter(User.active==True) query = query.group_by(Search.id) query = query.order_by('matches DESC') searches_and_count = query.offset(offset_number).limit(max_number) searches = [sc[0] for sc in searches_and_count] return searches
def get_single_filter( self, user, a_filter ): """ Filter by annotation and annotation owner. """ return self.model_class.annotations.any( and_( func.lower( self.model_annotation_association_class.annotation ).like( "%" + a_filter.lower() + "%" ), # TODO: not sure why, to filter by owner's annotations, we have to do this rather than # 'self.model_class.user==self.model_annotation_association_class.user' self.model_annotation_association_class.table.c.user_id == self.model_class.table.c.user_id ) )
def validate(self): location = self.name_input.value if (location is None) or (not ListManagerEditBlock.is_alnum_phrase(location)): error_popup = ErrorPopup(text="Name must be non-empty and contain only alphanumeric characters. Entered: %s" % location) error_popup.open() return False app = SymptomDiaryApp.get_running_app() session = app.getDBSession() pain_site_query = session.query(PainSite).filter(func.lower(PainSite.location) == location) try: result = pain_site_query.one() except NoResultFound: result = None if (result is not None) and (result.pain_site_id != self.record.pain_site_id): # We found a similar record (same name ) but it has a different ID # We should not allow such duplicates error_popup = ErrorPopup(text = "Pain site %s is already defined in the DB. You must edit that record instead" % (location) ) error_popup.open() return False return True
def index(self): c.groups = self.scm_model.get_repos_groups() c.group = None if c.visual.lightweight_dashboard is False: c.repos_list = self.scm_model.get_repos() ## lightweight version of dashboard else: c.repos_list = Repository.query()\ .filter(Repository.group_id == None)\ .order_by(func.lower(Repository.repo_name))\ .all() repos_data = [] total_records = len(c.repos_list) _tmpl_lookup = rhodecode.CONFIG['pylons.app_globals'].mako_lookup template = _tmpl_lookup.get_template('data_table/_dt_elements.html') quick_menu = lambda repo_name: (template.get_def("quick_menu") .render(repo_name, _=_, h=h, c=c)) repo_lnk = lambda name, rtype, private, fork_of: ( template.get_def("repo_name") .render(name, rtype, private, fork_of, short_name=False, admin=False, _=_, h=h, c=c)) last_change = lambda last_change: (template.get_def("last_change") .render(last_change, _=_, h=h, c=c)) rss_lnk = lambda repo_name: (template.get_def("rss") .render(repo_name, _=_, h=h, c=c)) atom_lnk = lambda repo_name: (template.get_def("atom") .render(repo_name, _=_, h=h, c=c)) def desc(desc): if c.visual.stylify_metatags: return h.urlify_text(h.desc_stylize(h.truncate(desc, 60))) else: return h.urlify_text(h.truncate(desc, 60)) for repo in c.repos_list: repos_data.append({ "menu": quick_menu(repo.repo_name), "raw_name": repo.repo_name.lower(), "name": repo_lnk(repo.repo_name, repo.repo_type, repo.private, repo.fork), "last_change": last_change(repo.last_db_change), "desc": desc(repo.description), "owner": h.person(repo.user.username), "rss": rss_lnk(repo.repo_name), "atom": atom_lnk(repo.repo_name), }) c.data = json.dumps({ "totalRecords": total_records, "startIndex": 0, "sort": "name", "dir": "asc", "records": repos_data }) return render('/index.html')
def is_unused_user_attribute(attrname, value, lowercase=False): attr = getattr(User, attrname) query = DBSession.query(User) if lowercase: query = query.filter(func.lower(attr) == value.lower()) else: query = query.filter(attr == value) return query.count() == 0
def inner(): r = ( self.db.query(Ban) .filter(func.lower(Ban.banmask) == mask.lower()) .filter(Ban.channel == channel) .delete(synchronize_session="fetch") ) self.db.commit() return r > 0
def find(cls, abbr): session = Session() try: t = session.query(NHLTeam).filter(func.lower(NHLTeam.abbr) == abbr.lower()).one() except: t = None finally: session.close() return t