コード例 #1
0
    def get_distinct_geometries_across_time(self, *kwargs):
        time_query = 'SELECT DISTINCT ST_AsText({geom_field}), COUNT(0) FROM {table} GROUP BY ST_AsText({geom_field})'
        time_query = time_query.format(geom_field=GEOM_FIELD_NAME, table=self.table)

        with connection.cursor() as c:
            c.execute(time_query)
            response = dictfetchall(c)

        return response
コード例 #2
0
ファイル: models.py プロジェクト: consbio/tablo
    def get_distinct_geometries_across_time(self, *kwargs):
        time_query = 'SELECT DISTINCT ST_AsText({geom_field}), COUNT(0) FROM {table} GROUP BY ST_AsText({geom_field})'
        time_query = time_query.format(geom_field=POINT_FIELD_NAME, table=self.table)

        with get_cursor() as c:
            c.execute(time_query)
            response = dictfetchall(c)

        return response
コード例 #3
0
    def perform_query(self, limit=0, offset=0, **kwargs):
        limit, offset = max(limit, 0), max(offset, 0)

        count_only = bool(kwargs.pop('count_only', False))
        ids_only = bool(kwargs.pop('ids_only', False))
        return_fields = [f.strip() for f in list(kwargs.get('return_fields') or '*')]
        return_geometry = bool(kwargs.get('return_geometry', True))
        out_sr = kwargs.get('out_sr') or WEB_MERCATOR_SRID

        additional_where_clause = kwargs.get('additional_where_clause')
        additional_where_clause = additional_where_clause.replace('"', '') if additional_where_clause else None

        # Break out fields and DESC / ASC modifiers
        order_by_field_objs = []
        for field in [f.strip() for f in kwargs.get('order_by_fields') or '']:
            m = re.match('(\S*)\s?(asc|desc)?', field, re.IGNORECASE)
            field_obj = {'field_name': m.group(1)}
            try:
                field_obj['order_modifier'] = m.group(2)
            except IndexError:
                pass  # No modifier
            order_by_field_objs.append(field_obj)

        order_by_field_names = [f['field_name'] for f in order_by_field_objs]

        # These are the possible points of SQL injection. All other dynamically composed pieces of SQL are
        # constructed using items within the database, or are escaped using the database engine.

        if not ids_only:
            include_related = bool(kwargs.get('object_ids'))

            self._validate_fields(return_fields, include_related)
            self._validate_fields(order_by_field_names, include_related)

        self._validate_where_clause(additional_where_clause)

        # Build SELECT, JOIN, WHERE and ORDER BY from inputs

        if count_only:
            return_fields = order_by_field_names = []
            select_fields = 'COUNT(0)'
        elif ids_only:
            return_fields = order_by_field_names = [self.object_id_field]
            select_fields = 'DISTINCT {0}'.format(self._alias_fields(return_fields))
        else:
            if self.object_id_field not in return_fields:
                return_fields.insert(0, self.object_id_field)

            select_fields = self._expand_fields(return_fields)
            if return_geometry:
                select_fields += ', ST_AsText(ST_Transform("source"."dbasin_geom", {0}))'.format(out_sr)

        join, related_tables = self._build_join_clause(return_fields, additional_where_clause)
        where, query_params = self._build_where_clause(additional_where_clause, count_only, **kwargs)
        order_by = '' if count_only else self._build_order_by_clause(
            field_objs=order_by_field_objs, related_tables=(None if ids_only else related_tables)
        )

        query_clause = 'SELECT {fields} FROM "{table}" AS "source" {join} {where} {order_by} {limit} {offset}'
        query_clause = query_clause.format(
            fields=select_fields, table=self.table, join=join.strip(), where=where.strip(), order_by=order_by,
            limit='' if limit == 0 else 'LIMIT {limit}'.format(limit=limit + 1),
            offset='' if offset == 0 else 'OFFSET {offset}'.format(offset=offset)
        )

        # Execute query with optional limit and offset, and prepare return data

        with connection.cursor() as c:
            c.execute(query_clause, query_params)
            queried_data = dictfetchall(c)

        limited_data = 0 < limit < len(queried_data)
        queried_data = queried_data[:-1] if limited_data else queried_data

        return {'data': queried_data, 'exceeded_limit': limited_data}
コード例 #4
0
ファイル: models.py プロジェクト: consbio/tablo
    def perform_query(self, limit=0, offset=0, **kwargs):
        limit, offset = max(limit, 0), max(offset, 0)

        count_only = bool(kwargs.pop('count_only', False))
        ids_only = bool(kwargs.pop('ids_only', False))
        return_fields = [f.strip() for f in list(kwargs.get('return_fields') or '*')]
        return_geometry = bool(kwargs.get('return_geometry', True))
        out_sr = kwargs.get('out_sr') or WEB_MERCATOR_SRID

        additional_where_clause = kwargs.get('additional_where_clause')
        additional_where_clause = additional_where_clause.replace('"', '') if additional_where_clause else None

        # Break out fields and DESC / ASC modifiers
        order_by_field_objs = []
        for field in [f.strip() for f in kwargs.get('order_by_fields') or '']:
            m = re.match('(\S*)\s?(asc|desc)?', field, re.IGNORECASE)
            field_obj = {'field_name': m.group(1)}
            try:
                field_obj['order_modifier'] = m.group(2)
            except IndexError:
                pass  # No modifier
            order_by_field_objs.append(field_obj)

        order_by_field_names = [f['field_name'] for f in order_by_field_objs]

        # These are the possible points of SQL injection. All other dynamically composed pieces of SQL are
        # constructed using items within the database, or are escaped using the database engine.

        if not ids_only:
            include_related = bool(kwargs.get('object_ids'))

            self._validate_fields(return_fields, include_related)
            self._validate_fields(order_by_field_names, include_related)

        self._validate_where_clause(additional_where_clause)

        # Build SELECT, JOIN, WHERE and ORDER BY from inputs

        if count_only:
            return_fields = order_by_field_names = []
            select_fields = 'COUNT(0)'
        elif ids_only:
            return_fields = order_by_field_names = [self.object_id_field]
            select_fields = 'DISTINCT {0}'.format(self._alias_fields(return_fields))
        else:
            if self.object_id_field not in return_fields:
                return_fields.insert(0, self.object_id_field)

            select_fields = self._expand_fields(return_fields)
            if return_geometry:
                select_fields += ', ST_AsText(ST_Transform("source"."dbasin_geom", {0}))'.format(out_sr)

        join, related_tables = self._build_join_clause(return_fields, additional_where_clause)
        where, query_params = self._build_where_clause(additional_where_clause, count_only, **kwargs)
        order_by = '' if count_only else self._build_order_by_clause(
            field_objs=order_by_field_objs, related_tables=(None if ids_only else related_tables)
        )

        query_clause = 'SELECT {fields} FROM "{table}" AS "source" {join} {where} {order_by} {limit} {offset}'
        query_clause = query_clause.format(
            fields=select_fields, table=self.table, join=join.strip(), where=where.strip(), order_by=order_by,
            limit='' if limit == 0 else 'LIMIT {limit}'.format(limit=limit + 1),
            offset='' if offset == 0 else 'OFFSET {offset}'.format(offset=offset)
        )

        # Execute query with optional limit and offset, and prepare return data

        with get_cursor() as c:
            c.execute(query_clause, query_params)
            queried_data = dictfetchall(c)

        limited_data = 0 < limit < len(queried_data)
        queried_data = queried_data[:-1] if limited_data else queried_data

        return {'data': queried_data, 'exceeded_limit': limited_data}