def getItems(self, category_id): """Convenience method to get all items for the given category. """ return self.db_session.query(Item) \ .filter_by(category_id=category_id) \ .order_by(collate(Item.name, 'NOCASE')) \ .all()
def get(self, query_args): query = Category.query filters = [] if "search" in query_args and query_args["search"]: filters.append(Category.name.like("%{filter}%".format(filter=query_args["search"]))) if "name" in query_args: filters.append(Category.name.like("%{filter}%".format(filter=query_args["name"]))) if "slug" in query_args: filters.append(Category.slug.like("%{filter}%".format(filter=query_args["slug"]))) if filters: query = query.filter(*filters) # Apply sorting order_by = Category.id if "sort" in query_args and query_args["sort"]: column, direction = PaginationHelper.decode_sort(query_args["sort"]) if column in set(Category.__table__.columns.keys()): order_by = getattr(Category, column) order_by = collate(order_by, "NOCASE") if direction == PaginationHelper.SORT_DESCENDING: order_by = desc(order_by) query = query.order_by(order_by) pagination_helper = PaginationHelper( request, query=query, resource_for_url="api.categories", key_name="results", schema=category_schema, query_args=query_args, ) result = pagination_helper.paginate_query() return result
def all_tags( user: orm_users.User = None, feed: orm_feeds.Feed = None, flag: bool = True, display: bool = True, ) -> typing.List[orm_feeds.Tag]: q = sqla.select(orm_feeds.Tag, ).order_by( sqla.collate(orm_feeds.Tag.Name, "NOCASE"), ) if user: q = q.where(orm_feeds.Tag.UserID == user.UserID) if feed: q_where = orm_feeds.Tag.feeds.any( orm_feeds.Feed.FeedID == feed.FeedID, ) if not flag: q_where = ~q_where q = q.where(q_where) if display: q = q.join(orm_feeds.Tag.feeds).join( orm_feeds.Feed.users.and_( orm_users.User.UserID == user.UserID, )).distinct() with model.get_session() as session: return [e[0] for e in session.execute(q)]
class CitationForm(Form): date = TextField('Date', validators=[DataRequired()]) circa = BooleanField('Circa') author = TextField('Author', validators=[DataRequired(), Length(max=50)]) # note: the source selection has had ".filter_by(archived=False)" removed # as on editing the citation, the source will become the first option # on the drop down list. Not an acceptable solution. # Would need a more sophisticated sql query to remove # the archived sources and still leave the already selected option. source = QuerySelectField(query_factory=lambda: db.session.query( Source).order_by(asc(collate(Source.name, 'NOCASE'))).all(), get_pk=lambda a: a.id, get_label=lambda a: a.name) vol_page = TextField('Volume/Page', validators=[Length(max=10)]) edition = TextField('Edition', validators=[Length(max=10)]) quote = TextAreaField('Quote', validators=[]) notes = TextAreaField('Notes', validators=[]) archived = BooleanField('Archived') def getattr(self, name): return getattr(self, name) def __init__(self, *args, **kwargs): super(CitationForm, self).__init__(*args, **kwargs) self.user = None def validate(self): initial_validation = super(CitationForm, self).validate() if not initial_validation: return False return True
def on_search(args): # TODO: Decode via actual tty encoding try: q = args.q[0].decode("utf-8") except AttributeError: q = args.q[0] pkg_names = set() # First, check for exact case-insensitive name matches for pkg in session.query(Package).filter(collate(Package.name,"NOCASE")==q).all(): pkg_names.add(pkg.name) # Check for substring name matches for pkg in session.query(Package).filter(Package.name.like(u('%{0}%').format(q))).all(): pkg_names.add(pkg.name) # Check for description matches for pkg in session.query(Package).filter(Package.description.like(u('%{0}%').format(q))).all(): pkg_names.add(pkg.name) if len(pkg_names) == 0: print_(u('No matching packages found.')) return # Nice column formatting max_len_name = max( len(name) for name in pkg_names ) for pkg_name in sorted(pkg_names): pkg = session.query(Package).get(pkg_name) print_(u('{name:{max_len_name}} {version:10} {desc}'.format(name=pkg.name, version=pkg.version, desc=pkg.description, max_len_name=max_len_name)))
def showSportsItem(sports_id): """Shows sports items and who created them""" sports = session.query(Sports).filter_by(id=sports_id).one() creator = getUserInfo(sports.user_id) items = session.query( User, SportsItem).filter(User.id == SportsItem.user_id).filter( SportsItem.sports_id == sports_id).order_by( asc(collate(SportsItem.name, 'NOCASE'))).all() if 'username' not in login_session: return render_template('sportsitems.html', items=items, sports=sports, creator=creator, loggedin="no") if 'username' not in login_session != login_session['user_id']: return render_template('sportsitems.html', items=items, sports=sports, creator=creator, loggedin="no") else: return render_template('sportsitems.html', items=items, sports=sports, creator=creator, loggedin="yes")
def get(self, query_args): query = Image.query # Apply filters filters = [] if "search" in query_args and query_args["search"]: filters.append( or_( Image.title.like( "%{filter}%".format(filter=query_args["search"])), Image.original_filename.like( "%{filter}%".format(filter=query_args["search"])), )) if "title" in query_args: filters.append( Image.title.like( "%{filter}%".format(filter=query_args["title"]))) if "original_filename" in query_args: filters.append( Image.original_filename.like("%{filter}%".format( filter=query_args["original_filename"]))) if "author_id" in query_args: filters.append(Post.author_id == query_args["author_id"]) if "post_id" in query_args: filters.append(Post.post_id == query_args["post_id"]) if "created_at" in query_args: filters.append(Post.created_at == query_args["created_at"]) if filters: query = query.filter(*filters) # Apply sorting order_by = Image.id if "sort" in query_args and query_args["sort"]: column, direction = PaginationHelper.decode_sort( query_args["sort"]) if column == "post.name": query = query.join(Post, Image.post) order_by = Post.name elif column == "author.name": query = query.join(User, Image.author) order_by = User.name elif column in set(Image.__table__.columns.keys()): order_by = getattr(Image, column) order_by = collate(order_by, "NOCASE") if direction == PaginationHelper.SORT_DESCENDING: order_by = desc(order_by) query = query.order_by(order_by) pagination_helper = PaginationHelper( request, query=query, resource_for_url="api.images", key_name="results", schema=image_schema, query_args=query_args, ) result = pagination_helper.paginate_query() return result
def get_projects(): data = [] for p in db_session.query(Project).order_by( collate(Project.sname, 'NOCASE')).all(): d = dict(p) d['project_start'] = date2dat(d['project_start']) d['project_end'] = date2dat(d['project_end']) if d['budget'] and d['budget'] > 0: d['fte'] = d['budget'] / 1680.0 data.append(d) return flask_response(data)
def get(cls, domain, name): """ Get the requested site entry @param domain: Domain name @type domain: Domain @param name: Site name @type name: str @rtype: Domain """ Site = cls return Session.query(Site).filter(Site.domain == domain).filter(collate(Site.name, 'NOCASE') == name).first()
def showMenu(restaurant_id): """Show all the menu items for a restaurant. Args: restaurant_id: (integer) Id of the restaurant in the database. """ restaurant = session.query(Restaurant).filter_by(id=restaurant_id).one() items = session.query(MenuItem).filter_by(restaurant_id=restaurant_id).order_by(collate(MenuItem.name, 'NOCASE')).all() if len(items) == 0: flash('There are no menu items for this restaurant.') return render_template('menu.html', restaurant=restaurant, items=items)
def showRestaurants(): """Show all the restuarants in the database.""" ''' restaurant = session.query(Restaurant).filter_by(id=restaurant_id).one() items = session.query(MenuItem).filter_by(restaurant_id=restaurant_id) return render_template('menu.html', restaurant=restaurant, items=items) ''' restaurants = session.query(Restaurant).order_by(collate(Restaurant.name, 'NOCASE')).all() if len(restaurants) == 0: flash('There are no restaurants in the database.') return render_template('restaurants.html', restaurants=restaurants)
def get(cls, domain, name): """ Get the requested site entry @param domain: Domain name @type domain: Domain @param name: Site name @type name: str @rtype: Domain """ Site = cls return Session.query(Site).filter(Site.domain == domain).filter( collate(Site.name, 'NOCASE') == name).first()
def get(self, query_args): query = User.query # Apply filters filters = [] if "search" in query_args and query_args["search"]: filters.append( or_( User.name.like("%{filter}%".format(filter=query_args["search"])), User.email.like("%{filter}%".format(filter=query_args["search"])), ) ) if "name" in query_args and query_args["name"]: filters.append(User.name.like("%{filter}%".format(filter=query_args["name"]))) if "email" in query_args and query_args["email"]: filters.append(User.email.like("%{filter}%".format(filter=query_args["email"]))) if "location" in query_args and query_args["location"]: filters.append(User.location.like("%{filter}%".format(filter=query_args["location"]))) if "confirmed" in query_args: filters.append(User.confirmed == query_args["confirmed"]) if "role_id" in query_args and query_args["role_id"]: filters.append(User.role_id == query_args["role_id"]) if "created_at" in query_args and query_args["created_at"]: filters.append(User.created_at == query_args["created_at"]) if filters: query = query.filter(*filters) # Apply sorting order_by = User.id if "sort" in query_args and query_args["sort"]: column, direction = PaginationHelper.decode_sort(query_args["sort"]) if column == "role.name": query = query.join(Role, User.role) order_by = Role.name elif column in set(User.__table__.columns.keys()): order_by = getattr(User, column) order_by = collate(order_by, "NOCASE") if direction == PaginationHelper.SORT_DESCENDING: order_by = desc(order_by) query = query.order_by(order_by) pagination_helper = PaginationHelper( request, query=query, resource_for_url="api.users", key_name="results", schema=user_schema, query_args=query_args, ) result = pagination_helper.paginate_query() return result
def all_langs( user: orm_users.User = None, ) -> typing.List[schema_feeds.Language]: q = sqla.select( orm_feeds.Feed.Language, ).order_by( sqla.collate(orm_feeds.Feed.Language, "NOCASE"), ).distinct() if user: q = feeds_filter.filter_display(q, user) with model.get_session() as session: return [schema_feeds.Language[e["Language"]] for e in session.execute(q)]
def get(self, query_args): query = Role.query filters = [] if "search" in query_args and query_args["search"]: filters.append( Role.name.like( "%{filter}%".format(filter=query_args["search"]))) if "name" in query_args: filters.append( Role.name.like("%{filter}%".format(filter=query_args["name"]))) if "default" in query_args: filters.append(Role.default == query_args["default"]) if "permissions" in query_args: filters.append(Role.permissions == query_args["permissions"]) if filters: query = query.filter(*filters) # Apply sorting order_by = Role.id if "sort" in query_args and query_args["sort"]: column, direction = PaginationHelper.decode_sort( query_args["sort"]) if column in set( Role.__table__.columns.keys() + ["follow", "comment", "write", "moderate", "admin"]): order_by = getattr(Role, column) order_by = collate(order_by, "NOCASE") if direction == PaginationHelper.SORT_DESCENDING: order_by = desc(order_by) query = query.order_by(order_by) pagination_helper = PaginationHelper( request, query=query, resource_for_url="api.roles", key_name="results", schema=role_schema, query_args=query_args, ) result = pagination_helper.paginate_query() return result
class HeadwordForm(Form): headword = TextField('Headword', validators=[DataRequired(), Length(max=50)]) definition = TextAreaField('Definition', validators=[DataRequired()]) see = TextField('See', validators=[]) pronunciation = TextField('Pronunciation', validators=[]) notes = TextAreaField('Notes', validators=[]) archived = BooleanField('Archived') word_class = QuerySelectField( query_factory=lambda: db.session.query(Word_class) .order_by(asc(collate(Word_class.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) data_set = QuerySelectField( query_factory=lambda: db.session.query(Data_set) .order_by(asc(collate(Data_set.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none") homonym_number = QuerySelectField( query_factory=lambda: db.session.query(Homonym_number) .order_by(asc(collate(Homonym_number.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) sense_number = QuerySelectField( query_factory=lambda: db.session.query(Sense_number) .order_by(asc(collate(Sense_number.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) origin = QuerySelectField( query_factory=lambda: db.session.query(Origin) .order_by(asc(collate(Origin.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) register = QuerySelectField( query_factory=lambda: db.session.query(Register) .order_by(asc(collate(Register.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) domain = QuerySelectField( query_factory=lambda: db.session.query(Domain) .order_by(asc(collate(Domain.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) region = QuerySelectField( query_factory=lambda: db.session.query(Region).filter_by(archived=False) .order_by(asc(collate(Region.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) flag = QuerySelectField( query_factory=lambda: db.session.query(Flag).filter_by(archived=False) .order_by(asc(collate(Flag.name, 'NOCASE'))).all(), get_pk =lambda a: a.id, get_label =lambda a: a.name, allow_blank=True, blank_text="none" ) def getattr(self, name): return getattr(self, name) def __init__(self, *args, **kwargs): super(HeadwordForm, self).__init__(*args, **kwargs) self.user = None def validate(self): initial_validation = super(HeadwordForm, self).validate() if not initial_validation: return False return True
def case_insensitive_sqlite(element, compiler, **kw): arg1, = list(element.clauses) return compiler.process(collate(arg1, 'NOCASE'), **kw)
def all(): return Category.query.order_by(collate(Category.name, 'NOCASE'))
def getAllItems(self): """Convenience method to get all items regardless of category. """ return self.db_session.query(Item) \ .order_by(collate(Item.name, 'NOCASE')).all()
def order_title(q: sqla.sql.Select, ) -> sqla.sql.Select: q = q.order_by(sqla.collate(orm_feeds.Feed.Title, "NOCASE"), ) return q
def get(self, query_args): query = Post.query # Apply filters filters = [] if "search" in query_args and query_args["search"]: filters.append( Post.title.like( "%{filter}%".format(filter=query_args["search"]))) if "title" in query_args: filters.append( Post.title.like( "%{filter}%".format(filter=query_args["title"]))) if "sub_title" in query_args: filters.append( Post.title.like( "%{filter}%".format(filter=query_args["sub_title"]))) if "slug" in query_args: filters.append( Post.slug.like("%{filter}%".format(filter=query_args["slug"]))) if "category_id" in query_args: filters.append( PostCategory.category_id == query_args["category_id"]) if "author_id" in query_args: filters.append(PostAuthor.user_id == query_args["author_id"]) if "created_at" in query_args: filters.append(Post.created_at == query_args["created_at"]) if "excluded_ids" in query_args: filters.append(Post.id.notin_(query_args["excluded_ids"])) if filters: query = query.filter(*filters) # Apply sorting order_by = Post.id if "sort" in query_args and query_args["sort"]: column, direction = PaginationHelper.decode_sort( query_args["sort"]) if column == "category.name": query = (query.join(PostCategory, Post.categories).join( Category, PostCategory.category).filter( PostCategory.primary.is_(True))) order_by = Category.name elif column == "author.name": query = (query.join(PostAuthor, Post.authors).join( User, PostAuthor.user).filter(PostAuthor.primary.is_(True))) order_by = User.name elif column in set(Post.__table__.columns.keys()): order_by = getattr(Post, column) order_by = collate(order_by, "NOCASE") if direction == PaginationHelper.SORT_DESCENDING: order_by = desc(order_by) query = query.order_by(order_by) pagination_helper = PaginationHelper( request, query=query, resource_for_url="api.posts", key_name="results", schema=post_schema, query_args=query_args, ) result = pagination_helper.paginate_query() return result
def attendants(request): attendants = Session.DBSession.query(Attendant).order_by( collate(Attendant.lname, 'NOCASE')) return {'attendants': attendants, 'path': request.path_info}
def apply_custom_query(search_type, db, keywords, filters): keyword_conditions = [] negation_argument = None for i, each_keyword in enumerate(keywords): if each_keyword == 'NOT': negation_argument = keywords[i + 1] if search_type == 'package': each_keyword_condition = Package.name.notlike( f'%{negation_argument}%') elif search_type == 'channel': each_keyword_condition = collate( Channel.name, "und-x-icu").notlike(f'%{negation_argument}%') else: if each_keyword != negation_argument: if search_type == 'package': each_keyword_condition = Package.name.ilike( f'%{each_keyword}%') elif search_type == 'channel': each_keyword_condition = collate( Channel.name, "und-x-icu").ilike(f'%{each_keyword}%') keyword_conditions.append(each_keyword_condition) query = db.filter(and_(*keyword_conditions)) for each_filter in filters: key, values = each_filter negate = False if key.startswith('-'): key = key[1:] negate = True each_filter_conditions = [] for each_val in values: each_val = each_val.strip('"').strip("'") if search_type == 'package': if key == 'channel': each_val_condition = collate( Channel.name, "und-x-icu").ilike(f'%{(each_val)}%') elif key == 'description': each_val_condition = Package.description.contains(each_val) elif key == 'summary': each_val_condition = Package.summary.contains(each_val) elif key == 'format': each_val_condition = cast(PackageVersion.package_format, String).ilike(f'%{(each_val)}%') elif key == 'platform': each_val_condition = Package.platforms.ilike( f'%{(each_val)}%') elif key == 'version': each_val_condition = PackageVersion.version.ilike( f'%{(each_val)}%') elif key == 'uploader': each_val_condition = User.username.ilike(f'%{(each_val)}%') elif search_type == 'channel': if key == 'description': each_val_condition = Channel.description.contains(each_val) elif key == 'private': each_val_condition = Channel.private.is_( bool(distutils.util.strtobool(each_val))) each_filter_conditions.append(each_val_condition) if negate: query = query.filter(not_(or_(*each_filter_conditions))) else: query = query.filter(or_(*each_filter_conditions)) return query
def getCategories(self): """Convenience method to get all categories. """ return self.db_session.query(Category) \ .order_by(collate(Category.name, 'NOCASE')) \ .all()
def showSports(): """This query will show all sports in asc order""" sports = session.query(Sports).order_by(asc(collate(Sports.name, 'NOCASE'))) return render_template(TEMPLATE_PATH + 'sports.html', sports=sports)