Python sqlalchemy.sql.expression.cast() Examples

def compile(self):
        # Json column?
        if self.is_column_json:
            # PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
            column = cast(self.column, Float)
        else:
            # Simply use
            column = self.column

        # Now, handle the operator, and apply it to the expression
        if self.operator == '$max':
            stmt = func.max(column)
        elif self.operator == '$min':
            stmt = func.min(column)
        elif self.operator == '$avg':
            stmt = func.avg(column)
        elif self.operator == '$sum':
            stmt = func.sum(column)
        else:
            raise InvalidQueryError('Aggregate: unsupported operator "{}"'.format(self.operator))
        return self.labeled_expression(stmt) 

def test_paginate_with_boolean_sort(self):
        s = Session()
        q = s.query(FakeTable)
        q = utils.paginate_query(q, FakeTable, 5, ['enabled'],
                                 sort_dirs=['asc'],
                                 marker=FakeTable(user_id='hello',
                                                  enabled=False))
        expected_core_sql = (
            select([FakeTable]).
            order_by(sqlalchemy.asc(FakeTable.enabled)).
            where(cast(FakeTable.enabled, Integer) > 0).
            limit(5)
        )

        self.assertEqual(
            str(expected_core_sql.compile()),
            str(q.statement.compile())
        ) 

def _serialize_and_update_indexes(self,obj,collection,d,for_update = False):

        pk_type = self._index_fields[collection]['pk']['type']

        for index_field,index_params in self._index_fields[collection].items():
            try:
                if for_update:
                    value = obj[index_field]
                else:
                    value = get_value(obj,index_field)
                if value is None:
                    if not index_params['field'].nullable:
                        raise ValueError("Value for %s is `None`, but this is a mandatory field!" % index_field)
                    d[index_params['column']] = null()
                else:
                    d[index_params['column']] = expression.cast(value,index_params['type'])
            except KeyError:
                if for_update:
                    continue
                if index_params['field'].default is not None:
                    d[index_params['column']] = index_params['field'].default
                elif not index_params['field'].nullable:
                    raise ValueError("No value for %s given, but this is a mandatory field!" % index_field)
                else:
                    d[index_params['column']] = null() 

def compile(self):
        # Remember that there is this special case: { $sum: 1 }
        if isinstance(self.expression, int):
            # Special case for count
            stmt = func.count()
            if self.expression != 1:
                # When $sum: N, we count N per row. That's multiplication
                stmt *= self.expression
        else:
            # Compile the boolean statement
            stmt = self.expression.compile_statement()
            # Sum the value of this expression (column, boolean, whatever)
            # Need to cast it to int
            stmt = cast(stmt, Integer)
            # Now, sum it
            stmt = func.sum(stmt)
        # Done
        return self.labeled_expression(stmt)

# endregion 

def preprocess_column_and_value(self):
        """ Preprocess the column and the value

            Certain operations will only work if the types are cast correctly.
            This is where it happens.
        """
        col, val = self.column, self.value

        # Case 1. Both column and value are arrays
        if self.is_column_array() and self.is_value_array():
            # Cast the value to ARRAY[] with the same type that the column has
            # Only in this case Postgres will be able to handles them both
            val = cast(pg.array(val), pg.ARRAY(col.type.item_type))

        # Case 2. JSON column
        if self.is_column_json():
            # This is the type to which JSON column is coerced: same as `value`
            # Doc: "Suggest a type for a `coerced` Python value in an expression."
            coerce_type = col.type.coerce_compared_value('=', val)  # HACKY: use sqlalchemy type coercion
            # Now, replace the `col` used in operations with this new coerced expression
            col = cast(col, coerce_type)

        # Done
        self.column_expression = col
        self.value_expression = val 

def get_tiles_by_quadkey(prediction_id: int, quadkeys: tuple, zoom: int):
        return db.session.query(func.substr(PredictionTile.quadkey, 1, zoom).label('qaudkey'),
                                func.avg(cast(cast(PredictionTile.predictions['ml_prediction'], sqlalchemy.String),
                                         sqlalchemy.Float)).label('ml_prediction'),
                                func.avg(cast(cast(PredictionTile.predictions['osm_building_area'], sqlalchemy.String),
                                         sqlalchemy.Float)).label('osm_building_area')).filter(PredictionTile.prediction_id == prediction_id).filter(
                                             func.substr(
                                              PredictionTile.quadkey, 1, zoom).in_(quadkeys)).group_by(func.substr(PredictionTile.quadkey, 1, zoom)).all() 

def fitness(self):
        """Retrieve fitness via property1."""
        return cast(self.property1, Integer) 

def proportion(self):
        """Make proportion queryable."""
        return cast(self.property4, Float) 

def score(self):
        """Make score queryable."""
        return cast(self.property3, Integer) 

def generation(self):
        """Make generation queryable."""
        return cast(self.property2, Integer) 

def chosen(self):
        """Retrieve chosen via property1."""
        return cast(self.property1, Boolean) 

def perm(cls) -> str:  # pylint: disable=no-self-argument
        return "[" + cls.cluster_name + "].(id:" + expression.cast(cls.id, String) + ")" 

def perm(cls) -> str:  # pylint: disable=no-self-argument
        return (
            "[" + cls.database_name + "].(id:" + expression.cast(cls.id, String) + ")"
        ) 

def get_aggregate_for_polygon(prediction_id: int, polygon: str):
        return db.session.query(func.avg(cast(cast(PredictionTile.predictions['ml_prediction'], sqlalchemy.String), sqlalchemy.Float)).label('ml_prediction'),
                                func.avg(cast(cast(PredictionTile.predictions['osm_building_area'],
                                         sqlalchemy.String), sqlalchemy.Float)).label('osm_building_area')).filter(
            PredictionTile.prediction_id == prediction_id).filter(ST_Within(PredictionTile.centroid, ST_GeomFromText(polygon)) == 'True').one() 

def chosen(self):
        """Retrieve chosen via property1."""
        return cast(self.property1, Boolean) 

def get_queryset(self,*args,**kwargs):
        if self._queryset is None:
            relationship_table = self.params['relationship_table']
            foreign_table = self.obj.backend.get_collection_table(self.params['collection'])
            condition = relationship_table.c[self.params['pk_field_name']] \
                == expression.cast(self.obj.pk,self.params['type'])
            self._queryset = QuerySet(backend = self.obj.backend,
                                      table = foreign_table,
                                      cls = self.params['class'],
                                      joins = [(relationship_table,)],
                                      condition = condition,
                                      objects = self._objects,
                                      *args,
                                      **kwargs)
        return self._queryset 

def age(cls, reference):
        """
        Person's age relative to a reference date.

        :param reference: Date object of reference date.
        :return: Integer value of person's age.
        """
        return cast((reference - cls.birth_date)/365.25 - 0.5, Integer) 

def generation(self):
        """Make generation queryable."""
        from sqlalchemy import Integer
        from sqlalchemy.sql.expression import cast

        return cast(self.property2, Integer) 

def proportion(self):
        """Make proportion queryable."""
        return cast(self.property1, Float) 

def proportion(self):
        """Make proportion queryable."""
        return cast(self.property4, Float) 

def score(self):
        """Make score queryable."""
        return cast(self.property3, Integer) 

def generation(self):
        """Make generation queryable."""
        return cast(self.property2, Integer) 

def _serialize_and_update_relations(self,obj,collection,d,deletes,inserts,autosave_dependent = True,for_update = False, save_cache=None):

        pk_type = self._index_fields[collection]['pk']['type']

        for related_field,relation_params in self._related_fields[collection].items():

            #we skip back-references...
            if relation_params.get('is_backref',None):
                continue

            try:
                if for_update:
                    value = obj[related_field]
                else:
                    value = get_value(obj,related_field)
                if isinstance(relation_params['field'],ManyToManyField):
                    if isinstance(value,ManyToManyProxy):
                        continue
                    relationship_table = self._relationship_tables[collection][related_field]
                    deletes.append(relationship_table.delete().where(relationship_table.c[relation_params['pk_field_name']] == expression.cast(obj['pk'],pk_type)))
                    for element in value:
                        if not isinstance(element,Document):
                            raise AttributeError("ManyToMany field %s contains an invalid value!" % related_field)
                        if autosave_dependent and element.pk is None:
                            self.save(element, save_cache=save_cache)
                        if element.pk is None:
                            raise AttributeError("Related document in field %s has no primary key!" % related_field)
                        ed = {
                            relation_params['pk_field_name'] : obj['pk'],
                            relation_params['related_pk_field_name'] : element.pk,
                        }
                        inserts.append(relationship_table.insert().values(**ed))
                elif isinstance(relation_params['field'],ForeignKeyField):
                    if value is None:
                        if not relation_params['field'].nullable:
                            raise AttributeError("Field %s cannot be None!" % related_field)
                        d[relation_params['column']] = null()
                    elif not isinstance(value,Document):
                        raise AttributeError("Field %s must be a document!" % related_field)
                    else:
                        if autosave_dependent and value.pk is None:
                            self.save(value, save_cache=save_cache)
                        if value.pk is None:
                            raise AttributeError("Related document in field %s has no primary key!" % related_field)
                        d[relation_params['column']] = expression.cast(value.pk,relation_params['type'])

            except KeyError:
                if for_update:
                    continue
                if isinstance(relation_params['field'],ForeignKeyField):
                    if not relation_params['field'].nullable:
                        raise ValueError("No value for %s given, but this is a mandatory field!" % relation_params['key'])
                    d[relation_params['column']] = null()