Python django.db.models.functions.Cast() Examples

def get_item_report(qs, total_log_count, **kwargs):
    if qs.count() == 0:
        return None

    min_count = kwargs.get('min_count', max(1, int(MINIMUM_THRESHOLD * total_log_count)))

    results = list(
        qs.values(
            'item',
            name=F('item__name'),
            icon=F('item__icon'),
        ).annotate(
            count=Count('pk'),
            min=Min('quantity'),
            max=Max('quantity'),
            avg=Avg('quantity'),
            drop_chance=Cast(Count('pk'), FloatField()) / total_log_count * 100,
            qty_per_100=Cast(Sum('quantity'), FloatField()) / total_log_count * 100,
        ).filter(count__gt=min_count).order_by('-count')
    )

    return results 

def get_feedback(self):
        """ Return all feedback for the participant.

        Activity chairs see the complete history of feedback (without the normal
        "clean slate" period). The only exception is that activity chairs cannot
        see their own feedback.
        """
        return (
            models.Feedback.everything.filter(participant=self.object.participant)
            .exclude(participant=self.chair)
            .select_related('leader', 'trip')
            .prefetch_related('leader__leaderrating_set')
            .annotate(
                display_date=Least('trip__trip_date', Cast('time_created', DateField()))
            )
            .order_by('-display_date')
        ) 

def check_if_inventory_linked(instance: Inventory, action: Text, **kwargs) -> NoReturn:
    if 'loaddata' in sys.argv or kwargs.get('raw', False):  # nocv
        return
    if action != "pre_remove":
        return
    removing_inventories = instance.inventories.filter(pk__in=kwargs['pk_set'])
    check_id = removing_inventories.values_list('id', flat=True)
    linked_templates = Template.objects.filter(inventory__iregex=r'^[0-9]{1,128}$').\
        annotate(inventory__id=Cast('inventory', IntegerField())).\
        filter(inventory__id__in=check_id)
    linked_periodic_tasks = PeriodicTask.objects.filter(_inventory__in=check_id)
    if linked_periodic_tasks.exists() or linked_templates.exists():
        raise_linked_error(
            linked_templates=list(linked_templates.values_list('id', flat=True)),
            linked_periodic_tasks=list(
                linked_periodic_tasks.values_list('id', flat=True)
            ),
        ) 

def get_average_execution_time_grouped(self, from_date: datetime.datetime,
                                           to_date: datetime.datetime) -> Optional[datetime.timedelta]:
        return self.select_related(
            'ethereum_tx', 'ethereum_tx__block'
        ).exclude(
            ethereum_tx__block=None,
        ).annotate(
            interval=Cast(F('ethereum_tx__block__timestamp') - F('created'),
                          output_field=DurationField())
        ).filter(
            created__range=(from_date, to_date)
        ).annotate(
            created_date=TruncDate('created')
        ).values(
            'created_date'
        ).annotate(
            average_execution_time=Avg('interval')
        ).values('created_date', 'average_execution_time').order_by('created_date') 

def test_cast_to_decimal_field(self):
        FloatModel.objects.create(f1=-1.934, f2=3.467)
        float_obj = FloatModel.objects.annotate(
            cast_f1_decimal=Cast('f1', models.DecimalField(max_digits=8, decimal_places=2)),
            cast_f2_decimal=Cast('f2', models.DecimalField(max_digits=8, decimal_places=1)),
        ).get()
        self.assertEqual(float_obj.cast_f1_decimal, decimal.Decimal('-1.93'))
        self.assertEqual(float_obj.cast_f2_decimal, decimal.Decimal('3.5'))
        author_obj = Author.objects.annotate(
            cast_alias_decimal=Cast('alias', models.DecimalField(max_digits=8, decimal_places=2)),
        ).get()
        self.assertEqual(author_obj.cast_alias_decimal, decimal.Decimal('1')) 

def test_cast_from_field(self):
        numbers = Author.objects.annotate(cast_string=Cast('age', models.CharField(max_length=255)),)
        self.assertEqual(numbers.get().cast_string, '1') 

def test_cast_to_char_field_without_max_length(self):
        numbers = Author.objects.annotate(cast_string=Cast('age', models.CharField()))
        self.assertEqual(numbers.get().cast_string, '1')

    # Silence "Truncated incorrect CHAR(1) value: 'Bob'". 

def test_cast_to_char_field_with_max_length(self):
        names = Author.objects.annotate(cast_string=Cast('name', models.CharField(max_length=1)))
        self.assertEqual(names.get().cast_string, 'B') 

def test_cast_from_python_to_date(self):
        today = datetime.date.today()
        dates = Author.objects.annotate(cast_date=Cast(today, models.DateField()))
        self.assertEqual(dates.get().cast_date, today) 

def test_cast_from_python_to_datetime(self):
        now = datetime.datetime.now()
        dates = Author.objects.annotate(cast_datetime=Cast(now, models.DateTimeField()))
        self.assertEqual(dates.get().cast_datetime, now) 

def test_cast_from_python(self):
        numbers = Author.objects.annotate(cast_float=Cast(decimal.Decimal(0.125), models.FloatField()))
        cast_float = numbers.get().cast_float
        self.assertIsInstance(cast_float, float)
        self.assertEqual(cast_float, 0.125) 

def test_expression_wrapped_with_parentheses_on_postgresql(self):
        """
        The SQL for the Cast expression is wrapped with parentheses in case
        it's a complex expression.
        """
        list(Author.objects.annotate(cast_float=Cast(Avg('age'), models.FloatField())))
        self.assertIn('(AVG("db_functions_author"."age"))::double precision', connection.queries[-1]['sql']) 

def test_cast_to_text_field(self):
        self.assertEqual(Author.objects.values_list(Cast('age', models.TextField()), flat=True).get(), '1') 

def test_cast_from_value(self):
        numbers = Author.objects.annotate(cast_integer=Cast(Value('0'), models.IntegerField()))
        self.assertEqual(numbers.get().cast_integer, 0) 

def test_cast_from_field(self):
        numbers = Author.objects.annotate(cast_string=Cast('age', models.CharField(max_length=255)),)
        self.assertEqual(numbers.get().cast_string, '1') 

def test_cast_to_char_field_without_max_length(self):
        numbers = Author.objects.annotate(cast_string=Cast('age', models.CharField()))
        self.assertEqual(numbers.get().cast_string, '1')

    # Silence "Truncated incorrect CHAR(1) value: 'Bob'". 

def test_cast_to_char_field_with_max_length(self):
        names = Author.objects.annotate(cast_string=Cast('name', models.CharField(max_length=1)))
        self.assertEqual(names.get().cast_string, 'B') 

def test_cast_aggregate(self):
        """
        Cast a geography to a geometry field for an aggregate function that
        expects a geometry input.
        """
        if not connection.ops.geography:
            self.skipTest("This test needs geography support")
        expected = (-96.8016128540039, 29.7633724212646, -95.3631439208984, 32.782058715820)
        res = City.objects.filter(
            name__in=('Houston', 'Dallas')
        ).aggregate(extent=models.Extent(Cast('point', models.PointField())))
        for val, exp in zip(res['extent'], expected):
            self.assertAlmostEqual(exp, val, 4) 

def test_cast_from_db_datetime_to_date(self):
        dt_value = datetime.datetime(2018, 9, 28, 12, 42, 10, 234567)
        DTModel.objects.create(start_datetime=dt_value)
        dtm = DTModel.objects.annotate(
            start_datetime_as_date=Cast('start_datetime', models.DateField())
        ).first()
        self.assertEqual(dtm.start_datetime_as_date, datetime.date(2018, 9, 28)) 

def test_cast_from_db_datetime_to_time(self):
        dt_value = datetime.datetime(2018, 9, 28, 12, 42, 10, 234567)
        DTModel.objects.create(start_datetime=dt_value)
        dtm = DTModel.objects.annotate(
            start_datetime_as_time=Cast('start_datetime', models.TimeField())
        ).first()
        rounded_ms = int(round(.234567, connection.features.time_cast_precision) * 10**6)
        self.assertEqual(dtm.start_datetime_as_time, datetime.time(12, 42, 10, rounded_ms)) 

def test_cast_from_db_date_to_datetime(self):
        dt_value = datetime.date(2018, 9, 28)
        DTModel.objects.create(start_date=dt_value)
        dtm = DTModel.objects.annotate(start_as_datetime=Cast('start_date', models.DateTimeField())).first()
        self.assertEqual(dtm.start_as_datetime, datetime.datetime(2018, 9, 28, 0, 0, 0, 0)) 

def test_cast_from_db_datetime_to_date_group_by(self):
        author = Author.objects.create(name='John Smith', age=45)
        dt_value = datetime.datetime(2018, 9, 28, 12, 42, 10, 234567)
        Fan.objects.create(name='Margaret', age=50, author=author, fan_since=dt_value)
        fans = Fan.objects.values('author').annotate(
            fan_for_day=Cast('fan_since', models.DateField()),
            fans=models.Count('*')
        ).values()
        self.assertEqual(fans[0]['fan_for_day'], datetime.date(2018, 9, 28))
        self.assertEqual(fans[0]['fans'], 1) 

def test_cast_from_python_to_date(self):
        today = datetime.date.today()
        dates = Author.objects.annotate(cast_date=Cast(today, models.DateField()))
        self.assertEqual(dates.get().cast_date, today) 

def test_cast_from_python(self):
        numbers = Author.objects.annotate(cast_float=Cast(decimal.Decimal(0.125), models.FloatField()))
        cast_float = numbers.get().cast_float
        self.assertIsInstance(cast_float, float)
        self.assertEqual(cast_float, 0.125) 

def test_expression_wrapped_with_parentheses_on_postgresql(self):
        """
        The SQL for the Cast expression is wrapped with parentheses in case
        it's a complex expression.
        """
        list(Author.objects.annotate(cast_float=Cast(Avg('age'), models.FloatField())))
        self.assertIn('(AVG("db_functions_author"."age"))::double precision', connection.queries[-1]['sql']) 

def test_cast_to_text_field(self):
        self.assertEqual(Author.objects.values_list(Cast('age', models.TextField()), flat=True).get(), '1') 

def _sum_positive_values(field):
    """
    SQL function which sums over `field` ignoring all negative values
    """
    field_as_float = Cast(field, FloatField())
    field_with_floor = Greatest(field_as_float, Value(0.0))
    return Sum(field_with_floor) 

def _build_search_expression(self, field_lookup):
        # TODO: is there no Django API which allows conversion of lookup to django field?
        model_field, _ = reduce(
            lambda model_tuple, field: self._get_model_field(model_tuple[1], field),
            field_lookup.split(LOOKUP_SEP),
            (None, self.model),
        )

        if isinstance(model_field, LocalizedField):
            lang = translation.get_language()
            return KeyTransform(lang, field_lookup)
        elif isinstance(model_field, JSONField):
            return Cast(field_lookup, models.TextField())

        return field_lookup 

def AsInt(expr):
    return Cast(expr, BigIntegerField()) 

def AsFloat(expr):
    return Cast(expr, FloatField())