async def test_request_query_args_custom_parsing_asgi(app): @app.get("/") def handler(request): return text("pass") request, response = await app.asgi_client.get( "/?test1=value1&test2=&test3=value3" ) assert request.get_query_args(keep_blank_values=True) == [ ("test1", "value1"), ("test2", ""), ("test3", "value3"), ] assert request.query_args == [("test1", "value1"), ("test3", "value3")] assert request.get_query_args(keep_blank_values=False) == [ ("test1", "value1"), ("test3", "value3"), ] assert request.get_args(keep_blank_values=True) == RequestParameters( {"test1": ["value1"], "test2": [""], "test3": ["value3"]} ) assert request.args == RequestParameters( {"test1": ["value1"], "test3": ["value3"]} ) assert request.get_args(keep_blank_values=False) == RequestParameters( {"test1": ["value1"], "test3": ["value3"]} )
def source(self, request): """Pulls values off the request in the provided location if location is str: json -> dict form, args, file -> RequestParameters if location is sequence: return RequestParameters :param request: The sanic request object to parse arguments from """ if isinstance(self.location, str): try: value = getattr(request, self.location, RequestParameters()) except InvalidUsage as e: if self.ignore_invalid_usage: return RequestParameters() else: raise e if callable(value): value = value() if value: return value else: values = RequestParameters() for l in self.location: value = getattr(request, l, None) if callable(value): value = value() if value: values.update(value) return values return RequestParameters()
async def decorated_filter(request, *args, **kwargs): data = { 'headers': CIDict(request.headers), 'path': request.app.router.get(request)[2], 'params': RequestParameters(request.args), 'body': {} } if request.body: # Get body if we have something there if request.form: data['body'] = RequestParameters(request.form) else: # will raise 400 if cannot parse json data['body'] = deepcopy(request.json) if hasattr(request_schema, 'body') and request.form: _convert_params(request_schema.body, data['body']) if hasattr(request_schema, 'params') and data['params']: _convert_params(request_schema.params, data['params']) # Now, validate the whole thing try: model = request_schema(data, strict=False, validate=False) model.validate() request.validated = model.to_native() except BaseError as e: raise ValidationErrors(e.to_primitive()) return await get_response(request, *args, **kwargs)
def test_request_query_args_custom_parsing(app): @app.get("/") def handler(request): return text("pass") request, response = app.test_client.get( "/?test1=value1&test2=&test3=value3") assert request.get_query_args(keep_blank_values=True) == [ ('test1', 'value1'), ('test2', ''), ('test3', 'value3') ] assert request.query_args == [('test1', 'value1'), ('test3', 'value3')] assert request.get_query_args(keep_blank_values=False) == [ ('test1', 'value1'), ('test3', 'value3') ] assert request.get_args(keep_blank_values=True) == RequestParameters({ "test1": ["value1"], "test2": [""], "test3": ["value3"] }) assert request.args == RequestParameters({ "test1": ["value1"], "test3": ["value3"] }) assert request.get_args(keep_blank_values=False) == RequestParameters({ "test1": ["value1"], "test3": ["value3"] })
def get_invoices(request, contractId=None): args = RequestParameters(request.args) factura_obj = request.app.erp_client.model('giscedata.facturacio.factura') filters = [ ('polissa_state', '=', 'activa'), ('type', '=', 'in_invoice'), ('polissa_id.emp_allow_send_data', '=', True), ] filters = get_invoice_user_filters(request.app.erp_client, request.ctx.user, filters) if contractId: filters.append(('polissa_id.name', '=', contractId)) if args: filters = get_request_filters( request.app.erp_client, request, filters, ) invoices_ids = factura_obj.search(filters) return invoices_ids
def parse_response(resp, content, strict=False, content_type=None): """Parse the response returned by :meth:`OAuthRemoteApp.http_request`. :param resp: response of http_request :param content: content of the response :param strict: strict mode for form urlencoded content :param content_type: assign a content type manually """ if not content_type: content_type = resp.headers.get('content-type', 'application/json') ct, options = parse_options_header(content_type) if ct in ('application/json', 'text/javascript'): if not content: return {} return json_loads(content) if ct in ('application/xml', 'text/xml'): return get_etree().fromstring(content) if ct != 'application/x-www-form-urlencoded' and strict: return content charset = options.get('charset', 'utf-8') return RequestParameters(parse_qs(content, encoding=charset))
async def data(self, request, name, hash, table): table = urllib.parse.unquote_plus(table) pks = await self.pks_for_table(name, table) is_view = bool( list(await self.execute( name, "SELECT count(*) from sqlite_master WHERE type = 'view' and name=:n", { 'n': table, }))[0][0]) view_definition = None table_definition = None if is_view: view_definition = list(await self.execute( name, 'select sql from sqlite_master where name = :n and type="view"', { 'n': table, }))[0][0] else: table_definition = list(await self.execute( name, 'select sql from sqlite_master where name = :n and type="table"', { 'n': table, }))[0][0] use_rowid = not pks and not is_view if use_rowid: select = 'rowid, *' order_by = 'rowid' else: select = '*' order_by = ', '.join(pks) if is_view: order_by = '' # We roll our own query_string decoder because by default Sanic # drops anything with an empty value e.g. ?name__exact= args = RequestParameters( urllib.parse.parse_qs(request.query_string, keep_blank_values=True)) # Special args start with _ and do not contain a __ # That's so if there is a column that starts with _ # it can still be queried using ?_col__exact=blah special_args = {} special_args_lists = {} other_args = {} for key, value in args.items(): if key.startswith('_') and '__' not in key: special_args[key] = value[0] special_args_lists[key] = value else: other_args[key] = value[0] # Handle ?_filter_column and redirect, if present redirect_params = filters_should_redirect(special_args) if redirect_params: return self.redirect(request, path_with_added_args(request, redirect_params), forward_querystring=False) filters = Filters(sorted(other_args.items())) where_clauses, params = filters.build_where_clauses() # _search support: fts_table = None fts_sql = detect_fts_sql(table) fts_rows = list(await self.execute(name, fts_sql)) if fts_rows: fts_table = fts_rows[0][0] search = special_args.get('_search') search_description = None if search and fts_table: where_clauses.append( 'rowid in (select rowid from [{fts_table}] where [{fts_table}] match :search)' .format(fts_table=fts_table)) search_description = 'search matches "{}"'.format(search) params['search'] = search next = special_args.get('_next') offset = '' if next: if is_view: # _next is an offset offset = ' offset {}'.format(int(next)) elif use_rowid: where_clauses.append('rowid > :p{}'.format(len(params), )) params['p{}'.format(len(params))] = next else: pk_values = compound_pks_from_path(next) if len(pk_values) == len(pks): param_counter = len(params) for pk, value in zip(pks, pk_values): where_clauses.append('"{}" > :p{}'.format( pk, param_counter, )) params['p{}'.format(param_counter)] = value param_counter += 1 where_clause = '' if where_clauses: where_clause = 'where {} '.format(' and '.join(where_clauses)) if order_by: order_by = 'order by {} '.format(order_by) # _group_count=col1&_group_count=col2 group_count = special_args_lists.get('_group_count') or [] if group_count: count_sql = None sql = 'select {group_cols}, count(*) as "count" from {table_name} {where} group by {group_cols} order by "count" desc limit 100'.format( group_cols=', '.join('"{}"'.format(group_count_col) for group_count_col in group_count), table_name=escape_sqlite_table_name(table), where=where_clause, ) is_view = True else: count_sql = 'select count(*) from {table_name} {where}'.format( table_name=escape_sqlite_table_name(table), where=where_clause, ) sql = 'select {select} from {table_name} {where}{order_by}limit {limit}{offset}'.format( select=select, table_name=escape_sqlite_table_name(table), where=where_clause, order_by=order_by, limit=self.page_size + 1, offset=offset, ) extra_args = {} if request.raw_args.get('_sql_time_limit_ms'): extra_args['custom_time_limit'] = int( request.raw_args['_sql_time_limit_ms']) rows, truncated, description = await self.execute(name, sql, params, truncate=True, **extra_args) columns = [r[0] for r in description] rows = list(rows) display_columns = columns if not use_rowid and not is_view: display_columns = ['Link'] + display_columns info = self.ds.inspect() table_rows = None if not is_view: table_rows = info[name]['tables'][table]['count'] # Pagination next link next_value = None next_url = None if len(rows) > self.page_size: if is_view: next_value = int(next or 0) + self.page_size else: next_value = path_from_row_pks(rows[-2], pks, use_rowid) next_url = urllib.parse.urljoin( request.url, path_with_added_args(request, { '_next': next_value, })) # Number of filtered rows in whole set: filtered_table_rows = None if not where_clauses and not is_view: # Use the pre-calculated total filtered_table_rows = table_rows elif not truncated and len(rows) < self.page_size: filtered_table_rows = len(rows) else: # Attempt a full count, if we can do it in < X ms if count_sql: try: count_rows = list(await self.execute(name, count_sql, params)) filtered_table_rows = count_rows[0][0] except sqlite3.OperationalError: # Almost certainly hit the timeout pass # human_filter_description combines filters AND search, if provided human_description = filters.human_description(extra=search_description) async def extra_template(): return { 'database_hash': hash, 'human_filter_description': human_description, 'supports_search': bool(fts_table), 'search': search or '', 'use_rowid': use_rowid, 'filters': filters, 'display_columns': display_columns, 'display_rows': await self.make_display_rows(name, hash, table, rows, display_columns, pks, is_view, use_rowid), } return { 'database': name, 'table': table, 'is_view': is_view, 'view_definition': view_definition, 'table_definition': table_definition, 'rows': rows[:self.page_size], 'truncated': truncated, 'table_rows': table_rows, 'filtered_table_rows': filtered_table_rows, 'columns': columns, 'primary_keys': pks, 'query': { 'sql': sql, 'params': params, }, 'next': next_value and str(next_value) or None, 'next_url': next_url, }, extra_template
async def webhook_enable(request): args = RequestParameters() args = request.args return text(args["challenge"][0])
async def data( self, request, database, hash, table, default_labels=False, _next=None, _size=None, ): canned_query = self.ds.get_canned_query(database, table) if canned_query is not None: return await self.custom_sql( request, database, hash, canned_query["sql"], metadata=canned_query, editable=False, canned_query=table, ) db = self.ds.databases[database] is_view = bool(await db.get_view_definition(table)) table_exists = bool(await db.table_exists(table)) if not is_view and not table_exists: raise NotFound("Table not found: {}".format(table)) pks = await db.primary_keys(table) use_rowid = not pks and not is_view if use_rowid: select = "rowid, *" order_by = "rowid" order_by_pks = "rowid" else: select = "*" order_by_pks = ", ".join([escape_sqlite(pk) for pk in pks]) order_by = order_by_pks if is_view: order_by = "" # We roll our own query_string decoder because by default Sanic # drops anything with an empty value e.g. ?name__exact= args = RequestParameters( urllib.parse.parse_qs(request.query_string, keep_blank_values=True) ) # Special args start with _ and do not contain a __ # That's so if there is a column that starts with _ # it can still be queried using ?_col__exact=blah special_args = {} special_args_lists = {} other_args = [] for key, value in args.items(): if key.startswith("_") and "__" not in key: special_args[key] = value[0] special_args_lists[key] = value else: for v in value: other_args.append((key, v)) # Handle ?_filter_column and redirect, if present redirect_params = filters_should_redirect(special_args) if redirect_params: return self.redirect( request, path_with_added_args(request, redirect_params), forward_querystring=False, ) # Spot ?_sort_by_desc and redirect to _sort_desc=(_sort) if "_sort_by_desc" in special_args: return self.redirect( request, path_with_added_args( request, { "_sort_desc": special_args.get("_sort"), "_sort_by_desc": None, "_sort": None, }, ), forward_querystring=False, ) table_metadata = self.ds.table_metadata(database, table) units = table_metadata.get("units", {}) filters = Filters(sorted(other_args), units, ureg) where_clauses, params = filters.build_where_clauses(table) extra_wheres_for_ui = [] # Add _where= from querystring if "_where" in request.args: if not self.ds.config("allow_sql"): raise DatasetteError("_where= is not allowed", status=400) else: where_clauses.extend(request.args["_where"]) extra_wheres_for_ui = [ { "text": text, "remove_url": path_with_removed_args(request, {"_where": text}), } for text in request.args["_where"] ] # Support for ?_through={table, column, value} extra_human_descriptions = [] if "_through" in request.args: for through in request.args["_through"]: through_data = json.loads(through) through_table = through_data["table"] other_column = through_data["column"] value = through_data["value"] outgoing_foreign_keys = await db.get_outbound_foreign_keys( through_table ) try: fk_to_us = [ fk for fk in outgoing_foreign_keys if fk["other_table"] == table ][0] except IndexError: raise DatasetteError( "Invalid _through - could not find corresponding foreign key" ) param = "p{}".format(len(params)) where_clauses.append( "{our_pk} in (select {our_column} from {through_table} where {other_column} = :{param})".format( through_table=escape_sqlite(through_table), our_pk=escape_sqlite(fk_to_us["other_column"]), our_column=escape_sqlite(fk_to_us["column"]), other_column=escape_sqlite(other_column), param=param, ) ) params[param] = value extra_human_descriptions.append( '{}.{} = "{}"'.format(through_table, other_column, value) ) # _search support: fts_table = special_args.get("_fts_table") fts_table = fts_table or table_metadata.get("fts_table") fts_table = fts_table or await db.fts_table(table) fts_pk = special_args.get("_fts_pk", table_metadata.get("fts_pk", "rowid")) search_args = dict( pair for pair in special_args.items() if pair[0].startswith("_search") ) search = "" if fts_table and search_args: if "_search" in search_args: # Simple ?_search=xxx search = search_args["_search"] where_clauses.append( "{fts_pk} in (select rowid from {fts_table} where {fts_table} match :search)".format( fts_table=escape_sqlite(fts_table), fts_pk=escape_sqlite(fts_pk) ) ) extra_human_descriptions.append('search matches "{}"'.format(search)) params["search"] = search else: # More complex: search against specific columns for i, (key, search_text) in enumerate(search_args.items()): search_col = key.split("_search_", 1)[1] if search_col not in await db.table_columns(fts_table): raise DatasetteError("Cannot search by that column", status=400) where_clauses.append( "rowid in (select rowid from {fts_table} where {search_col} match :search_{i})".format( fts_table=escape_sqlite(fts_table), search_col=escape_sqlite(search_col), i=i, ) ) extra_human_descriptions.append( 'search column "{}" matches "{}"'.format( search_col, search_text ) ) params["search_{}".format(i)] = search_text sortable_columns = set() sortable_columns = await self.sortable_columns_for_table( database, table, use_rowid ) # Allow for custom sort order sort = special_args.get("_sort") if sort: if sort not in sortable_columns: raise DatasetteError("Cannot sort table by {}".format(sort)) order_by = escape_sqlite(sort) sort_desc = special_args.get("_sort_desc") if sort_desc: if sort_desc not in sortable_columns: raise DatasetteError("Cannot sort table by {}".format(sort_desc)) if sort: raise DatasetteError("Cannot use _sort and _sort_desc at the same time") order_by = "{} desc".format(escape_sqlite(sort_desc)) from_sql = "from {table_name} {where}".format( table_name=escape_sqlite(table), where=("where {} ".format(" and ".join(where_clauses))) if where_clauses else "", ) # Copy of params so we can mutate them later: from_sql_params = dict(**params) count_sql = "select count(*) {}".format(from_sql) _next = _next or special_args.get("_next") offset = "" if _next: if is_view: # _next is an offset offset = " offset {}".format(int(_next)) else: components = urlsafe_components(_next) # If a sort order is applied, the first of these is the sort value if sort or sort_desc: sort_value = components[0] # Special case for if non-urlencoded first token was $null if _next.split(",")[0] == "$null": sort_value = None components = components[1:] # Figure out the SQL for next-based-on-primary-key first next_by_pk_clauses = [] if use_rowid: next_by_pk_clauses.append("rowid > :p{}".format(len(params))) params["p{}".format(len(params))] = components[0] else: # Apply the tie-breaker based on primary keys if len(components) == len(pks): param_len = len(params) next_by_pk_clauses.append( compound_keys_after_sql(pks, param_len) ) for i, pk_value in enumerate(components): params["p{}".format(param_len + i)] = pk_value # Now add the sort SQL, which may incorporate next_by_pk_clauses if sort or sort_desc: if sort_value is None: if sort_desc: # Just items where column is null ordered by pk where_clauses.append( "({column} is null and {next_clauses})".format( column=escape_sqlite(sort_desc), next_clauses=" and ".join(next_by_pk_clauses), ) ) else: where_clauses.append( "({column} is not null or ({column} is null and {next_clauses}))".format( column=escape_sqlite(sort), next_clauses=" and ".join(next_by_pk_clauses), ) ) else: where_clauses.append( "({column} {op} :p{p}{extra_desc_only} or ({column} = :p{p} and {next_clauses}))".format( column=escape_sqlite(sort or sort_desc), op=">" if sort else "<", p=len(params), extra_desc_only="" if sort else " or {column2} is null".format( column2=escape_sqlite(sort or sort_desc) ), next_clauses=" and ".join(next_by_pk_clauses), ) ) params["p{}".format(len(params))] = sort_value order_by = "{}, {}".format(order_by, order_by_pks) else: where_clauses.extend(next_by_pk_clauses) where_clause = "" if where_clauses: where_clause = "where {} ".format(" and ".join(where_clauses)) if order_by: order_by = "order by {} ".format(order_by) # _group_count=col1&_group_count=col2 group_count = special_args_lists.get("_group_count") or [] if group_count: sql = 'select {group_cols}, count(*) as "count" from {table_name} {where} group by {group_cols} order by "count" desc limit 100'.format( group_cols=", ".join( '"{}"'.format(group_count_col) for group_count_col in group_count ), table_name=escape_sqlite(table), where=where_clause, ) return await self.custom_sql(request, database, hash, sql, editable=True) extra_args = {} # Handle ?_size=500 page_size = _size or request.raw_args.get("_size") if page_size: if page_size == "max": page_size = self.ds.max_returned_rows try: page_size = int(page_size) if page_size < 0: raise ValueError except ValueError: raise DatasetteError("_size must be a positive integer", status=400) if page_size > self.ds.max_returned_rows: raise DatasetteError( "_size must be <= {}".format(self.ds.max_returned_rows), status=400 ) extra_args["page_size"] = page_size else: page_size = self.ds.page_size sql_no_limit = "select {select} from {table_name} {where}{order_by}".format( select=select, table_name=escape_sqlite(table), where=where_clause, order_by=order_by, ) sql = "{sql_no_limit} limit {limit}{offset}".format( sql_no_limit=sql_no_limit.rstrip(), limit=page_size + 1, offset=offset ) if request.raw_args.get("_timelimit"): extra_args["custom_time_limit"] = int(request.raw_args["_timelimit"]) results = await self.ds.execute( database, sql, params, truncate=True, **extra_args ) # Number of filtered rows in whole set: filtered_table_rows_count = None if count_sql: try: count_rows = list( await self.ds.execute(database, count_sql, from_sql_params) ) filtered_table_rows_count = count_rows[0][0] except QueryInterrupted: pass # facets support if not self.ds.config("allow_facet") and any( arg.startswith("_facet") for arg in request.args ): raise DatasetteError("_facet= is not allowed", status=400) # pylint: disable=no-member facet_classes = list( itertools.chain.from_iterable(pm.hook.register_facet_classes()) ) facet_results = {} facets_timed_out = [] facet_instances = [] for klass in facet_classes: facet_instances.append( klass( self.ds, request, database, sql=sql_no_limit, params=params, table=table, metadata=table_metadata, row_count=filtered_table_rows_count, ) ) for facet in facet_instances: instance_facet_results, instance_facets_timed_out = ( await facet.facet_results() ) facet_results.update(instance_facet_results) facets_timed_out.extend(instance_facets_timed_out) # Figure out columns and rows for the query columns = [r[0] for r in results.description] rows = list(results.rows) filter_columns = columns[:] if use_rowid and filter_columns[0] == "rowid": filter_columns = filter_columns[1:] # Expand labeled columns if requested expanded_columns = [] expandable_columns = await self.expandable_columns(database, table) columns_to_expand = None try: all_labels = value_as_boolean(special_args.get("_labels", "")) except ValueError: all_labels = default_labels # Check for explicit _label= if "_label" in request.args: columns_to_expand = request.args["_label"] if columns_to_expand is None and all_labels: # expand all columns with foreign keys columns_to_expand = [fk["column"] for fk, _ in expandable_columns] if columns_to_expand: expanded_labels = {} for fk, _ in expandable_columns: column = fk["column"] if column not in columns_to_expand: continue expanded_columns.append(column) # Gather the values column_index = columns.index(column) values = [row[column_index] for row in rows] # Expand them expanded_labels.update( await self.ds.expand_foreign_keys(database, table, column, values) ) if expanded_labels: # Rewrite the rows new_rows = [] for row in rows: new_row = CustomRow(columns) for column in row.keys(): value = row[column] if (column, value) in expanded_labels: new_row[column] = { "value": value, "label": expanded_labels[(column, value)], } else: new_row[column] = value new_rows.append(new_row) rows = new_rows # Pagination next link next_value = None next_url = None if len(rows) > page_size and page_size > 0: if is_view: next_value = int(_next or 0) + page_size else: next_value = path_from_row_pks(rows[-2], pks, use_rowid) # If there's a sort or sort_desc, add that value as a prefix if (sort or sort_desc) and not is_view: prefix = rows[-2][sort or sort_desc] if isinstance(prefix, dict) and "value" in prefix: prefix = prefix["value"] if prefix is None: prefix = "$null" else: prefix = urllib.parse.quote_plus(str(prefix)) next_value = "{},{}".format(prefix, next_value) added_args = {"_next": next_value} if sort: added_args["_sort"] = sort else: added_args["_sort_desc"] = sort_desc else: added_args = {"_next": next_value} next_url = self.ds.absolute_url( request, path_with_replaced_args(request, added_args) ) rows = rows[:page_size] # Detect suggested facets suggested_facets = [] if ( self.ds.config("suggest_facets") and self.ds.config("allow_facet") and not _next ): for facet in facet_instances: suggested_facets.extend(await facet.suggest()) # human_description_en combines filters AND search, if provided human_description_en = filters.human_description_en( extra=extra_human_descriptions ) if sort or sort_desc: sorted_by = "sorted by {}{}".format( (sort or sort_desc), " descending" if sort_desc else "" ) human_description_en = " ".join( [b for b in [human_description_en, sorted_by] if b] ) async def extra_template(): display_columns, display_rows = await self.display_columns_and_rows( database, table, results.description, rows, link_column=not is_view, truncate_cells=self.ds.config("truncate_cells_html"), ) metadata = ( (self.ds.metadata("databases") or {}) .get(database, {}) .get("tables", {}) .get(table, {}) ) self.ds.update_with_inherited_metadata(metadata) form_hidden_args = [] for arg in ("_fts_table", "_fts_pk"): if arg in special_args: form_hidden_args.append((arg, special_args[arg])) return { "supports_search": bool(fts_table), "search": search or "", "use_rowid": use_rowid, "filters": filters, "display_columns": display_columns, "filter_columns": filter_columns, "display_rows": display_rows, "facets_timed_out": facets_timed_out, "sorted_facet_results": sorted( facet_results.values(), key=lambda f: (len(f["results"]), f["name"]), reverse=True, ), "extra_wheres_for_ui": extra_wheres_for_ui, "form_hidden_args": form_hidden_args, "is_sortable": any(c["sortable"] for c in display_columns), "path_with_replaced_args": path_with_replaced_args, "path_with_removed_args": path_with_removed_args, "append_querystring": append_querystring, "request": request, "sort": sort, "sort_desc": sort_desc, "disable_sort": is_view, "custom_rows_and_columns_templates": [ "_rows_and_columns-{}-{}.html".format( to_css_class(database), to_css_class(table) ), "_rows_and_columns-table-{}-{}.html".format( to_css_class(database), to_css_class(table) ), "_rows_and_columns.html", ], "metadata": metadata, "view_definition": await db.get_view_definition(table), "table_definition": await db.get_table_definition(table), } return ( { "database": database, "table": table, "is_view": is_view, "human_description_en": human_description_en, "rows": rows[:page_size], "truncated": results.truncated, "filtered_table_rows_count": filtered_table_rows_count, "expanded_columns": expanded_columns, "expandable_columns": expandable_columns, "columns": columns, "primary_keys": pks, "units": units, "query": {"sql": sql, "params": params}, "facet_results": facet_results, "suggested_facets": suggested_facets, "next": next_value and str(next_value) or None, "next_url": next_url, }, extra_template, ( "table-{}-{}.html".format(to_css_class(database), to_css_class(table)), "table.html", ), )
async def data(self, request, name, hash, table, default_labels=False, _next=None, _size=None): canned_query = self.ds.get_canned_query(name, table) if canned_query is not None: return await self.custom_sql( request, name, hash, canned_query["sql"], editable=False, canned_query=table, ) is_view = bool(await self.ds.get_view_definition(name, table)) info = self.ds.inspect() table_info = info[name]["tables"].get(table) or {} if not is_view and not table_info: raise NotFound("Table not found: {}".format(table)) pks = table_info.get("primary_keys") or [] use_rowid = not pks and not is_view if use_rowid: select = "rowid, *" order_by = "rowid" order_by_pks = "rowid" else: select = "*" order_by_pks = ", ".join([escape_sqlite(pk) for pk in pks]) order_by = order_by_pks if is_view: order_by = "" # We roll our own query_string decoder because by default Sanic # drops anything with an empty value e.g. ?name__exact= args = RequestParameters( urllib.parse.parse_qs(request.query_string, keep_blank_values=True) ) # Special args start with _ and do not contain a __ # That's so if there is a column that starts with _ # it can still be queried using ?_col__exact=blah special_args = {} special_args_lists = {} other_args = {} for key, value in args.items(): if key.startswith("_") and "__" not in key: special_args[key] = value[0] special_args_lists[key] = value else: other_args[key] = value[0] # Handle ?_filter_column and redirect, if present redirect_params = filters_should_redirect(special_args) if redirect_params: return self.redirect( request, path_with_added_args(request, redirect_params), forward_querystring=False, ) # Spot ?_sort_by_desc and redirect to _sort_desc=(_sort) if "_sort_by_desc" in special_args: return self.redirect( request, path_with_added_args( request, { "_sort_desc": special_args.get("_sort"), "_sort_by_desc": None, "_sort": None, }, ), forward_querystring=False, ) table_metadata = self.table_metadata(name, table) units = table_metadata.get("units", {}) filters = Filters(sorted(other_args.items()), units, ureg) where_clauses, params = filters.build_where_clauses() # _search support: fts_table = info[name]["tables"].get(table, {}).get("fts_table") search_args = dict( pair for pair in special_args.items() if pair[0].startswith("_search") ) search_descriptions = [] search = "" if fts_table and search_args: if "_search" in search_args: # Simple ?_search=xxx search = search_args["_search"] where_clauses.append( "rowid in (select rowid from {fts_table} where {fts_table} match :search)".format( fts_table=escape_sqlite(fts_table), ) ) search_descriptions.append('search matches "{}"'.format(search)) params["search"] = search else: # More complex: search against specific columns valid_columns = set(info[name]["tables"][fts_table]["columns"]) for i, (key, search_text) in enumerate(search_args.items()): search_col = key.split("_search_", 1)[1] if search_col not in valid_columns: raise DatasetteError("Cannot search by that column", status=400) where_clauses.append( "rowid in (select rowid from {fts_table} where {search_col} match :search_{i})".format( fts_table=escape_sqlite(fts_table), search_col=escape_sqlite(search_col), i=i ) ) search_descriptions.append( 'search column "{}" matches "{}"'.format( search_col, search_text ) ) params["search_{}".format(i)] = search_text table_rows_count = None sortable_columns = set() if not is_view: table_rows_count = table_info["count"] sortable_columns = self.sortable_columns_for_table(name, table, use_rowid) # Allow for custom sort order sort = special_args.get("_sort") if sort: if sort not in sortable_columns: raise DatasetteError("Cannot sort table by {}".format(sort)) order_by = escape_sqlite(sort) sort_desc = special_args.get("_sort_desc") if sort_desc: if sort_desc not in sortable_columns: raise DatasetteError("Cannot sort table by {}".format(sort_desc)) if sort: raise DatasetteError("Cannot use _sort and _sort_desc at the same time") order_by = "{} desc".format(escape_sqlite(sort_desc)) from_sql = "from {table_name} {where}".format( table_name=escape_sqlite(table), where=( "where {} ".format(" and ".join(where_clauses)) ) if where_clauses else "", ) # Store current params and where_clauses for later: from_sql_params = dict(**params) from_sql_where_clauses = where_clauses[:] count_sql = "select count(*) {}".format(from_sql) _next = _next or special_args.get("_next") offset = "" if _next: if is_view: # _next is an offset offset = " offset {}".format(int(_next)) else: components = urlsafe_components(_next) # If a sort order is applied, the first of these is the sort value if sort or sort_desc: sort_value = components[0] # Special case for if non-urlencoded first token was $null if _next.split(",")[0] == "$null": sort_value = None components = components[1:] # Figure out the SQL for next-based-on-primary-key first next_by_pk_clauses = [] if use_rowid: next_by_pk_clauses.append("rowid > :p{}".format(len(params))) params["p{}".format(len(params))] = components[0] else: # Apply the tie-breaker based on primary keys if len(components) == len(pks): param_len = len(params) next_by_pk_clauses.append( compound_keys_after_sql(pks, param_len) ) for i, pk_value in enumerate(components): params["p{}".format(param_len + i)] = pk_value # Now add the sort SQL, which may incorporate next_by_pk_clauses if sort or sort_desc: if sort_value is None: if sort_desc: # Just items where column is null ordered by pk where_clauses.append( "({column} is null and {next_clauses})".format( column=escape_sqlite(sort_desc), next_clauses=" and ".join(next_by_pk_clauses), ) ) else: where_clauses.append( "({column} is not null or ({column} is null and {next_clauses}))".format( column=escape_sqlite(sort), next_clauses=" and ".join(next_by_pk_clauses), ) ) else: where_clauses.append( "({column} {op} :p{p}{extra_desc_only} or ({column} = :p{p} and {next_clauses}))".format( column=escape_sqlite(sort or sort_desc), op=">" if sort else "<", p=len(params), extra_desc_only="" if sort else " or {column2} is null".format( column2=escape_sqlite(sort or sort_desc) ), next_clauses=" and ".join(next_by_pk_clauses), ) ) params["p{}".format(len(params))] = sort_value order_by = "{}, {}".format(order_by, order_by_pks) else: where_clauses.extend(next_by_pk_clauses) where_clause = "" if where_clauses: where_clause = "where {} ".format(" and ".join(where_clauses)) if order_by: order_by = "order by {} ".format(order_by) # _group_count=col1&_group_count=col2 group_count = special_args_lists.get("_group_count") or [] if group_count: sql = 'select {group_cols}, count(*) as "count" from {table_name} {where} group by {group_cols} order by "count" desc limit 100'.format( group_cols=", ".join( '"{}"'.format(group_count_col) for group_count_col in group_count ), table_name=escape_sqlite(table), where=where_clause, ) return await self.custom_sql(request, name, hash, sql, editable=True) extra_args = {} # Handle ?_size=500 page_size = _size or request.raw_args.get("_size") if page_size: if page_size == "max": page_size = self.max_returned_rows try: page_size = int(page_size) if page_size < 0: raise ValueError except ValueError: raise DatasetteError("_size must be a positive integer", status=400) if page_size > self.max_returned_rows: raise DatasetteError( "_size must be <= {}".format(self.max_returned_rows), status=400 ) extra_args["page_size"] = page_size else: page_size = self.page_size sql = "select {select} from {table_name} {where}{order_by}limit {limit}{offset}".format( select=select, table_name=escape_sqlite(table), where=where_clause, order_by=order_by, limit=page_size + 1, offset=offset, ) if request.raw_args.get("_timelimit"): extra_args["custom_time_limit"] = int(request.raw_args["_timelimit"]) results = await self.ds.execute( name, sql, params, truncate=True, **extra_args ) # facets support facet_size = self.ds.config["default_facet_size"] metadata_facets = table_metadata.get("facets", []) facets = metadata_facets[:] if request.args.get("_facet") and not self.ds.config["allow_facet"]: raise DatasetteError("_facet= is not allowed", status=400) try: facets.extend(request.args["_facet"]) except KeyError: pass facet_results = {} facets_timed_out = [] for column in facets: if _next: continue facet_sql = """ select {col} as value, count(*) as count {from_sql} {and_or_where} {col} is not null group by {col} order by count desc limit {limit} """.format( col=escape_sqlite(column), from_sql=from_sql, and_or_where='and' if from_sql_where_clauses else 'where', limit=facet_size+1, ) try: facet_rows_results = await self.ds.execute( name, facet_sql, params, truncate=False, custom_time_limit=self.ds.config["facet_time_limit_ms"], ) facet_results_values = [] facet_results[column] = { "name": column, "results": facet_results_values, "truncated": len(facet_rows_results) > facet_size, } facet_rows = facet_rows_results.rows[:facet_size] # Attempt to expand foreign keys into labels values = [row["value"] for row in facet_rows] expanded = (await self.expand_foreign_keys( name, table, column, values )) for row in facet_rows: selected = str(other_args.get(column)) == str(row["value"]) if selected: toggle_path = path_with_removed_args( request, {column: str(row["value"])} ) else: toggle_path = path_with_added_args( request, {column: row["value"]} ) facet_results_values.append({ "value": row["value"], "label": expanded.get( (column, row["value"]), row["value"] ), "count": row["count"], "toggle_url": urllib.parse.urljoin( request.url, toggle_path ), "selected": selected, }) except InterruptedError: facets_timed_out.append(column) columns = [r[0] for r in results.description] rows = list(results.rows) filter_columns = columns[:] if use_rowid and filter_columns[0] == "rowid": filter_columns = filter_columns[1:] # Expand labeled columns if requested expanded_columns = [] expandable_columns = self.expandable_columns(name, table) columns_to_expand = None try: all_labels = value_as_boolean(special_args.get("_labels", "")) except ValueError: all_labels = default_labels # Check for explicit _label= if "_label" in request.args: columns_to_expand = request.args["_label"] if columns_to_expand is None and all_labels: # expand all columns with foreign keys columns_to_expand = [ fk["column"] for fk, _ in expandable_columns ] if columns_to_expand: expanded_labels = {} for fk, label_column in expandable_columns: column = fk["column"] if column not in columns_to_expand: continue expanded_columns.append(column) # Gather the values column_index = columns.index(column) values = [row[column_index] for row in rows] # Expand them expanded_labels.update(await self.expand_foreign_keys( name, table, column, values )) if expanded_labels: # Rewrite the rows new_rows = [] for row in rows: new_row = CustomRow(columns) for column in row.keys(): value = row[column] if (column, value) in expanded_labels: new_row[column] = { 'value': value, 'label': expanded_labels[(column, value)] } else: new_row[column] = value new_rows.append(new_row) rows = new_rows # Pagination next link next_value = None next_url = None if len(rows) > page_size and page_size > 0: if is_view: next_value = int(_next or 0) + page_size else: next_value = path_from_row_pks(rows[-2], pks, use_rowid) # If there's a sort or sort_desc, add that value as a prefix if (sort or sort_desc) and not is_view: prefix = rows[-2][sort or sort_desc] if prefix is None: prefix = "$null" else: prefix = urllib.parse.quote_plus(str(prefix)) next_value = "{},{}".format(prefix, next_value) added_args = {"_next": next_value} if sort: added_args["_sort"] = sort else: added_args["_sort_desc"] = sort_desc else: added_args = {"_next": next_value} next_url = urllib.parse.urljoin( request.url, path_with_replaced_args(request, added_args) ) rows = rows[:page_size] # Number of filtered rows in whole set: filtered_table_rows_count = None if count_sql: try: count_rows = list(await self.ds.execute( name, count_sql, from_sql_params )) filtered_table_rows_count = count_rows[0][0] except InterruptedError: pass # Detect suggested facets suggested_facets = [] if self.ds.config["suggest_facets"] and self.ds.config["allow_facet"]: for facet_column in columns: if facet_column in facets: continue if _next: continue if not self.ds.config["suggest_facets"]: continue suggested_facet_sql = ''' select distinct {column} {from_sql} {and_or_where} {column} is not null limit {limit} '''.format( column=escape_sqlite(facet_column), from_sql=from_sql, and_or_where='and' if from_sql_where_clauses else 'where', limit=facet_size+1 ) distinct_values = None try: distinct_values = await self.ds.execute( name, suggested_facet_sql, from_sql_params, truncate=False, custom_time_limit=self.ds.config["facet_suggest_time_limit_ms"], ) num_distinct_values = len(distinct_values) if ( num_distinct_values and num_distinct_values > 1 and num_distinct_values <= facet_size and num_distinct_values < filtered_table_rows_count ): suggested_facets.append({ 'name': facet_column, 'toggle_url': path_with_added_args( request, {'_facet': facet_column} ), }) except InterruptedError: pass # human_description_en combines filters AND search, if provided human_description_en = filters.human_description_en(extra=search_descriptions) if sort or sort_desc: sorted_by = "sorted by {}{}".format( (sort or sort_desc), " descending" if sort_desc else "" ) human_description_en = " ".join( [b for b in [human_description_en, sorted_by] if b] ) async def extra_template(): display_columns, display_rows = await self.display_columns_and_rows( name, table, results.description, rows, link_column=not is_view, ) metadata = self.ds.metadata.get("databases", {}).get(name, {}).get( "tables", {} ).get( table, {} ) self.ds.update_with_inherited_metadata(metadata) return { "database_hash": hash, "supports_search": bool(fts_table), "search": search or "", "use_rowid": use_rowid, "filters": filters, "display_columns": display_columns, "filter_columns": filter_columns, "display_rows": display_rows, "facets_timed_out": facets_timed_out, "sorted_facet_results": sorted( facet_results.values(), key=lambda f: (len(f["results"]), f["name"]), reverse=True ), "facet_hideable": lambda facet: facet not in metadata_facets, "is_sortable": any(c["sortable"] for c in display_columns), "path_with_replaced_args": path_with_replaced_args, "path_with_removed_args": path_with_removed_args, "append_querystring": append_querystring, "request": request, "sort": sort, "sort_desc": sort_desc, "disable_sort": is_view, "custom_rows_and_columns_templates": [ "_rows_and_columns-{}-{}.html".format( to_css_class(name), to_css_class(table) ), "_rows_and_columns-table-{}-{}.html".format( to_css_class(name), to_css_class(table) ), "_rows_and_columns.html", ], "metadata": metadata, "view_definition": await self.ds.get_view_definition(name, table), "table_definition": await self.ds.get_table_definition(name, table), } return { "database": name, "table": table, "is_view": is_view, "human_description_en": human_description_en, "rows": rows[:page_size], "truncated": results.truncated, "table_rows_count": table_rows_count, "filtered_table_rows_count": filtered_table_rows_count, "expanded_columns": expanded_columns, "expandable_columns": expandable_columns, "columns": columns, "primary_keys": pks, "units": units, "query": {"sql": sql, "params": params}, "facet_results": facet_results, "suggested_facets": suggested_facets, "next": next_value and str(next_value) or None, "next_url": next_url, }, extra_template, ( "table-{}-{}.html".format(to_css_class(name), to_css_class(table)), "table.html", )
async def data(self, request, name, hash, table): table = urllib.parse.unquote_plus(table) canned_query = self.ds.get_canned_query(name, table) if canned_query is not None: return await self.custom_sql( request, name, hash, canned_query["sql"], editable=False, canned_query=table, ) is_view = bool( list(await self.execute( name, "SELECT count(*) from sqlite_master WHERE type = 'view' and name=:n", {"n": table}, ))[0][0]) view_definition = None table_definition = None if is_view: view_definition = list(await self.execute( name, 'select sql from sqlite_master where name = :n and type="view"', {"n": table}, ))[0][0] else: table_definition_rows = list(await self.execute( name, 'select sql from sqlite_master where name = :n and type="table"', {"n": table}, )) if not table_definition_rows: raise NotFound("Table not found: {}".format(table)) table_definition = table_definition_rows[0][0] info = self.ds.inspect() table_info = info[name]["tables"].get(table) or {} pks = table_info.get("primary_keys") or [] use_rowid = not pks and not is_view if use_rowid: select = "rowid, *" order_by = "rowid" order_by_pks = "rowid" else: select = "*" order_by_pks = ", ".join([escape_sqlite(pk) for pk in pks]) order_by = order_by_pks if is_view: order_by = "" # We roll our own query_string decoder because by default Sanic # drops anything with an empty value e.g. ?name__exact= args = RequestParameters( urllib.parse.parse_qs(request.query_string, keep_blank_values=True)) # Special args start with _ and do not contain a __ # That's so if there is a column that starts with _ # it can still be queried using ?_col__exact=blah special_args = {} special_args_lists = {} other_args = {} for key, value in args.items(): if key.startswith("_") and "__" not in key: special_args[key] = value[0] special_args_lists[key] = value else: other_args[key] = value[0] # Handle ?_filter_column and redirect, if present redirect_params = filters_should_redirect(special_args) if redirect_params: return self.redirect( request, path_with_added_args(request, redirect_params), forward_querystring=False, ) # Spot ?_sort_by_desc and redirect to _sort_desc=(_sort) if "_sort_by_desc" in special_args: return self.redirect( request, path_with_added_args( request, { "_sort_desc": special_args.get("_sort"), "_sort_by_desc": None, "_sort": None, }, ), forward_querystring=False, ) table_metadata = self.table_metadata(name, table) units = table_metadata.get("units", {}) filters = Filters(sorted(other_args.items()), units, ureg) where_clauses, params = filters.build_where_clauses() # _search support: fts_table = info[name]["tables"].get(table, {}).get("fts_table") search_args = dict(pair for pair in special_args.items() if pair[0].startswith("_search")) search_descriptions = [] search = "" if fts_table and search_args: if "_search" in search_args: # Simple ?_search=xxx search = search_args["_search"] where_clauses.append( "rowid in (select rowid from [{fts_table}] where [{fts_table}] match :search)" .format(fts_table=fts_table)) search_descriptions.append( 'search matches "{}"'.format(search)) params["search"] = search else: # More complex: search against specific columns valid_columns = set(info[name]["tables"][fts_table]["columns"]) for i, (key, search_text) in enumerate(search_args.items()): search_col = key.split("_search_", 1)[1] if search_col not in valid_columns: raise DatasetteError("Cannot search by that column", status=400) where_clauses.append( "rowid in (select rowid from [{fts_table}] where [{search_col}] match :search_{i})" .format(fts_table=fts_table, search_col=search_col, i=i)) search_descriptions.append( 'search column "{}" matches "{}"'.format( search_col, search_text)) params["search_{}".format(i)] = search_text table_rows_count = None sortable_columns = set() if not is_view: table_rows_count = table_info["count"] sortable_columns = self.sortable_columns_for_table( name, table, use_rowid) # Allow for custom sort order sort = special_args.get("_sort") if sort: if sort not in sortable_columns: raise DatasetteError("Cannot sort table by {}".format(sort)) order_by = escape_sqlite(sort) sort_desc = special_args.get("_sort_desc") if sort_desc: if sort_desc not in sortable_columns: raise DatasetteError( "Cannot sort table by {}".format(sort_desc)) if sort: raise DatasetteError( "Cannot use _sort and _sort_desc at the same time") order_by = "{} desc".format(escape_sqlite(sort_desc)) from_sql = "from {table_name} {where}".format( table_name=escape_sqlite(table), where=("where {} ".format(" and ".join(where_clauses))) if where_clauses else "", ) count_sql = "select count(*) {}".format(from_sql) _next = special_args.get("_next") offset = "" if _next: if is_view: # _next is an offset offset = " offset {}".format(int(_next)) else: components = urlsafe_components(_next) # If a sort order is applied, the first of these is the sort value if sort or sort_desc: sort_value = components[0] # Special case for if non-urlencoded first token was $null if _next.split(",")[0] == "$null": sort_value = None components = components[1:] # Figure out the SQL for next-based-on-primary-key first next_by_pk_clauses = [] if use_rowid: next_by_pk_clauses.append("rowid > :p{}".format( len(params))) params["p{}".format(len(params))] = components[0] else: # Apply the tie-breaker based on primary keys if len(components) == len(pks): param_len = len(params) next_by_pk_clauses.append( compound_keys_after_sql(pks, param_len)) for i, pk_value in enumerate(components): params["p{}".format(param_len + i)] = pk_value # Now add the sort SQL, which may incorporate next_by_pk_clauses if sort or sort_desc: if sort_value is None: if sort_desc: # Just items where column is null ordered by pk where_clauses.append( "({column} is null and {next_clauses})".format( column=escape_sqlite(sort_desc), next_clauses=" and ".join( next_by_pk_clauses), )) else: where_clauses.append( "({column} is not null or ({column} is null and {next_clauses}))" .format( column=escape_sqlite(sort), next_clauses=" and ".join( next_by_pk_clauses), )) else: where_clauses.append( "({column} {op} :p{p}{extra_desc_only} or ({column} = :p{p} and {next_clauses}))" .format( column=escape_sqlite(sort or sort_desc), op=">" if sort else "<", p=len(params), extra_desc_only="" if sort else " or {column2} is null".format( column2=escape_sqlite(sort or sort_desc)), next_clauses=" and ".join(next_by_pk_clauses), )) params["p{}".format(len(params))] = sort_value order_by = "{}, {}".format(order_by, order_by_pks) else: where_clauses.extend(next_by_pk_clauses) where_clause = "" if where_clauses: where_clause = "where {} ".format(" and ".join(where_clauses)) if order_by: order_by = "order by {} ".format(order_by) # _group_count=col1&_group_count=col2 group_count = special_args_lists.get("_group_count") or [] if group_count: sql = 'select {group_cols}, count(*) as "count" from {table_name} {where} group by {group_cols} order by "count" desc limit 100'.format( group_cols=", ".join('"{}"'.format(group_count_col) for group_count_col in group_count), table_name=escape_sqlite(table), where=where_clause, ) return await self.custom_sql(request, name, hash, sql, editable=True) extra_args = {} # Handle ?_page_size=500 page_size = request.raw_args.get("_size") if page_size: if page_size == "max": page_size = self.max_returned_rows try: page_size = int(page_size) if page_size < 0: raise ValueError except ValueError: raise DatasetteError("_size must be a positive integer", status=400) if page_size > self.max_returned_rows: raise DatasetteError("_size must be <= {}".format( self.max_returned_rows), status=400) extra_args["page_size"] = page_size else: page_size = self.page_size sql = "select {select} from {table_name} {where}{order_by}limit {limit}{offset}".format( select=select, table_name=escape_sqlite(table), where=where_clause, order_by=order_by, limit=page_size + 1, offset=offset, ) if request.raw_args.get("_timelimit"): extra_args["custom_time_limit"] = int( request.raw_args["_timelimit"]) rows, truncated, description = await self.execute(name, sql, params, truncate=True, **extra_args) # facets support try: facets = request.args["_facet"] except KeyError: facets = table_metadata.get("facets", []) facet_results = {} for column in facets: facet_sql = """ select {col} as value, count(*) as count {from_sql} group by {col} order by count desc limit 20 """.format(col=escape_sqlite(column), from_sql=from_sql) try: facet_rows = await self.execute(name, facet_sql, params, truncate=False, custom_time_limit=200) facet_results[column] = [{ "value": row["value"], "count": row["count"], "toggle_url": urllib.parse.urljoin( request.url, path_with_added_args(request, {column: row["value"]}), ), } for row in facet_rows] except sqlite3.OperationalError: # Hit time limit pass columns = [r[0] for r in description] rows = list(rows) filter_columns = columns[:] if use_rowid and filter_columns[0] == "rowid": filter_columns = filter_columns[1:] # Pagination next link next_value = None next_url = None if len(rows) > page_size and page_size > 0: if is_view: next_value = int(_next or 0) + page_size else: next_value = path_from_row_pks(rows[-2], pks, use_rowid) # If there's a sort or sort_desc, add that value as a prefix if (sort or sort_desc) and not is_view: prefix = rows[-2][sort or sort_desc] if prefix is None: prefix = "$null" else: prefix = urllib.parse.quote_plus(str(prefix)) next_value = "{},{}".format(prefix, next_value) added_args = {"_next": next_value} if sort: added_args["_sort"] = sort else: added_args["_sort_desc"] = sort_desc else: added_args = {"_next": next_value} next_url = urllib.parse.urljoin( request.url, path_with_added_args(request, added_args)) rows = rows[:page_size] # Number of filtered rows in whole set: filtered_table_rows_count = None if count_sql: try: count_rows = list(await self.execute(name, count_sql, params)) filtered_table_rows_count = count_rows[0][0] except sqlite3.OperationalError: # Almost certainly hit the timeout pass # human_description_en combines filters AND search, if provided human_description_en = filters.human_description_en( extra=search_descriptions) if sort or sort_desc: sorted_by = "sorted by {}{}".format( (sort or sort_desc), " descending" if sort_desc else "") human_description_en = " ".join( [b for b in [human_description_en, sorted_by] if b]) async def extra_template(): display_columns, display_rows = await self.display_columns_and_rows( name, table, description, rows, link_column=not is_view, expand_foreign_keys=True, ) metadata = self.ds.metadata.get("databases", {}).get(name, {}).get("tables", {}).get(table, {}) self.ds.update_with_inherited_metadata(metadata) return { "database_hash": hash, "supports_search": bool(fts_table), "search": search or "", "use_rowid": use_rowid, "filters": filters, "display_columns": display_columns, "filter_columns": filter_columns, "display_rows": display_rows, "is_sortable": any(c["sortable"] for c in display_columns), "path_with_added_args": path_with_added_args, "request": request, "sort": sort, "sort_desc": sort_desc, "disable_sort": is_view, "custom_rows_and_columns_templates": [ "_rows_and_columns-{}-{}.html".format( to_css_class(name), to_css_class(table)), "_rows_and_columns-table-{}-{}.html".format( to_css_class(name), to_css_class(table)), "_rows_and_columns.html", ], "metadata": metadata, } return { "database": name, "table": table, "is_view": is_view, "view_definition": view_definition, "table_definition": table_definition, "human_description_en": human_description_en, "rows": rows[:page_size], "truncated": truncated, "table_rows_count": table_rows_count, "filtered_table_rows_count": filtered_table_rows_count, "columns": columns, "primary_keys": pks, "units": units, "query": { "sql": sql, "params": params }, "facet_results": facet_results, "next": next_value and str(next_value) or None, "next_url": next_url, }, extra_template, ( "table-{}-{}.html".format(to_css_class(name), to_css_class(table)), "table.html", )
from sanic.request import RequestParameters, Request if __name__ == '__main__': args = RequestParameters() args['titles'] = ['Post 1', 'Post 2'] print(args.get('titles')) print(args.getlist('titles'))
async def data(self, request, name, hash, table): table = urllib.parse.unquote_plus(table) canned_query = self.ds.get_canned_query(name, table) if canned_query is not None: return await self.custom_sql(request, name, hash, canned_query['sql'], editable=False, canned_query=table) is_view = bool( list(await self.execute( name, "SELECT count(*) from sqlite_master WHERE type = 'view' and name=:n", { 'n': table, }))[0][0]) view_definition = None table_definition = None if is_view: view_definition = list(await self.execute( name, 'select sql from sqlite_master where name = :n and type="view"', { 'n': table, }))[0][0] else: table_definition = list(await self.execute( name, 'select sql from sqlite_master where name = :n and type="table"', { 'n': table, }))[0][0] info = self.ds.inspect() table_info = info[name]['tables'].get(table) or {} pks = table_info.get('primary_keys') or [] use_rowid = not pks and not is_view if use_rowid: select = 'rowid, *' order_by = 'rowid' else: select = '*' order_by = ', '.join(pks) if is_view: order_by = '' # We roll our own query_string decoder because by default Sanic # drops anything with an empty value e.g. ?name__exact= args = RequestParameters( urllib.parse.parse_qs(request.query_string, keep_blank_values=True)) # Special args start with _ and do not contain a __ # That's so if there is a column that starts with _ # it can still be queried using ?_col__exact=blah special_args = {} special_args_lists = {} other_args = {} for key, value in args.items(): if key.startswith('_') and '__' not in key: special_args[key] = value[0] special_args_lists[key] = value else: other_args[key] = value[0] # Handle ?_filter_column and redirect, if present redirect_params = filters_should_redirect(special_args) if redirect_params: return self.redirect(request, path_with_added_args(request, redirect_params), forward_querystring=False) # Spot ?_sort_by_desc and redirect to _sort_desc=(_sort) if '_sort_by_desc' in special_args: return self.redirect( request, path_with_added_args( request, { '_sort_desc': special_args.get('_sort'), '_sort_by_desc': None, '_sort': None, }), forward_querystring=False) filters = Filters(sorted(other_args.items())) where_clauses, params = filters.build_where_clauses() # _search support: fts_table = None fts_sql = detect_fts_sql(table) fts_rows = list(await self.execute(name, fts_sql)) if fts_rows: fts_table = fts_rows[0][0] search = special_args.get('_search') search_description = None if search and fts_table: where_clauses.append( 'rowid in (select rowid from [{fts_table}] where [{fts_table}] match :search)' .format(fts_table=fts_table)) search_description = 'search matches "{}"'.format(search) params['search'] = search table_rows_count = None sortable_columns = set() if not is_view: table_rows_count = table_info['count'] sortable_columns = self.sortable_columns_for_table( name, table, use_rowid) # Allow for custom sort order sort = special_args.get('_sort') if sort: if sort not in sortable_columns: raise DatasetteError('Cannot sort table by {}'.format(sort)) order_by = escape_sqlite(sort) sort_desc = special_args.get('_sort_desc') if sort_desc: if sort_desc not in sortable_columns: raise DatasetteError( 'Cannot sort table by {}'.format(sort_desc)) if sort: raise DatasetteError( 'Cannot use _sort and _sort_desc at the same time') order_by = '{} desc'.format(escape_sqlite(sort_desc)) count_sql = 'select count(*) from {table_name} {where}'.format( table_name=escape_sqlite(table), where=('where {} '.format(' and '.join(where_clauses))) if where_clauses else '', ) # _group_count=col1&_group_count=col2 group_count = special_args_lists.get('_group_count') or [] if group_count: sql = 'select {group_cols}, count(*) as "count" from {table_name} {where} group by {group_cols} order by "count" desc limit 100'.format( group_cols=', '.join('"{}"'.format(group_count_col) for group_count_col in group_count), table_name=escape_sqlite(table), where=('where {} '.format(' and '.join(where_clauses))) if where_clauses else '', ) return await self.custom_sql(request, name, hash, sql, editable=True) _next = special_args.get('_next') offset = '' if _next: if is_view: # _next is an offset offset = ' offset {}'.format(int(_next)) else: components = urlsafe_components(_next) # If a sort order is applied, the first of these is the sort value if sort or sort_desc: sort_value = components[0] components = components[1:] print('sort_varlue = {}, components = {}'.format( sort_value, components)) # Figure out the SQL for next-based-on-primary-key first next_by_pk_clauses = [] if use_rowid: next_by_pk_clauses.append('rowid > :p{}'.format( len(params), )) params['p{}'.format(len(params))] = components[0] else: # Apply the tie-breaker based on primary keys if len(components) == len(pks): param_len = len(params) next_by_pk_clauses.append( compound_keys_after_sql(pks, param_len)) for i, pk_value in enumerate(components): params['p{}'.format(param_len + i)] = pk_value # Now add the sort SQL, which may incorporate next_by_pk_clauses if sort or sort_desc: where_clauses.append( '({column} {op} :p{p} or ({column} = :p{p} and {next_clauses}))' .format( column=escape_sqlite(sort or sort_desc), op='>' if sort else '<', p=len(params), next_clauses=' and '.join(next_by_pk_clauses), )) params['p{}'.format(len(params))] = sort_value else: where_clauses.extend(next_by_pk_clauses) where_clause = '' if where_clauses: where_clause = 'where {} '.format(' and '.join(where_clauses)) if order_by: order_by = 'order by {} '.format(order_by) # _group_count=col1&_group_count=col2 group_count = special_args_lists.get('_group_count') or [] if group_count: sql = 'select {group_cols}, count(*) as "count" from {table_name} {where} group by {group_cols} order by "count" desc limit 100'.format( group_cols=', '.join('"{}"'.format(group_count_col) for group_count_col in group_count), table_name=escape_sqlite(table), where=where_clause, ) return await self.custom_sql(request, name, hash, sql, editable=True) sql = 'select {select} from {table_name} {where}{order_by}limit {limit}{offset}'.format( select=select, table_name=escape_sqlite(table), where=where_clause, order_by=order_by, limit=self.page_size + 1, offset=offset, ) extra_args = {} if request.raw_args.get('_sql_time_limit_ms'): extra_args['custom_time_limit'] = int( request.raw_args['_sql_time_limit_ms']) rows, truncated, description = await self.execute(name, sql, params, truncate=True, **extra_args) columns = [r[0] for r in description] rows = list(rows) filter_columns = columns[:] if use_rowid and filter_columns[0] == 'rowid': filter_columns = filter_columns[1:] # Pagination next link next_value = None next_url = None if len(rows) > self.page_size: if is_view: next_value = int(_next or 0) + self.page_size else: next_value = path_from_row_pks(rows[-2], pks, use_rowid) # If there's a sort or sort_desc, add that value as a prefix if (sort or sort_desc) and not is_view: prefix = str(rows[-2][sort or sort_desc]) next_value = '{},{}'.format(urllib.parse.quote_plus(prefix), next_value) added_args = { '_next': next_value, } if sort: added_args['_sort'] = sort else: added_args['_sort_desc'] = sort_desc else: added_args = { '_next': next_value, } next_url = urllib.parse.urljoin( request.url, path_with_added_args(request, added_args)) rows = rows[:self.page_size] # Number of filtered rows in whole set: filtered_table_rows_count = None if count_sql: try: count_rows = list(await self.execute(name, count_sql, params)) filtered_table_rows_count = count_rows[0][0] except sqlite3.OperationalError: # Almost certainly hit the timeout pass # human_description_en combines filters AND search, if provided human_description_en = filters.human_description_en( extra=search_description) if sort or sort_desc: sorted_by = 'sorted by {}{}'.format( (sort or sort_desc), ' descending' if sort_desc else '', ) human_description_en = ' '.join( [b for b in [human_description_en, sorted_by] if b]) async def extra_template(): display_columns, display_rows = await self.display_columns_and_rows( name, table, description, rows, link_column=not is_view, expand_foreign_keys=True) metadata = self.ds.metadata.get('databases', {}).get(name, {}).get('tables', {}).get(table, {}) self.ds.update_with_inherited_metadata(metadata) return { 'database_hash': hash, 'supports_search': bool(fts_table), 'search': search or '', 'use_rowid': use_rowid, 'filters': filters, 'display_columns': display_columns, 'filter_columns': filter_columns, 'display_rows': display_rows, 'is_sortable': any(c['sortable'] for c in display_columns), 'path_with_added_args': path_with_added_args, 'request': request, 'sort': sort, 'sort_desc': sort_desc, 'disable_sort': is_view, 'custom_rows_and_columns_templates': [ '_rows_and_columns-{}-{}.html'.format( to_css_class(name), to_css_class(table)), '_rows_and_columns-table-{}-{}.html'.format( to_css_class(name), to_css_class(table)), '_rows_and_columns.html', ], 'metadata': metadata, } return { 'database': name, 'table': table, 'is_view': is_view, 'view_definition': view_definition, 'table_definition': table_definition, 'human_description_en': human_description_en, 'rows': rows[:self.page_size], 'truncated': truncated, 'table_rows_count': table_rows_count, 'filtered_table_rows_count': filtered_table_rows_count, 'columns': columns, 'primary_keys': pks, 'query': { 'sql': sql, 'params': params, }, 'next': next_value and str(next_value) or None, 'next_url': next_url, }, extra_template, ('table-{}-{}.html'.format(to_css_class(name), to_css_class(table)), 'table.html')