Python django.db.models.F Examples

def filter_queryset(self, queryset):
        if self.request.user.is_anonymous():
            self.permission_denied(self.request)
        is_project = self.kwargs.get('is_project', None)
        pk = self.kwargs.get('pk', None)
        if is_project == "1":
            queryset = queryset.filter(project__id=pk)
            return queryset.annotate(response_count=Count("schedule_forms__project_form_instances")).select_related('schedule_forms', 'schedule_forms__xf', 'schedule_forms__em')
        else:
            project_id = get_object_or_404(Site, pk=pk).project.id
            queryset = queryset.filter(Q(site__id=pk, schedule_forms__from_project=False)
                                       | Q(project__id=project_id))
            return queryset.annotate(
                site_response_count=Count("schedule_forms__site_form_instances", ),
                response_count=Count(Case(
                    When(project__isnull=False, schedule_forms__project_form_instances__site__id=pk, then=F('schedule_forms__project_form_instances')),
                    output_field=IntegerField(),
                ), distinct=True)

            ).select_related('schedule_forms','schedule_forms__xf', 'schedule_forms__em') 

def _populate_daily_summary_table(self):
        included_fields = ["usage_start", "usage_end", "usage_account_id", "availability_zone", "tags"]
        annotations = {
            "product_family": Concat("cost_entry_product__product_family", Value("")),
            "product_code": Concat("cost_entry_product__service_code", Value("")),
            "region": Concat("cost_entry_product__region", Value("")),
            "instance_type": Concat("cost_entry_product__instance_type", Value("")),
            "unit": Concat("cost_entry_pricing__unit", Value("")),
            "usage_amount": Sum("usage_amount"),
            "normalization_factor": Max("normalization_factor"),
            "normalized_usage_amount": Sum("normalized_usage_amount"),
            "currency_code": Max("currency_code"),
            "unblended_rate": Max("unblended_rate"),
            "unblended_cost": Sum("unblended_cost"),
            "blended_rate": Max("blended_rate"),
            "blended_cost": Sum("blended_cost"),
            "public_on_demand_cost": Sum("public_on_demand_cost"),
            "public_on_demand_rate": Max("public_on_demand_rate"),
            "resource_count": Count("resource_id", distinct=True),
            "resource_ids": ArrayAgg("resource_id", distinct=True),
        }

        entries = AWSCostEntryLineItemDaily.objects.values(*included_fields).annotate(**annotations)
        for entry in entries:
            alias = AWSAccountAlias.objects.filter(account_id=entry["usage_account_id"])
            alias = list(alias).pop() if alias else None
            summary = AWSCostEntryLineItemDailySummary(**entry, account_alias=alias)
            summary.save()
            self.current_month_total += entry["unblended_cost"] + entry["unblended_cost"] * Decimal(0.1)
        AWSCostEntryLineItemDailySummary.objects.update(markup_cost=F("unblended_cost") * 0.1) 

def get_rank_window_function(self, group_by_value):
        """Generate a limit ranking window function."""
        tag_column = self._mapper.tag_column
        rank_orders = []
        rank_field = group_by_value.pop()
        default_ordering = self._mapper.report_type_map.get("default_ordering")

        if self.order_field == "delta" and "__" in self._delta:
            delta_field_one, delta_field_two = self._delta.split("__")
            rank_orders.append(getattr(F(delta_field_one) / F(delta_field_two), self.order_direction)())
        elif self.parameters.get("order_by", default_ordering):
            rank_orders.append(getattr(F(self.order_field), self.order_direction)())
        if tag_column in rank_field:
            rank_orders.append(self.get_tag_order_by(rank_field))
        else:
            rank_orders.append(getattr(F(rank_field), self.order_direction)())

        return Window(expression=RowNumber(), partition_by=F("date"), order_by=rank_orders) 

def annotations(self):
        """Create dictionary for query annotations.

        Returns:
            (Dict): query annotations dictionary

        """
        annotations = {"date": self.date_trunc("usage_start")}
        # { query_param: database_field_name }
        fields = self._mapper.provider_map.get("annotations")
        for q_param, db_field in fields.items():
            annotations[q_param] = F(db_field)
        if (
            "project" in self.parameters.parameters.get("group_by", {})
            or "and:project" in self.parameters.parameters.get("group_by", {})
            or "or:project" in self.parameters.parameters.get("group_by", {})
        ):
            annotations["project"] = F("namespace")

        return annotations 

def test_execute_query_with_wildcard_tag_filter(self):
        """Test that data is filtered to include entries with tag key."""
        url = "?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly"  # noqa: E501
        query_params = self.mocked_query_params(url, AWSTagView)
        handler = AWSTagQueryHandler(query_params)
        tag_keys = handler.get_tag_keys()
        filter_key = tag_keys[0]
        tag_keys = ["tag:" + tag for tag in tag_keys]

        with tenant_context(self.tenant):
            totals = AWSCostEntryLineItemDailySummary.objects.filter(
                usage_start__gte=self.dh.this_month_start
            ).aggregate(**{"cost": Sum(F("unblended_cost") + F("markup_cost"))})

        url = f"?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly&filter[tag:{filter_key}]=*"  # noqa: E501
        query_params = self.mocked_query_params(url, AWSCostView)
        handler = AWSReportQueryHandler(query_params)
        data = handler.execute_query()
        data_totals = data.get("total", {})
        result = data_totals.get("cost", {}).get("total", {}).get("value")
        self.assertEqual(result, totals["cost"]) 

def filter_queryset(self, request, queryset, view):
        ordering = self.get_ordering(request, queryset, view)

        if ordering:
            f_ordering = []
            for o in ordering:
                if not o:
                    continue
                if o[0] == "-":
                    f_ordering.append(F(o[1:]).desc(nulls_last=True))
                else:
                    f_ordering.append(F(o).asc(nulls_last=True))

            return queryset.order_by(*f_ordering)

        return queryset 

def test_populate_markup_cost_no_billsids(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AZURE_REPORT_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.aggregate(markup=Sum(F("pretax_cost") * decimal.Decimal(0.1)))
            expected_markup = expected_markup.get("markup")
            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        self.accessor.populate_markup_cost(0.1, start_date, end_date, None)
        with schema_context(self.schema):
            query = self.accessor._get_db_obj_query(summary_table_name).aggregate(Sum("markup_cost"))
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def handle_rollover(inst: Instrument, new_bar: DailyBar):
    """
    换月处理, 基差=新合约收盘价-旧合约收盘价, 从今日起之前的所有连续合约的OHLC加上基差
    """
    product_code = re.findall('[A-Za-z]+', new_bar.code)[0]
    old_bar = DailyBar.objects.filter(exchange=inst.exchange, code=inst.last_main, time=new_bar.time).first()
    main_bar = MainBar.objects.get(
        exchange=inst.exchange, product_code=product_code, time=new_bar.time)
    if old_bar is None:
        old_close = new_bar.close
    else:
        old_close = old_bar.close
    basis = new_bar.close - old_close
    main_bar.basis = basis
    basis = float(basis)
    main_bar.save(update_fields=['basis'])
    MainBar.objects.filter(exchange=inst.exchange, product_code=product_code, time__lte=new_bar.time).update(
        open=F('open') + basis, high=F('high') + basis,
        low=F('low') + basis, close=F('close') + basis, settlement=F('settlement') + basis) 

def test_populate_markup_cost_no_billsids(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AWS_CUR_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.aggregate(markup=Sum(F("unblended_cost") * decimal.Decimal(0.1)))
            expected_markup = expected_markup.get("markup")

            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        self.accessor.populate_markup_cost(0.1, start_date, end_date, None)
        with schema_context(self.schema):
            query = self.accessor._get_db_obj_query(summary_table_name).aggregate(Sum("markup_cost"))
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def notify_stock_limits(self):
        """
        Notifies the correct parties of inventory items stocking status
        """
        subject = _(u"Products stocked below limit")

        for l in Location.objects.filter(enabled=True):
            out_of_stock = Inventory.objects.filter(
                location=l,
                amount_stocked__lt=F('amount_minimum')
            )

            table = CsvTable()
            table.addheader(['PRODUCT', 'MINIMUM', 'STOCKED'])

            for i in out_of_stock:
                table.addrow([i.product.code, i.amount_minimum, i.amount_stocked])

            if Configuration.notify_location():
                recipient = l.manager.email if l.manager else l.email
                send_table(self.mail_sender, recipient, subject, table)
            if self.mail_recipient:
                send_table(self.mail_sender, self.mail_recipient, subject, table) 

def reindex_all(apps, schema_editor):
    # Remove all indexed txs
    InternalTx = apps.get_model('relay', 'InternalTx')
    InternalTx.objects.all().delete()

    # Start indexing again
    SafeTxStatus = apps.get_model('relay', 'SafeTxStatus')
    SafeTxStatus.objects.update(tx_block_number=F('initial_block_number')) 

def populate_ethereum_tx_foreign_key(apps, schema_editor):
    # We can't import the Person model directly as it may be a newer
    # version than this migration expects. We use the historical version.
    SafeMultisigTx = apps.get_model('relay', 'SafeMultisigTx')
    SafeMultisigTx.objects.update(ethereum_tx=F('tx_hash')) 

def get_last_seen_manifest_ids(self, bill_date):
        """Return a tuple containing the assembly_id of the last seen manifest and a boolean

        The boolean will state whether or not that manifest has been processed."""
        assembly_ids = []
        # The following query uses a window function to rank the manifests for all the providers,
        # and then just pulls out the top ranked (most recent) manifests
        manifests = (
            CostUsageReportManifest.objects.filter(billing_period_start_datetime=bill_date)
            .annotate(
                row_number=Window(
                    expression=RowNumber(),
                    partition_by=F("provider_id"),
                    order_by=F("manifest_creation_datetime").desc(),
                )
            )
            .order_by("row_number")
        )
        for manifest in [manifest for manifest in manifests if manifest.row_number == 1]:
            # loop through the manifests and decide if they have finished processing
            processed = manifest.num_total_files == manifest.num_processed_files
            # if all of the files for the manifest have been processed we don't want to add it
            # to assembly_ids because it is safe to delete
            if not processed:
                assembly_ids.append(manifest.assembly_id)
        return assembly_ids 

def populate_markup_cost(self, markup, start_date, end_date, bill_ids=None):
        """Set markup costs in the database."""
        with schema_context(self.schema):
            if bill_ids and start_date and end_date:
                for bill_id in bill_ids:
                    AzureCostEntryLineItemDailySummary.objects.filter(
                        cost_entry_bill_id=bill_id, usage_start__gte=start_date, usage_start__lte=end_date
                    ).update(markup_cost=(F("pretax_cost") * markup))
            elif bill_ids:
                for bill_id in bill_ids:
                    AzureCostEntryLineItemDailySummary.objects.filter(cost_entry_bill_id=bill_id).update(
                        markup_cost=(F("pretax_cost") * markup)
                    )
            else:
                AzureCostEntryLineItemDailySummary.objects.update(markup_cost=(F("pretax_cost") * markup)) 

def populate_markup_cost(self, markup, start_date, end_date, cluster_id):
        """Set markup cost for OCP including infrastructure cost markup."""
        with schema_context(self.schema):
            OCPUsageLineItemDailySummary.objects.filter(
                cluster_id=cluster_id, usage_start__gte=start_date, usage_start__lte=end_date
            ).update(
                infrastructure_markup_cost=(
                    (Coalesce(F("infrastructure_raw_cost"), Value(0, output_field=DecimalField()))) * markup
                ),
                infrastructure_project_markup_cost=(
                    (Coalesce(F("infrastructure_project_raw_cost"), Value(0, output_field=DecimalField()))) * markup
                ),
            ) 

def populate_markup_cost(self, markup, start_date, end_date, bill_ids=None):
        """Set markup costs in the database."""
        with schema_context(self.schema):
            if bill_ids and start_date and end_date:
                for bill_id in bill_ids:
                    AWSCostEntryLineItemDailySummary.objects.filter(
                        cost_entry_bill_id=bill_id, usage_start__gte=start_date, usage_start__lte=end_date
                    ).update(markup_cost=(F("unblended_cost") * markup))
            elif bill_ids:
                for bill_id in bill_ids:
                    AWSCostEntryLineItemDailySummary.objects.filter(cost_entry_bill_id=bill_id).update(
                        markup_cost=(F("unblended_cost") * markup)
                    )
            else:
                AWSCostEntryLineItemDailySummary.objects.update(markup_cost=(F("unblended_cost") * markup)) 

def test_execute_query_with_tag_group_by(self):
        """Test that data is grouped by tag key."""
        # Pick tags for the same month we query on later
        url = "filter[resolution]=monthly&filter[time_scope_value]=-1&filter[time_scope_units]=month"
        query_params = self.mocked_query_params(url, AzureTagView)
        handler = AzureTagQueryHandler(query_params)
        tag_keys = handler.get_tag_keys()
        group_by_key = tag_keys[0]
        tag_keys = ["tag:" + tag for tag in tag_keys]

        ag_key = "cost_total"
        with tenant_context(self.tenant):
            totals = AzureCostEntryLineItemDailySummary.objects.filter(
                usage_start__gte=self.dh.this_month_start
            ).aggregate(**{ag_key: Sum(F("pretax_cost") + F("markup_cost"))})

        url = f"?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly&group_by[tag:{group_by_key}]=*"  # noqa: E501
        query_params = self.mocked_query_params(url, AzureCostView)
        handler = AzureReportQueryHandler(query_params)

        data = handler.execute_query()
        data_totals = data.get("total", {})
        data = data.get("data", [])
        expected_keys = ["date", group_by_key + "s"]
        for entry in data:
            self.assertEqual(list(entry.keys()), expected_keys)
        result = data_totals.get("cost", {}).get("total")
        self.assertIsNotNone(result)
        self.assertAlmostEqual(result.get("value"), totals[ag_key], 6) 

def forwards(apps, schema_editor):
    MyModel = apps.get_model("test_app", "MyModel")
    MyModel.objects.update(double_number=models.F('number') * 2) 

def test_populate_markup_cost(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AWS_CUR_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        bills = self.accessor.get_cost_entry_bills_query_by_provider(self.aws_provider.uuid)
        with schema_context(self.schema):
            bill_ids = [str(bill.id) for bill in bills.all()]

            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.filter(cost_entry_bill__in=bill_ids).aggregate(
                markup=Sum(F("unblended_cost") * decimal.Decimal(0.1))
            )
            expected_markup = expected_markup.get("markup")

        self.accessor.populate_markup_cost(0.1, start_date, end_date, bill_ids)
        with schema_context(self.schema):
            query = (
                self.accessor._get_db_obj_query(summary_table_name)
                .filter(cost_entry_bill__in=bill_ids)
                .aggregate(Sum("markup_cost"))
            )
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def test_execute_query_ocp_aws_storage_with_wildcard_tag_filter(self):
        """Test that data is filtered to include entries with tag key."""
        with tenant_context(self.tenant):
            labels = (
                OCPAWSCostLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago)
                .filter(product_family__contains="Storage")
                .values(*["tags"])
                .first()
            )
            self.assertIsNotNone(labels)
            tags = labels.get("tags")
            filter_key = list(tags.keys())[0]

            totals = (
                OCPAWSCostLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago)
                .filter(**{"tags__has_key": filter_key})
                .filter(product_family__contains="Storage")
                .aggregate(**{"usage": Sum("usage_amount"), "cost": Sum(F("unblended_cost") + F("markup_cost"))})
            )

        url = reverse("reports-openshift-aws-storage")
        client = APIClient()
        params = {f"filter[tag:{filter_key}]": "*"}

        url = url + "?" + urlencode(params, quote_via=quote_plus)
        response = client.get(url, **self.headers)
        self.assertEqual(response.status_code, status.HTTP_200_OK)

        data = response.json()
        data_totals = data.get("meta", {}).get("total", {})
        for key in totals:
            expected = float(totals[key])
            if key == "cost":
                result = data_totals.get(key, {}).get("total").get("value")
            else:
                result = data_totals.get(key, {}).get("value")
            self.assertEqual(result, expected) 

def test_execute_query_ocp_aws_storage_with_tag_filter(self):
        """Test that data is filtered by tag key."""
        with tenant_context(self.tenant):
            labels = (
                OCPAWSCostLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago)
                .filter(product_family__contains="Storage")
                .values(*["tags"])
                .first()
            )
            self.assertIsNotNone(labels)
            tags = labels.get("tags")
            filter_key = list(tags.keys())[0]
            filter_value = tags.get(filter_key)

            totals = (
                OCPAWSCostLineItemDailySummary.objects.filter(usage_start__gte=self.ten_days_ago)
                .filter(**{f"tags__{filter_key}": filter_value})
                .filter(product_family__contains="Storage")
                .aggregate(**{"usage": Sum("usage_amount"), "cost": Sum(F("unblended_cost") + F("markup_cost"))})
            )

        url = reverse("reports-openshift-aws-storage")
        client = APIClient()
        params = {f"filter[tag:{filter_key}]": filter_value}

        url = url + "?" + urlencode(params, quote_via=quote_plus)
        response = client.get(url, **self.headers)
        self.assertEqual(response.status_code, status.HTTP_200_OK)

        data = response.json()
        data_totals = data.get("meta", {}).get("total", {})
        for key in totals:
            expected = float(totals[key])
            if key == "cost":
                result = data_totals.get(key, {}).get("total").get("value")
            else:
                result = data_totals.get(key, {}).get("value")
            self.assertEqual(result, expected) 

def calculate_total(self, **units):
        """Calculate aggregated totals for the query.

        Args:
            units (dict): The units dictionary

        Returns:
            (dict) The aggregated totals for the query

        """
        query_group_by = ["date"] + self._get_group_by()
        query = self.query_table.objects.filter(self.query_filter)
        query_data = query.annotate(**self.annotations)
        query_data = query_data.values(*query_group_by)

        aggregates = copy.deepcopy(self._mapper.report_type_map.get("aggregates", {}))
        if not self.parameters.parameters.get("compute_count"):
            # Query parameter indicates count should be removed from DB queries
            aggregates.pop("count", None)

        counts = None

        if "count" in aggregates:
            resource_ids = (
                query_data.annotate(resource_id=Func(F("resource_ids"), function="unnest"))
                .values_list("resource_id", flat=True)
                .distinct()
            )
            counts = len(resource_ids)

        total_query = query.aggregate(**aggregates)
        for unit_key, unit_value in units.items():
            total_query[unit_key] = unit_value

        if counts:
            total_query["count"] = counts
        self._pack_data_object(total_query, **self._mapper.PACK_DEFINITIONS)

        return total_query 

def annotations(self):
        """Create dictionary for query annotations.

        Returns:
            (Dict): query annotations dictionary

        """
        units_fallback = self._mapper.report_type_map.get("cost_units_fallback")
        annotations = {
            "date": self.date_trunc("usage_start"),
            "cost_units": Coalesce(self._mapper.cost_units_key, Value(units_fallback)),
        }
        if self._mapper.usage_units_key:
            units_fallback = self._mapper.report_type_map.get("usage_units_fallback")
            annotations["usage_units"] = Coalesce(self._mapper.usage_units_key, Value(units_fallback))
        # { query_param: database_field_name }
        fields = self._mapper.provider_map.get("annotations")
        prefix_removed_parameters_list = list(
            map(
                lambda x: x if ":" not in x else x.split(":", maxsplit=1)[1],
                self.parameters.get("group_by", {}).keys(),
            )
        )
        for q_param, db_field in fields.items():
            if q_param in prefix_removed_parameters_list:
                annotations[q_param] = F(db_field)
        return annotations 

def _create_accounts_mapping(self):
        """Returns a mapping of org ids to accounts."""
        account_mapping = {}
        with tenant_context(self.tenant):
            for source in self.data_sources:
                # Grab columns for this query
                account_info = source.get("account_alias_column")
                # Create filters & Query
                filters = QueryFilterCollection()
                no_org_units = QueryFilter(field=f"{account_info}", operation="isnull", parameter=False)
                filters.add(no_org_units)
                composed_filters = filters.compose()
                account_query = source.get("db_table").objects
                account_query = account_query.filter(composed_filters)
                account_query = account_query.exclude(deleted_timestamp__lte=self.start_datetime)
                account_query = account_query.exclude(created_timestamp__gt=self.end_datetime)
                if self.access:
                    accounts_to_filter = self.access.get("aws.account", {}).get("read", [])
                    if accounts_to_filter and "*" not in accounts_to_filter:
                        account_query = account_query.filter(account_alias__account_id__in=accounts_to_filter)
                account_query = account_query.order_by(f"{account_info}", "-created_timestamp")
                account_query = account_query.distinct(f"{account_info}")
                account_query = account_query.annotate(
                    alias=Coalesce(F(f"{account_info}__account_alias"), F(f"{account_info}__account_id"))
                )
                for account in account_query:
                    org_id = account.org_unit_id
                    alias = account.alias
                    if account_mapping.get(org_id):
                        account_list = account_mapping[org_id]
                        account_list.append(alias)
                        account_mapping[org_id] = account_list
                    else:
                        account_mapping[org_id] = [alias]
        return account_mapping 

def migrate_data(apps, schema_editor):
    RRset = apps.get_model('desecapi', 'RRset')
    RRset.objects.filter(touched__isnull=True).update(touched=models.F('created')) 

def forwards(self, orm):
        MyModel = orm.MyModel
        MyModel.objects.update(double_number=models.F('number') * 2) 

def forwards(apps, schema_editor):
    MyModel = apps.get_model("test_app", "MyModel")
    MyModel.objects.update(double_number=models.F('number') * 2) 

def increase_usage(self, bytes):
        # increase usage for this user based on document size
        storage_qs = UserStorage.objects.filter(pk=self.request.user.storage.pk)
        storage_qs.update(bytes_used=F("bytes_used") + bytes) 

def forwards(apps, schema_editor):
    MyModel = apps.get_model("test_app", "MyModel")
    MyModel.objects.update(double_number=models.F('number') * 2) 

def forwards(self, orm):
        MyModel = orm.MyModel
        MyModel.objects.update(double_number=models.F('number') * 2)