def query_apply_date_filter(query: BaseQuery, date_from: Optional[int],
date_to: Optional[int]) -> BaseQuery:
if date_from is not None:
query = query.filter(Job.t_end > date_from)
if date_to is not None:
query = query.filter(Job.t_end < date_to)
return query
def model_filter_by_get_params(model: DefaultMeta, query: BaseQuery,
args: dict) -> BaseQuery:
"""
Here is implemented a search and filtering the model by request url params. There are several possible variants of
search, and, of course, they can be easily combined to attain the necessary result.
Method received three obligatory parameters: model - sqlalchemy orm model class, query - :class:`BaseQuery` object
to filtering and args - dict of already parsed url params.
1. User can look for the model instances by putting columns names directly and their theoretical values, like:
http://localhost:5000/api/users?username=dima&name=dmytro. Such queries will select only exact matches.
2. To search without an exact match use `-like` ending after db columns names. It works only if column has type
:class:`sqlalchemy.sql.sqltypes.String` and gives the result from sql `LIKE` statement. Common url looks
like: http://localhost:5000/api/users?username-like=ma
3. To sort an output use 'ordered-by' and 'ordered-by-desc' parameter and specify db column name to order by:
http://localhost:5000/api/users?name-like=a&ordered-by=username
4. To restrict the number of results use 'limit' and 'offset' statements together or separately:
http://localhost:5000/api/users?name-like=a&ordered-by=username&limit=2&offset=3
A simple use case is below::
from flask import request
from app import db
@route('/')
def index():
users = User.query
users = model_filter_by_get_params(User, users, request.args).all()
If given params are not valid, they will be ignored.
The function can pe applied for different models and queries and can be expanded if it is necessary.
:param model: model base class
:type model: DefaultMeta
:param query: not executed query object to filtering
:type query: BaseQuery
:param args: url query parameters
:type args: dict
:return: not executed filtered query object
:rtype: BaseQuery
"""
model_columns = model.__table__.c.keys()
for key, value in args.items():
if key == 'ordered-by':
if value in model_columns:
query = query.order_by(getattr(model, value))
elif key == 'ordered-by-desc':
if value in model_columns:
query = query.order_by(desc(getattr(model, value)))
elif key.endswith('-like'):
attr = key.split('-like')[0]
if attr in model_columns and isinstance(
model.__table__.c.get(attr).type, String):
query = query.filter(getattr(model, attr).ilike(f'%{value}%'))
elif key in model_columns:
query = query.filter(getattr(model, key) == value)
if 'limit' in args:
query = query.limit(int(args.get('limit')))
if 'offset' in args:
query = query.offset(int(args.get('offset')))
return query
def gen_where(base_query: BaseQuery, query: BaseQuery,
partition: Optional[str], account: Optional[str],
state: Optional[str]) -> BaseQuery:
if partition is not None:
query = query.filter(base_query.c.partition == partition)
if account is not None:
query = query.filter(base_query.c.account == account)
if state is not None: query = query.filter(base_query.c.state == state)
return query
def query_apply_common_filter(query: BaseQuery, accounts: List[str],
partitions: List[str],
states: List[str]) -> BaseQuery:
if len(accounts) > 0:
query = query.filter(Job.account.in_(accounts))
if len(partitions) > 0:
query = query.filter(Job.partition.in_(partitions))
if len(states) > 0:
query = query.filter(Job.state.in_(states))
return query
def get_filtered_query(self, query: BaseQuery, **kwargs) -> BaseQuery:
"""Filter query using query parameters"""
if 'q' in kwargs:
return query.filter(
ProductGroup.slug.ilike('%{}%'.format(kwargs['q'])))
return super().get_filtered_query(query, **kwargs)
def ql_don_hang_theo_ngay(page):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 1:
return redirect(url_for('dang_nhap', next=request.url))
form = Form_QL_don_hang()
tieu_de = 'Đơn hàng ngày hôm nay'
today = datetime.now()
ngay_tim_kiem = today.date()
if session.get('ngay_tim_kiem'):
temp_1 = datetime.strptime(session['ngay_tim_kiem'],
"%a, %d %b %Y %H:%M:%S %Z")
ngay_tim_kiem = temp_1.date()
if form.validate_on_submit():
ngay_tim_kiem = form.ngay_tim_kiem.data
session['ngay_tim_kiem'] = ngay_tim_kiem
page = 1
query = BaseQuery(Hoa_don, dbSession)
page_filter = query.filter(
Hoa_don.ngay_tao_hoa_don == ngay_tim_kiem).paginate(page, 5, False)
if page_filter.total == 0:
tieu_de = "Không tìm thấy hóa đơn"
else:
tieu_de = "Đơn hàng của ngày " + str(
ngay_tim_kiem.day) + " tháng " + str(
ngay_tim_kiem.month) + " năm " + str(ngay_tim_kiem.year)
return render_template('Quan_ly/QL_don_hang/QL_don_hang_theo_ngay.html',
page_filter=page_filter,
form=form,
tieu_de=tieu_de)
def force_query_by_request_values(model, query: BaseQuery = None, order=None):
if query is None:
query = model.query
for param, value in filter(lambda x: hasattr(model, x),
request.values.keys()):
query = query.filter(getattr(model, param).like('{}%'.format(value)))
return force_pagination(query, order)
def set_search_filter(
query: BaseQuery,
obj_model: Model,
search_field: str = None,
search_value=None,
*args,
**kwargs,
) -> BaseQuery:
"""
Utility for modifying a SQLAlchemy query by adding a filter. This uses the 'LIKE' modifier from SQL, and adds
wildcards to either side of the query. This does _not_ support lists of values to search through
Args:
query:
obj_model:
search_field:
search_value:
*args:
**kwargs:
Returns:
object:
"""
if search_field is not None and search_value is not None:
column = next(
(c for c in inspect(obj_model).columns if c.name == search_field),
None)
if column is not None:
query = query.filter(column.like(f"%{search_value}%"))
return query
def xem_hd_theo_trang_thai(id):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 1:
return redirect(url_for('dang_nhap', next=request.url))
form = Form_lua_chon()
query = BaseQuery(Hoa_don, dbSession)
trang_thai = 0
if session.get('trang_thai'):
trang_thai = session['trang_thai']
if form.validate_on_submit():
trang_thai = int(form.lua_chon.data)
session['trang_thai'] = trang_thai
id = 1
if trang_thai == 0:
tieu_de = 'Chưa thanh toán'
elif trang_thai == 1:
tieu_de = 'Đã thanh toán'
elif trang_thai == 2:
tieu_de = 'Huỷ'
page_filter = query.filter(Hoa_don.trang_thai == trang_thai).paginate(
id, 5, False)
return render_template(
'Quan_ly/QL_don_hang/QL_don_hang_theo_trang_thai.html',
tieu_de=tieu_de,
page_filter=page_filter,
form=form)
def paginated_search_query(model, query: BaseQuery = None):
if query is None:
query = model.query
for arg_name, value in request.args.items():
if arg_name == 'row_id': arg_name = 'id'
field = getattr(model, arg_name, None)
if field is not None and value:
from sqlalchemy import DateTime, Date
is_date_field = isinstance(field.property.columns[0].type,
(Date, DateTime))
if is_date_field and '_' in value:
value_from, value_to = value.split('_', 1)
query = query.filter(field.between(value_from, value_to))
else:
query = query.filter(field.like('%{}%'.format(value)))
return force_pagination(query)
def ql_so_luong_ton(page):
if not current_user.is_authenticated or current_user.ma_loai_nguoi_dung != 2:
return redirect(url_for('log_in', next=request.url))
form = Form_tim_kiem()
query = BaseQuery(San_pham, dbSession)
page_filter = query.paginate(page,5,False)
if form.validate_on_submit():
tim_kiem = form.noi_dung.data
if tim_kiem.isdigit():
page_filter = query.filter(San_pham.ma_san_pham == int(tim_kiem)).paginate(page,5,False)
else:
chuoi_truy_van = '%'+tim_kiem.upper()+'%'
page_filter = query.filter(San_pham.ten_san_pham.like(chuoi_truy_van)).paginate(page,5,False)
if len(page_filter.items) == 0:
thong_bao = 'Không tìm thấy sản phẩm!'
return render_template('Quan_ly/QL_kho_hang/Ton_kho.html', form=form, page_filter = page_filter)
def query_articles_by_search(self, q: str, query_obj: BaseQuery = None):
if query_obj is not None:
query_obj = query_obj.filter(
or_(Article.title.like(f'%{q}%'), Article.desc.like(f'%{q}%')))
else:
query_obj = Article.query.filter(
or_(Article.title.like(f'%{q}%'), Article.desc.like(f'%{q}%')))
return query_obj
def apply(self, query: BaseQuery, value: Any) -> BaseQuery:
"""
Filter saved queries to only those created by current user.
:returns: flask-sqlalchemy query
"""
return query.filter(SavedQuery.created_by == g.user # pylint: disable=comparison-with-callable
)
def apply(self, query: BaseQuery, func: Callable) -> BaseQuery:
"""
Filter queries to only those owned by current user if
can_only_access_owned_queries permission is set.
:returns: query
"""
if security_manager.can_only_access_owned_queries():
query = query.filter(Query.user_id == g.user.get_user_id())
return query
def apply(self, query: BaseQuery, value: Callable) -> BaseQuery:
"""
Filter queries to only those owned by current user. If
can_access_all_queries permission is set a user can list all queries
:returns: query
"""
if not security_manager.can_access_all_queries():
query = query.filter(Query.user_id == g.user.get_user_id())
return query
def public_or_owner(query: BaseQuery,
user: Optional[BrewerProfile]) -> BaseQuery:
"""Filter Brew query of all non-accessible objects.
:param query: query over Brew objects
:type query: BaseQuery
:param user: actor object, may be None
:type user: Optional[BrewerProfile]
:return: filtered query
:rtype: BaseQuery
"""
if user is not None:
query = query.filter(
db.or_(
BrewerProfile.id == user.id,
db.and_(BrewerProfile.is_public.is_(True),
Brew.is_public.is_(True))))
else:
query = query.filter(BrewerProfile.is_public.is_(True),
Brew.is_public.is_(True))
return query
def query_apply_tags_filter(query: BaseQuery, req_tags: List[str],
opt_tags: List[str],
no_tags: List[str]) -> BaseQuery:
for tag in req_tags:
query = query.filter(JobTag.tags.contains(tag))
# --
opt_tags_checks = []
for tag in opt_tags:
opt_tags_checks.append(JobTag.tags.contains(tag))
if len(opt_tags_checks) > 0:
query = query.filter(or_(*opt_tags_checks))
# --
for tag in no_tags:
query = query.filter(~JobTag.tags.contains(tag))
return query
def apply_filter(model: DefaultMeta, query: BaseQuery) -> BaseQuery:
for param, value in request.args.items():
if param not in {'fields', 'sort', 'page', 'limit'}:
operator = '=='
match = COMPARISON_OPERATOR_RE.match(param)
if match is not None:
param, operator = match.groups()
column_attr = getattr(model, param, None)
if column_attr is not None:
value = model.additional_validation(param, value)
if value is None:
continue
filter_argument = _get_filter_argument(column_attr, value, operator)
query = query.filter(filter_argument)
return query
def _construct_query(
query: BaseQuery,
filter: BinaryExpression = None,
order: BinaryExpression = None,
) -> BaseQuery:
"""
A convenience function to save code space for query generations. Takes filters
and order_bys and applies them to the query, returning a query ready to be ran.
:param query: A query that can be built upon
:param filter: A SQLAlchemy query filter expression
:param order: A SQLAlchemy query order_by expression
:return: A Flask-SQLAlchemy ``BaseQuery``
"""
if filter is not None:
query = query.filter(filter)
if order is not None:
query = query.order_by(order)
return query
def apply_filter(model: DefaultMeta, query: BaseQuery) -> BaseQuery:
"""
Functionality of filtering resources, returns filter arguments to query
(example: id[gte]=3)
"""
params = request.args.items()
if params:
for param, value in params:
if param not in ["fields", "sort", "page", "limit"]:
operator = "=="
match = COMPARISON_OPERATORS_RE.match(param)
if match is not None:
param, operator = match.groups()
column_attr = getattr(model, param, None)
if column_attr is not None:
value = model.additional_validation(param, value)
if value is None:
continue
filter_argument = _get_filter_argument(
column_attr, value, operator)
query = query.filter(filter_argument)
return query
def _filter_event(event_id: Union[BaseEvent, int],
add_to: BaseQuery) -> BaseQuery:
return add_to.filter(BaseWord.event_start_id <= event_id) \
.filter(or_(BaseWord.event_end_id > event_id, BaseWord.event_end_id.is_(None)))
def apply_filter(query: BaseQuery) -> BaseQuery:
for param, value in request.args.items():
if param not in {"fields", "sort", "page", "limit"}:
if param == "username":
query = query.filter(
GithubUserInfo.username.like(f"%{value}%"))
continue
if param == "language":
langs_list = value.split(" ")
for lang in langs_list:
query = query.join(GithubUserInfo.repositories).filter(
GithubRepositories.languages.contains(lang))
continue
if param.startswith("number_of_repositories"):
if param.endswith("[gt]"):
users_ids_list = []
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
if len(user.repositories) > int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
elif param.endswith("[gte]"):
users_ids_list = []
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
if len(user.repositories) >= int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
elif param.endswith("[lt]"):
users_ids_list = []
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
if len(user.repositories) < int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
elif param.endswith("[lte]"):
users_ids_list = []
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
if len(user.repositories) <= int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
else:
users_ids_list = []
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
if len(user.repositories) == int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
if param.startswith("date"):
pass
## last_commit_date scraping functionality to be added to database
## try:
## value = datetime.strptime(value, "%d-%m-%Y").date()
## if param.endswith("[gt]"):
## query = query.filter(GithubUserInfo.date > value)
## continue
## elif param.endswith("[gte]"):
## query = query.filter(GithubUserInfo.date >= value)
## continue
## elif param.endswith("[lt]"):
## query = query.filter(GithubUserInfo.date < value)
## continue
## elif param.endswith("[lte]"):
## query = query.filter(GithubUserInfo.date <= value)
## continue
## else:
## query = query.filter(GithubUserInfo.date == value)
## continue
## except ValueError:
## continue
if param.startswith("stars"):
if param.endswith("[gt]"):
users_ids_list = []
users_stars_dict = {}
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
users_stars_dict[user] = 0
for repo in user.repositories:
users_stars_dict[user] = (
users_stars_dict[user] + repo.stars)
if users_stars_dict[user] > int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
elif param.endswith("[gte]"):
users_ids_list = []
users_stars_dict = {}
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
users_stars_dict[user] = 0
for repo in user.repositories:
users_stars_dict[user] = (
users_stars_dict[user] + repo.stars)
if users_stars_dict[user] >= int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
elif param.endswith("[lt]"):
users_ids_list = []
users_stars_dict = {}
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
users_stars_dict[user] = 0
for repo in user.repositories:
users_stars_dict[user] = (
users_stars_dict[user] + repo.stars)
if users_stars_dict[user] < int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
elif param.endswith("[lte]"):
users_ids_list = []
users_stars_dict = {}
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
users_stars_dict[user] = 0
for repo in user.repositories:
users_stars_dict[user] = (
users_stars_dict[user] + repo.stars)
if users_stars_dict[user] <= int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
else:
users_ids_list = []
users_stars_dict = {}
subquery = (query.join(
GithubUserInfo.repositories).distinct(
GithubUserInfo.id).all())
for user in subquery:
users_stars_dict[user] = 0
for repo in user.repositories:
users_stars_dict[user] = (
users_stars_dict[user] + repo.stars)
if users_stars_dict[user] == int(value):
users_ids_list.append(user.id)
query = query.filter(
GithubUserInfo.id.in_(users_ids_list))
continue
return query.all()
return query
def _add_date_filter_to_query(self, query: BaseQuery,
start_date: datetime.datetime,
end_date: datetime.datetime) -> BaseQuery:
return query.filter(self.model.date.between(start_date, end_date))
def get_filtered_query(self, query: BaseQuery, **kwargs) -> BaseQuery:
"""Filter query using query parameters"""
end = kwargs.get('end')
start = kwargs.get('start')
return query.filter(IndicatorValue.timestamp >= start,
IndicatorValue.timestamp < end)
def gather_muts_and_sites(self,
mutations: BaseQuery,
sites: BaseQuery,
show_progress=True,
occurrences_in: List[MutationSource] = None,
intersection=None) -> MotifsData:
"""If occurrences_in is provided, the count of mutations will
represent number of occurrences of mutations in provided
sources, instead of number of distinct substitutions.
"""
if intersection:
accepted_sites = sites.join(Mutation.affected_sites).filter(
and_(*[Mutation.in_sources(source)
for source in intersection])).all()
else:
accepted_sites = sites.all()
mutations_affecting_sites = mutations.filter(
Mutation.affected_sites.any(Site.types.contains(self.site_type)))
muts_around_sites_with_motif = defaultdict(dict)
muts_breaking_sites_motif = defaultdict(dict)
sites_with_broken_motif = defaultdict(set)
sites_with_motif = select_sites_with_motifs(accepted_sites,
self.site_specific_motifs)
if occurrences_in:
def mutation_count(mut: Mutation):
return sum([
mut.sources_map[source.name].get_value()
if source.name in mut.sources_map else 0
for source in occurrences_in
])
else:
def mutation_count(mut):
return 1
is_affected = self.breaking_modes[self.mode]
if show_progress:
ptm_muts = mutations_affecting_sites.count()
mutations_affecting_sites = tqdm(mutations_affecting_sites,
total=ptm_muts)
for mutation in mutations_affecting_sites:
sites = mutation.affected_sites
for site in sites:
if site not in accepted_sites:
continue
for motif_name, motif in self.site_specific_motifs.items():
if site in sites_with_motif[motif_name]:
count = mutation_count(mutation)
muts_around_sites_with_motif[motif_name][
mutation] = count
mutated_sequence = mutate_sequence(site,
mutation,
offset=7)
if is_affected(mutated_sequence, motif):
sites_with_broken_motif[motif_name].add(site)
muts_breaking_sites_motif[motif_name][
mutation] = count
return MotifsData(
sites_with_motif=sites_with_motif,
sites_with_broken_motif=sites_with_broken_motif,
muts_around_sites_with_motif=muts_around_sites_with_motif,
muts_breaking_sites_motif=muts_breaking_sites_motif)