def get_user_paginator(
cls,
instance,
page=1,
item_count=None,
items_per_page=50,
user_ids=None,
GET_params=None,
):
"""
returns paginator over users belonging to the group
:param instance:
:param page:
:param item_count:
:param items_per_page:
:param user_ids:
:param GET_params:
:return:
"""
if not GET_params:
GET_params = {}
GET_params.pop("page", None)
query = instance.users_dynamic
if user_ids:
query = query.filter(
cls.models_proxy.UserGroup.user_id.in_(user_ids))
return SqlalchemyOrmPage(query,
page=page,
item_count=item_count,
items_per_page=items_per_page,
**GET_params)
def get_paginator(
cls,
user,
page=1,
item_count=None,
items_per_page=50,
order_by=None,
filter_settings=None,
db_session=None,
):
if not filter_settings:
filter_settings = {}
registry = get_current_registry()
resources = UserService.resources_with_perms(
user, ["view"], resource_types=registry.resource_types)
resource_ids = [r.resource_id for r in resources]
query = EventService.for_resource(resource_ids,
or_target_user_id=user.id,
limit=100,
db_session=db_session)
paginator = SqlalchemyOrmPage(query,
page=page,
items_per_page=items_per_page,
**filter_settings)
return paginator
def get_user_paginator(cls, group, page=1, item_count=None,
items_per_page=50, db_session=None, **kwargs):
""" returns paginator over users belonging to the group"""
query = group.users_dynamic
return SqlalchemyOrmPage(query, page=page, item_count=item_count,
items_per_page=items_per_page,
**kwargs)
def get_rooms(request):
"""Retrieve a list of rooms"""
page = to_int(request.GET.get("page"), 1)
limit = to_int(request.GET.get("limit"), 10)
name = request.GET.get("name")
host_id = request.GET.get("host_id")
capacity = request.GET.get("capacity")
sort = request.GET.get("sort", "created_at")
sort_order = request.GET.get("sort_order")
session = request.dbsession
query = session.query(Room)
# Filter
if host_id is not None:
query = query.filter_by(host_id=host_id)
if capacity is not None:
query = query.filter_by(capacity=capacity)
if name is not None:
query = query.filter(Room.name.ilike("%" + name + "%"))
# Sorting
try:
if sort is not None:
order = desc if sort_order == "desc" else asc
query = query.order_by(order(getattr(Room, sort)))
except AttributeError:
raise HTTPBadRequest("Invalid sort params")
# Paging
page = SqlalchemyOrmPage(query, page=page, items_per_page=limit, db_session=session)
rooms = [encoding.encode_room(room) for room in page.items]
return {"data": rooms, "total": page.item_count}
def admin_get_pars(page_num: int) -> dict:
session = db_session.create_session()
query = session.query(History)
page = SqlalchemyOrmPage(query, page=page_num, items_per_page=10)
pars = sorted(page.items, key=lambda x: x.id, reverse=True)
max_page = query.count() // 10 + 1
session.close()
return {'pars': pars, 'max': max_page}
def paginator(request, q, pp=50):
query_params = request.params.mixed()
page = int(request.params.get("page", 1))
def url_maker(link_page):
query_params['page'] = link_page
return request.current_route_url(_query=query_params)
return SqlalchemyOrmPage(q, page, items_per_page=pp,
url_maker=url_maker)
def paging(self, query):
"""
Creating paging response object with records
:param query: SqlAlchemyQuery object
"""
return (SqlalchemyOrmPage(query,
page=self.page['current_page'],
url_maker=self.page['url_for_page'],
items_per_page=self.page['items_per_page']))
def get_paginator(self, query, page=1):
query_params = self.request.GET.mixed()
def url_maker(link_page):
# replace page param with values generated by paginator
query_params['page'] = link_page
return self.request.current_route_url(_query=query_params)
return SqlalchemyOrmPage(query, page, items_per_page=2, url_maker=url_maker)
def get_paginator(cls, page=1, item_count=None, items_per_page=50,
db_session=None, **kwargs):
""" returns paginator over users belonging to the group"""
db_session = get_db_session(db_session)
query = db_session.query(Group)
query = query.order_by(Group.group_name)
return SqlalchemyOrmPage(query, page=page, item_count=item_count,
items_per_page=items_per_page,
**kwargs)
def get_paginator(cls, request, page=1):
query = request.dbsession.query(BlogRecord)
query = query.order_by(sa.desc(BlogRecord.created))
query_params = request.GET.mixed()
def url_maker(link_page):
query_params['page'] = link_page
return request.current_route_url(_query=query_params)
return SqlalchemyOrmPage(query, page, items_per_page=5, url_maker=url_maker)
def list_of_users(current_page: int, current_items_per_page: int
): # выдача списка пользователей с пагинацией
query = session.query(User) # загружаем всю базу из класса
page = SqlalchemyOrmPage(query,
page=current_page,
items_per_page=current_items_per_page)
list_of_users = page.items
final_list = []
for element in list_of_users:
final_list.append(element.login)
return final_list
def admin_get_users(page_num: int) -> dict:
session = db_session.create_session()
query = session.query(User).filter(User.is_approved == True)
page = SqlalchemyOrmPage(query, page=page_num, items_per_page=10)
users = sorted(page.items, key=lambda x: x.id, reverse=True)
max_page = query.count() // 10 + 1
for user in users:
auth = user.auth
user.auth = auth
session.close()
return {'users': users, 'max': max_page}
def list_view(self):
rows = self.context.crud.read()
try:
paginator_attr = get_paginator(self.request,
self.context.items_per_page - 1)
except ValueError:
raise HTTPNotFound
params = {'paginator': SqlalchemyOrmPage(rows, **paginator_attr)}
return render_to_response(self.context.renderer,
params,
request=self.request)
def get_paginator(cls, request, model, page=1):
query = request.dbsession.query(model)
query_params = request.GET.mixed()
def url_maker(link_page):
query_params['page'] = link_page
return request.current_route_url(_query=query_params)
return SqlalchemyOrmPage(query,
page,
items_per_page=30,
url_maker=url_maker)
def get_paginator(cls, request, page=1):
query = request.dbsession.query(Results)
query = query.order_by(sa.desc(Results.dt))
query_params = request.GET.mixed()
def url_maker(link_page):
# replace page param with values generated by paginator
query_params['page'] = link_page
return request.current_route_url(_query=query_params)
return SqlalchemyOrmPage(query, page, items_per_page=5,
url_maker=url_maker)
def get_paginator(cls, request, page=1):
query = cls.query.order_by(sa.desc(BlogRecord.created))
query_params = request.GET.mixed()
def url_maker(link_page):
# replace page param with values generated by paginator
query_params['page'] = link_page
return request.current_route_url(_query=query_params)
return SqlalchemyOrmPage(query,
page,
items_per_page=10,
url_maker=url_maker)
def admin_get_reg(page_num: int) -> dict:
session = db_session.create_session()
query = session.query(Applications)
page = SqlalchemyOrmPage(query, page=page_num, items_per_page=10)
users = sorted(page.items, key=lambda x: x.date)
for application in users:
user = application.user
auth = user.auth
user.auth = auth
application.user = user
max_page = query.count() // 10 + 1
session.close()
return {'users': users, 'max': max_page}
def get_wrapper(obj, *args, **kwargs):
if isinstance(obj, (list, tuple)):
return Page(obj, *args, **kwargs)
if isinstance(obj, sqlalchemy.orm.query.Query):
return SqlalchemyOrmPage(obj, *args, **kwargs)
required_methods = ["__iter__", "__len__", "__getitem__"]
for m in required_methods:
if not hasattr(obj, m):
break
else:
return obj
raise TypeError(INCOMPATIBLE_COLLECTION_TYPE)
def cql_view(request):
if "ast" not in request.GET:
messages.error(request, 'No CQL Found')
return redirect("carpyncho:index")
user = request.user
session = request.corral_session
page = request.GET.get("page", 1)
# REGISTER THE QUERY
ast = models.CQLAst.get_register(request.GET["ast"])
cql_query = models.CQLQuery.get_register(request.GET["cql"], ast, user)
# THE QUERY ITSELF
result = cql.eval(ast.ast, session)
if isinstance(result, cql.Writer):
ext = result.fmt()
filename = "carpyncho_{}.{}".format(timezone.now().isoformat(), ext)
content_type = "text/{}".format(ext)
stream = result.stream(Echo())
response = StreamingHttpResponse(stream, content_type=content_type)
response["Content-Disposition"] = 'attachment; filename="{}"'.format(
filename)
return response
sources_query, columns = result
items_per_page = ast.ast["options"].get("limit")
if items_per_page is None or items_per_page >= ITEMS_PER_PAGE:
items_per_page = ITEMS_PER_PAGE
sources = SqlalchemyOrmPage(sources_query,
page,
items_per_page=items_per_page)
page = 1 if sources.page_count < page else page
ctx = {
"sources": sources,
"columns": columns,
"cql": cql_query.query,
"page": page
}
return render(request, 'webproject/cql.html', ctx)
def get_paginator(cls, request, page=1):
allowed_courses = set(['all']) | request.courses
query = cls.for_active_courses(allowed_courses)
query_params = request.GET.mixed()
def url_maker(link_page):
# replace page param with values generated by paginator
query_params['page'] = link_page
return request.current_route_url(_query=query_params)
return SqlalchemyOrmPage(
query,
page,
items_per_page=10,
url_maker=url_maker,
)
def get_paginator(cls,
page=1,
item_count=None,
items_per_page=50,
order_by=None,
filter_settings=None,
exclude_columns=None,
db_session=None):
registry = get_current_registry()
if not exclude_columns:
exclude_columns = []
if not filter_settings:
filter_settings = {}
db_session = get_db_session(db_session)
q = db_session.query(User)
if filter_settings.get('order_col'):
order_col = filter_settings.get('order_col')
if filter_settings.get('order_dir') == 'dsc':
sort_on = 'desc'
else:
sort_on = 'asc'
q = q.order_by(getattr(sa, sort_on)(getattr(User, order_col)))
else:
q = q.order_by(sa.desc(User.registered_date))
# remove urlgen or it never caches count
cache_params = dict(filter_settings)
cache_params.pop('url', None)
cache_params.pop('url_maker', None)
@registry.cache_regions.redis_min_5.cache_on_arguments()
def estimate_users(cache_key):
o_q = q.order_by(False)
return o_q.count()
item_count = estimate_users(cache_params)
# if the number of pages is low we may want to invalidate the count to
# provide 'real time' update - use case -
# errors just started to flow in
if item_count < 1000:
item_count = estimate_users.refresh(cache_params)
paginator = SqlalchemyOrmPage(q,
page=page,
item_count=item_count,
items_per_page=items_per_page,
**filter_settings)
return paginator
def get_paginator(cls,
page=1,
item_count=None,
items_per_page=50,
db_session=None,
filter_params=None,
**kwargs):
""" returns paginator over users belonging to the group"""
if filter_params is None:
filter_params = {}
db_session = get_db_session(db_session)
query = db_session.query(Entry)
query = query.order_by(Entry.resource_id)
return SqlalchemyOrmPage(query,
page=page,
item_count=item_count,
items_per_page=items_per_page,
**kwargs)
def get_paginator(cls,
page=1,
item_count=None,
items_per_page=50,
db_session=None,
filter_params=None,
**kwargs):
""" returns paginator over users belonging to the group"""
if filter_params is None:
filter_params = {}
db_session = get_db_session(db_session)
query = db_session.query(User)
user_name_like = filter_params.get('user_name_like')
if user_name_like:
query = query.filter(User.user_name.like(user_name_like + '%'))
query = query.order_by(User.id)
return SqlalchemyOrmPage(query,
page=page,
item_count=item_count,
items_per_page=items_per_page,
**kwargs)
def sa_list(self):
delete_action = self.make_selected_action()
if delete_action:
return delete_action
items_per_page = getattr(self.table, 'items_per_page', 10)
rows = self.crud.read()
try:
paginator_attr = get_paginator(self.request, items_per_page - 1)
except ValueError:
raise HTTPNotFound
paginator = SqlalchemyOrmPage(rows, **paginator_attr)
return {
'rows':
rows,
'paginator':
paginator,
'pk_to_list':
pk_to_list,
'breadcrumbs':
breadcrumbs(self.tname, get_table_verbose_name(self.table),
'sa_list')
}
def tweets():
page_num = int(request.args.get('_page', 1))
items_per_page = int(request.args.get('_items_per_page', 20))
feature_query = feature_query_args()
tweets = (
db.session.query(model.Tweet)
.filter(model.Tweet.collection == g.collection)
.filter(*(model.Tweet.features.contains({k: [v]}) for k, v in feature_query.items() if not k.startswith('_')))
.order_by(model.Tweet.created_at, model.Tweet.tweet_id)
)
page = SqlalchemyOrmPage(
tweets,
page=page_num, items_per_page=items_per_page,
url_maker=url_for_other_page,
)
return render_template(
'root/tweets.html',
page=page,
)
def get_users(request):
"""Retrieve a list all registered users or one user if username is in url query string."""
username_query = request.GET.get("username")
page = to_int(request.GET.get("page"), 1)
limit = to_int(request.GET.get("limit"), 10)
sort = request.GET.get("sort", "created_at")
sort_order = request.GET.get("sort_order")
session = request.dbsession
if username_query is not None:
user = session.query(User).filter_by(username=username_query).first()
if user is not None:
# enclosed in an array for return data consistency
return {"data": [encoding.encode_user(user)]}
else:
raise HTTPNotFound()
else:
query = session.query(User)
# Sorting
try:
if sort is not None:
order = desc if sort_order == "desc" else asc
query = query.order_by(order(getattr(User, sort)))
except AttributeError:
raise HTTPBadRequest("Invalid sort params")
# Paging
page = SqlalchemyOrmPage(query,
page=page,
items_per_page=limit,
db_session=session)
users = [encoding.encode_user(user) for user in page.items]
return {"data": users, "total": page.item_count}
name = Column(String(250), nullable=False)
def __repr__(self):
return "<{}>".format(self.name)
engine = create_engine('sqlite:///sqlalchemy_example.db')
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession()
for i in range(100):
new_person = Person(name='new person #%s' % i)
session.add(new_person)
session.commit()
query = session.query(Person)
print(query.count()) # 100
print
from paginate_sqlalchemy import SqlalchemyOrmPage
page = SqlalchemyOrmPage(query, page=5, items_per_page=8)
print(page)
print(page.items)
print(page.items[6].name)
print(page.page_count)
def index(self):
"""
List all users.
:param int page: The page number to load (e.g. ``/api/users/?page=2``).
:returns: A JSON object containing:
:data: A list of ``User`` objects.
:meta: Pagination information.
Example request::
http -j :/api/users/ Authorization:"Token ..."
Example response:
.. code-block:: json
{
"data": [
{
"created": "2014-09-19T12:34:07.937062+00:00",
"email": "*****@*****.**",
"id": 1,
"last_login": "******",
"profile": {
"first_name": "John",
"last_name": "Smith"
},
"role": "user",
"updated": "2014-09-19T12:47:08.303298+00:00"
},
{
"created": "2014-09-19T12:34:07.938278+00:00",
"email": "*****@*****.**",
"id": 2,
"last_login": null,
"profile": {
"first_name": "Jane",
"last_name": "Smith"
},
"role": "superuser",
"updated": "2014-09-19T12:34:07.938270+00:00"
}
],
"meta": {
"item_count": 2,
"items_per_page": 100,
"page": 1,
"page_count": 1
}
}
"""
# Initialize request variables
request = self.request
params = self.validation_results
meta = {}
# Initialize pager
page = SqlalchemyOrmPage(User.query,
page=params.get('page', 1),
items_per_page=self.items_per_page,
item_count=User.count())
# Build "meta" object
for key in ['page', 'page_count', 'item_count', 'items_per_page']:
meta[key] = getattr(page, key)
return dict(data=page.items, meta=meta)
